Records with highest value per group on report

[starbest]

My report shows test grades for each member. There are two tests: 1. Math 2. Reading. A member may have several grades. I only want to include on the report, the highest grade for math and the highest grade for reading. Other times, however, there will be more than one record per subject and member, but the same grade with a different score.

Report based on a Select Query. Grouped based on member number.

Report looks like this:

MEMBERNUMBER GRADE SCORE TEST

32621 6 231 Reading
8 237 Reading
10 321 Math
9 312 Math

23157 5 219 Reading
5 216 Reading
7 235 Math
7 234 Math

How do I get the report to show only one grade and the highest grade per member, per subject.

 

[Remou]

Have you considered a query using Max?

 

[dhookom]

Group your report on:
MemberNumber
Test Group Header
Grade Descending
Place the text boxes in the Test Group Header section and hide your detail section.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[starbest]

Brilliant. Thank you. The only problem is that for some reason I am not able to group on TEST. It is not showing in the list to group on when using the report wizard. When I force a grouping through the 'Sorting/Grouping' method on the drop down menu, it still will not group on the report by TEST.

 

[dhookom]

The only reason that I can think of that you couldn't group on a field is if the field is a memo field. This would not make any sense in your situation.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]