Default Value In Table To Be Calculated Field?

[Guest]

I'm trying to create a simple table that has an employee's hourly wage rate,
fringe benefit rate, and then a total wage amount. The Total amount being
the sum of the hourly and fringe fields. Am I able to set a default value
for the "Total" field to be the sum of "Hourly" and "Fringe" or is better not
to do this according to best practices database design? I can do a default
value in a form to accomplish this, but don't know how to make it store the
value in a "Total" field in the hosting table for that form. Thanks in
advance for anyone's opinion on this matter. I'm not quite the Access guru
as you can tell from this.

 

[Rick Brandt]

I'm trying to create a simple table that has an employee's hourly
wage rate, fringe benefit rate, and then a total wage amount. The
Total amount being the sum of the hourly and fringe fields. Am I
able to set a default value for the "Total" field to be the sum of
"Hourly" and "Fringe" or is better not to do this according to best
practices database design? I can do a default value in a form to
accomplish this, but don't know how to make it store the value in a
"Total" field in the hosting table for that form. Thanks in advance
for anyone's opinion on this matter. I'm not quite the Access guru
as you can tell from this.

It is improper database design to store such a calculation. Create a query
based on your table (from which you have removed the Total field) and in the
query produce the total as a calculated field. Now simply use this query every
place you are now using the table (or at least in those places where you need
the Total).

You are using a computer so let it compute.

 

[Brendan Reynolds]

It is better not to do it in the table. You can easily calculate it as
required in forms, reports, or queries.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

That's kind of what I thought. Just not too confident yet on mixing tables
and queries on a form. I'm the naive Access developer at this stage that
reluctantly tries to store the world on one table and have my forms reference
one and only one table. I'll get it right someday. I'm comfortable doing
the calculated field in a query (as I should be doing).

Thanks for the advice, Rick! I'll make sure to rate your response as soon
as I figure it out. My first time here in the discussion groups.

Jay

 

[Rick Brandt]

That's kind of what I thought. Just not too confident yet on mixing
tables and queries on a form. I'm the naive Access developer at this
stage that reluctantly tries to store the world on one table and have
my forms reference one and only one table. I'll get it right
someday. I'm comfortable doing the calculated field in a query (as I
should be doing).

Thanks for the advice, Rick! I'll make sure to rate your response as
soon as I figure it out. My first time here in the discussion groups.

Jay

The only time using queries in a form can become problematic is if the query is
trying to aggregate or includes multiple tables. A query that is essentially
the source table with a few calculated fields added and some sorting imposed
will work in a form just the same as the source table would.

As a convention it is a good idea to display the calculated fields on your form
differently so as to convey to the user that they are not editable. I usually
display them in a TextBox with a transparent border and background. The users
will instinctively pick up on the fact that only the values "Inside the boxes"
are editable.

 

[Guest]

Thank you, Brendan. I kinda figured that would be the best answer. I'm just
one of the Access folks that needs the security blanket of having the data
(even from a calculated field) in the table. I'm trying to change, just a
bit stubborn. Thanks again for the advice!

 

[Guest]

Thanks, Rick. I like that concept. I'm trying to "leave the nest" in Access
and not cling to my security blanket of having to have all the data (even
calculated fields) all stuffed into the table. I like how you put it...
"You are using a computer... so let it compute". Very sage advice. Thanks
again!