Sum in footer multiplied

[Scoob55k]

I'm thinking this is an easy one and I've figured it out before. Anyway, not today.

I have a report that has a couple of headers and footers asssigned in the sorting & grouping section. It was set up originally using the wizard summing feature, but changes, of course, have arose.

We are summing each footer that ends up with 2 subtotals and a grand total. The problem is the first subtotal is throwing the others off as it is multiplying the total by the number of "Unit's" the report has. Meaning, on the data in the report I am viewing now, it has 2 units of which the total assigned should equal 8 and the values are 6 and 2, but the subtotal is 16.

If the report only lists 1 unit it is correct. If it had 3 units, it would be tripled. I moved the data fields from the detail section to one of the headers so it would not show that data multiple times, but the sum in the footer is still adding them.

Thanks!!

 

[PaulBricker]

Can you give us more info. What is the Control Source for the textbox in the footer? It's hard to diagnose without more info.

Paul

 

[Scoob55k]

OK, hopefully I can make sense of this to you.

The report is broken down and summarized by MANAGER, UNIT and EMPLOYEE. The data for the Detail section is simply a tally of occurrences. The MANAGER has a header and summed values in the footer section. The UNIT has a header and summed values in the footer section while the EMPLOYEE only has a header. The sum that is multiplying is the summed values in the UNIT footer section [=Sum([SFPSPAssigned])] where SFPSPAssigned is a numerical value.

Where the problem in the sum arises is when the MANAGER has more than one UNIT as it will multiply the SFPSPAssigned value by the number of units the manager has. I moved the SFPSPAssigned textbox from the detail section of the report to the EMPLOYEE header as it would show the detail record in multiples of the number of units. So even though it only shows the detail record once now per EMPLOYEE, it still is summing up what would have showed in the Detail section before I moved the text boxes.

Please let me know and I appreciate your help. Thanks!!

 

[PaulBricker]

If SFPSPAssigned relates to something that an employee does then you should probably have those SFPSPAssigned values in the Detail section and have an Employee Footer and Sum the value there. Then sum those values inside the Unit Footer to get the totals for each employee. Then sum those in the Manager footer to get total units for each manager.

Try it and let me know how it goes.

Paul

 

[Scoob55k]

It shows the record per employee in the multiple of the amount of units the manager has on the reports, in this specific case, 2 units or twice.

We do not want the sum for the employee as it is only one record for the employee per report. I tired it, but the report still multiplies the data per the number of units.

I know how to code it or pull the data via a DLookup, however was trying to use the "simpler" solution so a co-worker would understand more easily. Like I said, I feel I've figured it out before, but it escapes me at this time.

Again, I appreciate your assistance on trying to find a solution this time around. Thanks!!

 

[Scoob55k]

I so apologize, however the issue has been solved. The issue was in the query actually. I had to change the setting for Unique Values to Yes and all is good.

Again, thanks so much for your assitance and accept my apology. Have a great Monday!!

 

[PaulBricker]

Glad you got it.

Paul