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.
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.