assigning values to dates

T

Tara

I posted a message on this several weeks ago and got a
reply from Tom (thanks by the way), however I didn't
completely understand it and am still having problems so
here it goes again. I have a query that pulls test data
from a table called "tblChildTests". Each child in this
table can have several instances of several different
tests. For example, Child 1 can have a Lit test on
01/01/2004 and another Lit tests on 01/29/2004. He can
also have a Readers test on 01/01/2004 another on
02/10/2004, etc. What I need is to assign each of these
tests a value based on the test date, so...Child 1's Lit
test on 01/01/2004 is 1, his Lit test on 01/29/2004 is 2,
Readers test on 01/01/2004 is 1, etc. I know this can be
done much easier in a report, but I need to do it in the
query because I then have to calculate due dates for
future tests based on these numbers.
 
R

Roger Carlson

Perhaps a sample might help. On my website is a small sample database
called "NumberedQuery", which might help you to understand it.
 
C

Chris2

Tara said:
I posted a message on this several weeks ago and got a
reply from Tom (thanks by the way), however I didn't
completely understand it and am still having problems so
here it goes again. I have a query that pulls test data
from a table called "tblChildTests". Each child in this
table can have several instances of several different
tests. For example, Child 1 can have a Lit test on
01/01/2004 and another Lit tests on 01/29/2004. He can
also have a Readers test on 01/01/2004 another on
02/10/2004, etc. What I need is to assign each of these
tests a value based on the test date, so...Child 1's Lit
test on 01/01/2004 is 1, his Lit test on 01/29/2004 is 2,
Readers test on 01/01/2004 is 1, etc. I know this can be
done much easier in a report, but I need to do it in the
query because I then have to calculate due dates for
future tests based on these numbers.
Click to expand...


CREATE TABLE tblChildren
(ChildID Integer
,CONSTRAINT pk_tblChildren PRIMARY KEY (ChildID))

CREATE TABLE tblTests
(TestID Integer
,CONSTRAINT pk_tblChildren PRIMARY KEY (TestID))

CREATE TABLE tblChildTests
(ChildID Integer
,TestID Integer
,TestDate Date
,TestScore Integer
,CONSTRAINT pk_tblChildTests PRIMARY KEY (ChildID, TestID, TestDate)
,CONSTRAINT fk_tblChildren FOREIGN KEY (ChildID)
REFERENCES tblChildren (ChildID)
,CONSTRAINT fk_tblTests FOREIGN KEY (TestID)
REFERENCES tblTests (TestID)
)

The following table structures allow for everything necessary to do what
is described above.

Of course, this setup doesn't allow for more than one of the same test on
the same day. If you include a time, as well, like TestTime, and make it a
part of the Primary Key of tblChildTests, that will allow for any number of
the same kind of test on the same day (as long as the same time isn't used
for each score on the same test, so the rows must be INSERTed at different
times).
 
T

Tara

Roger, Thanks for the help! (I learn much better by
actually seeing how something works...)This gets me
headed in the right direction, but still not getting
quite what I need. By following the example on your
website, I was able to get it rank each test by date, but
not by individual child. For example Child 1 had a lit
test on 01/05/2004 and one on 02/02/2004. His test
numbers are 3 and 57, rather than 1 and 2, because other
children had tests entered in between those dates. I
need it to rank each test by child, and by type of test
as well. I know it should be simple now that I have the
basic ranking thing out of the way, but I just can seem
to get it!! Any more help would be greatly appreciated
 
R

Roger Carlson

Then you need to do the DCount (or the subquery) on the Date field, rather
than the child field. In my sample, look at the difference between the
query for AUID and AuName.
 
Top