Daily Data for multiple units

[Nick M via AccessMonster.com]

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

 

[Duane Hookom]

I don't see a date field.

 

[Nick M via AccessMonster.com]

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.

 

[Haris Rashid]

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

 

[Robert Morley]

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

 

[Robert Morley]

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

 

[Nick M via AccessMonster.com]

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.

 

[Nick M via AccessMonster.com]

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?