Unanswered: match positive and negative amounts (was "Urgent help needed with query criteria")
I have a query 'pos' that extracts all the positive amounts from a table called client_os and another query 'neg' that extracts all the negative amounts from the same table both were the os_amt is <> 0.
I also have 2 other queries (uniquepos and uniqueneg) that would match the osamt field in 'pos' and 'neg' and remove the record(s) that are of the same value in both 'pos' and 'neg'. Example if neg has ($200.00) and pos has
($200.00) it would remove the record. This works but I want it to only match the records where the client number is the same in other words in the example above if the client number and value is the same in both 'pos' and 'neg' I want it to matchup and remove but if the client number is different I want it to remain. I attempted to put the client number in the criteria but was unsuccessful
Below is the SQL code for uniquepos and uniqueneg:
SELECT Pos.osamt, Pos.ClientNo, Pos.ClientName, Pos.Address1, Pos.Address2, Pos.Address3, Pos.Address4, Pos.invdate, Pos.Invno, Pos.PolicyNo, Pos.amount, Pos.Taxamt, Pos.Receiptamt, Pos.Currcode
FROM Pos LEFT JOIN Neg ON Pos.osamt=abs(Neg.osamt)
WHERE (((Neg.osamt) Is Null));
SELECT Neg.osamt, Neg.ClientNo, Neg.ClientName, Neg.Address1, Neg.Address2, Neg.Address3, Neg.Address4, Neg.invdate, Neg.Invno, Neg.PolicyNo, Neg.amount, Neg.Taxamt, Neg.Receiptamt, Neg.currcode
FROM Neg LEFT JOIN Pos ON Neg.osamt=Pos.osamt*-1
WHERE (((Pos.osamt) Is Null));
Thanks - that worked but it brought up another problem with the code. If Client A has 2 debits for say $100.00 and 1 credit for ($100.00) what the code is doing is applying the 1 credit to both of the debits. How do I apply the 1 credit to 1 of the debits of the same amount?