Can sum on only one of two group footers

[hilian]

I am summing the results of a calculated field from a query in a report. The report is grouped at two levels, and I need to sum at two levels. The amounts I am summing are expenditures by social service agencies. Each agency has several programs. I need a sum for each program and then for each agency. I am able to get a sum for program by putting the field (called Expr10) in the Program group footer. However, when I try to sum the sums by putting the field in the Agency group footer, I get the error message, “Data type mismatch in criteria expression.” I’ve tried it several ways: by simply putting the field in the Agency footer; by putting it in the footer and changing the Running Sum property to “over group;” and by using the formula =sum([Expr10]). I can also put the field in the Agency group footer without putting it in the Program group footer, and it will work. But I can’t put it in both places. I’ve tried other fields in the report, and I can get sums at both levels, just not Expr10. Can anybody help?



Thanks,

Henry

[June7]

Want to provide the project for analysis? Follow instructions at bottom of post.

[hilian]

Want to provide the project for analysis? Follow instructions at bottom of post.

I have attached a zipped copy of the application. I'll be grateful for whatever you find out. Thanks

[June7]

I get the type mismatch no matter where I put the calculation, one or both at a time. At the root of this error is the HeavyDutyCleaning field. It is text instead of number type. Fix the type and the calc works.

Why do you have records in the group footer instead of detail section? This means only the first record of each group is displayed. For example: group 'Agency Number 1: Program Number: 1' shows one record with 1 bunk bed and 1 toddler bed but the number of requests total is 18. Is that the display you want? If what you want are totals for each fee type, then try: instead of textboxes bound directly to the fee type fields, use expression: Sum(IIf([Decision]="Approve", [Full$165],0)). Name the textboxes different from the referenced field to avoid circular reference error, like tbxFull.

The data structure is not normalized and you are hard-coding rates into the field names and calculations. What if rates change?

Should avoid use of spaces, special characters (such as $), punctuation (underscore is exception) in field names and no reserved words as field names.

[hilian]

Want to provide the project for analysis? Follow instructions at bottom of post.

Thank you so much for your careful explanation. The data structure was set up by somebody else in Excel. I thought that Access would be much better for maintaining data integrity, but I didn't want to make too many changes in what people were used to. I see why it's important, to make those changes, though. I'll work through your suggestions and report back.

[June7]

You might want to review the 'sticky' thread tutorials found at http://forums.aspfree.com/microsoft-access-help-18/

[hilian]

I went back to the data table and changed the HeavyDutyCleaning field to number. Access must have interpreted it as text when I imported the table from Excel. I never thought to look. Now I know. I used the expression, Sum(IIf([Decision]="Approve", [Expr10],0)), and it summed in both footers. I am now reviewing the sticky thread tutorials. Very useful.

Many thanks,

Henry