Re: Tbl design for expiration dates

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%23wrB5cSNGHA.3896@xxxxxxxxxxxxxxxxxxxxxxx
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LMB" <RomulanQueen@xxxxxxxxxxxxxxx> wrote in message
news:eW10SPQNGHA.2012@xxxxxxxxxxxxxxxxxxxxxxx
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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:e%23Itkx2MGHA.516@xxxxxxxxxxxxxxxxxxxxxxx
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" <RomulanQueen@xxxxxxxxxxxxxxx> wrote in message
news:uBfGIe2MGHA.1488@xxxxxxxxxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Re: Tbl design for expiration dates
    ... It is rather unlikely that your database will still be in use then. ... Credentials do not drive anything but pay. ... the employees who didn't need certain credentials renewed but I have been ... never expire. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Tbl design for expiration dates
    ... Credentials do not drive anything but pay. ... We just need a report that shows ... the employees who didn't need certain credentials renewed but I have been ... never expire. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Select records on a Continuous form
    ... employees and then change their credentials. ... successful in building the ablity for the control key to be held ... down while selecting several employee records. ...
    (microsoft.public.access.forms)
  • Re: Select records on a Continuous form
    ... user to use the control key to select a block of employees and then ... their credentials. ... control key to be held down while selecting several employee records. ... You can build a form that contains a multiselect listbox which will ...
    (microsoft.public.access.forms)
  • Tbl design for expiration dates
    ... credentials of each employee and the expiration date of the credentials. ... final report will need to list all the employees, ... thinking a y/n field would be in order but I have not used one before and ...
    (microsoft.public.access.tablesdbdesign)