Need help with query to sum top N values in group and rank the results

[Desert Moon]

I need some help with an existing query that isn't working quite
right.

I have a database for scoring show cats. Each ring (no, we don't walk
the cats around as the dog folks do) and each cat's 50 highest-scoring
rings are scored for awards.

So I need a query to add up the top 50 rings for each cat and then
rank the results in descending order to tabulate the standings. The
query I have works except that if a cat has several rings all with the
same value, they are all added in; e. g., cat's rings are

Ring# Score
49 82
50 71
51 71
52 71
53 71

I need Access to keep rings 49 amd 50 but drop 51 through 53. Right
now I'm getting the top 53 rings instead of just the top 50!


SELECT Cats.CatName, Sum(qryCats_Rings.RegionalPts) AS Score
FROM qryCats_Rings INNER JOIN Cats ON qryCats_Rings.CatID = Cats.CatID
WHERE (((qryCats_Rings.RegionalPts) In (SELECT top 50 RegionalPts from
qryCats_Rings
WHERE Cats.CatID=qryCats_Rings.CatID and Cats.Class="HHP"
ORDER BY RegionalPts DESC)))
GROUP BY Cats.CatName, Cats.Region
ORDER BY Sum(qryCats_Rings.RegionalPts) DESC;


That's my SQL. "RegionalPts" is the ring score, a not-very-elegant
nested Iif formula based on info in three other fields.

Thanks in advance,

Cheryl Leigh

 

[Duane Hookom]

Add the primary key from qryCats_Rings into the subqueries ORDER BY
SELECT Cats.CatName, Sum(qryCats_Rings.RegionalPts) AS Score
FROM qryCats_Rings INNER JOIN Cats ON qryCats_Rings.CatID = Cats.CatID
WHERE (((qryCats_Rings.RegionalPts) In (SELECT top 50 RegionalPts from
qryCats_Rings
WHERE Cats.CatID=qryCats_Rings.CatID and Cats.Class="HHP"
ORDER BY RegionalPts DESC, PKField)))
GROUP BY Cats.CatName, Cats.Region
ORDER BY Sum(qryCats_Rings.RegionalPts) DESC;

 

[Desert Moon]

Since qryCats_Rings is a query, there's no primary key. I tried
inserting a field which would be unique (RingID) but that didn't help.

The reason I used that query for the join instead of a table with most
of the same information is the query contains the "RegionalPts"
calculation.

Cheryl Leigh

 

[Duane Hookom]

You need some stored value to differentiate the "tie" records. No
differentiation, no breaking ties.

 

[Desert Moon]

When I first designed this (years ago), I was only scoring my own cats
and didn't have to rank them. I think my table structure is logical;
cat info in one, ring info in another, with a cats_rings table to link
them. The primary key in cats_rings is cat ID and ring ID. Guess I
need a unique ID for each record in cats_rings insread of relying on
the combined keys from the other two tables.

Thanks for your help with this. Time to head back to the drawing
board.

Cheryl Leigh