Employee Database

  • Thread starter Thread starter LMB
  • Start date Start date
L

LMB

Hi Everyone,

I am starting a new database. I made one a couple of years ago but it's a nightmare. I thought I would go step by step with you guys. The first 2 tables are going to be tblEmployee and tblShift. Some employees work both shifts so should I need 3 tables? I will eventually want to have separate reports which will bring up a list of night shift workers and one for day shift workers. Confession, in my first database, I put shift in the Employee Table.

Here is what I am thinking

tblEmployees
EmployeeID
EmployeeLastName
EmployeeFirstName
Etc....

tblShift
ShiftID
Shift

Do I need another table?

tblShiftWorked
ShiftWorkedID
ShiftID
EmployeeID

As I look at these tables, should I put shift ID in the Employee Table?

Thanks,
Linda
 
Linda,

You need to give further thought as to specifically what you eventually want
to do with the database. You need to consider if you need the hours a shift
spans and the date worked. The date worked needs careful consideration if
say Shift 3 is something like 10PM -6AM spanning two dates. You may need
just a start workdate. You also need to consider how to handle the situation
where an employee works a double.

Steve
PC Datasheet

Hi Everyone,

I am starting a new database. I made one a couple of years ago but it's a
nightmare. I thought I would go step by step with you guys. The first 2
tables are going to be tblEmployee and tblShift. Some employees work both
shifts so should I need 3 tables? I will eventually want to have separate
reports which will bring up a list of night shift workers and one for day
shift workers. Confession, in my first database, I put shift in the
Employee Table.

Here is what I am thinking

tblEmployees
EmployeeID
EmployeeLastName
EmployeeFirstName
Etc....

tblShift
ShiftID
Shift

Do I need another table?

tblShiftWorked
ShiftWorkedID
ShiftID
EmployeeID

As I look at these tables, should I put shift ID in the Employee Table?

Thanks,
Linda
 
Shift is used just as information as to what shift the employee works. Long term plan is to keep track of hours worked, not the dates worked then only print out the night shift employees list along with the total types of hours they work so decisions can be made based on number of hours. We only have two 12 hour shifts. I guess thinking about it and talking to one of the supervisors, there are not that many people who work both shifts and when they have someone who is they just print out a list of all shifts and can see who has worked more hours.

Thanks,
Linda


Linda,

You need to give further thought as to specifically what you eventually want
to do with the database. You need to consider if you need the hours a shift
spans and the date worked. The date worked needs careful consideration if
say Shift 3 is something like 10PM -6AM spanning two dates. You may need
just a start workdate. You also need to consider how to handle the situation
where an employee works a double.

Steve
PC Datasheet

Hi Everyone,

I am starting a new database. I made one a couple of years ago but it's a
nightmare. I thought I would go step by step with you guys. The first 2
tables are going to be tblEmployee and tblShift. Some employees work both
shifts so should I need 3 tables? I will eventually want to have separate
reports which will bring up a list of night shift workers and one for day
shift workers. Confession, in my first database, I put shift in the
Employee Table.

Here is what I am thinking

tblEmployees
EmployeeID
EmployeeLastName
EmployeeFirstName
Etc....

tblShift
ShiftID
Shift

Do I need another table?

tblShiftWorked
ShiftWorkedID
ShiftID
EmployeeID

As I look at these tables, should I put shift ID in the Employee Table?

Thanks,
Linda
 
It sounds like you want to track total hours worked during the year for the
purpose of allocating OT. That being the case, it seems you need to have two
fields, HoursWorked and EndOfWeek, in tblShiftWorked. This assumes an
employee works the same shift for the whole week. If there is the
possibility an employee may work different shifts during the week, then you
need the field WorkDate rather than EndOfWeek. This table then gives you the
ability to print out the night shift employees list along with the total
types of hours they work as well as the total hours for any employee. You
would use a totals query for these. TblHoursWorked might make more sense for
the name of the table.

Steve
PC Datasheet


Shift is used just as information as to what shift the employee works. Long
term plan is to keep track of hours worked, not the dates worked then only
print out the night shift employees list along with the total types of hours
they work so decisions can be made based on number of hours. We only have
two 12 hour shifts. I guess thinking about it and talking to one of the
supervisors, there are not that many people who work both shifts and when
they have someone who is they just print out a list of all shifts and can
see who has worked more hours.

