Access Top N Query where N changes by group

Question

I am trying to write a top n value query in Microsoft Access where n may change by group. For example, one group may be top 3 records, another may be top 2 records, and another may be top 4 records. I have a table with the field "TopN" where I store how many values to show for that group.

Sample of Top 3 query that works, I am trying to replace Top 3 with Top N where N is a DLookup or something that uses the value in TopN field:

SELECT Payout.GDate, Payout.[Game Type], Payout.Denom, Payout.Segment, Payout.Manufacturer_Description, Payout.Stand, Payout.Payout
  FROM Payout, PayoutShowSegment
  WHERE (((Payout.Payout) IN (SELECT TOP 3 [Payout] 
                                FROM [Payout] 
                                WHERE [Payout].[Segment]=[PayoutShowSegment].[Segment])))
  ORDER BY Payout.[Game Type], Payout.Denom, Payout.Payout DESC;

Try to emulate row_number in access then you can do the look up for each category. http://stackoverflow.com/questions/21917637/achieving-row-number-partition-by-in-ms-access — Juan Carlos Oropeza, Nov 17 '15 at 22:54

score 0 · Answer 1 · answered Nov 18 '15 at 17:32

I had a similar problem a couple of months ago.

Working with your query, something like this should work:

SELECT      T1.GDate, 
            T1.[Game Type], 
            T1.Denom, 
            T1.Segment, 
            T1.Manufacturer_Description, 
            T1.Stand, 
            T1.Payout
FROM        Payout T1 INNER JOIN Payout T2 ON
                T1.[Game Type] = T2.[Game Type] AND
                T1.Denom = T2.Denom AND
                T1.Segment = T2.Segment AND
                T1.Manufacturer_Description = T2.Manufacturer_Description AND
                T1.Stand = T2.Stand AND
                T1.Payout = T2.Payout AND
                T1.GDate >= T2.GDate
GROUP BY    T1.GDate, 
            T1.[Game Type], 
            T1.Denom, 
            T1.Segment, 
            T1.Manufacturer_Description, 
            T1.Stand, 
            T1.Payout
HAVING      COUNT(*) <= (
                         SELECT TopN
                         FROM   MyOtherTable
                         WHERE  MyOtherTable.IDField = T1.IDField
                        )
ORDER BY    T1.GDate

Note - the join can probably be reduced to the PK in the Payout table.

For more info check this site: http://www.sql-ex.com/help/select16.php

Thank you for your suggestion/example. I used this and kept getting the whole table back and not the TopN records per group. Tried several modifications to different sections of the statements with varying results. I think instead I will use VBA to create a union query stepping through each segment (there are about 20). — Ed Blackburn, Nov 19 '15 at 16:12
That's a shame. My method uses the row numbering that @Juan Carlos Oropeza mentioned in his comment and selects the records that have a row number less than or equal to the desired amount. It's well worth looking into. — Darren Bartrup-Cook, Nov 19 '15 at 16:33

Access Top N Query where N changes by group

1 Answers1