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