Select highest total amount in different tables (Access)

Question

Hi

I have 2 tables: "sales" and "services". Both tables have these fields: customer and amount

I need to retrieve the customer with the highest total amount (sum all amounts), between dates., in the both tables.

Example:

sales

Mary | $100

John | $200

Mary | $200

services

Mary | $40

John | $300

If we sum all amounts of every customer we obtain:

Mary | $340

John | $500

Then the customer with the highest total amount is: John

But how I can do this using an Access' query ?

Thanks in advance for any help.

Answer 1

I think a union query would be most appropriate:

SELECT Top 1 Customer, Sum(Amount) As Total FROM
(SELECT Customer, Amount, Date FROM Sales
UNION ALL
SELECT Customer, Amount, Date FROM Services)
WHERE Date Between Date() AND Date()-30
GROUP BY Customer
ORDER BY Sum(Amount) DESC