Access query help

[PaulCL]

What would the access query be if I wanted only the top 3 firmNames for each change in market in my table(Crab Market Share) with their total volume displayed.
Thanks
Market FirmName Total Volume

ALBAL Joes Crabs 1,000
ALBAL Freds Crabs 2,000
.
.
.
.
ALDOT Sams Crabs 1,500
ALDOT Walt's Crabs 3,000
.
.
.
ALALB xxxxxx xxxxx
...

 

[ranman256]

it needs a field to show it changed, like [DateStamp]
ALBAL Joes Crabs 1,000, 9/1/15
ALBAL Freds Crabs 2,000, 9/1/15
...
ALDOT Sams Crabs 1,500, 8/31/15
ALDOT Walt's Crabs 3,000, 8/31/15

now we can collect the top N of each mkt change using this datestamp. Do you have that?
what marks the Mkt Change?

 

[PaulCL]

it needs a field to show it changed, like [DateStamp]
ALBAL Joes Crabs 1,000, 9/1/15
ALBAL Freds Crabs 2,000, 9/1/15
...
ALDOT Sams Crabs 1,500, 8/31/15
ALDOT Walt's Crabs 3,000, 8/31/15

now we can collect the top N of each mkt change using this datestamp. Do you have that?
what marks the Mkt Change?

I do not. How do I add an unique identifier to my query? The market change is the different 240 regions i.e. ALBAL, ALDOT, ALAUB...

 

[PaulCL]

I do not. How do I add an unique identifier to my query? The market change is the different 240 regions i.e. ALBAL, ALDOT, ALAUB...

 

[Micron]

ranman256: do you think a totals query that contains a subquery clause (e.g. SELECT DISTINCT Market FROM [Crab Market Share) would solve the issue? Or maybe query a query that uses the same sql?

PaulCL: the top 3 firmNames - do you mean the firmNames for the top 3 'Total Volume' values?
Also, if you're going to entirely quote someone else's post, please enclose it in quote tags (see reply toolbar) otherwise, it's more difficult to read.

 

[Micron]

This board's 10 minute edit policy is ROYAL PITA. I lost my changes in the middle of an edit.
While checking my terminology (subquery) I came across this here Microsoft Access tips: Subquery basics

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) 
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

So it looks like what I said is doable if I understand both the need and the method.

 

[PaulCL]

Thanks... I'm looking for the top 3 FirmNames for each 240 regions based on Total Volume.

 

[Micron]

OK. So the posted sql should work if you can modify it to your table/field names. Give it a try! You will learn more by doing.