Increment a count based on test date order in a query 1

[dbfNeophyte]

I need to Increment a count based on the test date (CLTESTDATE) for each Test Type Code (CLTESTTYPECD) with the oldest date being 1, the next oldest 2 and so on. The count for each person tested should never exceed 6 or 7, also the count needs to populate to a new field in the query. I will combine the Test Type Code and the Count together for later use in other queries and reports and I know how to do that, its the incrementation of the numbers that looses me. Here is the SQL from my current query. SELECT [*LitNum].NAME, [*LitNum].CLTESTDATE, [*LitNum].CLTESTSCORE, [*LitNum].CLTESTTYPECD
FROM [*LitNum];
Thanks, Virgil

 

[PHV]

Search keywords: ranking query

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbfNeophyte]

Hi PH,
Yes, having the correct wording for the search made all the difference. Here is what I have curretnly SELECT A.NAME, A.CLTESTTYPECD, A.CLTESTDATE, (SELECT Count(*) FROM tblNUMLitTesting WHERE NAME =A.NAME and CLTESTDATE<=A.CLTESTDATE)-1 AS RANK
FROM tblNUMLitTesting AS A
GROUP BY A.NAME, A.CLTESTTYPECD, A.CLTESTDATE;
This seems to work as expected in all but one case.
NAME CLTESTTYPECD CLTESTDATE RANK
DUSTIN, BUDDY 1 9/22/2006 1
DUSTIN, BUDDY 1 6/28/2007 2
DUSTIN, BUDDY 2 9/22/2006 1
DUSTIN, BUDDY 2 8/22/2007 3
DUSTIN, BUDDY 2 9/30/2007 4
I think the reason is 2 has been used in the second row. Is there a way to correct that to a count of 1,2,3 for "CLTESTTYPECD 2" rather than 1,3,4? Thanks for your suggestion it was a great help. Virgil

 

[PHV]

SELECT Count(*) FROM tblNUMLitTesting WHERE NAME=A.NAME And CLTESTTYPECD=A.CLTESTTYPECD And CLTESTDATE<=A.CLTESTDATE

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbfNeophyte]

Works perfectly! Thanks, Virgil