Aggregate function based on Calculated Control

[kirstenlargent]

I have a Form based on a Select query that pulls all Locations in a certain area. The user enters Square Footage (fieldname “Measured SF”) for each Location in the Detail Section of the form, and the Total Square Footage (fieldname “TotalMeasured”) entered is displayed in the Form Footer. I then have a Percent of Total Square feet calculated field in the Detail Section (fieldname “PercentMeasured”), which simply divides the Square Footage entered by the Total Square Footage - calculation is “=([Measured SF]/[TotalMeasured])”.

All this works fine, until I try to total up the PercentMeasured field in the Form Footer – I get an #ERROR in the calculated total field, and also errors in my calculated PercentMeasured and TotalMeasured fields.

I have read the help file, and it says I can’t use the name of a calculated control in an aggregate function, I need to repeat the expression in the calculated control instead. So instead of “=SUM ([PercentMeasured])”, I tried “=SUM ([Measured SF]/[TotalMeasured])”, but still no luck. I even tried spelling out the whole form name instead of just the fields names in the control, but that didn’t work either. And I made sure it was a number format, using Cint, but that didn’t help either.

HELP!

 

[beetee]

Do any of your records have a '0' in TotalMeasured?

 

[kirstenlargent]

No zeroes, and each record has a numeric value entered for Measured SF.

 

[beetee]

Just checking, because #Error often means a divide by zero or some such thing.

Maybe you can sneak up on the problem by calculating each sum separately in your form footer; then calculate the division based on the the two calculated sums, e.g.

Control SFSum contains
Sum([Measured SF])

Control MeasuredSum contains
Sum([TotalMeasured])

Control PercentMeasured contains
[SFSum] / [MeasuredSum]

 

[kirstenlargent]

I see what you are saying, but this still does not help me with other columns on my form that I also need totaled. The detail section of the form has the calculated controls, and those all work ok. But the moment I try to SUM any of them in the form footer, it gives me an ERROR on EVERY calculated control on the Entire form.
There are no zeroes, and all the fields are filled in, and I can't find out why the SUM is causing that.

Here's how my form is set up:

DETAIL:
Measured PctMeasured Common TotalSqFt
(=Measued/TotMeas) (=CmmnSqFt* (=Measured+
Measured) Common)
FOOTER:
TotMeas. TotPctMeasured TotalCommon TotalTotalSF
(totals ok) (will not total) (will not tot) (will not tot)

The "Measured" field is the only field that is input by the user.
The "Common" field uses CmmnSqFt, which is a fixed number from an underlying table.

The only total that will work is the TotMeasured, since it is not based on a calculated control. None of the other totals will work, even though the related calculated control in the detail section works fine.

Any suggestions?!

 

[beetee]

I think I'm starting to see the problem.

I believe you mentioned that you cannot sum on calculated controls, yet you stated that you are trying to sum on calculated controls in the form footer.

Here's a suggestion:
1) Create a query based on the table used by the form
2) replace all calculated controls on the form with equivalent calculations in the query; then refer to that calculated column in the form control, e.g:
PctMeasured:[Measured]/[TotalMeasured]
then set the control source to PctMeasured.
3) Perform the summations on the columns calculated in the query.

this may be a more efficient solution than calculating sums on expressions in the form footer.

Additionally, you will find it easier to weed out any problems by being able to view the calculated results en-masse in the query.

 

[kirstenlargent]

I tried that too, and the calculations work in the query, but the problem I run into with that is after I create my subtotal query and link it to the main query, the form will no longer let me update the Measured SF field that I need to update on. Am I doing the subtotal query and linking wrong?
I read the help file, and I think it's not going to let me update the Measured SF field because that is the field that I am subtotaling on.
Is there some way around this? I need to be able to update the Measured SF field and have the subtotal change, and the calculated percentages change.
It all works fine in an Excel spreadsheet, I just need to be able to pull the same format into Access so I can do more with it.
Thanks for all your help!

 

[beetee]

I don't think you should aggregate on, or add any queries that aggregate on, the record source of your form (i.e. in the query).

I'm just suggesting that you do the simple calculations in the query, and put the sum functions in your form footer.