Summing multiple fields in matrix/table (qry)

My form populates a table with Products, Employees, and Emp hours worked on
each product.
Example:

Product Emp1 Emp1hrs Emp2 Emp2hrs Emp3 Emp3hurs
PLANES JOE 1 BILL 4 MARY 5
TRAINS JIM 2 JOE 3 BILL 3
CARS John 3 Mark 1 Joe 5

I would like to quickly add any or all employees hours for all products
worked for ALL products. For example, Joes total hours = 9 which = 1+3+5.
How can I do this with this table/query layout.

Steve

If that's the table structure you are using, you have committed spreadsheet
on Access. Access is a relational database, and its features and functions
are optimized for well-normalized data, not 'sheet data.

With the design you describe, you will have to modify your table, your
(related) queries, your (related) forms, your (related) reports, etc. EVERY
time you decide to change the number of employees for which you are
tracking.

Consider the following structu

tblProduction
ProductionID
EmployeeID (points at an Employee table's primary key -- no need to
repeat "Bob" ... or misspell it!)
ProductID (points at a Product table's primary key -- no need to
repeat "Airplane" ... or misspell it!)
EmpHours
?DateProduced

To find out the sum of hours per product, use a simple query.

To find out the sum of hours per employee, use a simple query.

To find out the sum of hours for Airplanes produced after 1/1/2010, use a
simple query.

This is a pay now (normalize your table structure) or pay later (keep having
to modify everything everytime something changes) situation.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Steve Stad" wrote in message
...
My form populates a table with Products, Employees, and Emp hours worked
on
each product.
Example:

Product Emp1 Emp1hrs Emp2 Emp2hrs Emp3 Emp3hurs
PLANES JOE 1 BILL 4 MARY 5
TRAINS JIM 2 JOE 3 BILL 3
CARS John 3 Mark 1 Joe 5

I would like to quickly add any or all employees hours for all products
worked for ALL products. For example, Joes total hours = 9 which = 1+3+5.
How can I do this with this table/query layout.

Jeff - How would you enter/add employee Nme, hrs, etc to the products table.
I need to add up to 35 employee Names, hrs, etc. to the products table for
any given product.

"Jeff Boyce" wrote:

Steve

If that's the table structure you are using, you have committed spreadsheet
on Access. Access is a relational database, and its features and functions
are optimized for well-normalized data, not 'sheet data.

With the design you describe, you will have to modify your table, your
(related) queries, your (related) forms, your (related) reports, etc. EVERY
time you decide to change the number of employees for which you are
tracking.

Consider the following structu

tblProduction
ProductionID
EmployeeID (points at an Employee table's primary key -- no need to
repeat "Bob" ... or misspell it!)
ProductID (points at a Product table's primary key -- no need to
repeat "Airplane" ... or misspell it!)
EmpHours
?DateProduced

To find out the sum of hours per product, use a simple query.

To find out the sum of hours per employee, use a simple query.

To find out the sum of hours for Airplanes produced after 1/1/2010, use a
simple query.

This is a pay now (normalize your table structure) or pay later (keep having
to modify everything everytime something changes) situation.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Steve Stad" wrote in message
...
My form populates a table with Products, Employees, and Emp hours worked
on
each product.
Example:

Product Emp1 Emp1hrs Emp2 Emp2hrs Emp3 Emp3hurs
PLANES JOE 1 BILL 4 MARY 5
TRAINS JIM 2 JOE 3 BILL 3
CARS John 3 Mark 1 Joe 5

I would like to quickly add any or all employees hours for all products
worked for ALL products. For example, Joes total hours = 9 which = 1+3+5.
How can I do this with this table/query layout.


.

Hold on! If you have a table that lists products, an attribute of a product
is NOT how long, or who. Check the suggested structure again. It only
covers how to relate (remember, "relational") persons and products and
hours.

You'll still need your Products table, and your Employees table, each with
their own lists (of products, and of employees, respectively).

If you want to SEE the hours per product, or employees-working-on-product,
use queries.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Steve Stad" wrote in message
...
Jeff - How would you enter/add employee Nme, hrs, etc to the products
table.
I need to add up to 35 employee Names, hrs, etc. to the products table for
any given product.

"Jeff Boyce" wrote:

Steve

If that's the table structure you are using, you have committed
spreadsheet
on Access. Access is a relational database, and its features and
functions
are optimized for well-normalized data, not 'sheet data.

With the design you describe, you will have to modify your table, your
(related) queries, your (related) forms, your (related) reports, etc.
EVERY
time you decide to change the number of employees for which you are
tracking.

Consider the following structu

tblProduction
ProductionID
EmployeeID (points at an Employee table's primary key -- no need
to
repeat "Bob" ... or misspell it!)
ProductID (points at a Product table's primary key -- no need to
repeat "Airplane" ... or misspell it!)
EmpHours
?DateProduced

To find out the sum of hours per product, use a simple query.

To find out the sum of hours per employee, use a simple query.

To find out the sum of hours for Airplanes produced after 1/1/2010, use a
simple query.

This is a pay now (normalize your table structure) or pay later (keep
having
to modify everything everytime something changes) situation.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Steve Stad" wrote in message
...
My form populates a table with Products, Employees, and Emp hours
worked
on
each product.
Example:

Product Emp1 Emp1hrs Emp2 Emp2hrs Emp3 Emp3hurs
PLANES JOE 1 BILL 4 MARY 5
TRAINS JIM 2 JOE 3 BILL 3
CARS John 3 Mark 1 Joe 5

I would like to quickly add any or all employees hours for all products
worked for ALL products. For example, Joes total hours = 9 which =
1+3+5.
How can I do this with this table/query layout.


.

Steve Stad wrote:
My form populates a table with Products, Employees, and Emp hours worked on
each product.
Example:

Product Emp1 Emp1hrs Emp2 Emp2hrs Emp3 Emp3hurs
PLANES JOE 1 BILL 4 MARY 5
TRAINS JIM 2 JOE 3 BILL 3
CARS John 3 Mark 1 Joe 5

I would like to quickly add any or all employees hours for all products
worked for ALL products. For example, Joes total hours = 9 which = 1+3+5.
How can I do this with this table/query layout.

Consider a different structure... I have been down this road (not of my own
making), and it is NOT fun.

WorksOn(
ProductID int,
EmployeeID int,
WorkDate date,
Hours decimal
)

Now you can have a million instances of someone working on something and it
all gets summarized in ONE query

SELECT EmployeeID, ProductID, DatePart("w",WorkDate) As WorkWeek, SUM(Hours)
FROM WorksOn
GROUP BY EmployeeID, ProductID, WorkWeek;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1