Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2999
  • Last Modified:

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
0
staceymoore
Asked:
staceymoore
  • 8
  • 6
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
This might be what you are looking for:


http://www.allenbrowne.com/subquery-01.html#TopN

mx
0
 
staceymooreAuthor Commented:
Hi

I tried this but it still doesn't work when I total the Volumes.
0
 
aikimarkCommented:
I think the problem is with different table/query name confusion:
qry_Sum
qry_Sumdupe
dupe
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
staceymooreAuthor Commented:
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
0
 
aikimarkCommented:
what does qry_Sum SQL look like?
0
 
staceymooreAuthor Commented:
SELECT qry_Detail.Market, qry__Detail.CustomerName, Sum(qry_Detail.Barrels) AS TotalBarrels
FROM qry__Detail
GROUP BY qry_Detail.Market, qry_Detail.CustomerName;
0
 
aikimarkCommented:
The column name should be TotalBarrels and not TotalVolume
0
 
staceymooreAuthor Commented:
...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;
0
 
aikimarkCommented:
does this query run ok?
0
 
staceymooreAuthor Commented:
yes
0
 
aikimarkCommented:
Does this work?
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;

Open in new window


Can you check to see if "dupe" is used as a table alias somewhere down the chain of queries.
0
 
staceymooreAuthor Commented:
Hi

I tried the above code and it still returns the "datatype mismatch..." error

"dupe" is not used in any of the other queries
0
 
aikimarkCommented:
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?
0
 
staceymooreAuthor Commented:
Hi all

Just letting you know that I was able to create a query that returned the results that I wanted.  Apparently, I needed to add the tblMarkets to the query in order for it to work.  So here goes:

tblMarkets:
MarketID (primary key)
MarketLongName

qrySum:
MarketID
CustomerName
TotalVolume

Create a join between tblMarkets.MarketID and qrySum.MarketID

SQL code:

SELECT tblMarkets.MarketLongName, qrySum.CustomerName, qrySum.TotalVolume

FROM tblMarkets INNER JOIN qrySum ON tblMarkets.Market_ID = qrySum.Market_ID

WHERE (((qrySum.TotalVolume) In (Select Top 3 [TotalVolume] From [qrySum] Where
 [MarketID]=[tblMarkets].[MarketID] Order By [TotalVolume] Desc)));
0
 
staceymooreAuthor Commented:
tested this on my own and it worked
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now