Create formula for "Total Hours" field

[Celestej]

My question will show what an obvious Newbie I am to Access!

I am using Access 2007 to maintain a database of volunteers. The form was
created before I arrived and one field is named "Total Hours" but is not
used as a calculated field. There are numerous other fields where I enter
"hours worked in '06, 07, 08 etc. ...and I would like to change the Total
Hours column to be a calculated value rather than one I must manually
calculate and enter. Is this possible and if so, how? Ultimately, I would
like to enter a volunteer's current hours and have it update the Total Hours
for each record.

 

[John W. Vinson]

My question will show what an obvious Newbie I am to Access!

I am using Access 2007 to maintain a database of volunteers. The form was
created before I arrived and one field is named "Total Hours" but is not
used as a calculated field. There are numerous other fields where I enter
"hours worked in '06, 07, 08 etc. ...and I would like to change the Total
Hours column to be a calculated value rather than one I must manually
calculate and enter. Is this possible and if so, how? Ultimately, I would
like to enter a volunteer's current hours and have it update the Total Hours
for each record.

It sounds like your table structure is more like a spreadsheet than like a
relational database! Do you in fact have a different *FIELD* for each year?
What do you do in January - alter the structure of your table (and all your
queries and all your forms and all your reports) to add a new field for the
year? OUCH!

Much better would be to have two tables in a one to many relationship:
Volunteers, with fields VolunteerID, LastName, FirstName, and other
biographical data; and a work table with either one record per year, or
(perhaps better) one record per "work episode". This would let you run a
Totals query summing the hours for a volunteer.

In no case should you store the Total Hours anywhere in any table - just
calculate it on demand.