Selecting members with highest score (1 Viewer)

[Brian Butters]

I have a query with 3 fields MemberID,CategoryID, ScoreID whereby a number of members have scored from 1-5 in several different categories. I want to select only those records where the members have the highest score in each category. Where 2 or more members have the high score,I then want to select only one member (say with lowest ID)for each category with the high score. Can anyone help please - I suspect DISTINCT is involved somewhere but can't think how the max score is derived.

 

[SMG]

You could create a select query from the table and then set the criteria for each field to 5. That will at least show all records where the top score was 5 in all categories.

 

[Brian Butters]

Thanks but I already know how to do that. I suspect that there are no straightforward SQL statements or wizards and its a VBA code route.

 

[KKilfoil]

You can do this with a few queries. The first one is a summary query that groups by CategoryID and totals with Max for the ScoreID. The result of this is a list of the Maximum score for each category.

The second query would include as source your original table and the above table, with relationships between MaxScoreID and ScoreID and the CategoryID's in each table, . Group by Category, and use Min for MemberID to find the lowest ID number (from a set of data that includes only those records with the maxium score in each category).

[This message has been edited by KKilfoil (edited 05-10-2002).]

 

[Brian Butters]

Thanks, that worked