Assigning a value to a date

G

Guest

I need help with a query which pulls test data from
several families. What I need is to assign each of these
tests a value based on date. For example, Family A had a
test on 01/01/2004 and a test on 02/01/2004. The test on
01/01/2004 needs to be assigned the value of 1, the test
on 02/01/2004 a value of 2, etc. Family B had a test on
01/22/2004 and a test on 02/01/2004. Again, the test on
01/22/2004 needs a value of 1, 02/01/2003 a value of 2,
etc, etc. Any ideas?
 
T

Tom Ellison

Dear ??:

What you describe is a typical Ranking query. This is another form of
correlated subquery.

I'll assume a table TestData with columns FamilyName and TestDate.

SELECT FamilyName, TestDate,
(SELECT COUNT(*) + 1 FROM TestData T1
WHERE T1.FamilyName = T.FamilyName
AND T1.TestDate < T.TestDate)
FROM TestData T
ORDER BY FamilyName, TestDate

There are ways of implementing this in a report that are more
efficient than can be done in a query. If the above is slow, you may
want to consider this. The above would also benefit by an index on
TestData on the columns FamilyName and TestDate (in that order).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Top