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

  • Thread starter Thread starter Desert Moon
  • Start date Start date
D

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
 
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;
 
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
 
You need some stored value to differentiate the "tie" records. No
differentiation, no breaking ties.
 
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
 
Back
Top