troubleshooting Question

SQL Query Join issue

Avatar of dwknight
dwknight asked on
SQL
4 Comments1 Solution243 ViewsLast Modified:
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;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros