Remove Hidden Duplicates from sum

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I have a report that tracks rev/expense. In both rev/expense I have misc
sales and misc expense. Both of these have detail. For example I may have
on a single line(date) a misc expense of $100 that breaks down into detail of
Misc Expense 1 $50, Misc Exoense 2 $30 Misc Expense 3 $20.

If I just run a single line per date with sums at the bottom (like below)
everything works.

Date Sales Misc Expense Misc Expense Detail

01/01/2009 $1000 $0
01/02/2009 $1000 $100


Total $2000 $100

If I break out the detail in Misc Expense and set it to Can Grow = Yes I get
the following

Date Sales Misc Expense Misc Expense Detail

01/01/2009 $1000 $0
01/02/2009 $1000 $100 Exp 1
$50
01/02/2009 $1000 $100 Exp 2
$30
01/02/2009 $1000 $100 Exp 3
$20

Total $4000 $300


By setting all the fields in the record line to be DO NOT SHOW DUPLICATES I
can get the report to look like this (below)

Date Sales Misc Expense Misc Expense Detail

01/01/2009 $1000 $0
01/02/2009 $1000 $100 Exp 1
$50
Exp 2
$30
Exp 3
$20

Total $4000 $300

NOTE that the totals still include the hidden duplicates. My totals are in
text boxes with a simple =sum[fieldname] statement.

My question is: How can I get a sum that DOES NOT INCLUDE the duplicates?

Any help appreciated.



How can I
 
Hi Ray,

Instead of including the miscellaneous detail in the main query and
report, remove it from both and create a subreport for just the detail. Then
the sums will work correctly. If you are not familiar with subreports do a
search in the online help for "subreport". And if you run into trouble, post
back.

Clifford Bass
 
Back
Top