If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Summing multiple fields in matrix/table (qry)
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. |
#3
|
|||
|
|||
Summing multiple fields in matrix/table (qry)
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. . |
#4
|
|||
|
|||
Summing multiple fields in matrix/table (qry)
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. . |
#5
|
|||
|
|||
Summing multiple fields in matrix/table (qry)
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 |
Thread Tools | |
Display Modes | |
|
|