If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Single Query for Top 10 of Multiple Items
Greetings,
I'm going to confess right up front that I'm an Access noob, so there is a great possibility that what I'm asking is basic and that I just don't have enough knowledge yet. That said, here is the situation: Let us say you have been provided a database of various fruit purchases. The fields concerned would be: "key" "customer" "fruit" "# of pieces purchased". The database is tens or hundreds of thousands of records. What I ultimately am trying to get is an output that would show those four fields for the top 10 of "# of pieces purchased" for each of the various fruits. So, assuming I had ten different fruits listed, I'd get 100 records in my query, which would give me the top 10 customers for each type of fruit. I've been able to get a query to show me which customer is the biggest buyer for each type of fruit by using totals and "group by" on the "fruit" field and MAX on the "# of piec..." field, but that only gives one record per fruit. I imagine that I could break this out into ten different queries and use the "Top" button and select 10 for each fruit separately, but I figured there was a more elegant way to do it. I should also state that I know nearly nothing of SQL statements, and am thus working just from the query GUI. If there are pertinent SQL things I need to learn for this, my brain is open and very willing to absorb whatever I can! Thanks in advance for any time and consideration given to this question. I really am at a roadblock, constrained by my own initial ignorance as I try to move forward. Joe |
Thread Tools | |
Display Modes | |
|
|