URGENT - Need to match debits and credits

[toukey]

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 where 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 or value is different I
want it to remain.

Below is the SQL code for uniquepos and uniqueneg which works fine
except if I have 2 positive values of say $200.00 and 1 negative value
of ($200.00), it applies the 1 occurrence of ($200.00) to the both
$200.00. I need the 1 negative amount to apply to 1 positive $200.00.

Uniquepos:
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));

Uniqueneg:
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));

Urgent assistance will be appreciated.

 

[John Nurick]

AFAIK what you want is only possible if you can include in your
query/ies some criterion that specifies which credit must be offset
against which debit.

Often this would be an invoice number or similar. Does InvNo serve this
purpose? If so, I'd junk the pos and neg queries and start with an inner
join that returns all the matched pairs, i.e. the records that you want
to cancel out. This is from my test database; ID is the primary key of
the table:

SELECT A.TransactionID
FROM Toukey AS A INNER JOIN Toukey AS B
ON (A.InvNo = B.InvNo)
AND (A.Amount = -1 * B.Amount)

Then wrap this in another SELECT statement to return the un-cancelled
records:

SELECT C.TransactionID, C.InvNo, C.Amount ...
FROM Toukey AS C
WHERE C.TransactionID NOT IN (
SELECT A.TransactionID
FROM Toukey AS A INNER JOIN Toukey AS B
ON (A.InvNo = B.InvNo)
AND (A.Amount = -1 * B.Amount)
)
ORDER BY ...




if not, maybe there's a business rule that you could implement, e.g.

"If there are two or more outstanding debits of $X against client C,
then the next credit of $X to C's account should be applied against the
oldest debit."