Help for Access formula

firemedic2317 · July 27th, 2008, 09:26 PM

I have a database with employee training classes with their expiration dates. I would like to output their names and only the expirations that are within 90 days from todays date. There are multiple expirations for each employee. The table name EMS Certifications and the expiration fields are EMT Lic Exp, Other Lic Exp (if outputed then I would have to have the data in the "Other Lic field outputed too), CPR Exp, ACLS Exp, PALS Exp, PHTLS Exp, FarmMed Exp, Exp 1 (and output Class 1 field too if applicable), Exp 2 (same as Exp 1.

Old Pedant · July 28th, 2008, 12:04 AM

Well, you've made a big mistake in your database design, so you've made the code to answer this one really really tough.

Are you amenable to changing the DB design??

I'd suggest *something* like this:

Code:

Table: Employees
    empid autonumber, primary key
    empname ...
    ... other fields ...

Table: Certifications
    certid autonumber, primary key
    certname text

Table: EmployeeCertifications
    empid int, foreign key to Employees table
    certid int, foreign key to Certifications table
    expirationDate datetime

So example:

Employees
   1  John
   2  Mary

Certifications
   1  EMT
   2  CPR
   3  ACLS
   4  PALS
   ... etc. ...

EmployeeCertifications
   1  1  17 Oct 2008 (John's EMT cert)
   1  3  21 Dec 2008 (John's ACLS cert)
   2  1  14 Feb 2009 (Mary's EMT cert)
   ... etc. ...

*NOW* it's easy to create a query to answer your question, getting *ALL* certifications for *ALL* employees that expire at any point in time (e.g., within 90 days).

Code:

SELECT E.name, C.certname, EC.expirationDate
FROM Employees AS E, Certifications AS C, EmployeeCertifications AS EC
WHERE E.empid = EC.empid
  AND EC.certid = C.certid
  AND EC.expirationDate < ( Date() + 90 )
ORDER BY E.name, C.expirationDate

***********

In order to do the same thing with your current design, you would have to have a *SEPARATE* WHERE condition for *EACH* certificate type *AND* you'd have to do some MAJOR messing around to make the report show only the ones expiring in 90 days. Probably what you'd end up with is a UNION of *ONE SEPARATE QUERY* for each certification type. Ugly, slow, and just bad DB design.

rgerald · July 28th, 2008, 11:55 AM

I concur with "Old Pedant". The entire database needs to be normalized.

You should remember that Access is usually sold with Excel - the two products complement each other, they do not compete with each other.

Access is not a spreadsheet and should not be treated like one. Many beginners try to do that and wind up with an application that is hard to use.

Rand

Old Pedant · July 28th, 2008, 02:06 PM

Quote:

quote:Originally posted by firemedic2317
I have a database with employee training classes with their expiration dates. I would like to output their names and only the expirations that are within 90 days from todays date. There are multiple expirations for each employee. The table name EMS Certifications and the expiration fields are EMT Lic Exp, Other Lic Exp (if outputed then I would have to have the data in the "Other Lic field outputed too), CPR Exp, ACLS Exp, PALS Exp, PHTLS Exp, FarmMed Exp, Exp 1 (and output Class 1 field too if applicable), Exp 2 (same as Exp 1.

Just to show you what I meant about a really ugly UNION...

Code:

SELECT empid, 'EMT' AS certificationType, [EMT Lic Exp] AS expirationDate
FROM [EMS Certifications] 
WHERE [EMT Lic Exp] < ( Date() + 90 )
    UNION
SELECT empid, 'CPR', [CPR Exp]
FROM [EMS Certifications] 
WHERE [CPR Exp] < ( Date() + 90 )
    UNION
SELECT empid, 'ACLS', [ACLS Exp]
FROM [EMS Certifications] 
WHERE [ACLS Exp] < ( Date() + 90 )
    UNION
... and so on, one SELECT per expiration field ...
ORDER BY empid, expirationDate

So, you see, it *IS* possible. Just horribly horribly ugly.

But now consider how you would implement a question such as "Show all people who have 3 or more licenses expiring within the next 6 months and who have a total of 6 or more licenses."

I can't even begin to think of how ugly that would be, given your current DB design. And yet it's pretty easy with my design.