I have a SQL statement that matches credit amounts to debit amounts which works fine except in cases when there is more than one debit amount for the same value, the code then applies the 1 credit to all the debits of the same amount.

eg. if I have the following

Amount Inv. No.
$200.00 345
$300.00 567
$200.00 129

In the above example using the code below, if I have a credit of $200.00 it will be applied to both inv nos. 345 and 129. I would just like it applied to either 345 OR 129.

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

Any help would be appreciated.

Your design is extremely flawed, matching debits to credits by amount only can result in applying the incorrect credit/debit combination even if the client# is the same.

That's only part of it. I just need it resolved.

Well, it is YOU who has to know whether it will be 345 or 129. What happens when there's another Inv. no with the same credit ($200)? And another?

There, of course, is a way - you could, for example, choose MIN(inv_no) or MAX(inv_no) or ...

Data model is awful; you'd better change it (if you can), because this promises you only sweat, blood and tears.

...just out of curiosity how do you propose to match a payment against an invoice where the payer is disputing part of the invoice (ie they are paying some, but not all of the invoice), or where the payer makes a single payment covering many invoices?