Filtering a report based upon a subtotal

[donross]

Dear Group:
After my best attempts, I'm admitting defeat on this issue.
I've got a single table, simple in layout. Relevant fields to this
issue are as follows:
Stock Number, Debit, Credit.

Sample table data is as follows.

Stock # Debit Credit
1000 100
1000 100
1001 200
1001 160
1001 50

I have a report which groups the information by stock # and subtotals
the debits and credits. Report looks something like this:

Stock # Debit Credit
1000
100
100
______ ________
Subtotal 100 100

1001 200
160
50
______ _______
Subtotal 200 210

Issue:
I would like to only see references to Stock numbers where the total of
the debits and credits aren't equal (ie they are out of balance).
(Where sum of debits and credits for a particular stock number are
equal, I'd like the report to exclude the information altogether).

I've tried filtering the report based upon this concept and haven't
been able to get it to work.

It could be my approach is flawed and I should be attempting this
through some sort of query.

Any advice would be greatly appreciated.

Respectfully submitted,

Don

 

[Duane Hookom]

Create another query similar to your report's record source. Change it to a
totals/group by query like:

SELECT [Stock Number], Sum([Debit]) - Sum([Credit]) as TheTotal
FROM [a single table]
GROUP BY [Stock Number];

You can then add this query to your report's record source and join the
Stock Number fields. Set the criteria under the [TheTotal] field to <>0.

 

[donross]

Dear Mr. Hookom:

Thanks for your time and advise. It's greatly appreciated.

Respectfully submitted,

Don