Stored calculated report values (2003 DB in 2007)

[BobSullivan]

I have been trying to figure this one out for some time now, and can't come up with a solution. I'm confident that the loungers can help me out. Here's the scenario:

I need to calculate a ratio based on two group footers that are determined via a calulation of data. The problem is that once I change groups, I no longer have the first calculation result, and therefore can't compare it to the second calculated result. Here's the details, and I can provide a mini-database if necessary.

I am writing a report for a trucking company, that compares it's values to those of the industry. These comparisons, for flexibility, can be for a given shipping point (or all of them). Another similar report is for a given destination point (or all of them). And so forth. The information is stored in two tables (one for the industry, one for the company). I've written a union query to stack the data in a single result, and then written reports based on this stacked data. The only thing that is giving me problems is the Cost per hundredweight (CWT) calculation. The calculation is Revenue / Weight / 100. I have a revenue column, I have a weight column. I sort the data by terminal, by company (industry first, company second) so that the calculations come out for each terminal. I can calculate the CWT for the industry by terminal, and I can calculate the CWT for the company. But in the comparison line, which is the Terminal footer section of the report, I need to calculate the ratio of the CWT for the company vs. the industry. So if the CWT for the company calculates to $5, and the industry calculates to $10, the ratio is 50%. When I switch over to the company numbers from the industry numbers, I lose the industry total because it's a new section. I can't seem to recalculate the numbers in the terminal using the same calculated formula.

Report sections:

Report Header
Terminal Header - for a given terminal (or could be all terminals)
Industry header - shows column titles
Detail - Hidden
Industry Footer - shows results first for the Industry, then for the company
Terminal Footer - Shows comparisons in percentages of the whole (except for the CWT calculation)
Report Footer

I'm stumped. Thanks for any help that can be provided.

[HansV]

Could you post a stripped down, compacted and zipped sample database (without proprietary data)? Thanks in advance.

[BobSullivan]

Attached is a zipped, cut-down version of the database. I had to remove about 300,000 records.

The two fields I am interested are the yellow fields. The Green fields are working fine, the yellow is what I need. I need to calculate the percentage of the first green field (Industry total) vs. the second green field (my company total).

To run the report, just click on the button on the form. Don't bother to enter in a terminal, because I don't know what's left and if you don't enter any terminals, you get all of them.

Thanks for any help provided.

[HansV]

You have to perform the sum calculations in the control source of the text box itself. Basically, you want this:

=Sum([Revenue0]*([Carrier]="My Company"))*Sum([weight0]*([Carrier]="Industry"))/(Sum([weight0]*([Carrier]="My Company"))*Sum([Revenue0]*([Carrier]="Industry")))

But this will return #Div/0 if the denominator is 0. To suppress this, you can use IIf to check the denominator:

=IIf(Sum([weight0]*([Carrier]="My Company"))*Sum([Revenue0]*([Carrier]="Industry"))=0,Null,Sum([Revenue0]*([Carrier]="My Company"))*Sum([weight0]*([Carrier]="Industry"))/(Sum([weight0]*([Carrier]="My Company"))*Sum([Revenue0]*([Carrier]="Industry"))))

For the other report, you have to use "My Comp" instead of "My Company".

See the attached version.

Test Database.zip