Thanks,
Linda


Linda,

You need to give further thought as to specifically what you eventually want
to do with the database. You need to consider if you need the hours a shift
spans and the date worked. The date worked needs careful consideration if
say Shift 3 is something like 10PM -6AM spanning two dates. You may need
just a start workdate. You also need to consider how to handle the situation
where an employee works a double.

Steve
PC Datasheet

Hi Everyone,

I am starting a new database. I made one a couple of years ago but it's a
nightmare. I thought I would go step by step with you guys. The first 2
tables are going to be tblEmployee and tblShift. Some employees work both
shifts so should I need 3 tables? I will eventually want to have separate
reports which will bring up a list of night shift workers and one for day
shift workers. Confession, in my first database, I put shift in the
Employee Table.

Here is what I am thinking

tblEmployees
EmployeeID
EmployeeLastName
EmployeeFirstName
Etc....

tblShift
ShiftID
Shift

Do I need another table?

tblShiftWorked
ShiftWorkedID
ShiftID
EmployeeID

As I look at these tables, should I put shift ID in the Employee Table?

Thanks,
Linda
 
Yes, there will be many HoursWorked type tables...MTOHours, VTOHours, VTOOnCallHours, OnCallHoursWorked, and OnCallHoursApproved. That is my next question to post....when I get there. We will keep track forever in the tables but in a query I'll report the hours for a rolling 90 day period for some, Quarterly for some and for all time in another.

Thanks,
Linda
It sounds like you want to track total hours worked during the year for the
purpose of allocating OT. That being the case, it seems you need to have two
fields, HoursWorked and EndOfWeek, in tblShiftWorked. This assumes an
employee works the same shift for the whole week. If there is the
possibility an employee may work different shifts during the week, then you
need the field WorkDate rather than EndOfWeek. This table then gives you the
ability to print out the night shift employees list along with the total
types of hours they work as well as the total hours for any employee. You
would use a totals query for these. TblHoursWorked might make more sense for
the name of the table.

Steve
PC Datasheet


Shift is used just as information as to what shift the employee works. Long
term plan is to keep track of hours worked, not the dates worked then only
print out the night shift employees list along with the total types of hours
they work so decisions can be made based on number of hours. We only have
two 12 hour shifts. I guess thinking about it and talking to one of the
supervisors, there are not that many people who work both shifts and when
they have someone who is they just print out a list of all shifts and can
see who has worked more hours.

Thanks,
Linda


Linda,

You need to give further thought as to specifically what you eventually want
to do with the database. You need to consider if you need the hours a shift
spans and the date worked. The date worked needs careful consideration if
say Shift 3 is something like 10PM -6AM spanning two dates. You may need
just a start workdate. You also need to consider how to handle the situation
where an employee works a double.

Steve
PC Datasheet

Hi Everyone,

I am starting a new database. I made one a couple of years ago but it's a
nightmare. I thought I would go step by step with you guys. The first 2
tables are going to be tblEmployee and tblShift. Some employees work both
shifts so should I need 3 tables? I will eventually want to have separate
reports which will bring up a list of night shift workers and one for day
shift workers. Confession, in my first database, I put shift in the
Employee Table.

Here is what I am thinking

tblEmployees
EmployeeID
EmployeeLastName
EmployeeFirstName
Etc....

tblShift
ShiftID
Shift

Do I need another table?

tblShiftWorked
ShiftWorkedID
ShiftID
EmployeeID

As I look at these tables, should I put shift ID in the Employee Table?

Thanks,
Linda
 
Consider having these tables:

TblWorkHoursType
WorkHoursTypeID
WorkHoursType

tblHoursWorked
HoursWorkedID
EmployeeID
WorkDate
ShiftID
WorkHoursTypeID
HoursWorked

<<in a query I'll report the hours for a rolling 90 day period for some,
Quarterly for some and for all time in another.>>
You need the WorkDate to be able to do this!

