Add new tables to database

Hi,

I have a database with a table of employees.

tblEmployees
numEmployeeID (autonumber) PK
strEmpLastName


We need to keep track of total hours worked in 5 different work areas.

My plan is to add 2 more tables.

tblWorkArea
numWorkAreaID(autonumberPK)
strWorkAreaName

tblAssignments
numAssignmentID(autonumberPK)
numEmployeeID
numWorkAreaID
dttAssignmentDate
numAssignmentHours

Is this right? Employees can work in several areas on the same day. The
point is to keep track of total hours worked in each area so we can assign
them equally when possible.

Thanks,
Linda

Linda,

Looks good to me.

--
Steve Schapel, Microsoft Access MVP

LMB wrote:
Hi,

I have a database with a table of employees.

tblEmployees
numEmployeeID (autonumber) PK
strEmpLastName


We need to keep track of total hours worked in 5 different work areas.

My plan is to add 2 more tables.

tblWorkArea
numWorkAreaID(autonumberPK)
strWorkAreaName

tblAssignments
numAssignmentID(autonumberPK)
numEmployeeID
numWorkAreaID
dttAssignmentDate
numAssignmentHours

Is this right? Employees can work in several areas on the same day. The
point is to keep track of total hours worked in each area so we can assign
them equally when possible.

Thanks,
Linda

Hmm. After staring at the tables in the relationships view for a while, I
thought I needed to add Employee ID to the tblWorkedAreas or will the
Employee ID in the tblAssignments link the employees with worked areas?....I
am so confused about how these relationships work.

My final goal is to have a report that will look like a spreadsheet with all
employees names showing Vertically and the worked areas across the top.
Then the total hours that employee has worked in an area will be totaled up
by their name under the work area.


CV ER NICU
Linda 12 36 0
Steve 6 54 12


If Steve and Linda are working today, I would send Linda to NICU, and Steve
to CV to even up the hours.

Thanks,
Linda





"Steve Schapel" wrote in message
...
Linda,

Looks good to me.

--
Steve Schapel, Microsoft Access MVP

LMB wrote:
Hi,

I have a database with a table of employees.

tblEmployees
numEmployeeID (autonumber) PK
strEmpLastName


We need to keep track of total hours worked in 5 different work areas.

My plan is to add 2 more tables.

tblWorkArea
numWorkAreaID(autonumberPK)
strWorkAreaName

tblAssignments
numAssignmentID(autonumberPK)
numEmployeeID
numWorkAreaID
dttAssignmentDate
numAssignmentHours

Is this right? Employees can work in several areas on the same day.
The
point is to keep track of total hours worked in each area so we can
assign
them equally when possible.

Thanks,
Linda

Lida,

No, it is not correct to put Employee ID in tblWorkAreas table.
TblAssignments table is where you see which employee works in which work
area. Your original design is correct. There is a one-to-many
relationship between Employee and Assignment, and a one-to-many
relationship between WorkArea and Assignment. The Employees table is
where you store employee-specific information. THe WorkAreas table is
where you store information specific to the work areas (which has got
nothing to do with employees). And the Assignment table is where you
store information about each "event" where a particular employee works
in a particular work area.

When it comes to the point of getting the report as you require, you
will use a Crosstab Query as the basis of the report.

--
Steve Schapel, Microsoft Access MVP

LMB wrote:
Hmm. After staring at the tables in the relationships view for a while, I
thought I needed to add Employee ID to the tblWorkedAreas or will the
Employee ID in the tblAssignments link the employees with worked areas?....I
am so confused about how these relationships work.

My final goal is to have a report that will look like a spreadsheet with all
employees names showing Vertically and the worked areas across the top.
Then the total hours that employee has worked in an area will be totaled up
by their name under the work area.


CV ER NICU
Linda 12 36 0
Steve 6 54 12


If Steve and Linda are working today, I would send Linda to NICU, and Steve
to CV to even up the hours.

Thanks,
Linda

Ok. I'll keep the faith. I have been reading a few books, they all keep
saying the same thing when it comes to normalization and table structure and
I keep getting the feeling that I don't understand, then I read the next
book and it says the same thing but I still can't grasp it so I guess I sort
of know what they are telling me to do and I'll go to the next step and post
on the relationships board, then I'll post on the forms board because I
think I need to make this Assignment table a subform on the Employees form.

I'll trudge on....

Thanks a million

Linda


"Steve Schapel" wrote in message
...
Lida,

No, it is not correct to put Employee ID in tblWorkAreas table.
TblAssignments table is where you see which employee works in which work
area. Your original design is correct. There is a one-to-many
relationship between Employee and Assignment, and a one-to-many
relationship between WorkArea and Assignment. The Employees table is
where you store employee-specific information. THe WorkAreas table is
where you store information specific to the work areas (which has got
nothing to do with employees). And the Assignment table is where you
store information about each "event" where a particular employee works
in a particular work area.

When it comes to the point of getting the report as you require, you
will use a Crosstab Query as the basis of the report.

--
Steve Schapel, Microsoft Access MVP

LMB wrote:
Hmm. After staring at the tables in the relationships view for a while,
I
thought I needed to add Employee ID to the tblWorkedAreas or will the
Employee ID in the tblAssignments link the employees with worked
areas?....I
am so confused about how these relationships work.

My final goal is to have a report that will look like a spreadsheet with
all
employees names showing Vertically and the worked areas across the top.
Then the total hours that employee has worked in an area will be totaled
up
by their name under the work area.


CV ER NICU
Linda 12 36 0
Steve 6 54 12


If Steve and Linda are working today, I would send Linda to NICU, and
Steve
to CV to even up the hours.

Thanks,
Linda

Hi LMB,

If an employee can work in more than 1 area in the same day, then they can probably work in an area more than once in the same day.

To account for this possiblity, I'd remove tblAssignments.numAssignmentHours and replace it with columns for StartTime and EndTime. You can calculate the total hours at runtime.

Jay

I keep getting the feeling that I don't understand, then I read the next

Hi Linda,

It takes time to understand normalization, so don't doubt yourself. Keep at it until third normal form clicks in your head and then give it a rest. If you can get your db into 3NF, you're in good shape Your task might be easier if you first spend time learning how to make entity relationship models.

Jay

Linda,

Yes, it would be good to have a form based on the Assignment table as a
subform on your Empoyees form. You could also have a form based on the
Assignments form as a subform on the WorkAreas form. You could have
both, that would probably be a good idea. I *imagine* in practice
entering the Assignment data via the WorkAreas form would prove to be
the most useful.

Happy trudging!

--
Steve Schapel, Microsoft Access MVP

LMB wrote:
Ok. I'll keep the faith. I have been reading a few books, they all keep
saying the same thing when it comes to normalization and table structure and
I keep getting the feeling that I don't understand, then I read the next
book and it says the same thing but I still can't grasp it so I guess I sort
of know what they are telling me to do and I'll go to the next step and post
on the relationships board, then I'll post on the forms board because I
think I need to make this Assignment table a subform on the Employees form.

I'll trudge on....

Thanks a million

Linda

They are assigned an area for the entire time they work. Most are 12 hour
blocks but sometimes it may be 4 or 6 hours and that's about it. I thought
about including start time and end time but I think the supervisors would
rather just type in 1 number per area instead of 2 for each area worked.

Thanks!

Linda


"Jay Vinton" wrote in message
...
Hi LMB,

If an employee can work in more than 1 area in the same day, then they can
probably work in an area more than once in the same day.

To account for this possiblity, I'd remove
tblAssignments.numAssignmentHours and replace it with columns for StartTime
and EndTime. You can calculate the total hours at runtime.

Jay

There can be 2 or more employees assigned to 1 work area. Does that make
any difference?

Thanks,
Linda

"Steve Schapel" wrote in message
...
Lida,

No, it is not correct to put Employee ID in tblWorkAreas table.
TblAssignments table is where you see which employee works in which work
area. Your original design is correct. There is a one-to-many
relationship between Employee and Assignment, and a one-to-many
relationship between WorkArea and Assignment. The Employees table is
where you store employee-specific information. THe WorkAreas table is
where you store information specific to the work areas (which has got
nothing to do with employees). And the Assignment table is where you
store information about each "event" where a particular employee works
in a particular work area.

When it comes to the point of getting the report as you require, you
will use a Crosstab Query as the basis of the report.

--
Steve Schapel, Microsoft Access MVP

LMB wrote:
Hmm. After staring at the tables in the relationships view for a while,
I
thought I needed to add Employee ID to the tblWorkedAreas or will the
Employee ID in the tblAssignments link the employees with worked
areas?....I
am so confused about how these relationships work.

My final goal is to have a report that will look like a spreadsheet with
all
employees names showing Vertically and the worked areas across the top.
Then the total hours that employee has worked in an area will be totaled
up
by their name under the work area.


CV ER NICU
Linda 12 36 0
Steve 6 54 12


If Steve and Linda are working today, I would send Linda to NICU, and
Steve
to CV to even up the hours.

Thanks,
Linda