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

    Question Unanswered: How can I accomplish this (if at all)

    I created a report which shows outstanding monies due from clients (a client statement to be exact) an extract of which is shown below.

    date inv. no. amount
    ----- ---------- ---------
    1/1/03 100 $ 50.00
    2/1/03 200 $100.00
    3/1/03 300 ($ 50.00)

    What I would like to accomplish is to remove from the report where the debits and credits are equal. In this case to only show the amount of $100.00.

    Can this be done??????

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: How can I accomplish this (if at all)

    Originally posted by toukey1
    I created a report which shows outstanding monies due from clients (a client statement to be exact) an extract of which is shown below.

    date inv. no. amount
    ----- ---------- ---------
    1/1/03 100 $ 50.00
    2/1/03 200 $100.00
    3/1/03 300 ($ 50.00)

    What I would like to accomplish is to remove from the report where the debits and credits are equal. In this case to only show the amount of $100.00.

    Can this be done??????
    Shouldn't you only do that if the INV_NO are the same? If so, you could group on INV_NO and only show balances <> 0.

  3. #3
    Join Date
    Sep 2001
    Posts
    175
    No, the Inv. no. is not the same. Suppose I raised an Invoice for the client refunding him money. The inv. no. would be different.

    Any other ideas??

  4. #4
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by toukey1
    No, the Inv. no. is not the same. Suppose I raised an Invoice for the client refunding him money. The inv. no. would be different.

    Any other ideas??
    Sorry, but I am missing the purpose of this. What happens if you have an invoice for $100 and then you end up raising 2 more invoices for credits in the amounts of ($75) and ($25). All 3 records would show up on your report.

  5. #5
    Join Date
    Jan 2003
    Posts
    4

    Post

    If you are using negative values in your fields, as your example seems to display, use the Sum function. It will add/subtract the money just fine. Group by the Client to get the total for each.

    Chris

  6. #6
    Join Date
    Sep 2001
    Posts
    175
    Tried what Stamey suggested but that did not work as I wanted it. From my example, I don't want the positive and negative $50.00 to show. All I want to see on the report is the $100.00. How do I go about matching debits and credits that are of the same amount?

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    let me just try to clarify

    date inv. no. amount
    ----- ---------- ---------
    1/1/03 100 $ 50.00
    2/1/03 200 $100.00
    3/1/03 300 ($ 50.00)

    read
    $100.00

    would
    1/1/03 100 $ 50.00
    2/1/03 200 $100.00
    3/1/03 300 ($ 50.00)
    4/1/03 400 $ 70.00
    5/1/03 500 $100.00
    6/1/03 600 ($ 50.00)

    read as
    $220
    or
    $100
    $70
    $100
    ($50)
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Sep 2001
    Posts
    175
    For clarification - it would be read as:

    $100
    $70
    $100
    ($50)

  9. #9
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by toukey1
    For clarification - it would be read as:

    $100
    $70
    $100
    ($50)
    Kind of ugly but it works. You will need to create 5 queries with the last one being a UNION.

    Query 1: select where amount > 0
    SELECT Table1.Value FROM Table1 WHERE Table1.Value > 0;

    Query 2: select where amount < 0
    SELECT Table1.Value FROM Table1 WHERE Table1.Value < 0;

    Query 3: unique amounts > 0. Its a join of Query 1 and Query 2
    SELECT Pos.Value FROM Pos LEFT JOIN Neg ON Pos.Value = abs(Neg.Value) WHERE (((Neg.Value) Is Null));

    Query 4: unique amounts < 0. Its a join of Query 1 and Query 2
    SELECT Neg.Value FROM Neg LEFT JOIN Pos ON Neg.Value = Pos.Value * -1 WHERE (((Pos.Value) Is Null));

    Query 5: UNION the Query 3 and Query 4
    select * from uniquepos UNION select * from uniqueneg;

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    looks looks like something that should be done in VB, and the code below is the first method that spring to mind of how to do it

    Code:
    Type invoice
        invDate As Date
        invNo As Integer
        invVal As Currency
    End Type
    
    sub removeEqualNeg()
        Dim neg(0) As invoice, pos(0) As invoice
        Dim rs As DAO.Recordset
        Dim negct As Integer, posct As Integer
        Set rs = CurrentDb.OpenRecordset("invoices", , snapshot)
        If (rs.MoveFirst = Error) Then
            GoTo fin
            While Not EOF
                If rs(2) < 0 Then
                    negct = negct + 1
                    ReDim Preserve neg(negct)
                    neg.invDate = rs(0)
                    neg.invNo = rs(1)
                    neg.invVal = rs(2)
                Else
                    posct = posct + 1
                    ReDim Preserve neg(negct)
                    pos.invDate = rs(0)
                    pos.invNo = rs(1)
                    pos.invVal = rs(2)
                End If
            Wend
            For i = 0 To posct
                For j = 1 To negct
                    If pos(i).invVal = -1 * neg(j).invVal Then
                        For x = i + 1 To posct
                            pos(x - 1) = pos(x)
                        Next
                        posct = posct - 1
                        For x = j + 1 To negct
                            neg(x - 1) = neg(x)
                        Next
                        negct = negcunt - 1
                    End If
                Next
            Next
        End If
        For i = 0 To poscnt
            'Put results where you want them
        Next
        For i = 0 To negcnt
            'Put results where you want them
        Next
    fin: rs.Close
    end sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Nov 2002
    Posts
    150
    Like I said, ugly either way you do it....

  12. #12
    Join Date
    Sep 2001
    Posts
    175
    Thanks - 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
  •