Top 10 Invoice Values Per Customer & Site - ??

[Dave Gibson]

Hi,

Would anyone please be able to assist me with the SQL for a query?

I have a list of customers, each one has 4 or 5 different sites that
purchase a range of goods.

What I wish to do is to get a query to list the top 10 invoice values
per site per customer. I have a table with the details of the
customers, sites and invoice values which are as below.

[CUSTOMER]
[SITE]
[INVOICEVALUE]

I have tried using the query grid for the top values but it is not
producing the top 10 invoice values per customer per site, rather, it
is getting me the global top 10 invoice values across all cutomer
sites.

Also, at some point in the future I might need to change the top 10
invoice values to the top 5 invoice values or top 20 invoice values
per site per customer


Any help would be greatly appreciated.

Regards

 

[Allen Browne]

See:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/kb/210039/en-us

Possibly also:
How to Create a Top Values Per Group Report
at:
http://support.microsoft.com/kb/208822/en-us

 

[Michael Gramelspacher]

Subject: Top 10 Invoice Values Per Customer & Site - ??
From: Dave Gibson <[email protected]>
Newsgroups: microsoft.public.access.queries

Hi,

Would anyone please be able to assist me with the SQL for a query?

I have a list of customers, each one has 4 or 5 different sites that
purchase a range of goods.

What I wish to do is to get a query to list the top 10 invoice values
per site per customer. I have a table with the details of the
customers, sites and invoice values which are as below.

[CUSTOMER]
[SITE]
[INVOICEVALUE]

I have tried using the query grid for the top values but it is not
producing the top 10 invoice values per customer per site, rather, it
is getting me the global top 10 invoice values across all cutomer
sites.

Also, at some point in the future I might need to change the top 10
invoice values to the top 5 invoice values or top 20 invoice values
per site per customer


Any help would be greatly appreciated.

Regards

My knowledge is quite limited, but possibly
something like the following could work.

SELECT T.customer_nbr,
T.site_nbr,
T.invoice_value,
T.invoice_nbr,
COUNT(*) AS rank
FROM Invoices AS T
INNER JOIN Invoices AS T1
ON T.customer_nbr = T1.customer_nbr
AND T.site_nbr = T1.site_nbr
AND ((T.invoice_value < T1.invoice_value)
OR (T.invoice_value = T1.invoice_value
AND T.invoice_nbr <= T1.invoice_nbr))
GROUP BY T.customer_nbr,
T.site_nbr,
T.invoice_value
HAVING 1*[Enter number:] >= COUNT(*)
ORDER BY T.customer_nbr, site_nbr, COUNT(*);