Ranking, sorting, distinct records; help please!

[ACTAngler]

Hi, new to the forum, I have a fishing competition database that I am automating. We have different divisions that people can enter fish in (based on length) and I need to:

- rank the entries in each division;
- assign club points to the top 3 placings in each division;
- only allow 1 entry per person per division (for some divisions), or 1 entry per species per person per division (for other divisions).

Ive got everything working except the 3rd bit. I found some helpful advice on this forum for the first 2, which led me to a 2-stage query:

Query 1 (which ranks the entries in the division):

SELECT [12MTH].[ANGLERS NAME], [12MTH].CLUB, [12MTH].SPECIES, [12MTH].LENGTH, (Select Count(*) from [12 MONTH] Where DIV = 23 and [LENGTH] > [12Mth].[LENGTH])+1 AS RANK, [12MTH].[Club Points], [12MTH].POINTS, [12MTH].[NO], [12MTH].DIV
FROM [12 MONTH] AS 12MTH
WHERE ((([12MTH].DIV)=23))
ORDER BY [12MTH].LENGTH DESC;

Query 2 (which gives me the top 3, including ties):

SELECT TOP 3 Divis23.[ANGLERS NAME], Divis23.CLUB, Divis23.SPECIES, Divis23.LENGTH, (Select Count(*) from Div23 Where[RANK] < Divis23.[RANK])+1 AS PLACING, Divis23.POINTS, Divis23.[NO], Divis23.DIV, (Select Count(*) from Div23 Where[RANK]=1) AS 1st, (Select Count(*) from Div23 Where[RANK]=2) AS 2nd, (Select Count(*) from Div23 Where[RANK]=3) AS 3rd
FROM Div23 AS Divis23
ORDER BY Divis23.LENGTH DESC;

Then I have another query which allocates club points (3, 2, 1, points shared for ties).

So all this works fine, except if the same person has 2 entries in a division, I only want to include the best entry. For example:

Person 1 - 565mm - 1st place, 3 club points
Person 1 - 460mm
Person 2 - 500mm - 2nd place, 2 club points
Person 2 - 490mm
Person 3 - 450mm - 3rd place, 1 club point

Can someone please help me add this feature to either of the 2 queries? All of the captures are entered in the "12 MONTH" Table. I don't mind, actually it would be better if this could be combined into a single query for all Divisions, or simplified another way, as currently I have 2 queries for each Division and 23 divisions! Some divisions are fish species specific (eg division 23 is only for Salmon, so only the best salmon per person counts), but some divisions allow different species per person (eg division 6 is Fly Fishing, anglers can enter multiple species, but only 1 of each species per angler counts).

Thanks in advance. It's doing my head in!

 

[John Big Booty]

Welcome to the forum.

I suspect that what you will need to do is to use a Totals Query to group your participants and select their Max fish size, you should then be able to rank and score those results.

 

[ACTAngler]

Thanks John, I will give that a try.

 

[Severin]

in your query designer press the button marked with sigma, that will give you more options including max!~)

 

[jdraw]

Can you post a jpg of your tables and relationships? It may help someone else.

You may get some ideas from Allen Browne's subquery info TOP x
http://allenbrowne.com/subquery-01.html