Notice EmployeeID is the second field. Remember from the lessons that when
the second field ends in ID that it is a foreign key. So you would set up a
form/subform to enter hours worked. The main form would be based on
TblEmployee and the subform would be based on TblHoursWorked. You coukl put
an unbound textbox for WorkDate (Filled by a calendar control ??) and an
unbound combobox for ShiftID in the main form's header so you could quickly
enter all the employees' workhours for a certain date and a certain shift
without having to enter the date and shift for each employee. You would then
put hidden bound textboxes in the subform for WorkDate and ShiftID. Put the
following code in the Enter event of the subform control:
If IsNull(Me!WorkDate) Then
MsgBox "Enter Work Date first"
Me!WorkDate.SetFocus
ExitSub
End If
If IsNull(Me!ShiftID) Then
MsgBox "Enter Shift first"
Me!ShiftID.SetFocus
End If

Put the following code in the AfterUpdate event of WorkHoursTypeID:
Me!WorkDate = Me.Parent!Workdate
Me!ShiftID = Me.Parent!ShiftID

In the Enter event of HoursWorked, you should put code to check that
WorkHoursType is entered first. I'll let you design that!!

Happy Computing!!

Steve

PS Eight Computers, huh?




Yes, there will be many HoursWorked type tables...MTOHours, VTOHours,
VTOOnCallHours, OnCallHoursWorked, and OnCallHoursApproved. That is my next
question to post....when I get there. We will keep track forever in the
tables but in a query I'll report the hours for a rolling 90 day period for
some, Quarterly for some and for all time in another.

Thanks,
Linda
It sounds like you want to track total hours worked during the year for the
purpose of allocating OT. That being the case, it seems you need to have two
fields, HoursWorked and EndOfWeek, in tblShiftWorked. This assumes an
employee works the same shift for the whole week. If there is the
possibility an employee may work different shifts during the week, then you
need the field WorkDate rather than EndOfWeek. This table then gives you the
ability to print out the night shift employees list along with the total
types of hours they work as well as the total hours for any employee. You
would use a totals query for these. TblHoursWorked might make more sense for
the name of the table.

Steve
PC Datasheet


Shift is used just as information as to what shift the employee works. Long
term plan is to keep track of hours worked, not the dates worked then only
print out the night shift employees list along with the total types of hours
they work so decisions can be made based on number of hours. We only have
two 12 hour shifts. I guess thinking about it and talking to one of the
supervisors, there are not that many people who work both shifts and when
they have someone who is they just print out a list of all shifts and can
see who has worked more hours.

Thanks,
Linda


Linda,

You need to give further thought as to specifically what you eventually want
to do with the database. You need to consider if you need the hours a shift
spans and the date worked. The date worked needs careful consideration if
say Shift 3 is something like 10PM -6AM spanning two dates. You may need
just a start workdate. You also need to consider how to handle the situation
where an employee works a double.

Steve
PC Datasheet

Hi Everyone,

I am starting a new database. I made one a couple of years ago but it's a
nightmare. I thought I would go step by step with you guys. The first 2
tables are going to be tblEmployee and tblShift. Some employees work both
shifts so should I need 3 tables? I will eventually want to have separate
reports which will bring up a list of night shift workers and one for day
shift workers. Confession, in my first database, I put shift in the
Employee Table.

Here is what I am thinking

tblEmployees
EmployeeID
EmployeeLastName
EmployeeFirstName
Etc....

tblShift
ShiftID
Shift

Do I need another table?

tblShiftWorked
ShiftWorkedID
ShiftID
EmployeeID

As I look at these tables, should I put shift ID in the Employee Table?

Thanks,
Linda
 
Hi Steve and thanks for anwsering,

I am not very good at explaining what I want yet, so I'll try to do a better job. I worked out your plan on paper and I noticed that you suggest keeping track of all the hours the employees work. We don't want to keep track of the hours worked everyday by every employee. We only want to keep track of the employees who take certain types of time and we have to offer the type of time based on seniority and turn. I am going to start a new thread on this subject because I have been chasing my tale tyring to figure out the correct way to do that. I am saving your post because it looks like something I can use in another database I am planning to do eventually.

I think as far as my original question about shift...Here is my "better" question.

If we only need to use "shift" for the purpose of printing lists that only show day shift employees or night shift employees (we only have 2 shifts) should I put the field shift in the main employee table and use "days" or "nights" as the data for each employee? Thinking ahead, if we do add another shift in 5 years, it will be easier to add if I make a seperate table and link the two. Here is what I worked out.


