Limit number of records returned by SQL Query

[scoobs1000]

I am designing a database for a fishing club to keep track of the fishing competition scores (in MS Access XP).

I have three tables; Catch (containing date, boat, fish, fish weight), Boats (list of boats), Fish (list of fish types and how many points they are worth). The score is determined as follows; each species of fish is worth a different score (per 100g), the score for each boat is determined as the sum of the top 5 scoring fish for each species. i.e. the score for Boat1 is determined from the five heaviest flounder, snapper, crayfish, etc.

I have written the following query to do this:

SELECT Boats.Boatname, Catch.Date, Fish.Species, Catch.Weight, Fish.[Weighted points], [Weighted points]*[weight] AS [Points Awarded]
FROM Boats INNER JOIN (Fish INNER JOIN Catch ON Fish.ID = Catch.Fish) ON Boats.ID = Catch.Boat 
WHERE (((Catch.Weight) In (SELECT TOP 5 Catch.Weight From Catch WHERE Catch.Fish = Fish.ID AND Boats.ID = Catch.Boat ORDER BY Catch.Weight DESC)))
ORDER BY Boats.Boatname, Fish.Species, Catch.Weight DESC;

The query works fine except when there are several fish with the same weight that are tied for fifth position. The TOP query returns all ties in Access SQL so I end up with six or seven fish returned which give a high score. I need to return only 5 fish even if there is a tie.

This seems to be possible in Oracle and Access Server using rownum or limit functions but these are not available in Access XP. Is there a way to limit the number of records returned by the subquery in Access XP?

 

[john471]

That is an interesting little conundrum !

I only have Access97 to play with, and can't think of/find an easy solution to your problem. The only things I can think of are :-

Change the scoring rules

- or -

To the Catch table, add a new yes/no field "addsToScore"

Then:-
Write Access VBA code to:-
a) Re-set all the addsToScore fields to False (or all for a given date - if you apply that limitation below too)
b) Retrieve a recordset similar to your current one, with the addition of the Boat.ID, Fish.ID and Catch.addsToScore fields
c) Loop through that recordset, tracking the Boat.ID & Fish.ID, and setting the addsToScore field to True for only the first five of each Boat.ID, Fish.ID combination.

Then :-
Write a query similar to the one you have to return the scores, except the criteria would be based on the addsToScore field, and not use the "In (Select Top....)" for the final version.

Somebody else is bound to have a better idea... but to date they haven't put it forward. It would not be unusual for me to have missed the simple solution .

Good luck.

 

[maxmangion]

in your query design view, go to View -> Properties and set the Property Top to 5 instead of all.

 

[scoobs1000]

Thanks John,

It is not an ideal solution but it is the best one I have had to-date. I feel much more comfortable in VB so I will give it a go.

 

[john471]

Yes, not ideal, agreed. Would be nicer if Access SQL had an option include or exclude "ties" (for last place).

Happy coding !