A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Single Query for Top 10 of Multiple Items



 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old May 27th, 2010, 04:24 PM posted to microsoft.public.access.queries
Joe Murphy[_5_]
external usenet poster
 
Posts: 1
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.