employee availability

[Dan]

I am setting up an employee database to track their training and availability
(access 2003). I have 5 tables so far:

tbl_employees
EmployeeId
employeenumber
firstname
lastname

tbl_department
DepartmentID
department
Manager

tbl_job
jobID
job
startingwage

tbl_availability
availabilityID
monday (yes/no)
tuesday (yes/no)

tbl_employeeavailability
employeeavailabilityID
EmployeeID
AvailabilityId

Do I need a seperate table for training? If so how do I track the dates the
employee took the training. we have upward of 5 training programs.

Is it possible to create one tabbed form for all my tables? 1st tab Employee
info, 2nd tab - availability etc.

I have more questions, but lets get the basics work out first. I am fairly
new to Access.

thanks

 

[Pieter Wijnen]

Training
---------
TrainingID
Training

EmployeeTraining
------------------
EmployeeTrainingID (not neccessary, but)
TrainingID
EmployeeID
Datum (Avoid Date, as it is reserved in VBA)
-- or --
EmployeeTraining
------------------
EmployeeTrainingID (not neccessary, but)
TrainingID
EmployeeID
DateFrom
DateTo

HTH

Pieter

 

[Dan]

Thanks, now I have 7 tables, but I am not sure I understand how the Forms
will work. Are the forms based on the 5 main tables or the 2 junction tables?

 

[Dan]

to rephrase my last stupid question, I think I need 1 form for each of the 7
tables, but do I need to do a 1 or more queries to make them work. My
ultimate goal is to have 1 main form for each emplyee with multiple tabs :
tab one - emplyee info
tab 2 - availability
tab 3 - training
etc...

 

[John W. Vinson]

to rephrase my last stupid question, I think I need 1 form for each of the 7
tables, but do I need to do a 1 or more queries to make them work. My
ultimate goal is to have 1 main form for each emplyee with multiple tabs :
tab one - emplyee info
tab 2 - availability
tab 3 - training

No. You don't need one form for each employee. You need one form based on the
Employees table, which you can use to find any employee's information. You
might or might not want to use a Tab Control - it is handy if you have a lot
of information to display; whether or not they are on a tab control, though,
you will want Subforms for the related tables.

John W. Vinson [MVP]

 

[Dan]

Thanks John, no I do not need or want 1 form for each employee, What I meant
was 1 form to show all the employees information,availability, and training.
I almost have it figured out, but my subforms seem to be causing errors, as
the record the subform is on does not match the record the main form is on. I
created a form for availability, and added it as a subform on the employee
form.

Thanks for your help

 

[Pieter Wijnen]

The subForm *control*'s properties Link Child/Parent field is for that
purpose

Pieter

 

[John W. Vinson]

I am setting up an employee database to track their training and availability
(access 2003). I have 5 tables so far:

tbl_employees
EmployeeId
employeenumber

Why both fields? If the employee number is unique, stable, and reasonably
short, it would be a perfectly suitable primary key.

firstname
lastname

Surely you need a DepartmentID field in tblEmployees, as a link to
tbl_Department, so you can tell which department the employee is in?

tbl_department
DepartmentID
department
Manager

If the manager is an Employee, then the manager field should be that
employee's employee ID.

tbl_job
jobID
job
startingwage

Are Jobs associated with departments? should there be a department ID here?

tbl_availability
availabilityID
monday (yes/no)
tuesday (yes/no)

Ummm.... No.

That's storing data in fieldnames (a day) - never a good idea. This table
would need to store all 120 possible combinations of the five checkboxes, and
would be all but impossible to actually use! It also assumes that if Joe Jones
is available next Monday, he will be available on all mondays from here on out
- is that a reasonable assumption?

tbl_employeeavailability
employeeavailabilityID
EmployeeID
AvailabilityId

Rather than AvailabilityID I'd simply use either a date field ("Joe Jones is
available September 24, 2007") or, if you do want to track days of the week,
just store 2 for Monday, 3 for Tuesday, etc. (to match Access' default Weekday
function). If the employee is available Monday, Wednesday and Friday you would
have three records, with values 2, 4, 6.

