calculated field

[Bob76]

I've set up a table with the following fields:
Emp#, Name, AnnualSalary, MonthlySalary, LTDBenefit

I have a value for AnnualSalary..I would like to have the following
calcualted fields:
MonthlySalary = AnnualSalary/12 and
LTDBenefit = AnnualSalary*0.667

i read in a book about using Expression builder to do this..I tried to put
this in the designView under the field's Validation Rule, as such:
=[AnnualSalary]/12
but i keep on getting a syntax error... What is wrong? How/where should i do
this calculation?

Thanks, Bob76

 

[Tom Wickerath]

Hi Bob,

I've set up a table with the following fields:
Emp#, Name, AnnualSalary, MonthlySalary, LTDBenefit

It is best to avoid the use of special characters and reserved words,
including the # sign, spaces, etc. in anything that you assign a name to
within Access:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Also, "Name" is considered a reserved word. You should avoid using any
reserved
words when assigning names to anything in Access (fields, tables, queries,
forms, reports, macros, modules, variables, controls on forms & reports,
etc.). Access MVP Allen Browne maintains an extensive list of reserved words
here:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

He also offers a free utility, called the "Database Issue Checker Utility".
This is a .mdb file, which you can use to check your existing Access
databases for reserved words.

Notes:
It covers fields, tables and queries only (not controls, variable names, etc.)
Your database should be closed when using this utility to test your database.


In general, you do not want to store values in tables that can be
calculated. Doing so violates normalization rules of database design. In some
cases, people will store values that can be calculated to help with
performance issues, but this is best done in a database that supports
triggers, so that if one of the values is changed, the calculated value will
be automatically updated. Check out the two database design papers written by
Michael Hernandez, here:


http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
(See the first download titled "Understanding Normalization")

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I've set up a table with the following fields:
Emp#, Name, AnnualSalary, MonthlySalary, LTDBenefit

I have a value for AnnualSalary..I would like to have the following
calcualted fields:
MonthlySalary = AnnualSalary/12 and
LTDBenefit = AnnualSalary*0.667

i read in a book about using Expression builder to do this..I tried to put
this in the designView under the field's Validation Rule, as such:
=[AnnualSalary]/12
but i keep on getting a syntax error... What is wrong? How/where should i do
this calculation?

Thanks, Bob76

 

[Jeanette Cunningham]

Hi Bob 76,
you do the calculation in a query based on the table.
Instead of storing the monthly salary and the ltdbenefit, you calculate them
when you need them.

If you wanted to store them in a table, you would create a field called
MonthlySalary and one called LTDBenefit.

Use a query to update the value of MonthlySalary.
Create a select query using the field called MonthlySalary.
Change the query to an update query.
In the update to row, put ([NameOfTable].[MonthlySalary])/12

Run the query.
Now open the table and see the values for monthly salary.

Do a similar thing for ltd benefit.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Bob76]

Hi Jeanette,
Thanks a lot!! I did exactly what you instructed and it worked!!!

Have another question:
What do you put in the "update to" for this case:
If the field "Status" is F, update the fixed amt of "22.00" into the field
"HealthPremium".
If the field "Status" is S, update "55.00" to "HealthPremium"

Thanks, Bob76

Hi Bob 76,
you do the calculation in a query based on the table.
Instead of storing the monthly salary and the ltdbenefit, you calculate them
when you need them.

If you wanted to store them in a table, you would create a field called
MonthlySalary and one called LTDBenefit.

Use a query to update the value of MonthlySalary.
Create a select query using the field called MonthlySalary.
Change the query to an update query.
In the update to row, put ([NameOfTable].[MonthlySalary])/12

Run the query.
Now open the table and see the values for monthly salary.

Do a similar thing for ltd benefit.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I've set up a table with the following fields:
Emp#, Name, AnnualSalary, MonthlySalary, LTDBenefit

