Software

A couple of options, if I'm picturing this correctly. To get the score and specific test to come up, try using Where, with a criteria that you know will always be true, something like <>0 or is not null.

Other option is to have three queries. Two that give me the 'single line' returns, one that unites those two and displays them all together. This way I can make changes to one set without messing up the returns for the other.

Sample data

ID TestType Test TestDate Score
5 Office Excel 6/1/06 68
5 Office Word 6/1/06 95
5 Office Excel 7/30/06 60

The most recent test score for each type of test is the one I need to report. So I would need it to find the Word test for this person on 6/1/06 and the Excel test for 7/30/06. Along with the same type of information for the other people who tested possibly on other tests and other test dates.
Right now, I'm doing it with one query, grouping by ID, Test Type, Test, and finding the MAX date, and then relating that to the original table by the grouped fields and test date --> max test date, to pull out the scores, but every extra time I run against the data (not local), it adds significant time. I haven't been able to find Where criteria that will isolate it correctly. Is there a way to do it with dlookup functions or conditional statements? It seems like there should be a way to have the data inspected to find the max test date per person and test and return the related score.

Yep, you'll need at least 2 queries. You can do it in just 2, though. Have one that has ID, TestType, Test, TestDate. Grouping on, Group By for everything but Date, which should be set to Max. Then, create a second query. Add the original table, and the first query. Link the table and the query on all fields. Select all fields from the Table, including Score. Running it this way returns, using your sampel data, 2 records:

ID TestType Test TestDate Score
5 Office Word 6/1/2006 95
5 Office Excel 7/30/2006 60

If you want the sample I built to confirm it, PM me an email.

Hi
Please try the following query:

SELECT id,test,testtype,max(score) as TestScore,max(testdate) as LastTestDate from table1 where testdate in (select max(testdate) from table1 group by id,testtype,test) group by id,testtype,test

That should retrieve the data rows that you need.

Chandra