How can I join 2 tables? 1

[NFLDUser]

I have a table called Billing that holds all a stores transactions. I use the following query to figure out totals based on each payment method (Cash, Visa, Debit, etc.).

SELECT Sum(IIF(isnull(paid),0,paid) + IIF(isnull(provided),0,provided)) AS totalpaid, method AS typepaid
FROM Billing
GROUP BY Billing.method;

That works fine.
But I have another table called Credits that has a field called 'amount' that holds the amount that I am crediting back to the customer and another field called 'method' that is where the amount was credited to (Cash, Visa, Debit, etc.). I want to subtract my credit totals from my regular totals.

EX:
The store ended with $1000 cash.
The store gave out $50 cash in credit.

So the total I should end up with is $950.


IDEAS??

 

[PHV]

Perhaps something like this ?
SELECT Sum(Nz(B.paid,0)+Nz(B.provided,0)-Nz(C.amount,0)) AS total, B.method AS typepaid
FROM Billing AS B LEFT JOIN Credits AS C ON B.Customer=C.Customer AND B.method=C.method
GROUP BY B.method

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NFLDUser]

works...thanks!