Find Top Five Query Results

[thisbenroberts]

I have a query that returns a list of transaction ID's, the state in which the transaction took place, and the dollar amounts of the transactions. What my boss is looking for is a list of the top 5 transactions per state. To be clear there may not 5 transactions per state and there may not be transactions in all 50 states. The query sorts by state so that they are in alphabetic order, and in descending value.

My thought was to get a count of the unique states, and then a count of unique transactions per state. Then somehow loop through and put the top 'x' transactions in a table.

Am I on the right track? I can't even figure out how to get the unique count in VBA. I can get it as part of a query using a secondary query but that doesn't seem helpful.

Thanks in advance.

Ben

 

[mdlueck]

Are the queries logged somewhere? Are they plain SQL? You could use a SQL GROUP BY query to organize the data by the type of query, then within that query place a COUNT(*) AS [count] to count up how many in each group... that is, each type of query executed.