creating test numbers

[Tara]

I need some help with a query in which I need to group by
and then count the numbers within each group, then assign
each record a number. For example, I have a child who
has taken a Home Literacy test three times. I need to
assign the test that occured on 10/01/03 the number 1,
the test he took on 11/01/03 the number 2, etc. I have
another child who has taken the Home Literacy test twice
and the Creating Readers test three times. Again, I need
to number these tests as (Home Literacy)1, 2, (Creating
Readers)1, 2, 3 according to date. Test numbers are not
in the table so I need to create it within the query.
Any ideas? Hope this is enough info! Thanks in advance
for any help!!!

 

[Trias]

Hi Tara,

i think i know the answer. However, i have trouble in explain it to you. Therefore i'll write down every info about my test db so that you can recreate it and see if it makes sense to you. I create 3 tables, Child, Test, Date_CT. The table properties are:
Child: CID (autonumber), Name (Text)
Test: TestID (autonumber), TestName (Text)
Date_Ct: CID, TestID, DateTest (date/time), Primary Key is (CID, TestId, DateTest).

The table relationship are: Child to Date_CT is one to many, Test to Date_CT is one to many.
Then, i create 1 query, adding table Date_CT twice (2x) as recordsource, so that in the query design window i have table Date_CT and table Date_CT_1.

My query SQL looks like this:
SELECT DATE_CT.CID, DATE_CT.TestID, DATE_CT.DateTest, Count(DATE_CT_1.DateTest) AS TestNumber
FROM DATE_CT, DATE_CT AS DATE_CT_1
WHERE (((DATE_CT.DateTest)>=[date_ct_1].[DateTest]) AND ((DATE_CT_1.CID)=[date_ct].[cid]) AND ((DATE_CT_1.TestID)=[date_ct].[testid]))
GROUP BY DATE_CT.CID, DATE_CT.TestID, DATE_CT.DateTest;

The result i got looks like this:
Name TestNAme DateTest TestNumber
A T1 1/1/2003 1
A T1 1/2/2003 2
A T1 1/3/2003 3
B T1 1/1/2003 1
B T1 1/2/2003 2
B T2 2/1/2003 1
B T2 2/2/2003 2
B T2 2/3/2003 3
C T1 2/1/2003 1
C T2 1/1/2003 1


HTH
----- Tara wrote: -----

I need some help with a query in which I need to group by
and then count the numbers within each group, then assign
each record a number. For example, I have a child who
has taken a Home Literacy test three times. I need to
assign the test that occured on 10/01/03 the number 1,
the test he took on 11/01/03 the number 2, etc. I have
another child who has taken the Home Literacy test twice
and the Creating Readers test three times. Again, I need
to number these tests as (Home Literacy)1, 2, (Creating
Readers)1, 2, 3 according to date. Test numbers are not
in the table so I need to create it within the query.
Any ideas? Hope this is enough info! Thanks in advance
for any help!!!