Calculation on an MS Access Form and updating table

[lmmoorewi]

I created an MS Access form for emmployees time cards and tasks.

I created a table (Hours) to store all of the entries for each employee. How can I create a calculation on a form and have the data stored in the table?

Form:
Sunday Hours
Monday Hours
Tuesday Hours
Wednesday Hours
Thursday Hours
Friday Hours
Saturday Hours
Total Hours - this is ths field that will sum all of the fields above (Sunday Hours, Monday Hours etc.).

I also want the Total Hours stored in the table I created for the hours.

 

[dhookom]

Why do you want to store the total hours when this can always be calculated from the other field values. This is generally considered bad practice.

I would also recommend storing the hours in a normalized table where each hour value is stored in its own record.

Duane
Hook'D on Access
MS Access MVP

 

[lmmoorewi]

Hi -

Thanks for responding.

I wanted to store the total hours for reporting purposes. This is not a good idea?

What is a 'normalized table'? Here is what I have created. Each employee's worklog is in a table in it's own record. Then I created an' Hours' table just to store each employee's worklog hours for that week in it's own record. Was this a good idea/best practice?

Thanks for your help.

 

[PHV]

What is a 'normalized table'?
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lmmoorewi]

Thanks for the information. Yes, all of my tables are normalized - I just didn't know the meaning.

So.....back to my question.

How can I calculate and store the Sum of the Hours.

 

[Dontremb]

Immoorewi,

Back to what Dhookom said, you should NEVER put calculated values in a table. I believe that violates the 1st normalization rule. (I could be mistaken on which rule, but it definitely violates it.)

What you COULD do, if you want to store the calculated hours somewhere, is just set up a query...

On the query, you could have all the calculations you want (that's what they're there for.), and then have your form edit, or update the query.

But yea, never ever have calculated values in a table.

 

[lmmoorewi]

ooohhh, ok I get it.

I will create a query for those calculations.

Thanks for your help!

 

[dhookom]

If you have fields for each day of the week then IMHO, that is not normalized.

Duane
Hook'D on Access
MS Access MVP