Update table with record sequence numbers

P

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

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
 
J

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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top