Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 27th, 2008, 09:26 PM
Registered User
 
Join Date: Jul 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help for Access formula

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 July 28th, 2008, 12:04 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old July 28th, 2008, 11:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 July 28th, 2008, 02:06 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula scandalous Access VBA 11 February 27th, 2007 09:49 AM
asp 3.0 classic&access *formula retrival)? saad_rashad Classic ASP Databases 3 February 18th, 2007 03:47 PM
using formula in access database design Lovehead Access VBA 1 January 3rd, 2007 01:44 PM
formula sinha Crystal Reports 1 October 26th, 2005 12:53 PM
Formula Ned Pro VB 6 2 September 10th, 2003 10:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.