Top Value Query help

[mpreston14]

Hello,

I have a query I use to calculate the difficulty of a Football (Soccer) referee's match, it generates the value as I need it to based on our criteria. I have a query that provides the Difficulty in descending order, but it is for every match for every referee.

What I really need though is a query that will list for me each referee's most difficult match as a "Referee" and as an "Assistant Referee".

This is basically what I want:

LastName FirstName League Match Position GameDate FinalSemiFinal Difficulty
Smith John XXXX BU15 Referee 05/17/2013 No 375
Smith John YYYY BU17 AR 06/04/2013 No 200
Jones Jennifer AAAA GU19 Referee 07/13/13 No 490
Jones Jennifer BBBB BU16 AR 07/05/13 Final 600


League, Match, Position, FinalSemiFinal are all tables. I have a Referee Table that has the demographic info in it. I import the actual match data, run it through some scrubbing queries to limit it to my states referee's only and all of the data compiles into an Officiated Matches Table where it is stored. I use the Officiate Matches Table to pull any list type data I need to get out of the system.



Is this even a Top Value Query? I don't really need the top 10 total or top 5% just the top match for each "Position" by Referee.

Is there a simple way to do this? I am SQL "limited" as in I can read most of it, but I can't really create my own unless you count copy/paste and edit.

As always, any help is greatly appreciated.

Mike

[mpreston14]

Not sure why the spacing didn't work out well in the above, sorry...

[offie]

The easiest way would be to use more than one query. It's possible in one but it gets more complicated.

Make a query that find the most difficult game per R and AR.
If you include firstname lastname the R/AR field, difficults, and something elese that you can group by and you can use to link to other tables, group by all exepct difficulty, make difficulty max

Then go back in and use this query to add the rest of the information

[mpreston14]

Sorry so long in reply, but I managed to build multiple queries and tables to support what I needed like you said Offie, then a final query to pull the list I need. Thanks!