Stop Subtotal From Adding Detail Lines

[davec69]

I'm a noob at Access, sorry for the lengthy question.

Can someone tell me how to keep Access from including the detail line items in my report total?

Below is a quick image of part of the report.

The report is compiled from two tables. The first table has detail information on each line, in four columns:

State Name Amount Quantity (Not used)

AL Store 1 313.94 60
AL Store 2 12.59 1
AZ Store 3 576.45 90
CO Store 4 172.22 24
CO Store 5 502.25 88


The 2nd table is a summary by state with three columns:

State Amount Quantity (Not used)
AL 326.53 61
AZ 576.45 90
CO 674.47 112

I'm trying to make the report group by state, then name, amount and a subtotal for each state. This all works fine in the report.

I want to also add the amount field from the 2nd table (State Subtotals) to the report just for reference. I've managed to make this work as well. (See State Total on image)

The problem that I'm having is that when I try to do a final total of the State Total column in the report footer, it seems to add the (State Subtotals) amount field on every detail line (See red numbers in picture. they are not actually on the report, but Access thinks they are and includes them in the final total).

So far I've tried different joins, hiding various fields, sub reports, ect. I just can't figure out how to stop Access from calculating the detail lines.

Any help would be appreciated.

 

[burrina]

The Report is doing that you tell it to do. Did you create this Report using the Wizard?
Are you using Sum for the State Total?

 

[davec69]

Sorry for the confusion. The field "State Total" in the report footer section is not totaling correctly. It should be around 12500.00 not 130335.94.

What I think is happening is that even though I don't have that field in the detail section of this report, the state totel amount from table 2 is being multiplied for each line (See the numbers in red) and added to the total in the report footer.

For example, from the two tables that I am pulling the report information.

The detail table 1, for the state of "VA" has 3 transactions which total 99.31
The summary table 2 also has a single line for the state of "VA" totaling 99.31

Somehow, on my new report below, Access is adding 99.31 for every line, to get a total of 3 x 99.31 = 297.93. This number does not show in the "State Subtotals" field, in the page footer, but it does show in the total of the report footer. Which gives me this crazy number of 130335.94.

The Report is doing that you tell it to do. Did you create this Report using the Wizard?
Are you using Sum for the State Total?