Help with Query Please

[zinja]

I am having the hardest time grasping how this query should be written. What I am trying to do is get totals (by transdepartment) for all records in the tblTransactions table that are not Charge payments (in the tblPayments table). I am not getting consistent results with the current query as there could be multiple payment methods for each transactionnum (if 100.00 was due, 60 could be paid in Cash and 30 in Check and 10.00 in Charge). So I need totals for all sales that were not paid for by "Charge".

Here is what I got:

SELECT transdepartment AS DEPT, SUM(amount * quantity) AS TOTAL, COUNT(DISTINCT(transactionid)) AS COUNT FROM tblTransactions t, tblPayments p WHERE t.transdepartment <> 'ROA' AND t.reportid= 0 AND t.transactionnum = p.transactionnum AND p.paymentmethod <> 'Charge' AND t.storeid = '8' AND t.registernum = '2' GROUP BY transdepartment ORDER BY transdepartment ASC

Any help would be greatly appreciated!

Thanks,


LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.