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 |
#2
|
|||
|
|||
Single Query for Top 10 of Multiple Items
This could be slow but try the following. This will return for the tenth
position. SELECT Key, Customer, Fruit, [# of pieces purchased] FROM [Fruits] WHERE [KEY] in (SELECT TOP 10 [Key] FROM [Fruits] as Temp WHERE Temp.Fruit = [Fruits].Fruit ORDER BY [# of pieces purchased] DESCENDING) Make sure that # of pieces purchased and Fruit is indexed. If that is too slow, there are ways to speed it up. It involves multiple Top queries (one for each type of fruit), a union query, and a knowledge of the exact number of different fruits. The union query would end up being used in the where clause. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Joe Murphy wrote: 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 |
#3
|
|||
|
|||
Single Query for Top 10 of Multiple Items
A join would probably be faster, but has the disadvantage that in the event
of there being a tie for 10th place between two customers for a particular fruit only the customers up to 9 places for that particular fruit would be returned rather than 11 or more as should really be the case in the event of a tie: SELECT P1.[key], P1.[customer], P1.[fruit],P1.[# of pieces purchased] FROM [PurchaseTable] AS P1 INNER JOIN [PurchaseTable] As P2 ON P1.[fruit] = P2.[fruit] AND P1.[.[# of pieces purchased] = P2.[.[# of pieces purchased] GROUP BY P1.[key], P1.[customer], P1.[fruit],P1.[# of pieces purchased] HAVING COUNT(*) = 10 ORDER BY P1.[fruit], P1.[# of pieces purchased] DESC: Ken Sheridan Stafford, England Joe Murphy wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 |
Thread Tools | |
Display Modes | |
|
|