Top X of recordset query...with a twist

[GSMike]

This is a tough one. Hope I can explain it.
I have one query that pulls the top 5 states from a recordset, based on the sum of a currency field. No problems there.
What I need then is the top two cities (based on sum of the same currency field) from each of the five states...hopefully returned in one query.
So I need the query to return 10 records (5 X 2). I thought I could do it with five queries (one query returning two cities for each of the five states in the original query) joined together with a union query. Having problems there, even though I thought that was the obvious solution.
Even if I could get that solution to work, it's no fun having queries that are eight queries deep. I prefer to have one well-written deal that does the job. Ya know?
Thanks for your help!
M

 

[ghubbell]

Given little info on the structure of your DB (do you have a table of states, a table of cities, a table of orders?) If so you can do this with 3 queries. Don't forget: sometimes more is less as you have to maintain these queries too. Retreive your top 5 sum sales based on states with Query1. Retreive All sum sales based on cities in Query2. Query3 contains 1 and 2, inner joined on the states, Group for the Max of the sum sales from Query2 and take the top 10. If your structure is similar to this you should get decent results and as this is probably for a report, worry about the sorting/grouping when you get there. I have some queries running math equations and conversion functions that are just too complex to run as one query and even if possible, would only make maintenance a nightmare so don't be shy about stacking up queries. Write back if you need more help. Gord
ghubbell@total.net