Linda
Additional comments in-line below...
Linda
I like the tables I see. What I don't see is how you determine several
conditions:
When did employee receive certification on Test/Cert #3? You probably
need a DateCertified in that "join" table.
My first thought is that we really don't need to know what date they were
certified. If they have a certification in a particular thing we only want
to know that it is not expired. When we run the monthly report we would see
who is expired and then send a friendly reminder that they need to get
recertified if they want to stay at their current rate of pay.
So it sounds like you'd need to update the ExpirationDate each time they
re-cert. And it sounds like you don't need to know how many times they've
re-certified...
Is it certain employees, or is it certain Test/Certs that have 1 year
vs
2 year (vs ??!some other period) of renewal/expiration? I'm guessing you
need to add a "term" field to your Test/Cert table -- how long is this
Test/Cert good for?
Certain test/certs have either a 1 or 2 year time period. One new one
that has been recently added is a 5 year renewal but only one employee out
of 70 has or needs that one.
If you need to handle the situation one time, you need to handle the
situation. If you design for the possibility of different "terms", you can
handle ANY term.
You don't actually need to store "expiration date" if you have date
certified and "term" for that certification. You can calculate when it
will
expire. There are some excellent reasons NOT to store a calculated value
in
your database (check on this topic in the tablesdbdesign newsgroup if you
are curious).
I knew I shouldn't put a calculated field in a table but I thought it
would be ok to put expiration date in the table since we don't really need
to know the date the cert was obtained we are only keeping track of if it's
expired.
I'm not entirely convinced that you don't need DateCertified. If someone
takes the test and passes on January 30th, but the fact doesn't get recorded
until February 2nd, and the cert lasts for two years, do you add 2 years to
today's date to get the ExpirationDate? Or to the date of the test? Where
do you put that? And if you have to manually enter an ExpirationDate, what
good does it do you to have the "terms" in your database, as you wouldn't be
using them (you could have a paper list of terms).
You mention that some certs/tests are required, and others are ...
optional? Does every employee need to do every test/cert? If not, you
need
a way to categorize what it is about employee (and/or test/cert) that
makes
it required.
There are only 5 that are required of all employees so far and 2 that are
not. There are 20 employees who need the 2 for their job classification.
See my comment above about needing to handle the situation once -- if you
have (or could, in the future) at least one employee that needs something
special, design for it now.
You mention needing a way to find folks who DON'T have any entry for
one
of the Test/Certs. One way to handle this is to come up with a way to
indicate which Test/Certs ARE required. This could be as simple as a
checkbox in the Test/Cert table to indicate "Required". With something
like
this, you can compare an employee's list of "current" qualifications
(i.e.,
those which have NOT expired) with a list of all "Required"
qualifications.
Where these two don't match, you have the "what the employee has to do"
list.
I understand I think...in what table would I add the required field since
not all employees are required to have all certifications. Only employees
who are level 4 therapists need PALS and NRP. I do have a table that keeps
track of what level the therapists are.
It sounds like you need a table that lists job-class and re-cert. This is a
"junction" table that resolves the fact that one job-class (e.g., "level 4
therapist") could have many re-cert/test requirements (e.g., PALS, NRP,
....), and one re-cert/test requirement could "belong" to multiple
job-classes (?CPR, ...).
Thanks,
Linda
--
Good luck
Jeff Boyce
<Access MVP>
--
Good luck
Jeff Boyce
<Access MVP>
I need to add a new part to my employee database. I want to report
expiration dates for certifications and tests. The problem is that all
employees are not required to have all certifications but for other
certifications all employees are required to have it. Also, some
certifications are good for 1 year and some are 2 years. I am planning on
having the expiration dates recorded in the table instead of the date
certified. We need to do this because an employee may be at a certain
level
of pay as of Jan 1st because they have all of their requirements done and
up
to date. But...if that person's BLS expires in June, they can not
continue
at that same level of pay until they get certified again.
This is what I have so far...
tblEmployee
EmployeeID (pk)
LName
FName
Etc....
tblTestsAndCerts
TestAndCertsID (pk)
EmployeeID (fk)
TestsAndCertsTypeID (fk)
TestsAndCertsExpirationDate
tblTestsAndCertsType
TestsAndCertsID (pk)
TestsAndCerts
Here are some of the tests and certifications we will be tracking. All
employees need these...ACLS, BLS, RC License, TB Test. Only certain
employees need these NRP, PALS.
I need to have a report that is printed monthly to show who's
certifications
are to expire that month. My problem is I can't quite figure out how this
will work out because if employee 1 is supposed to have acls but for some
reason is not entered, they will never show up as expired and they will
slip
through a crack. Also, is there a way to have a field which shows the
word
"expired" for any dates after the expiration month we will be looking?
As you see, I am kind of mixed up so if I need to completely change my
tables, let me know how. Remember to make this a simple as possible
because
I am still a beginner.
Thanks,
Linda
