How to count records of the same individual?

[smucha]

Hello,

I am working on a database with the following structure:

ID trait1 trait2
1 899 789
1 600 700
1 500 560
2 430 560
2 600 700
2 800 230
2 500 650
...

So I have multiple measurements per each individual.
I would like to add a column numbering observations for each individual.
I would like it to look like that:

measurement ID trait1 trait2
1 1 899 789
2 1 600 700
3 1 500 560
1 2 430 560
2 2 600 700
3 2 800 230
4 2 500 650
...

Is that possible in Access?
I will be very grateful for your help.

[liimra]

Yes it is possible.

First, create another field in your table to number your records. Call it SeqID

SeqID - ID -- trait1 -- trait2
1------- 1 -----899 ----- 789
2------- 1 -----600 ----- 700
3------- 1 -----500 ----- 560
4------- 2 -----430 ----- 560
5------- 2 -----600 ----- 700
6------- 2 -----800 ----- 230
7------- 2------500 ----- 650

Now you create a query that will do what require:

SELECT TableName.SeqID, TableName.ID, DCount("[ID]","ThisQueryName","[ID] = '" & [ID] & "'")-DCount("[ID]","ThisQueryName","[ID] = '" & [ID] & "' AND [SeqID] > " & [SeqID]) AS RunningCount
FROM TableName
GROUP BY ThisQueryName.SeqID, ThisQueryName.ID
ORDER BY ThisQueryName.SeqID;

Of course, you can add all the fields you want to this query.

Regards,
Ali