In regard to subforms, a Subform Control has a "Master/Child Link Field". The
recordsource of the parent form should contain that table's primary key
(EmployeeID say), and the child form should contain the matching foreign key
(EmployeeID in tblEmployeeAvailability or whatever child table you're using).
The Master Link Field and Child Link Field should contain these fieldnames.

John W. Vinson [MVP]

 

[Dan]

tbl_employees

Why both fields? If the employee number is unique, stable, and reasonably
short, it would be a perfectly suitable primary key.

Employee number is a unique 5-6 digit number, however I will not know that
number until after the info on this database is mostly filled in.

Surely you need a DepartmentID field in tblEmployees, as a link to
tbl_Department, so you can tell which department the employee is in?

I do have DepartmentID and also JobID in the tblemployees, I just didn't
list all the fields here.

If the manager is an Employee, then the manager field should be that
employee's employee ID.

The manager's are technically employees, but would not be listed in the
employees tbl. I could set up another table for managers as there is 1
manager for each dept.

Are Jobs associated with departments? should there be a department ID here?

Would it be better to remove the JobID from the tblemployee and add the
departmentID to the tbljob?

Ummm.... No.

That's storing data in fieldnames (a day) - never a good idea. This table
would need to store all 120 possible combinations of the five checkboxes, and
would be all but impossible to actually use! It also assumes that if Joe Jones
is available next Monday, he will be available on all mondays from here on out
- is that a reasonable assumption?

This is an event driven business, not a 9-5 monday through friday type job.
All staff are parttime employees which cannot work all events on all
weekdays.

would it be better to have the table:
tbl_availability
availabilityID
weekday
eventID (which I assume I would need a tbl_events)

Rather than AvailabilityID I'd simply use either a date field ("Joe Jones is
available September 24, 2007") or, if you do want to track days of the week,
just store 2 for Monday, 3 for Tuesday, etc. (to match Access' default Weekday
function). If the employee is available Monday, Wednesday and Friday you would
have three records, with values 2, 4, 6.

This makes sense, however I am not sure I understand . Does this require
another table for weekdays?

 

[John W. Vinson]

Employee number is a unique 5-6 digit number, however I will not know that
number until after the info on this database is mostly filled in.

Ok. Makes sense...

I do have DepartmentID and also JobID in the tblemployees, I just didn't
list all the fields here.

Sorry, should have guessed!

The manager's are technically employees, but would not be listed in the
employees tbl. I could set up another table for managers as there is 1
manager for each dept.

Well... if that's the case, I'd not really recommend a separate managers
table. What's wrong with just putting managers into the Employees table and
using the ID though? Alternatively a text field for the manager name would be
reasonable.

Would it be better to remove the JobID from the tblemployee and add the
departmentID to the tbljob?

Well... no. I assume each employee has a job, and each department has several
- you would need to have the JobID in the Employee table in order to specify
which job they have.

This is an event driven business, not a 9-5 monday through friday type job.
All staff are parttime employees which cannot work all events on all
weekdays.

would it be better to have the table:
tbl_availability
availabilityID
weekday
eventID (which I assume I would need a tbl_events)

Do events span multiple days? It sounds like weekday and EventID would be
redundant if you can instead just specify that a given employee is available
for a given event (on whatever day it happens). If events run multiple days
then you may need both fields.

This makes sense, however I am not sure I understand . Does this require
another table for weekdays?

Not really, though you might want one just so a combo box could store 2 while
displaying Monday... again, if it makes sense to say that Joe Jones is
available on all Mondays no matter which week".

John W. Vinson [MVP]

 

[Dan]

would it be better to have the table:

Do events span multiple days? It sounds like weekday and EventID would be
redundant if you can instead just specify that a given employee is available
for a given event (on whatever day it happens). If events run multiple days
then you may need both fields.

It is an arena which has hockey, lacross, concerts etc. Some staff will
work every event, other staff have other jobs or school, so they may only
commit to hockey games which occur on mondays and wednesdays. So in a sense
events do span multiple days, as the minor hockey would have 36 games during
the season.

Not really, though you might want one just so a combo box could store 2 while
displaying Monday... again, if it makes sense to say that Joe Jones is
available on all Mondays no matter which week".

In our business, it actually does make sense that JOe Jones will only work
Mondays when we have an event. One of the possible uses for this database is
for scheduling purposes, I could print a report showing all staff who is
available to work this monday for a concert, for example.

Thanks for your feedback, I come from many years of using Excel and still
learning the potential for Access.

Dan