"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