Report totals off due to duplicate values - Help...

[Mark]

I have a rpt showing costs charged and denied for invoices. Each invoice can
have multiple line items, and each line item will have a single charge
amount. Likewise, each line item can have multiple denials assoicated with
it. So I have a one to many relationship between line items and denials. So
in my query behind the rpt, if a line item has two denials, the charged
amount for that line item will show up twice, once for each denial. This
throws my totals off because the amount charged for each individual line item
gets duplicated by the number of denials associated with it. Is there a way
to run a sum that only counts the charges once per line item using the line
item id?

Here's a simple example of what the data looks like: (denied_amt and
comments come from the child table)

invoice line_item_id charge_amt denied_amt comments
123 10001 $50.00 $10.00 Text
unique to this denial
123 10002 $100.00 $25.00 Text
unique to this denial
123 10002 $100.00 $15.00 Text
unique to this denial

The total charge amount for invoice #123 should be $150, but my report shows
$250. My brain is fried from trying to figure this one out. Anybody got any
ideas?

Thanks!!!
Mark

 

[Allen Browne]

Use a running sum in the line_item_id group footer to aggregate the total.
Steps:

1. In report design view, open the Sorting And Grouping sheet.

2. On a fresh row, choose the line_item_id field, and set it so it has a
group footer. You will see a new line_item_id_Group_Footer section on your
report.

3. In this section, place a text box with these properties:
Control Source charge_amt
Running Sum Over All
Name txtChargeRS
Visible No

4. In the Invoice group footer section (or in the Report Footer if you want
the total for all invoices), place a text box with these properties:
Control Source =[txtChargeRS]
Format Currency