Find the 3 largest entry's per customer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,I have a table containing 50.000 order lines divides between 2.000
customers. I need to find the three largest orders per customer. Can I do
this by a simpel Qurry in Access ?
 
Perhaps, It depends on the structure of your data and how you define largest
orders.

Do you have a separate table for customers or do you only have one table?

Do you have a separate table for orders (order level information only)?

By largest order - do you mean the largest monetary value of the combined order lines?

Finally, this can't be done with a simple query. It can be done but the query
will be complex especially with only one table as you may have to use the table
three times (or perhaps more)

IF nothing else you should post your structure or a query that has all the
information in it that you need. It doesn't need to do the summing, top 3, etc
 
Hi John,
Thanks for helping.
I have two separate tables. In the order table every order only consist of
one line and yes, It is the monetary value of the 3 largest orderlines per
client I am trying to find.

I have enclosd the querry expression below, however, It is not yet refined
to only list the three largest order lines pr. client., Can you help ?

SELECT TblCust.CustID, TblCust.CustName, TblOrder.OrderAmount,
TblOrder.OrderId
FROM TblCust INNER JOIN TblOrder ON TblCust.CustID = TblOrder.CustId;
 
Hi John,
Thanks for helping,
I have two tables, one for order lines and one for customers. Every order
only consist of one line in the order table. And yes, I am looking for the
three largest order lines per client in monetary amounts (Order.Amount)

I have made a SQL statement without the summing up part. It goes as follow;

SELECT TblCust.CustID, TblCust.CustName, TblOrder.OrderAmount,
TblOrder.OrderId
FROM TblCust INNER JOIN TblOrder ON TblCust.CustID = TblOrder.CustId;
 
Great. Thanks for the query and the details.


SELECT TblCust.CustID, TblCust.CustName, TblOrder.OrderAmount,
TblOrder.OrderId
FROM TblCust INNER JOIN TblOrder ON TblCust.CustID = TblOrder.CustId
WHERE tblOrder.OrderID IN
(SELECT TOP 3 Dupe.CustID
FROM tblOrder as Dupe
WHERE Dupe.CustID = tblOrder.CustID
ORDER BY Dupe.OrderAmount Desc)

If you want to limit the returns to exactly three records then you may need to
add one more bit to the ORDER BY in the subquery. Right now ties can return
more than three records for any one customer. Let's say that customer 22 has
the following orders and amounts.

22 $100
28 $99
31 $98
27 $98
84 $72

The query would return 22,28, 27, and 31 (because there is a tie for third
place). If all the values in the example were $100, then all five would be returned.

You can limit it to exactly 3 by including the primary key field (orderID) in
the Order by clause

SELECT TblCust.CustID, TblCust.CustName, TblOrder.OrderAmount,
TblOrder.OrderId
FROM TblCust INNER JOIN TblOrder ON TblCust.CustID = TblOrder.CustId
WHERE tblOrder.OrderID IN
(SELECT TOP 3 Dupe.CustID
FROM tblOrder as Dupe
WHERE Dupe.CustID = tblOrder.CustID
ORDER BY Dupe.OrderAmount Desc, Dupe.OrderID)
 
Back
Top