[AccessD] Query Question

1 table for Certs
1 table for Employees
1 table for EmployeeCerts with date of expiry

I've found that certifications change with time.  Expiry ranges change, we 
track new certifications, we've added locations with different certs.

You can place a date taken in the junction table and use a duration from the 
Certs table, as this is more flexible.  You can place a unique index on the 
EmployeeCerts table, on the EmployeeID and CertID combination, but then you 
cannot track history of certifications, only update the date taken or 
expired depending on the design.  Placing an exclusive index on the 
combination of IDs and date would prevent accidental duplicates.

Reporting may be done with a Parent report of employees with a sub report of 
Certs including the EmployeeID and expiry from the EmployeeCerts junction.  
You can place a parameter on expiry 'Between Now() and UserChosenExpiryMax'.

My employees have a course that only has a date taken (non-expiry), though 
most entities requiring this cert will only recognize it for 3 years.  
Personally, I think it makes more sense to include course validity range in 
the course table as a characteristic of the course and the date taken as a 
characteristic of the Employee Course junction table because users can pick 
the date taken more accurately than some future expiry date.  This 
complicates the expiry query a bit as it requires a calculated field so it 
slows things up a bit.

Recalling the 'Performance' thread, normalization is usually a good thing, 
but it can hurt performance.  A one to one relationship, or storing the cert 
expirys in the Employee table directly is a denormalized approach that would 
give significantly better performance.  Sometimes you need to choose between 
performance and flexibilty.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Joe Hecht" <jmhecht at earthlink.net>
>I I have a table with employee's five different certifications they need to
>work and the expiration date of these certificates.
>
>
>
>It is a one to one relationship between employee and certification.
>
>
>
>Professional license
>
>Medical Cert
>
>Dot Cert
>
>Cpr
>
>Drivers License
>
>
>
>It is a one to one relation between the license and expiration date.
>
>
>
>I need report that goes some thing this.
>
>
>
>Parameter = # of days into the future to check (user entered)
>
>
>
>For any expiration date in the criteria
>
>
>
>Return the employee, the license that is expiring and the name of that
>license only (not all their valid licensees)
>
>
>
>Or
>
>
>
>If returning all the licensees bold the license that is meeting the 
>criteria
>and adding the employee to the report.
>
>
>
>TIA
>
>
>
>Joe Hecht
>
>jmhecht at earthlink.net

_________________________________________________________________
Upgrade to Windows Live Hotmail for free today! 
www.newhotmail.ca?icid=WLHMENCA151