I have a value for AnnualSalary..I would like to have the following
calcualted fields:
MonthlySalary = AnnualSalary/12 and
LTDBenefit = AnnualSalary*0.667

i read in a book about using Expression builder to do this..I tried to put
this in the designView under the field's Validation Rule, as such:
=[AnnualSalary]/12
but i keep on getting a syntax error... What is wrong? How/where should i
do
this calculation?

Thanks, Bob76

 

[Jeanette Cunningham]

Easiest to do it as 2 queries.

Create a query with the status field and the health premium.
In the criteria for status put F and uncheck the checkbox for show for this
field.
Change the query to an update query and in the update to row for health
premium put "$22.00"

Run the query.

Change the query for the other combination of status and health premium.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,
Thanks a lot!! I did exactly what you instructed and it worked!!!

Have another question:
What do you put in the "update to" for this case:
If the field "Status" is F, update the fixed amt of "22.00" into the
field
"HealthPremium".
If the field "Status" is S, update "55.00" to "HealthPremium"

Thanks, Bob76

Hi Bob 76,
you do the calculation in a query based on the table.
Instead of storing the monthly salary and the ltdbenefit, you calculate
them
when you need them.

If you wanted to store them in a table, you would create a field called
MonthlySalary and one called LTDBenefit.

Use a query to update the value of MonthlySalary.
Create a select query using the field called MonthlySalary.
Change the query to an update query.
In the update to row, put ([NameOfTable].[MonthlySalary])/12

Run the query.
Now open the table and see the values for monthly salary.

Do a similar thing for ltd benefit.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I've set up a table with the following fields:
Emp#, Name, AnnualSalary, MonthlySalary, LTDBenefit

I have a value for AnnualSalary..I would like to have the following
calcualted fields:
MonthlySalary = AnnualSalary/12 and
LTDBenefit = AnnualSalary*0.667

i read in a book about using Expression builder to do this..I tried to
put
this in the designView under the field's Validation Rule, as such:
=[AnnualSalary]/12
but i keep on getting a syntax error... What is wrong? How/where should
i
do
this calculation?

Thanks, Bob76

 

[Bob76]

Thanks so much! Got it to work as per your suggestion, really appreciate
your prompt reply and big help.

I started Access'07 databases last year but this is my 1st time to deal w/
calculated fields.. Am getting the hang of it, thanks!
Regards,
Bob76

Easiest to do it as 2 queries.

Create a query with the status field and the health premium.
In the criteria for status put F and uncheck the checkbox for show for this
field.
Change the query to an update query and in the update to row for health
premium put "$22.00"

Run the query.

Change the query for the other combination of status and health premium.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,
Thanks a lot!! I did exactly what you instructed and it worked!!!

Have another question:
What do you put in the "update to" for this case:
If the field "Status" is F, update the fixed amt of "22.00" into the
field
"HealthPremium".
If the field "Status" is S, update "55.00" to "HealthPremium"

Thanks, Bob76

Hi Bob 76,
you do the calculation in a query based on the table.
Instead of storing the monthly salary and the ltdbenefit, you calculate
them
when you need them.

If you wanted to store them in a table, you would create a field called
MonthlySalary and one called LTDBenefit.

Use a query to update the value of MonthlySalary.
Create a select query using the field called MonthlySalary.
Change the query to an update query.
In the update to row, put ([NameOfTable].[MonthlySalary])/12

Run the query.
Now open the table and see the values for monthly salary.

Do a similar thing for ltd benefit.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I've set up a table with the following fields:
Emp#, Name, AnnualSalary, MonthlySalary, LTDBenefit

I have a value for AnnualSalary..I would like to have the following
calcualted fields:
MonthlySalary = AnnualSalary/12 and
LTDBenefit = AnnualSalary*0.667

i read in a book about using Expression builder to do this..I tried to
put
this in the designView under the field's Validation Rule, as such:
=[AnnualSalary]/12
but i keep on getting a syntax error... What is wrong? How/where should
i
do
this calculation?

Thanks, Bob76