Tbl design for expiration dates

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

LMB

Hi everyone,

I need to add a table to my existing database which will list the
credentials of each employee and the expiration date of the credentials. My
final report will need to list all the employees, their credentials and show
that they are current with the credentials they have on file. The problem
is that some of the employees have an RRT credential (registered respiratory
therapist) but they were "grandfathered" if they were registered before 2002
which means their credentials do not expire. This is also true for all of
the possible credentials, CRT, CPFT etc... We need to make sure no ones
credentials have expired because salary depends on credentials. I was
thinking a y/n field would be in order but I have not used one before and
can't really visualize how this will work since not all employees will have
all the credentials. My other issue is that not all employees have all
credentials. Any advice would be appreciated.

Would this table set up work?

tblCredentials
CredentialsID(pk)
CredentialType(fk)
EmpID(fk)
CredentialRenewalReq y/n
DateCredentialObtained dtt

tblCredentialType
CredentialTypeID(pk)
CredentialName

Thanks,
Linda
 
Presumably the credentials are tied to responsibilities, so a staff member
in a particular position must have particular credentials.

One employee could have multiple positions (e.g. 2 days in one role, and 3
in another), and the credentials they need might overlap, but ultimately
they need all the current credentials for each position they hold.

The following tables are probably a minimum:

Position: One record for each staff position.
PositionID

Credential: One record for each credential
CredentialID
Frequency Number How often it needs renewing.
PeriodType Text "yyyy", or "m", or "d"

PositionCredential: One record for each credential you need for a position.
PositionID
CredentialID

Employee: One record for each person.
EmpID

EmployeePosition: One record for each position an employee works.
EmpID
PositionID

EmployeeCredential: One record for each credential an employee has.
EmpID
CredentialID
AcquireDate Date/Time when certified
ExpireDate Date/Time when needs to be renewed.

With that structure, you can determine what credentials an employee should
have (based on the positions they currently work), and what credentials they
currently have (based on the EmployeeCredential table). That lets you
determine gap (anything they don't hold that they should), and what
credentials are expiring soon.

The suggestion to include an ExpireDate in EmployeeCredential is arguable,
but:
a) You can set a date of 1/1/2999 for the employees whose credentials never
expire.

b) If someone decides that, from today a certificate now expires every 6
months instead of every 12 months, you can change the Frequency in the
Credential table, without affecting those who already have a piece of paper
to say they are qualified for 12 months.

c) In the screen where you enter the EmployeeCredential, the database can
calculate the expiry date for you, as:
DateAdd([PeriodType], [Frequency], [AcquireDate])
but the user can override and shorten or lengthen the date for particular
cases.

HTH.
 
Thanks, Allen,

Credentials do not drive anything but pay. We just need a report that shows
all employees and what their credentials are and if and when their
credential expires. The other things that you suggested are similar to
other tables that are already set up and working well for us. I thought
maybe by using a y/n field I could eventually run a report that would show
the employees who didn't need certain credentials renewed but I have been
drawing it out and can't seem to get how to set it up since there will be
some employees who have some credentials that do not expire and some that
do.

Linda

Allen Browne said:
Presumably the credentials are tied to responsibilities, so a staff member
in a particular position must have particular credentials.

One employee could have multiple positions (e.g. 2 days in one role, and 3
in another), and the credentials they need might overlap, but ultimately
they need all the current credentials for each position they hold.

The following tables are probably a minimum:

Position: One record for each staff position.
PositionID

Credential: One record for each credential
CredentialID
Frequency Number How often it needs renewing.
PeriodType Text "yyyy", or "m", or "d"

PositionCredential: One record for each credential you need for a
position.
PositionID
CredentialID

Employee: One record for each person.
EmpID

EmployeePosition: One record for each position an employee works.
EmpID
PositionID

EmployeeCredential: One record for each credential an employee has.
EmpID
CredentialID
AcquireDate Date/Time when certified
ExpireDate Date/Time when needs to be renewed.

