Running count within group

[foxtrot123]

I have data like so:

SubjectID  VisitID 
1    5  
1    5
2    7
2    7
2    9
2    9
etc

And need to return a running count for the number of visits per subject, so:

SubjectID  VisitID  VisitCount
1    5   1
1    5   1
2    7   1
2    7   1
2    9   2
2    9   2
etc.

I'd like to use DCount and have tried all variations of the following, but none of them returns the correct number:

VisitNumber: DCount("*","mytable","VisitID >= " & [VisitID] & " AND SubjectID =" & [SubjectID])

Suggestions?

 

[plog]

The problem is you have duplicate data. You're on the right track, but you can't use mytable as the datasource for your Dcount. you need to create a query to make all the permutations in mytable unique:

SELECT SubjectID, VisitID FROM mytable GROUP BY SubjectID, VisitID

Save that query, then use it to do your Dcount.

 

[foxtrot123]

The problem is you have duplicate data. You're on the right track, but you can't use mytable as the datasource for your Dcount. you need to create a query to make all the permutations in mytable unique:

SELECT SubjectID, VisitID FROM mytable GROUP BY SubjectID, VisitID

Save that query, then use it to do your Dcount.

Hmm ... each row has several variables that make each row unique. I left them out just to save space.

It's typical normalized data, so something like:

SubjectID  VisitID  Question Response
1          5        Fever   Yes
1          5        Age     12
2          7        Fever   No
2          7        Age     18
2          9        Fever   Yes
2          9        Age     18
etc

 

[plog]

My advice is the same--You need a query to get all the unique SubjectID/VisitID permutations.

 

[foxtrot123]

The problem is you have duplicate data. You're on the right track, but you can't use mytable as the datasource for your Dcount. you need to create a query to make all the permutations in mytable unique:

SELECT SubjectID, VisitID FROM mytable GROUP BY SubjectID, VisitID

Save that query, then use it to do your Dcount.

Thank you. This did the trick.