Summing multiple fields in matrix/table (qry)

  • Thread starter Thread starter Steve Stad
  • Start date Start date
S

Steve Stad

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 structure:

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.
 
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.
 
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.
 
Back
Top