tblEmployee
EmployeeID (PK) AutoNumber
ShiftID (FK)
EmpLName
EmpFName
Etc.....

tblShiftWorked
ShiftWorkedID (PK) AutoNumber
EmployeeID (FK)
ShiftID (FK)

tblShift
ShiftID (PK) AutoNumber
Shift.......the fields would be Days and Nights

I'll be playing around with this because I have no idea how to get a printed list of day shift employees or night shift employees from this type of setup.


Linda

In our current database I have a seperate table for each type of time. For instance....

VTOOnCall (VoluntaryTimeOffOnCall)-If we need to send someone home because there is not enough work, the supervisor will offer this to someone working based on 1) Seniority, 2) least amount of VTOOnCall worked already this quarter (our quarters begin in January) I have a query set up to sort by seniority, and a totals column of VTOOnCall hours. I also have date in there so the totals can go back to zero at the beginning of the quarter.

MTO (Manditory Time Off)-If we need to send someone home because there is not enough work and there is no one willing to take VTOOnCall or VTO, the person working that day with the lowest seniority and the lowest total MTO hours for a work quarter (Jan, Feb, Mar/Apr, May, Jun/ etc) is forced to go home. Those hours are entered into the database and then that person is now at the bottom of the list to go home because they have MTO hours.






Consider having these tables:

TblWorkHoursType
WorkHoursTypeID
WorkHoursType

tblHoursWorked
HoursWorkedID
EmployeeID
WorkDate
ShiftID
WorkHoursTypeID
HoursWorked
 
You don't need ShiftID in TblEmployee. Fields in TblEmployee should relate
directly to describing the employee. TblShiftWorked tells you what shift any
employee worked. BTW, you need date worked in that table to make ShiftWorked
meaningful.

To get a list of dayshift employees, create a query based on TblShiftWorked
and TblEmployee joined on EmployeeID. TblEmployee is included to get the
employee's name. Include ShiftID in the query. You need form to specify the
shift and to open the report. Call the form, PFrmShiftToPrint, and out a
combobox based on TblShift on the form to select the shift to print. Put the
following expression in the criteria of the ShiftID field in the query:
Forms!PFrmShiftToPrint!ShiftID.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Hi Steve and thanks for anwsering,

I am not very good at explaining what I want yet, so I'll try to do a better
job. I worked out your plan on paper and I noticed that you suggest keeping
track of all the hours the employees work. We don't want to keep track of
the hours worked everyday by every employee. We only want to keep track of
the employees who take certain types of time and we have to offer the type
of time based on seniority and turn. I am going to start a new thread on
this subject because I have been chasing my tale tyring to figure out the
correct way to do that. I am saving your post because it looks like
something I can use in another database I am planning to do eventually.

I think as far as my original question about shift...Here is my "better"
question.

If we only need to use "shift" for the purpose of printing lists that only
show day shift employees or night shift employees (we only have 2 shifts)
should I put the field shift in the main employee table and use "days" or
"nights" as the data for each employee? Thinking ahead, if we do add
another shift in 5 years, it will be easier to add if I make a seperate
table and link the two. Here is what I worked out.


tblEmployee
EmployeeID (PK) AutoNumber
ShiftID (FK)
EmpLName
EmpFName
Etc.....

tblShiftWorked
ShiftWorkedID (PK) AutoNumber
EmployeeID (FK)
ShiftID (FK)

tblShift
ShiftID (PK) AutoNumber
Shift.......the fields would be Days and Nights

I'll be playing around with this because I have no idea how to get a printed
list of day shift employees or night shift employees from this type of
setup.


Linda

In our current database I have a seperate table for each type of time. For
instance....

VTOOnCall (VoluntaryTimeOffOnCall)-If we need to send someone home because
there is not enough work, the supervisor will offer this to someone working
based on 1) Seniority, 2) least amount of VTOOnCall worked already this
quarter (our quarters begin in January) I have a query set up to sort by
seniority, and a totals column of VTOOnCall hours. I also have date in
there so the totals can go back to zero at the beginning of the quarter.

