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