I am in need of a way to number a large number of records in MS Access. The numbering needs to restart with each new client. E.g. In the table below, client 56789 is open for 3 days. In the SeqNum column I need the records to read 1, 2, and 3 for the three ServiceDays listed. Same for client 78965, I need their records to be numbered 1,2,3 and 4. How can this be done?

Client ServiceDay NumofDays SeqNum
56789 4/25/2007 3
56789 4/24/2007 3
56789 4/23/2007 3
78965 1/13/2007 4
78965 1/12/2007 4
78965 1/11/2007 4
78965 1/10/2007 4
...

Thank you ahead of time.

Chad

Why you don't want perform it in a report?. That would work the same you need.

Because I will be appending the records to another table that will be used by future queries.

Does anyone know how to do this in a query? or using code?

You could do this quite easily in code. For data that is already there, you will need some other parameter to sequence the records, unless you just want to take, say 4 records for a customer for a day, and randomly number them 1, 2, 3, 4.

If you add this number each time a record is created (on the form's Before Insert event) then it will go smoothly.

How do you want to proceed?


mmcdonal