I am writing an SQL query to obtain the total payment made in a financial transaction that can be made up of more than one item and queried on payment dates and salesperson. (sales and payments are kept in 2 separate tables with a salesperson index in the third). PaymentItemList holds the payments, appblillitemlist holds the sales and billitemindex holds the salesperson id. What I am finding is that if I have one sales transaction and one payment transaction - the query works. I run into an issue when I have two sales as a part of a single transaction and a single payment for both sale items. The below query runs and returns the same sum value twice for each sales parts of the one transaction. For example - If I sell $50.00 in one transaction and a payment of 50.00 is received, then the query returns the correct values. If however, I have two sales of $50.00 as a part of the one transaction and one payment of 100.00 then the query returns 100 for each sales part (hence a payment total of 200 rather than 100). I am looking for the query to return one payment total for all of the sales in a single transaction. Thanks for the help.
SELECT PaymentItemList.PaymentMethod, sum(PaymentItemList.PaymentAmount) as TotalByPayMethod FROM ((PaymentItemList INNER JOIN Booking ON PaymentItemList.BookingNo = Booking.BOOKINGNo) INNER JOIN AppBillItemList ON Booking.BOOKINGNo = AppBillItemList.BookingNo) INNER JOIN BillItemIndex ON AppBillItemList.ItemCode = BillItemIndex.TreatmentCode WHERE PaymentItemList.PaymentDate >= #01/12/2009# And PaymentItemList.PaymentDate <= #01/12/2009# AND TreatSelectedCategory Like '/2/' Group by PaymentItemList.PaymentMethod
GROUP BY PaymentItemList.PaymentMethod;