MTO (Manditory Time Off)-If we need to send someone home because there is
not enough work and there is no one willing to take VTOOnCall or VTO, the
person working that day with the lowest seniority and the lowest total MTO
hours for a work quarter (Jan, Feb, Mar/Apr, May, Jun/ etc) is forced to go
home. Those hours are entered into the database and then that person is now
at the bottom of the list to go home because they have MTO hours.






Consider having these tables:

TblWorkHoursType
WorkHoursTypeID
WorkHoursType

tblHoursWorked
HoursWorkedID
EmployeeID
WorkDate
ShiftID
WorkHoursTypeID
HoursWorked
 
Thanks...date is on my written table design, I forgot to type it in.
You don't need ShiftID in TblEmployee. Fields in TblEmployee should relate
directly to describing the employee. TblShiftWorked tells you what shift any
employee worked. BTW, you need date worked in that table to make ShiftWorked
meaningful.

To get a list of dayshift employees, create a query based on TblShiftWorked
and TblEmployee joined on EmployeeID. TblEmployee is included to get the
employee's name. Include ShiftID in the query. You need form to specify the
shift and to open the report. Call the form, PFrmShiftToPrint, and out a
combobox based on TblShift on the form to select the shift to print. Put the
following expression in the criteria of the ShiftID field in the query:
Forms!PFrmShiftToPrint!ShiftID.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Hi Steve and thanks for anwsering,

I am not very good at explaining what I want yet, so I'll try to do a better
job. I worked out your plan on paper and I noticed that you suggest keeping
track of all the hours the employees work. We don't want to keep track of
the hours worked everyday by every employee. We only want to keep track of
the employees who take certain types of time and we have to offer the type
of time based on seniority and turn. I am going to start a new thread on
this subject because I have been chasing my tale tyring to figure out the
correct way to do that. I am saving your post because it looks like
something I can use in another database I am planning to do eventually.

I think as far as my original question about shift...Here is my "better"
question.

If we only need to use "shift" for the purpose of printing lists that only
show day shift employees or night shift employees (we only have 2 shifts)
should I put the field shift in the main employee table and use "days" or
"nights" as the data for each employee? Thinking ahead, if we do add
another shift in 5 years, it will be easier to add if I make a seperate
table and link the two. Here is what I worked out.


tblEmployee
EmployeeID (PK) AutoNumber
ShiftID (FK)
EmpLName
EmpFName
Etc.....

tblShiftWorked
ShiftWorkedID (PK) AutoNumber
EmployeeID (FK)
ShiftID (FK)

tblShift
ShiftID (PK) AutoNumber
Shift.......the fields would be Days and Nights

I'll be playing around with this because I have no idea how to get a printed
list of day shift employees or night shift employees from this type of
setup.


Linda

In our current database I have a seperate table for each type of time. For
instance....

VTOOnCall (VoluntaryTimeOffOnCall)-If we need to send someone home because
there is not enough work, the supervisor will offer this to someone working
based on 1) Seniority, 2) least amount of VTOOnCall worked already this
quarter (our quarters begin in January) I have a query set up to sort by
seniority, and a totals column of VTOOnCall hours. I also have date in
there so the totals can go back to zero at the beginning of the quarter.

MTO (Manditory Time Off)-If we need to send someone home because there is
not enough work and there is no one willing to take VTOOnCall or VTO, the
person working that day with the lowest seniority and the lowest total MTO
hours for a work quarter (Jan, Feb, Mar/Apr, May, Jun/ etc) is forced to go
home. Those hours are entered into the database and then that person is now
at the bottom of the list to go home because they have MTO hours.






Consider having these tables:

TblWorkHoursType
WorkHoursTypeID
WorkHoursType

tblHoursWorked
HoursWorkedID
EmployeeID
WorkDate
ShiftID
WorkHoursTypeID
HoursWorked
 
Whoops...Date is on my worked areas not in the shift table because people either work the day shift or the night shift.

Thanks...date is on my written table design, I forgot to type it in.
You don't need ShiftID in TblEmployee. Fields in TblEmployee should relate
directly to describing the employee. TblShiftWorked tells you what shift any
employee worked. BTW, you need date worked in that table to make ShiftWorked
meaningful.
 
Back
Top