Auto Populating A Column In A Table Using A Formula

[J]

I have the formula:

=((([Estimated Budget (Mat'l) * [% Complete (Mat'l)])+([Estimated
Budget (Trans)]*[% Complete (Trans)])+([Estimated Budget (Labour)]*[%
Complete Labour)]))/(([Estimated Budget (Trans)]+[Estimated Budget
(Labour)]+[Estimated Budget (Mat'l)]))) * 100

which is supposed to find an overall percentage of the work completed
based on monetary values entered in the table. It calculates the total
of work that is done by multiplying the budgets and adding them
together and then dividing this total by the total of all the budgets
added together. This value is then multiplied by 100 to give a
percentage.

My main problem is that I receive an error saying:

"The database engine does not recognize either the field 'Estimated
Billing (Mat'l)' in a validation expression, or the default value in
the table 'Status wk 01'.

The table mentioned above is where I am trying to insert the formula.
I'm entering it into the default value field, I thought that would be
the corrrect place to enter it but I may be wrong.

Any help on this topic would be appreciated.

 

[Rick Brandt]

I have the formula:

=((([Estimated Budget (Mat'l) * [% Complete (Mat'l)])+([Estimated
Budget (Trans)]*[% Complete (Trans)])+([Estimated Budget (Labour)]*[%
Complete Labour)]))/(([Estimated Budget (Trans)]+[Estimated Budget
(Labour)]+[Estimated Budget (Mat'l)]))) * 100

which is supposed to find an overall percentage of the work completed
based on monetary values entered in the table. It calculates the
total of work that is done by multiplying the budgets and adding them
together and then dividing this total by the total of all the budgets
added together. This value is then multiplied by 100 to give a
percentage.

My main problem is that I receive an error saying:

"The database engine does not recognize either the field 'Estimated
Billing (Mat'l)' in a validation expression, or the default value in
the table 'Status wk 01'.

The table mentioned above is where I am trying to insert the formula.
I'm entering it into the default value field, I thought that would be
the corrrect place to enter it but I may be wrong.

Any help on this topic would be appreciated.

Formulas don't go into tables. Those are for raw data only. Use your
expression in forms, reports, and queries. If you only want to write it once
then create a simple SELECT query containing all fields from your table plus a
calculated column based on your expression and then just use the query every
place you would otherwise have used the table.

 

[J]

hmmm.....I Suppose it would work if I had a form that contained the
formula and had an update button so that the information would be
updated and written to the corresponding fields within the table.

 

[Tim Ferguson]

I Suppose it would work if I had a form that contained the
formula and had an update button so that the information would be
updated and written to the corresponding fields within the table.

It would work even better if you just put the formula into a SELECT query
whenever you needed to see the values...


Tim F

 

[Rick Brandt]

hmmm.....I Suppose it would work if I had a form that contained the
formula and had an update button so that the information would be
updated and written to the corresponding fields within the table.

That would work, but it would be the WRONG way to do it.
If it can be calculated it does not need (nor should it be ) stored.

 

[Guest]

There is a problem with putting calculated information back into a table. It
can be illustrated like this: Suppose you have balanced your checking
account, and written the account balance on a piece of paper, which you then
put into your wallet. How long would that account balance be valid? It
would be valid as long as you didn't write any more checks, have a bank fee
paid, have interest put into your account, or have an autopay bill go
through. It is temporary information.

How long will your calculated information in your table be valid? Only as
long as nothing else happens in your database. The point of calculated
information is to either look at it, or print it out (hopefully with a date)
in a report. If this is some sort of archival information that you regularly
compile, then placing it in a table with some appropriate labeling (such as
the date) would be valid, using the same table each time. If you need to
look through this archival information, then the label will allow you to sort
through it as needed.