Date + ID as Primary key?

[Fear No Evil]

Hi, I am a sales rep and am trying to track Demonstrations, Expenses, Miles,
and Receipts.
Without recreating all four tables, the thing they all have in common is a
Date field. Of course I can enter many separate records per day so the date
alone can't be a primary key. Currently I am using an autonumber record Id
and date as primary key for each. But, I cannot seem to create a set of
relationships that will allow me to pull a daily rollup that will show the
results of each day. IOW The query pulls zero records. Obviously I have
some form of relationship problem and I think its related to the key
assignments. Any ideas? I used to extensively work with access in the
service but am really rusty. Thanks in advance for pointers.
Phil

 

[Armen Stein]

Hi, I am a sales rep and am trying to track Demonstrations, Expenses, Miles,
and Receipts.
Without recreating all four tables, the thing they all have in common is a
Date field. Of course I can enter many separate records per day so the date
alone can't be a primary key. Currently I am using an autonumber record Id
and date as primary key for each. But, I cannot seem to create a set of
relationships that will allow me to pull a daily rollup that will show the
results of each day. IOW The query pulls zero records. Obviously I have
some form of relationship problem and I think its related to the key
assignments. Any ideas? I used to extensively work with access in the
service but am really rusty. Thanks in advance for pointers.
Phil

Hi Phil,

Since an AutoNumber is unique, you can use it alone as a Primary Key.
Then your Date becomes just another data field. Use your primary keys
to join all your tables.

Your dates should roll up together in queries. Make sure you aren't
also including a time component in your dates. This would happen if you
used Now() instead of Date() to set your date values.

Hope this helps,
--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

 

[Craig Alexander Morrison]

You should note that the Date field is in fact a Date/Time field.

If you are setting the value of the field using Date() use Now() instead and
you will
record the date and the time down to the second.

If you are recording no more than one record a second this should do the job
for you.

 

[Craig Alexander Morrison]

Further,

When querying your data be sure to evaluate the field as a date to show all
values with that date value, irrespective of the time.

As pointed out in another answer you may in fact be recording the date and
time and then looking only for the date. If you do this you will only get
back records that were recorded at midnight (time 00:00:00).

 

[Fear No Evil]

I sent a Thanks out for this over a week ago. But the NG didn't pick it up.
Thanks to all. The date format *was* set to now(). So we got that fixed.

Phil

Top's Blog