Access Top N by Group

Question

I need to get the Top 2 records per Customer AND Product. So my data looks like this...

Customer ProdCode
-------- --------
Cust1    PROD1
Cust1    PROD1
Cust1    PROD1
Cust1    PROD1
Cust1    PROD2
Cust1    PROD2
Cust1    PROD2
Cust1    PROD2
Cust1    PROD2
Cust2    PROD1
Cust2    PROD1
Cust2    PROD1
Cust2    PROD1
Cust2    PROD1

but I want it to look like this...

Customer ProdCode
-------- --------
Cust1    PROD1
Cust1    PROD1
Cust1    PROD2
Cust1    PROD2
Cust2    PROD1
Cust2    PROD1

I've found a number of examples of Top N per Group but not for two columns.

possible duplicate of [selecting top N rows for each group in a table](http://stackoverflow.com/questions/3998529/selecting-top-n-rows-for-each-group-in-a-table) — Joe, Jan 23 '12 at 02:50
I see it as similar but not a duplicate. I'm trying to do it with two fields from the table, not one... and with access SQL. — petedotg, Jan 23 '12 at 03:42
This question is similar (and for Access): [complicated-ms-access-greatest-n-per-group-problem](http://stackoverflow.com/questions/1803396/complicated-ms-access-greatest-n-per-group-problem) — ypercubeᵀᴹ, Jan 23 '12 at 13:09

score 0 · Answer 1 · answered Jan 23 '12 at 12:12

0

You cannot get the displayed result from the data shown. Access provides matches for top N and your data has multiple matches, so these will be returned. It may be possible to get the required result with more realistic data, as there are several workarounds that can force an exact return.

answered Jan 23 '12 at 12:12

Fionnuala

90,370
7
114
152

Could you please give a quick example of what you mean? – petedotg Jan 23 '12 at 21:00
You have 4 `Cust1 PROD1`, so no matter whether you choose Top 1, Top 2 or Top n, 4 results will be returned, because they are matches. – Fionnuala Jan 23 '12 at 21:08

Access Top N by Group

1 Answers1