 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / April 2005Daily Data for multiple units
I need to set up a table/query system that will allow me to enter daily information about several different units. Example: unit data info etc.. 1 .... ... .. 2 .... ... .. I am trying to figuure out the proper method of setting up tables so that I will be able to do queries by unit # and also by date. I've tried a few different layouts and have not found one that will allow me to effectively run queries on the data. Thanks, Nick M I don't see a date field.  SignatureDuane Hookom MS Access MVP >I need to set up a table/query system that will allow me to enter daily > information about several different units. [quoted text clipped - 14 lines] > > Nick M I actually need to be able to do this for every day. My first thought was to add the date to each record, but then I would have to type the unit number every time. The end goal is that I create a form with the date as a header, and the body has the listing of all the units and the corresponding information (mostly in check box form). My biggest problem seems to be setting up the tables and queries in such a way that I can standardize the form. If you want to be able to query by Unit Number or by Date, you'll almost certainly have to have both fields in your table, unless there's a guaranteed method of converting one to the other (i.e., if you produce 100 units a day without fail, no holidays, no nothing, you may be able to get away with doing the conversion mathematically...probably not a good idea, though). In terms of typing the Unit Number, I'm not sure what you mean...if you run a query on a specific date, it'll return all Unit Numbers from that date; if you run a query on Unit Numbers between X and Y, it'll return the dates for each of those Unit Numbers. I'd suggest creating a drop-down box at the top of your form that allows the person to select the dates (i.e. "SELECT DISTINCT UnitDate FROM MyUnitsTable ORDER BY UnitDate DESC"), then in the AfterUpdate event for the combo box, you can simply reset the form's Filter property to the value of the box (and don't forget to change the FilterOn property to True). Rob >I actually need to be able to do this for every day. My first thought was > to add the date to each record, but then I would have to type the unit [quoted text clipped - 4 lines] > (mostly in check box form). My biggest problem seems to be setting up the > tables and queries in such a way that I can standardize the form. I actually will have the same units every single day. They are actually data collection units and I need to track daily if they collected new data, if it was good data, and any additional comments. I think the overall problem is the hierarchy I am trying to set up. The form I have in mind is on where each day the user can start a new record by typing the date. Once the new date has been established, the list of units with checkboxes for the above information is displayed and the user can quickly check all the units that apply. Essentially I am identifying my data first by the date, and second by the unit number. At this point it seem that developing the form is a bigger problem than the queries. Oh, if you're worried about typing the Unit Number or Date into the table, you may want to look at AutoNumber fields for the Unit Number, if that would be appropriate, and change the Default Value of the UnitDate field to read = Date(), and that should probably do the trick, as long as you're generally entering the Unit Numbers on the same date as they're created. Rob >I actually need to be able to do this for every day. My first thought was > to add the date to each record, but then I would have to type the unit [quoted text clipped - 4 lines] > (mostly in check box form). My biggest problem seems to be setting up the > tables and queries in such a way that I can standardize the form. This may be a little closer, but I'm still not seeing how to keep my units constant. On any given day, there will always be an entry (good or bad) for every single unit. The date is the only factor that will always change. It seems that from all suggestions I am going to be able to run good queries, but the form is still not functioning as needed. Maybe this is a better question in the form discussion group? hi Nick, You can create your table with unit number as the primary key and add a date field in the table. On a daily basis you will enter the data (in other fields) along with the date and unit number. In queries use the unit number or date or both. When using date in your query remember that by default date includes both date and time. Compare only the date in your query. You can get the date part in your query to return records matched by date only. When creating a query, you can specify one or more criteria to retrieve only a specific set of records. For example, the units for 2005 query can include the criterion Between #1/1/2005# And #12/31/2005#, to include only the units data for year 2005. If you want some flexibility with the criteria, create a parameter query. A parameter query lets you specify the criteria just before the query is run. Following is a article on Microsoft site about dates and times. http://office.microsoft.com/en-us/assistance/HA010546621033.aspx ------------- Haris Rashid http://www.manage-systems.com -------------------------------------- > I need to set up a table/query system that will allow me to enter daily > information about several different units. [quoted text clipped - 13 lines] > > Nick M |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.