Trying to summarise orders and payments

[annysjunkmail]

Hi Group,

I am using the Orders database supplied with A2K.
I have data like so...

Customer ID Total Sales Payment Balance
Let's Stop N Shop £233.05 £0.00 £233.05
Let's Stop N Shop £724.25 £100.00 £624.25
Old World Delicatessen £51.2 £0.00 £0.00
Old World Delicatessen £211.45 £0.00 £211.45
Rattlesnake Canyon Grocery £214.2 £0.00 £214.20
Rattlesnake Canyon Grocery £136.65 £1,000.00 -£863.35

I am trying to do the following...

1 Group CustomerID's and total sales to show overall balance
outstanding WHERE CustomerID has 1 or more payments.
2 Return count of orders by CustomerID

all within the same query to end up like so...

Customer ID Order Count Total Sales Payment Balance
Let's Stop N Shop 2 £957.30 £100.00 £857.30
Rattlesnake Canyon Grocery 2 £350.85 £1,000.00 -£649.15

here is my SQL

SELECT DISTINCTROW Orders.CustomerID,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS [Total
Sales], Payments.PaymentAmount,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)-[PaymentAmount]
AS [Balance Outstanding]
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID) LEFT JOIN Payments ON Orders.OrderID =
Payments.OrderID
GROUP BY Orders.CustomerID, Payments.PaymentAmount;


Can it be done?

Thanks for all your help - much appreciated as it has be stumped!
Chris

 

[Guest]

You'll have to do this in 3 steps:

1-A query that summarizes Sales grouped by CustomerId
2-A query that summarizes Payments grouped by CustomerId
3-A final query joining the prior 2 queries by customerId and subtracting
Total Payments from Total Sales to come up with your balance,