# Thread: How can I accomplish this (if at all)

1. Registered User
Join Date
Sep 2001
Posts
175

## 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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4
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. Registered User
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. Registered User
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)

\$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)

\$220
or
\$100
\$70
\$100
(\$50)

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

\$100
\$70
\$100
(\$50)

9. Registered User
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. Registered User
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```

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

12. Registered User
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
•