Software

Ask something
Discussion Locked

Access Database Query Problem

0 helpful
4 replies

by comp_instructor

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
In a query with multiple groupings, Access seems to lose specificity. Specifically, table structure - ID, Test Type, Test, Test Date, and Score. Group by ID, Test Type, Test, and Max(Test Date) in a totals query, but can't get score for that date and specific test in the same query. I've tried aggregate functions too, but constantly find myself getting error messages. I can do it as two queries, but it would greatly simplify my life if there was a way to do it all in one.

4 replies

by maevinn

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.

by comp_instructor

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.

by maevinn

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.

by chandraram

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

This discussion is closed to new replies.