staceymoore
asked on
MS Access - Top N per Group and Totals
Hi all
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
FROM qry_Detail
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
FROM qry_Sum
WHERE [TotalVolume] In
(SELECT TOP 3 [TotalVolume]
FROM qry_Sumdupe
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!
Stacey
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
FROM qry_Detail
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
FROM qry_Sum
WHERE [TotalVolume] In
(SELECT TOP 3 [TotalVolume]
FROM qry_Sumdupe
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!
Stacey
ASKER
Hi
I tried this but it still doesn't work when I total the Volumes.
I tried this but it still doesn't work when I total the Volumes.
I think the problem is with different table/query name confusion:
qry_Sum
qry_Sumdupe
dupe
qry_Sum
qry_Sumdupe
dupe
ASKER
My apologies, the code should read:
SELECT Market, CustomerName, TotalVolume
FROM qry_Sum
WHERE [TotalVolume] In
(SELECT TOP 3 [TotalVolume]
FROM qry_Sum dupe
WHERE qry_Sum.[Market] = dupe.[Market]
ORDER BY dupe.[TotalVolume] DESC)
ORDER BY Market, [TotalVolume] DESC;
...still doesn't work
SELECT Market, CustomerName, TotalVolume
FROM qry_Sum
WHERE [TotalVolume] In
(SELECT TOP 3 [TotalVolume]
FROM qry_Sum dupe
WHERE qry_Sum.[Market] = dupe.[Market]
ORDER BY dupe.[TotalVolume] DESC)
ORDER BY Market, [TotalVolume] DESC;
...still doesn't work
what does qry_Sum SQL look like?
ASKER
SELECT qry_Detail.Market, qry__Detail.CustomerName, Sum(qry_Detail.Barrels) AS TotalBarrels
FROM qry__Detail
GROUP BY qry_Detail.Market, qry_Detail.CustomerName;
FROM qry__Detail
GROUP BY qry_Detail.Market, qry_Detail.CustomerName;
The column name should be TotalBarrels and not TotalVolume
ASKER
...typo error
SELECT qry_Detail.Market, qry__Detail.CustomerName, Sum(qry_Detail.Volume) AS TotalVolume
FROM qry__Detail
GROUP BY qry_Detail.Market, qry_Detail.CustomerName;
SELECT qry_Detail.Market, qry__Detail.CustomerName, Sum(qry_Detail.Volume) AS TotalVolume
FROM qry__Detail
GROUP BY qry_Detail.Market, qry_Detail.CustomerName;
does this query run ok?
ASKER
yes
Does this work?
Can you check to see if "dupe" is used as a table alias somewhere down the chain of queries.
SELECT Market, CustomerName, TotalVolume
FROM qry_Sum
WHERE [TotalVolume] In
(SELECT TOP 3 dupe.[TotalVolume]
FROM qry_Sum As dupe
WHERE qry_Sum.[Market] = dupe.[Market]
ORDER BY dupe.[TotalVolume] DESC)
ORDER BY Market, [TotalVolume] DESC;Can you check to see if "dupe" is used as a table alias somewhere down the chain of queries.
ASKER
Hi
I tried the above code and it still returns the "datatype mismatch..." error
"dupe" is not used in any of the other queries
I tried the above code and it still returns the "datatype mismatch..." error
"dupe" is not used in any of the other queries
I know this isn't going to give you the correct results, but do you still get the error if you use Top 1 instead of Top 3?
If you removed the Top 3 completely, does you still get an error?
If you removed the Top 3 completely, does you still get an error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tested this on my own and it worked
http://www.allenbrowne.com/subquery-01.html#TopN
mx