My apologies for not making myself clear. What I am trying to achieve is NOT to have on my Client Statement report (by client) records where debits and credits can be matched and removed from the report as clients find it confusing. For example, I don't want to see say
Amount Inv. No.
$120.00 12
($120.00) 16
$200.00 4
($200.00) 10
So, with the little programming experiece I have I created a query (POS) to extract all the positive amounts from a query that I already had existing and another query (NEG) to extract all the negative amounts.
I then created 2 other queries (uniquepos and uniqueneg) to match them up and return those that were not matched.
It all worked fine until I had a client with 2 debit amounts for say $200.00 and 1 credit for ($200.00). What the code did was apply the credit to both debits. How I want it to work is to apply the credit to ONLY 1 of the deibts where the amounts are equal (whether it's the first or last).
My SQL statements are listed below.
POS query
SELECT qryclientstat.ClientNo, qryclientstat.FirstName, qryclientstat.LastName, qryclientstat.Address1, qryclientstat.Address2, qryclientstat.City, qryclientstat.Country, qryclientstat.TransDate, qryclientstat.InvoiceNo, qryclientstat.PolicyNo, qryclientstat.TransAmt, qryclientstat.OsAmt, qryclientstat.TaxAmt, qryclientstat.ReceiptAmt, qryclientstat.Currency
FROM qryclientstat
WHERE (((qryclientstat.OsAmt)>0));
NEG query
SELECT qryclientstat.ClientNo, qryclientstat.LastName, qryclientstat.FirstName, qryclientstat.Address1, qryclientstat.Address2, qryclientstat.City, qryclientstat.Country, qryclientstat.TransDate, qryclientstat.InvoiceNo, qryclientstat.PolicyNo, qryclientstat.TransAmt, qryclientstat.OsAmt, qryclientstat.TaxAmt, qryclientstat.ReceiptAmt, qryclientstat.Currency
FROM qryclientstat
WHERE (((qryclientstat.OsAmt)<0));
UNIQUEPOS query
SELECT Pos.osamt, Pos.ClientNo, Pos.FirstName, Pos.LastName, Pos.Address1, Pos.Address2, Pos.City, Pos.Country, Pos.TransDate, Pos.InvoiceNo, Pos.PolicyNo, Pos.TransAmt, Pos.TaxAmt, Pos.ReceiptAmt, Pos.Currency
FROM Pos LEFT JOIN Neg ON (Pos.osamt = abs(Neg.osamt)) AND (Pos.ClientNo = Neg.ClientNo)
WHERE (((Neg.osamt) Is Null));
UNIQUENEG query
SELECT Neg.osamt, Neg.ClientNo, Neg.FirstName, Neg.LastName, Neg.Address1, Neg.Address2, Neg.City, Neg.Country, Neg.TransDate, Neg.InvoiceNo, Neg.PolicyNo, Neg.TransAmt, Neg.TaxAmt, Neg.ReceiptAmt, Neg.Currency
FROM Neg LEFT JOIN Pos ON Neg.osamt = Pos.osamt * -1
WHERE (((Pos.osamt) Is Null));
UNION query
SELECT * FROM uniquepos UNION select * FROM uniqueneg;
