Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001
    Posts
    175

    Red face Unanswered: match positive and negative amounts (was "Urgent help needed with query criteria")

    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 were 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 is different I want it to remain. I attempted to put the client number in the criteria but was unsuccessful

    Below is the SQL code for uniquepos and uniqueneg:

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

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try joining pos and neg on client number and setting the criteria for pos.osamt <> abs(neg.osamt).

  3. #3
    Join Date
    Sep 2001
    Posts
    175
    I tried your suggestion (code below) but was unsuccessful. For each positive it gave all the negatives that were not equal to that positive.

    SELECT Neg.osamt, 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, Neg
    WHERE pos.clientno = neg.clientno
    and pos.osamt <> abs(neg.osamt)

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Shouldn't it be

    SELECT Neg.osamt, 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 INNER JOIN Neg ON pos.clientno = neg.clientno
    WHERE pos.osamt = abs(neg.osamt)


    ??

    MTB

  5. #5
    Join Date
    Sep 2001
    Posts
    175
    Thanks - that worked but it brought up another problem with the code. If Client A has 2 debits for say $100.00 and 1 credit for ($100.00) what the code is doing is applying the 1 credit to both of the debits. How do I apply the 1 credit to 1 of the debits of the same amount?

    Any help would be appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •