Add new tables to database

[LMB]

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

 

[Steve Schapel]

Linda,

Looks good to me.

 

[LMB]

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]

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.

 

[LMB]

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

 

[Jay Vinton]

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

 

[Jay Vinton]

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

 

[Steve Schapel]

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!

 

[LMB]

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

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.

 

[LMB]

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

Thanks,
Linda

 

[Steve Schapel]

Linda,

I knew that No, it doesn't make any difference, in fact that's the
whole point... your proposed table structure allows for this, as it
reflects the real-life relationships between the real-life data
elements. The data in the Assignments table will look something like this:

AssignmentID EmployeeID WorkArea AssDate Hours
1 1 CV 28/6/04 12
2 2 ER 28/6/04 8
3 1 ER 29/6/04 8
4 2 NICU 29/6/04 6
5 2 CV 29/6/04 6
6 1 NICU 30/6/04 12
7 2 NICU 30/6/04 12
.... so you see, it allows for any given Work Area to have more than one
Employee working there on any given day, and it allows for any given
Employee to work in more than one Work Area, including on any given day.
As I understand your requirements, this model should be ideal for your
purposes. And you will be able to easily use queries to obtain
summaries, per Employee, or per Work Area, or per date.

 

[LMB]

Faith...I need to have faith...Thanks

Linda,

I knew that No, it doesn't make any difference, in fact that's the
whole point... your proposed table structure allows for this, as it
reflects the real-life relationships between the real-life data
elements. The data in the Assignments table will look something like this:

AssignmentID EmployeeID WorkArea AssDate Hours
1 1 CV 28/6/04 12
2 2 ER 28/6/04 8
3 1 ER 29/6/04 8
4 2 NICU 29/6/04 6
5 2 CV 29/6/04 6
6 1 NICU 30/6/04 12
7 2 NICU 30/6/04 12
... so you see, it allows for any given Work Area to have more than one
Employee working there on any given day, and it allows for any given
Employee to work in more than one Work Area, including on any given day.
As I understand your requirements, this model should be ideal for your
purposes. And you will be able to easily use queries to obtain
summaries, per Employee, or per Work Area, or per date.