With that structure, you can determine what credentials an employee should
have (based on the positions they currently work), and what credentials
they currently have (based on the EmployeeCredential table). That lets you
determine gap (anything they don't hold that they should), and what
credentials are expiring soon.

The suggestion to include an ExpireDate in EmployeeCredential is arguable,
but:
a) You can set a date of 1/1/2999 for the employees whose credentials
never expire.

b) If someone decides that, from today a certificate now expires every 6
months instead of every 12 months, you can change the Frequency in the
Credential table, without affecting those who already have a piece of
paper to say they are qualified for 12 months.

c) In the screen where you enter the EmployeeCredential, the database can
calculate the expiry date for you, as:
DateAdd([PeriodType], [Frequency], [AcquireDate])
but the user can override and shorten or lengthen the date for particular
cases.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LMB said:
Hi everyone,

I need to add a table to my existing database which will list the
credentials of each employee and the expiration date of the credentials.
My final report will need to list all the employees, their credentials
and show that they are current with the credentials they have on file.
The problem is that some of the employees have an RRT credential
(registered respiratory therapist) but they were "grandfathered" if they
were registered before 2002 which means their credentials do not expire.
This is also true for all of the possible credentials, CRT, CPFT etc...
We need to make sure no ones credentials have expired because salary
depends on credentials. I was thinking a y/n field would be in order but
I have not used one before and can't really visualize how this will work
since not all employees will have all the credentials. My other issue
is that not all employees have all credentials. Any advice would be
appreciated.

Would this table set up work?

tblCredentials
CredentialsID(pk)
CredentialType(fk)
EmpID(fk)
CredentialRenewalReq y/n
DateCredentialObtained dtt

tblCredentialType
CredentialTypeID(pk)
CredentialName

Thanks,
Linda
Click to expand...
Click to expand...
 
Why not just set an expiry date of say December 31 2999 as the expiry date?
It is rather unlikely that your database will still be in use then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LMB said:
Thanks, Allen,

Credentials do not drive anything but pay. We just need a report that
shows all employees and what their credentials are and if and when their
credential expires. The other things that you suggested are similar to
other tables that are already set up and working well for us. I thought
maybe by using a y/n field I could eventually run a report that would show
the employees who didn't need certain credentials renewed but I have been
drawing it out and can't seem to get how to set it up since there will be
some employees who have some credentials that do not expire and some that
do.

Linda

Allen Browne said:
Presumably the credentials are tied to responsibilities, so a staff
member in a particular position must have particular credentials.

One employee could have multiple positions (e.g. 2 days in one role, and
3 in another), and the credentials they need might overlap, but
ultimately they need all the current credentials for each position they
hold.

The following tables are probably a minimum:

Position: One record for each staff position.
PositionID

Credential: One record for each credential
CredentialID
Frequency Number How often it needs renewing.
PeriodType Text "yyyy", or "m", or "d"

PositionCredential: One record for each credential you need for a
position.
PositionID
CredentialID

Employee: One record for each person.
EmpID

EmployeePosition: One record for each position an employee works.
EmpID
PositionID

EmployeeCredential: One record for each credential an employee has.
EmpID
CredentialID
AcquireDate Date/Time when certified
ExpireDate Date/Time when needs to be renewed.

With that structure, you can determine what credentials an employee
should have (based on the positions they currently work), and what
credentials they currently have (based on the EmployeeCredential table).
That lets you determine gap (anything they don't hold that they should),
and what credentials are expiring soon.

The suggestion to include an ExpireDate in EmployeeCredential is
arguable, but:
a) You can set a date of 1/1/2999 for the employees whose credentials
never expire.

b) If someone decides that, from today a certificate now expires every 6
months instead of every 12 months, you can change the Frequency in the
Credential table, without affecting those who already have a piece of
paper to say they are qualified for 12 months.

c) In the screen where you enter the EmployeeCredential, the database can
calculate the expiry date for you, as:
DateAdd([PeriodType], [Frequency], [AcquireDate])
but the user can override and shorten or lengthen the date for particular
cases.

LMB said:
Hi everyone,

