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

    Question Unanswered: How to match debits and credits in a table

    I have a table called clientoi which holds client number, amount (which can either be debit or credit), os_amt and invoice number.

    I created a report (Client Statement) that shows me all my client balances by client e.g.
    invoice # amount
    100000 $100.00
    100001 $200.00
    100002 ($100.00)
    --------
    $200.00

    Based on the example above how do I create a query that would match the debit $100.00 to the credit ($100.00) and only show the $200.00?????

  2. #2
    Join Date
    Jul 2005
    Posts
    39
    Perhaps this will help. I call your table clientoi as tblOpenInvoice.
    Code:
    SELECT tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt
    FROM tblOpenInvoice
    WHERE (((tblOpenInvoice.invoiceNo) Not In (SELECT tblOpenInvoice.invoiceNo
    FROM tblOpenInvoice INNER JOIN tblOpenInvoice AS tblOpenInvoice_1 ON tblOpenInvoice.clientNo = tblOpenInvoice_1.clientNo
    WHERE ((([tblOpenInvoice].[os_amt]+[tblOpenInvoice_1].[os_amt]=0)=-1))
    GROUP BY tblOpenInvoice.invoiceNo)))
    GROUP BY tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt;

  3. #3
    Join Date
    Sep 2001
    Posts
    175
    I was actually able to get something going before viewing your reply where I wrote queries that would extract all the positive values and another to extract all the negative values. I then created a third query to match them (code shown below) but the problem is if I have say 2 values of $1200.00 and 1 value of ($1200.00), it is applying the one occurrence of the negative value to BOTH positive values. How can I get it to apply only to one????

    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 LEFT JOIN Neg ON Pos.osamt = abs(Neg.osamt)
    WHERE (((Neg.osamt) Is Null));

  4. #4
    Join Date
    Jul 2005
    Posts
    39
    This is different from your original context. Perhaps these steps will help.

    create tblOffsetPositive and tblOffsetNegative with attributes (index, clientNo, invoiceNo,os_amt). Set the attribute index as autonumber type. We will use the index to do the offset.

    now from tblOpenInvoices insert population selections like

    Code:
    INSERT INTO tblOffsetNegative ( clientNo, invoiceNo, os_amt )
    SELECT tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt
    FROM tblOpenInvoice INNER JOIN tblOpenInvoice AS tblOpenInvoice_1 ON tblOpenInvoice.clientNo = tblOpenInvoice_1.clientNo
    WHERE ((([tblOpenInvoice].[os_amt]+[tblOpenInvoice_1].[os_amt]=0)=-1) AND ((tblOpenInvoice.os_amt)<0))
    GROUP BY tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt;
    and

    Code:
    INSERT INTO tblOffsetPositive ( clientNo, invoiceNo, os_amt )
    SELECT tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt
    FROM tblOpenInvoice INNER JOIN tblOpenInvoice AS tblOpenInvoice_1 ON tblOpenInvoice.clientNo = tblOpenInvoice_1.clientNo
    WHERE ((([tblOpenInvoice].[os_amt]+[tblOpenInvoice_1].[os_amt]=0)=-1) AND ((tblOpenInvoice.os_amt)>0))
    GROUP BY tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt;
    To find the offset invoices, create two precursor queries as qryCountOffsetNegative:

    Code:
    SELECT tblOffsetNegative.clientNo, tblOffsetNegative.os_amt, Count(tblOffsetNegative.clientNo) AS frequency
    FROM tblOffsetNegative
    GROUP BY tblOffsetNegative.clientNo, tblOffsetNegative.os_amt;
    and qryReIndexNegative as

    Code:
    SELECT tblOffsetNegative.clientNo, tblOffsetNegative.invoiceNo, tblOffsetNegative.os_amt, 1*Abs([index]-[frequency]) AS [match]
    FROM tblOffsetNegative INNER JOIN qryCountOffsetNegative ON (tblOffsetNegative.os_amt = qryCountOffsetNegative.os_amt) AND (tblOffsetNegative.clientNo = qryCountOffsetNegative.clientNo);
    Now create a new query as qryOffsetInvoices:

    Code:
    SELECT tblOffsetPositive.invoiceNo
    FROM tblOffsetPositive INNER JOIN qryReIndexNegative ON tblOffsetPositive.index = qryReIndexNegative.match;
    UNION SELECT qryReIndexNegative.invoiceNo
    FROM tblOffsetPositive INNER JOIN qryReIndexNegative ON tblOffsetPositive.index = qryReIndexNegative.match;
    that will be used against tblOpenInvoice in the final query qryOpenInvoicesNet:

    Code:
    SELECT tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt
    FROM tblOpenInvoice LEFT JOIN qryOffsetInvoices ON tblOpenInvoice.invoiceNo = qryOffsetInvoices.invoiceNo
    WHERE (((qryOffsetInvoices.invoiceNo) Is Null))
    GROUP BY tblOpenInvoice.clientNo, tblOpenInvoice.invoiceNo, tblOpenInvoice.os_amt;

  5. #5
    Join Date
    Sep 2001
    Posts
    175
    Thanks - I will give it a try.

Posting Permissions

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