By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

top 5 of group?

P: n/a
Hello:

tblDetails has the following fields:
AgencyCode, ProducerCode, AnnualPX
How can I create a query to show the Top 5 producers per agency with
the highest
Sum(PX)?

Oct 25 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I should point out that I am looking at top 5 per agency, not just 5
returned rows overall.

Thanks

BerkshireGuy wrote:
Hello:

tblDetails has the following fields:
AgencyCode, ProducerCode, AnnualPX
How can I create a query to show the Top 5 producers per agency with
the highest
Sum(PX)?
Oct 25 '06 #2

P: n/a
MP
"BerkshireGuy" <bd*****@yahoo.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
I should point out that I am looking at top 5 per agency, not just 5
returned rows overall.

Thanks

BerkshireGuy wrote:
Hello:

tblDetails has the following fields:
AgencyCode, ProducerCode, AnnualPX
How can I create a query to show the Top 5 producers per agency with
the highest
Sum(PX)?
Don't know if this will help
but heres what i found somewhere on jet.sql

The TOP keyword
The TOP keyword is used to return a certain number of rows that fall at the
top or bottom of a range that is specified by an ORDER BY clause. The ORDER
BY clause is used to sort the rows in either ascending or descending order.
If there are equal values present, the TOP keyword will return all rows that
have the equal value. Let's say that we want to determine the highest three
invoice amounts in our invoices database. We'd write a statement like this:

SELECT TOP 3 InvoiceDate, Amount
FROM tblInvoices
ORDER BY Amount DESC

We can also use the optional PERCENT keyword with the TOP keyword to return
a percentage of rows that fall at the top or bottom of a range that is
specified by an ORDER BY clause. The code looks like this:

SELECT TOP 25 PERCENT InvoiceDate, Amount
FROM tblInvoices
ORDER BY Amount DESC

Note that if you do not specify an ORDER BY clause, the TOP keyword will not
be helpful: it will return a random sampling of rows.

For more information about predicates, type all, distinct predicates in the
Office Assistant or on the Answer Wizard tab in the Microsoft Access Help
window, and then click Search.

hth
Mark
Oct 25 '06 #3

P: n/a
BerkshireGuy wrote:
I should point out that I am looking at top 5 per agency, not just 5
returned rows overall.

Thanks

BerkshireGuy wrote:
Hello:

tblDetails has the following fields:
AgencyCode, ProducerCode, AnnualPX
How can I create a query to show the Top 5 producers per agency with
the highest
Sum(PX)?
tblDetails
AgencyCode Long
ProducerCode Long
AnnualPX Double

qryRankPX:
SELECT AgencyCode, ProducerCode, (SELECT Count(A.ID) FROM tblDetails AS
A WHERE A.AnnualPX tblDetails.AnnualPX AND A.AgencyCode =
tblDetails.AgencyCode)+1 AS RankWithinGroup FROM tblDetails;

qryTopFive:
SELECT AgencyCode, ProducerCode, RankWithinGroup FROM qryRankPX WHERE
RankWithinGroup <=5;

or

qryTopFiveShowingTies:
SELECT AgencyCode, ProducerCode, RankWithinGroup & IIf((SELECT
Count(A.ProducerCode) FROM qryRankPX AS A WHERE A.AgencyCode =
qryRankPX.AgencyCode AND A.RankWithinGroup = qryRankPX.RankWithinGroup)
1, ' (tie)', '') AS AgencyRanking FROM qryRankPX WHERE RankWithinGroup <=5;
tblDetails
AgencyCode ProducerCode AnnualPX

1 1 1 9
2 1 2 9
3 1 3 9
4 1 4 8
5 1 5 8
6 1 6 8
7 1 7 4
8 1 8 2
9 1 9 2
10 1 10 2
11 1 11 2
12 1 12 2
13 1 13 2
14 2 14 12
15 2 15 12
16 2 16 11
17 2 17 10
18 2 18 9
19 2 19 9
20 2 20 4
21 2 21 4
22 2 22 4
23 2 23 4
24 2 24 4
25 2 25 4

!qryTopFiveShowingTies:
AgencyCode ProducerCode AgencyRanking
1 1 1 (tie)
1 2 1 (tie)
1 3 1 (tie)
1 4 4 (tie)
1 5 4 (tie)
1 6 4 (tie)
2 14 1 (tie)
2 15 1 (tie)
2 16 3
2 17 4
2 18 5 (tie)
2 19 5 (tie)

James A. Fortune
CD********@FortuneJames.com

Oct 25 '06 #4

P: n/a
"BerkshireGuy" <bd*****@yahoo.comwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:
I should point out that I am looking at top 5 per agency, not just
5 returned rows overall.
You'll need to do a correlated subquery for that and the performance
will probably suck.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 26 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.