Report based on date

[signallt03]

I have a table that saves test scores based on SSN and Date. I want to generate a report that gives the last test score for each person

 

[cheiboy]

In your query, try making the SSN field a part of it and in the criteria type in the following:

[Enter Provider ID]

And for the next field, use the test scores and show them in decending order.

 

[mpastore]

Create a query with the following SQL:

SELECT ssn,test_date,score from tbTest a
WHERE test_date IN (select max(test_date) from tbTest where ssn = a.ssn);

This query assumes a table name of TbTest and a test date field of test_date

The query will show latest test date and scroe for each person

 

[signallt03]

yes, but what if one person failed to take it on the last date. How would i show there last completed test.

 

[cheiboy]

Correction to my posting, you'll need the date also.

 

[cheiboy]

[Enter Provider ID]should have been [Enter SSN]. Sorry

 

[signallt03]

mpastore-

Thanks for the help
It worked great!!!!