Adding Number Count to Imported Table

[Janis]

I have an imported table that contains multiple student records with multiple
transaction dates for each student. I want to add another field that assigns
a sequence number by student. For instance, I have studentID=123 with
transaction dates of 1/2/07, 3/5/07, 7/7/07. I want the SeqNumber field to
be 1 for the 1/2/07 date, 2 for the 3/5/07 date and 3 for the 7/7/07 date.
The SeqNumber field should start over at 1 for each student in this record
set and increment from there based on the transaction date.

Thanks in advance!

 

[Duane Hookom]

You could create the sequence number dynamically in a query without actually
storing it. However, if you want to store it, you could use DCount() in an
update query:
UPDATE tblImport
SET SeqNumber = DCount("*","tblImport","StudentID = " & StudentID & " AND
TransDate <=#" & TransDate & "#")

 

[Janis]

It worked! Thank you so much!

You could create the sequence number dynamically in a query without actually
storing it. However, if you want to store it, you could use DCount() in an
update query:
UPDATE tblImport
SET SeqNumber = DCount("*","tblImport","StudentID = " & StudentID & " AND
TransDate <=#" & TransDate & "#")