Matching debits and credits

toukey1

New member
Local time
Today, 13:26
Joined
Nov 6, 2006
Messages
7
How do I adjust the SQL query below so that if the client has 2 or more debits for the same amount say in the example below $1,272.00 and the client has 1 credit on their account for ($1,272.00), I would like the credit to be applied to any one of the debits once the amount is the same. The query below works except for situations where there is more than one debit for the same amount.

Uniquepos query:
> 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)
> AND Pos.ClientNo = Neg.ClientNo
> WHERE (((Neg.osamt) Is Null));

Amount Client # Invoice #
$1,272.00 0000750 0026726
$1,272.00 0000750 0027148

Any assistance would be greatly appreciated.
 

Users who are viewing this thread

Back
Top Bottom