Calculating Percents in Report

[ThaBizness]

How do I calculate a "percent of" in a report on a line in the group header when the total sum of what I need to do the "percent of" isn't totally figured out until the group footer?

 

[llkhoutx]

The calculation can probably be done in the header, as well as the footer. If not, reference the footer field in the header.

 

[ThaBizness]

When I do the calculation it only works if I add in the correct # for the total. If I use the formula =[SumOfT_RegularHours]/[TotalRegHours] the number is wrong. If I use =[SumOfT_RegularHours]/244, with 244 being the number that should be in the TotalRegHours it works.

 

[llkhoutx]

Check the value in [TotalRegHours]

 

[ThaBizness]

The value in TotalRegHours is correct when the report is displayed. [TotalRegHours] is in the group footer. My thought is that the value of [TotalRegHours] isn't completely finished computing when Im trying to get the percentage of [TotalRegHours] with the formula =[SumOfT_RegularHours] / [TotalRegHours] in the same group header.

Is that possible?

 

[llkhoutx]

[SumOfT_RegularHours] appears to be a sum, is [TotalRegHours] also a sum?

Post the sql for the data being reported and I'll build sum sums o0n my PC.

 

[ThaBizness]

[SumOfT_RegularHours] is a a sum of hours worked on a specific job type such as Service Calls, Inventory,or Sales Support... The [TotalRegHours] is the total sum of the hours for the individual person over all the job types. The percent is the % of time spent on any specific job type when compared to total time.

The data being reported is from 9 queries which dumps data into a table and the report is driven from that.

I would send the file - but its 143meg. Would you like to see the access file with out the data in it? That would give you a idea what I am doing.

 

[llkhoutx]

Try totaling in hidden cells, then use those cells in computing the percentage; then you know that they have been computed.

"Where there's a will, there a way to get into it."

 

[ThaBizness]

I tried that. Ok. Here is a access file that has the report and a small snap shot of the data from the table. Looking at this would proabably help you in my dilema. I took out my attempts to calculate the percentage.


Thank you for all of your help.

 

[llkhoutx]

I could calculate it. See attached mdb.

 

[ThaBizness]

Sorry but I was looking for the percent of the individual groups like Corporate Functions, Customer Issues, Hardware Repair and Maint.

Like on Page 1 on the Current Month I want to calculate the percent of time spent doing Corporate Functions or the calulation 3.92/244 and display that percent in the percent column. Then the percent of time doing Customer Issues or the calculation 6.25/244.

Is that possible?

 

[llkhoutx]

You were right, the total hasn't been calculated until the group is completed, and therefore cannot be used within the group.

You're going to have to build the total elsewhere with a couple queries, then join the result with your report recordsource.

Multiple queries run very very fast, as you probably know.