Multiple Expiration Dates Query

[ADG0001]

I am attempting to amend an existing query which currently only lists the
License expiration date for each employee based on "Between xx/xx/xx And
xx/xx/xx" criteria. I want to include 3 additional expiration fields
(CPR/ACLS/PALS), but don't know how to change to criteria so all 4 expiration
dates will run independently because it is rare that an employee’s
certification(s) would expire simultaneously.

The other problem I’m having is that depending on their Position and
Classification, some expiration dates will be blank:
If Classification includes ER, then License, CPR, ACLS, & PALS are required.
If Classification includes Tele, then License, CPR, & ACLS are required.
If Classification is “Med/Surg Only†or Position is “NTâ€, then License & CPR
are required.
If Position is “PCTâ€, then only CPR is required.

I want the query to show anyone whose License or CPR or ACLS or PALS are
scheduled to expire in the upcoming month (regardless of Classification and
Title).

Any assistance offered will be greatly appreciated!
Thanks-ADG

 

[Allen Browne]

In query design, under the Criteria row are some other rows titled "Or".
Use those rows.

In the Criteria row under the License expiration date, you have:
Between #xx/xx/xx# And #xx/xx/xx#
Repeat that expression under the next date field, on the Or row.
Repeat it again under the 3rd date field, on the next Or row.

If you have other criteria that should apply to all cases, repeat them on
the Or rows as well (under their field.)

For the 2nd part of your question, presumably you have a lookup table
containing the various classifications. Add a yes/no field to this table to
indicate which ones require licences. You can then include this field in the
criteria of your table, to indicate which ones are required to be renewed.

It sounds like one person could have multiple licences, and you have fields
for these. That's not a normalized design. It would be better to have 3
tables:
- Employee (one record per person)
- Classification (one record for CPR, one for ACLS, etc.)
- EmployeeClassification.
The 3rd table will have fields such as:
o EmployeeID relates to Employee.EmployeeID
o ClassificationID relates to Classification.ClassificationID
o ClassificationDate when this employee rec'd this classification
You can then determine which licences are expiring when.