MS Access - Top N per Group and Totals
Posted on 2012-08-22
I have a list of records in a query and I would like to create another query that would allow me to return the Top 5 Customers in a particular market by volume.
The tables in the database are linked to an Excel spreadsheet and there are 4 linked tables in the database (i.e. Market 1, 2, 3 and 4). I then created a union query called qry_Detail that combines all of the records in the 4 tables.
The columns in the tables and query are Market, CustomerName, Volume.
I used the following code and it works by returning the top 3 records/transactions for each market:
SELECT Market, CustomerName, Volume
WHERE [Volume] In
(SELECT TOP 3 [Volume]
FROM qry_Detail dupe
WHERE qry_Detail.[Market] = dupe.[Market]
ORDER BY dupe.[Volume] DESC)
ORDER BY Market, [Volume] DESC;
However, when I create another query called qry_Sum (simply using the same fields as qry_Detail, totalling the Volume and renaming this field TotalVolume) and tried to run the below code, I get the following error:
"Datatype mismatch in criteria expression"
Here is the code that I tried:
SELECT Market, CustomerName, TotalVolume
WHERE [TotalVolume] In
(SELECT TOP 3 [TotalVolume]
WHERE qry_Sum.[Market] = dupe.[Market]
ORDER BY dupe.[TotalVolume] DESC)
ORDER BY Market, [TotalVolume] DESC;
Hoping that someone can help me with this...it is driving me crazy!