Totals in Report Footer

[Rick B]

I have a report that pulls from an orders detail table. One line per product.

I am printing the Group footer for each order only and summing the Qty, Sell
Price, Cost, Etc. I am also pulling the invoice total sales tax from an
invoice table (not details).

This part all works great. on each line I have:
Total Items =Sum([Qty])
Total Retail =Sum([RetialPrice])
etc.

I also have..
Total Tax =[OrderSummary].[TotalTax]
Invoice totla =[TotalRetail]+[Total Tax]

Each line looks finr.

In my report footer, I used "Sum" to get the total number of items on the
report, the total retail, total cost, etc. No matter what I do, I can't
total the Tax or the Invoice Amount.

If I run it so only one invoice shows up, and it has tax of $1.00 and total
items sold of 3, My tax total ends up being $3.00. This is because my query
is based off a detail table (one record per item) and that is linked to the
Summary Order table where the amount of tax for the whole invoice lives.

Can anyone tell me how to get the report total tax and report total invoice
amounts?

Thanks!!!!

 

[Duane Hookom]

If your tax is based on the summary table, then you can't use Sum() since it
will be multiplied by the number of details. You can reference the value
without using Sum() in the invoice header or footer.

If you have multiple invoices (each having their own tax amount), you can
use a running sum on a text box in the invoice header or footer. If the name
of the running sum tax text box is "txtRunSumTax", you can place a text box
in the report footer with a control source:
=txtRunSumTax