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
"Ray" wrote:
[Quoted Text] > 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.
|