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