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
  #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

  #2  
Old May 27th, 2010, 05:14 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old May 27th, 2010, 07:32 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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

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 12:42 PM.


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