if Employees can have multiple roles and Roles can be held by many employees, you need a linking table that stores nothing but id's of the 2 things it links.
if I could draw a piece of paper, it would be so very easy to show you, but hopefully my explanation helps:
Employee table
-- emp id
-- first name
-- last name
Roles table
-- role id
-- role name
-- etc.
Emp-roles table
-- emp id
-- role id
EMPS
213, john, jones
315, cindy, smith
244, linda, lane
ROLES
a122, shop manager
b122, safety manager
a223, hazmat specialist
EMP-ROLES
a122,213
b122,213
This way we know that John Jones has the Shop Manager role as well as Safety manager role
Now create the training classes
Training class table
-- class id
-- class name
445, blood borne pathogens
488, eye and ear protection
Train-Roles linking table
-- role id
-- class id
and another linking table where you link which training classes are needed by which roles, many roles can have same class, many class required by multiple roles
TRAIN-ROLES
a122, 445
a122, 488
etc.
the Shop manager role would require taking the 445 and 488 classes.
I hope this helps, if unclear, post back.
----- Original Message ----
From: Norman Hutchins <nhutchins@...>
To: vantage@yahoogroups.com
Sent: Fri, May 21, 2010 1:38:16 PM
Subject: [Vantage] OT - trying to create a Training Needs db
I’ve been asked to put together a simple database it track training
completion and needs. Sounds easy, except I have done an access db in years
and I’m a little lost. Here’s what we’re looking for:
*TABLES*
* FIELDS*
* *
*Employee***
* EmployeeID*
* FirstName*
* LastName*
* Roles(s) – *linked to Roles.Role*
Notes*
*Roles* – such as; AP, AR, Shop Floor, ISO, etc…
* ID*
* Role*
* ISO Training Needed – *linked to ISO.Section**
* Additional Training – *linked to AddTraning.Training**
*ISO*
*ISOSection*
* SectionTitle*
*AddTraning *– safety, eye test, respirator training, etc…
* ID*
* Training*
* *
The problem I’m having is the each role can have multiple training needs and
each employee can have multiple roles. So I’m not sure how to work with the
two levels of one-to-many links. They also want date completed for each
training needed.
How do other people track training needs?
Any input would be great, thanks!
* *
Norman Hutchins
Network Administrator
Howell Laboratories, Inc.
Shively Labs
207-647-3327
[Non-text portions of this message have been removed]
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
if I could draw a piece of paper, it would be so very easy to show you, but hopefully my explanation helps:
Employee table
-- emp id
-- first name
-- last name
Roles table
-- role id
-- role name
-- etc.
Emp-roles table
-- emp id
-- role id
EMPS
213, john, jones
315, cindy, smith
244, linda, lane
ROLES
a122, shop manager
b122, safety manager
a223, hazmat specialist
EMP-ROLES
a122,213
b122,213
This way we know that John Jones has the Shop Manager role as well as Safety manager role
Now create the training classes
Training class table
-- class id
-- class name
445, blood borne pathogens
488, eye and ear protection
Train-Roles linking table
-- role id
-- class id
and another linking table where you link which training classes are needed by which roles, many roles can have same class, many class required by multiple roles
TRAIN-ROLES
a122, 445
a122, 488
etc.
the Shop manager role would require taking the 445 and 488 classes.
I hope this helps, if unclear, post back.
----- Original Message ----
From: Norman Hutchins <nhutchins@...>
To: vantage@yahoogroups.com
Sent: Fri, May 21, 2010 1:38:16 PM
Subject: [Vantage] OT - trying to create a Training Needs db
I’ve been asked to put together a simple database it track training
completion and needs. Sounds easy, except I have done an access db in years
and I’m a little lost. Here’s what we’re looking for:
*TABLES*
* FIELDS*
* *
*Employee***
* EmployeeID*
* FirstName*
* LastName*
* Roles(s) – *linked to Roles.Role*
Notes*
*Roles* – such as; AP, AR, Shop Floor, ISO, etc…
* ID*
* Role*
* ISO Training Needed – *linked to ISO.Section**
* Additional Training – *linked to AddTraning.Training**
*ISO*
*ISOSection*
* SectionTitle*
*AddTraning *– safety, eye test, respirator training, etc…
* ID*
* Training*
* *
The problem I’m having is the each role can have multiple training needs and
each employee can have multiple roles. So I’m not sure how to work with the
two levels of one-to-many links. They also want date completed for each
training needed.
How do other people track training needs?
Any input would be great, thanks!
* *
Norman Hutchins
Network Administrator
Howell Laboratories, Inc.
Shively Labs
207-647-3327
[Non-text portions of this message have been removed]
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links