Prev: Multiple Fields Query Question
Next: Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?
From: Steve Stad on 3 May 2010 15:41
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.
From: Jeff Boyce on 3 May 2010 16:05
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.

"Steve Stad" <SteveStad(a)discussions.microsoft.com> wrote in message
news:34C2F324-87F4-4F63-A62D-5B33186F7A3E(a)microsoft.com...
> 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.


From: Steve Stad on 4 May 2010 13:50
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 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.
>
> "Steve Stad" <SteveStad(a)discussions.microsoft.com> wrote in message
> news:34C2F324-87F4-4F63-A62D-5B33186F7A3E(a)microsoft.com...
> > 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.
>
>
> .
>
From: Jeff Boyce on 5 May 2010 12:01
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" <SteveStad(a)discussions.microsoft.com> wrote in message
news:70E56684-C46B-4776-916A-8D84CD79D098(a)microsoft.com...
> 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 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.
>>
>> "Steve Stad" <SteveStad(a)discussions.microsoft.com> wrote in message
>> news:34C2F324-87F4-4F63-A62D-5B33186F7A3E(a)microsoft.com...
>> > 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.
>>
>>
>> .
>>


From: PieterLinden via AccessMonster.com on 5 May 2010 12:34
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/Forums.aspx/access-queries/201005/1

 | 
Pages: 1
Prev: Multiple Fields Query Question
Next: Can you merge a "Count" Crosstab with a "Money Sum" Crosstab?