Query to Return each Customer's Largest Order

[Mike Schumann]

I have two tables:

Customer (fields: CustomerID, Name)

Orders (fields: OrderNumber, CustomerID, OrderTotal)

Each customer can have multiple orders.

Could anyone provide some help on how to create a query that will return a
single record for each customer including the OrderNumber and OrderTotal for
the customer's largest order (result fields: CustomerID, Name, OrderNumber,
OrderTotal)?

Thanks,

 

[David S via AccessMonster.com]

Hi Mike,

I think you're going to need two queries, one to get the highest OrderTotal,
and another to get the corresponding Orders:

HighestOrderTotal:
SELECT Customer.CustomerID, Customer.Name, Max(Orders.OrderTotal) AS
MaxOfOrderTotal
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
GROUP BY Customer.CustomerID, Customer.Name;

(This uses the Group By to get the maximum OrderTotal for each customer)

HighestOrders:
SELECT HighestOrderTotal.CustomerID, HighestOrderTotal.Name, Orders.
OrderNumber, Orders.OrderTotal
FROM HighestOrderTotal INNER JOIN Orders ON (HighestOrderTotal.
MaxOfOrderTotal = Orders.OrderTotal) AND (HighestOrderTotal.CustomerID =
Orders.CustomerID);

Note that if more than one Order has the highest OrderTotal, both will appear
in the output

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

The article gives 4 solutions.

BTW, having an OrderTotal field in Orders looks wrong. Generally you would
have an OrderDetails table (as in the Northwind sample database), and the
amounts would be there rather than in the Orders table.