Sum on multi fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field on a form to store the "total" number of hrs an employee uses
for certain tasks within their day. I have a text box within the form and
have placed the function:

=Sum([
[HrsTrainingAgts]+[HrsClassess]+[HrsMeetings]+[HrsVolunteering]+[HrsProject/Assignment]+[HrsPhoneDuty]+[HrsSeniorAgentReview]+[HrsAbsent]+[HrsTOS/QMF/UeWi]+[HrsReports])

With the function above I get #Error? Is this the correct way to add all
these fields together. I have search for answers in my books and on help and
cannot seem to find the answer. Is this to many fields to total?

Thanks for any help you can give.
 
I have a field on a form to store the "total" number of hrs an employee uses
for certain tasks within their day. I have a text box within the form and
have placed the function:

=Sum([
[HrsTrainingAgts]+[HrsClassess]+[HrsMeetings]+[HrsVolunteering]+[HrsProject/Assignment]+[HrsPhoneDuty]+[HrsSeniorAgentReview]+[HrsAbsent]+[HrsTOS/QMF/UeWi]+[HrsReports])

With the function above I get #Error? Is this the correct way to add all
these fields together. I have search for answers in my books and on help and
cannot seem to find the answer. Is this to many fields to total?

Thanks for any help you can give.
Click to expand...

I see three possible problems here... one possibly serious.

Simplest is that you can only Sum records in a form in the form's
Footer or Header, not in the detail section of the form. It may be
that Sum is inappropriate anyway - if you just want to add all these
values up, simply add them up - no function is needed.

A bit more subtle: if (as is likely) any of these values are NULL, the
whole sum will be NULL. You can get around this by using

NZ([HrsTrainingAgts]) + NZ([HrsClasses]) + NZ(...

The serious problem is that your table structure appears to be
incorrectly normalized. Rather than a different FIELD for each
activity, you should consider a different way of looking at the data:
you have many Employees; you have many Activities; you have a many to
many relationship from Employees to Activities. A better structure
would be to have THREE tables:

Employees
EmployeeID
LastName
FirstName
<etc etc>

Activities
ActivityID <Autonumber Primary Key>
Activity <e.g. Training Agents, Classes, Meetings, Volunteering>

EmployeeHours
EmployeeID <link to Employees>
ActivityDate <if you want to track hours spent each day>
ActivityID <link to Activities>
HoursSpent

The Primary Key of this table would be the THREE fields, EmployeeID,
ActivityDate, ActivityID; if Joe Doakes spent two hours in meetings,
you would add a record to this table with Joe's ID, the date he was
wasting time... ummm... contributing to the meetings, the ActivityID
of Meetings, and 2.0 in the HoursSpent field. You could use a Subform
to fill this table, and a very simple totals query to add up the data,
get percentages of time spent in various activities, sum up the time
spent in training by all employees, etc. etc.


John W. Vinson[MVP]
 
Thank you for your response. I also want you to know that I bought your book
and find it very useful. I have several Access books and find that you really
speak to the developer on this one. I aspire to have your type of knowledge
in Access but this is not my main job. I am building this database for my
administrator who wishes to track employee production. This is a State
Taxation Dept so there are many things to track. I have ask many questions
within Database design and got very helpful answers as well.

Now your response has me questioning my entire design of the tables. I am
now at the report building stage and do not wish to undo all my form design
but, if it will make the database more normalized I will. If I posted my
tables and their fields as I have them could you help me place them in the
correct tables?


John Vinson said:
I have a field on a form to store the "total" number of hrs an employee uses
for certain tasks within their day. I have a text box within the form and
have placed the function:

=Sum([
[HrsTrainingAgts]+[HrsClassess]+[HrsMeetings]+[HrsVolunteering]+[HrsProject/Assignment]+[HrsPhoneDuty]+[HrsSeniorAgentReview]+[HrsAbsent]+[HrsTOS/QMF/UeWi]+[HrsReports])

With the function above I get #Error? Is this the correct way to add all
these fields together. I have search for answers in my books and on help and
cannot seem to find the answer. Is this to many fields to total?

Thanks for any help you can give.
Click to expand...

I see three possible problems here... one possibly serious.

Simplest is that you can only Sum records in a form in the form's
Footer or Header, not in the detail section of the form. It may be
that Sum is inappropriate anyway - if you just want to add all these
values up, simply add them up - no function is needed.

A bit more subtle: if (as is likely) any of these values are NULL, the
whole sum will be NULL. You can get around this by using

NZ([HrsTrainingAgts]) + NZ([HrsClasses]) + NZ(...

The serious problem is that your table structure appears to be
incorrectly normalized. Rather than a different FIELD for each
activity, you should consider a different way of looking at the data:
you have many Employees; you have many Activities; you have a many to
many relationship from Employees to Activities. A better structure
would be to have THREE tables:

Employees
EmployeeID
LastName
FirstName
<etc etc>

Activities
ActivityID <Autonumber Primary Key>
Activity <e.g. Training Agents, Classes, Meetings, Volunteering>

EmployeeHours
EmployeeID <link to Employees>
ActivityDate <if you want to track hours spent each day>
ActivityID <link to Activities>
HoursSpent

The Primary Key of this table would be the THREE fields, EmployeeID,
ActivityDate, ActivityID; if Joe Doakes spent two hours in meetings,
you would add a record to this table with Joe's ID, the date he was
wasting time... ummm... contributing to the meetings, the ActivityID
of Meetings, and 2.0 in the HoursSpent field. You could use a Subform
to fill this table, and a very simple totals query to add up the data,
get percentages of time spent in various activities, sum up the time
spent in training by all employees, etc. etc.


John W. Vinson[MVP]
Click to expand...
 
Thank you for your response. I also want you to know that I bought your book
and find it very useful.
Click to expand...

I wish I could write like that - but it's not my book, it's my
esteemed colleague John Viescas'.
I have several Access books and find that you really
speak to the developer on this one. I aspire to have your type of knowledge
in Access but this is not my main job. I am building this database for my
administrator who wishes to track employee production. This is a State
Taxation Dept so there are many things to track. I have ask many questions
within Database design and got very helpful answers as well.

Now your response has me questioning my entire design of the tables. I am
now at the report building stage and do not wish to undo all my form design
but, if it will make the database more normalized I will. If I posted my
tables and their fields as I have them could you help me place them in the
correct tables?
Click to expand...

I'd suggest that you start a new thread in the Database Design
newsgroup. You are probably right about wanting to normalize: you're
apparently storing data (types of expenses) in fieldnames. If you ever
have to add a new kind of expense, with your current structure it's
necessary to restructure your tables anyhow (not to mention all your
queries, forms, reports, etc). It may be better to do it now to get a
structure that has the flexibility to handle such changes.

John W. Vinson[MVP]
 
Back
Top