I need to add a table to my existing database which will list the
credentials of each employee and the expiration date of the credentials.
My final report will need to list all the employees, their credentials
and show that they are current with the credentials they have on file.
The problem is that some of the employees have an RRT credential
(registered respiratory therapist) but they were "grandfathered" if they
were registered before 2002 which means their credentials do not expire.
This is also true for all of the possible credentials, CRT, CPFT etc...
We need to make sure no ones credentials have expired because salary
depends on credentials. I was thinking a y/n field would be in order
but I have not used one before and can't really visualize how this will
work since not all employees will have all the credentials. My other
issue is that not all employees have all credentials. Any advice would
be appreciated.

Would this table set up work?

tblCredentials
CredentialsID(pk)
CredentialType(fk)
EmpID(fk)
CredentialRenewalReq y/n
DateCredentialObtained dtt

tblCredentialType
CredentialTypeID(pk)
CredentialName
Click to expand...
Click to expand...
Click to expand...
 
That was my initial idea but I thought there may be a better way to handle
it, but it may be too complicated for me anyway. Probably involve some
code. I can do this one probably all by myself.

Thanks,

Linda

Allen Browne said:
Why not just set an expiry date of say December 31 2999 as the expiry
date? It is rather unlikely that your database will still be in use then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LMB said:
Thanks, Allen,

Credentials do not drive anything but pay. We just need a report that
shows all employees and what their credentials are and if and when their
credential expires. The other things that you suggested are similar to
other tables that are already set up and working well for us. I thought
maybe by using a y/n field I could eventually run a report that would
show the employees who didn't need certain credentials renewed but I have
been drawing it out and can't seem to get how to set it up since there
will be some employees who have some credentials that do not expire and
some that do.

Linda

Allen Browne said:
Presumably the credentials are tied to responsibilities, so a staff
member in a particular position must have particular credentials.

One employee could have multiple positions (e.g. 2 days in one role, and
3 in another), and the credentials they need might overlap, but
ultimately they need all the current credentials for each position they
hold.

The following tables are probably a minimum:

Position: One record for each staff position.
PositionID

Credential: One record for each credential
CredentialID
Frequency Number How often it needs renewing.
PeriodType Text "yyyy", or "m", or "d"

PositionCredential: One record for each credential you need for a
position.
PositionID
CredentialID

Employee: One record for each person.
EmpID

EmployeePosition: One record for each position an employee works.
EmpID
PositionID

EmployeeCredential: One record for each credential an employee has.
EmpID
CredentialID
AcquireDate Date/Time when certified
ExpireDate Date/Time when needs to be renewed.

With that structure, you can determine what credentials an employee
should have (based on the positions they currently work), and what
credentials they currently have (based on the EmployeeCredential table).
That lets you determine gap (anything they don't hold that they should),
and what credentials are expiring soon.

The suggestion to include an ExpireDate in EmployeeCredential is
arguable, but:
a) You can set a date of 1/1/2999 for the employees whose credentials
never expire.

b) If someone decides that, from today a certificate now expires every 6
months instead of every 12 months, you can change the Frequency in the
Credential table, without affecting those who already have a piece of
paper to say they are qualified for 12 months.

c) In the screen where you enter the EmployeeCredential, the database
can calculate the expiry date for you, as:
DateAdd([PeriodType], [Frequency], [AcquireDate])
but the user can override and shorten or lengthen the date for
particular cases.

Hi everyone,

I need to add a table to my existing database which will list the
credentials of each employee and the expiration date of the
credentials. My final report will need to list all the employees, their
credentials and show that they are current with the credentials they
have on file. The problem is that some of the employees have an RRT
credential (registered respiratory therapist) but they were
"grandfathered" if they were registered before 2002 which means their
credentials do not expire. This is also true for all of the possible
credentials, CRT, CPFT etc... We need to make sure no ones credentials
have expired because salary depends on credentials. I was thinking a
y/n field would be in order but I have not used one before and can't
really visualize how this will work since not all employees will have
all the credentials. My other issue is that not all employees have
all credentials. Any advice would be appreciated.

Would this table set up work?

tblCredentials
CredentialsID(pk)
CredentialType(fk)
EmpID(fk)
CredentialRenewalReq y/n
DateCredentialObtained dtt

tblCredentialType
CredentialTypeID(pk)
CredentialName
Click to expand...
Click to expand...
Click to expand...
 
Back
Top