Selecting Max from grouped data (1 Viewer)

[emz]

Hi,

I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.

So the data looks like this:
Maths 54
Maths 69
Maths 41
English 71
English 55
Art 44
Art 43
Art 66

I would like to write a query that selects only the highest score for each sublect and presents it like this:

Maths 69
English 71
Art 66

Please can someone help me do this?

Thank-you in advance.

 

[MarkK]

Hello and welcome to the forum

What have you tried and how has that failed? It's useful, in helping you, to know where you got stuck.

 

[emz]

I have just been using the query wizard and have put the two fields I am interested to into the query fields - when I run this it selects the two queries with no problems.

I have then tried typing Max in the scores field to pull up the maximum scores, when I do this I get an error message that says "Data mismatch in query or expression"

 

[MarkK]

when I run this it selects the two queries with no problems.

Do you mean, selects the two fields with no problems?

One thing you need to do is click the Summation symbol on the ribbon. Do you have a ribbon? It looks like a big letter E, and says, "Totals". This will add a new line in the query design grid with a combobox of options. Then you can select Max and Group By, and others, in that row.

 

[emz]

Brilliant - thank-you so much!