Update table with record sequence numbers

[Paul]

I need to update a table with a sequence number for the records. The
sequence number should be primarily based on the ID field, then followed by
the DATE and TIME fields, but should continue a sequential numbering sequence
whether the DATE passes to another day. See the example below. The SEQ
field would be the new field:

ID DATE TIME SEQ
01 10/1/05 8:00AM 1
01 10/2/05 10:30AM 2
02 10/4/05 1:00PM 1
03 10/3/05 9:00PM 1
03 10/4/05 7:00AM 2
03 10/4/05 2:00PM 3

Thanks for any help!

 

[S.Clark [Access MVP]]

You may be able to do this with a subquery, but I just do it with VBA.

Steve Clark, Access MVP
fmsinc.com/consulting

 

[John Spencer]

UPDATE YourTable
SET SEQ = DCount("*","YourTable","ID='" & YourTable.ID &"' AND [Date]<=#" &
YourTable.[Date] &"#")

This assumes that ID is a text field (which I assume based on ID have
leading zeroes.)