Query expiration dates within specific date ranges

[dslatten]

So I have a database where employees are entered along with 3 specific credentials and their expiration dates. When an employee has any of the three credentials expired they are marked as "Inactive". I have figured out how to build a query that shows a person who has an expiration date within a specific date range. This is my issue: I have to report how many people have been inactive as follows: within 30 days, 30-60 days, 60-90 days, 90-180 days, over 180 days. I have figured out how to build the queries to show all three credentials that have expired within those date ranges. My problem is that if an individual has say a Drivers License that has expired within 30 days, they will show up in that query but if they have an EMS certification that has expired within 30-60 days, they show up again in the second query. I need to be able to exclude them if they have already been counted. Hope that makes sense. Any help would be greatly appreciated.

[Dal Jeanis]

Basically, you want the oldest expiration date. So, if these are the fields:

tblStaff
  StaffID
  ExDate1
  ExDate2
  ExDate3

Then this gets you the oldest date for each staff member

SELECT
  StaffID,
  IIF(ExDate1 < ExDate2, 
      IIF(ExDate1 < ExDate3, ExDate1, ExDate3), 
      IIF(ExDate2 < ExDate3, ExDate2, ExDate3)
     ) AS OldestDate
FROM tblStaff;

And then use Oldestdate to determine the number of days inactive.

NOTE 1: I'm only testing "less than", because if two expiration dates are the same, then it doesn't matter which one you get.

NOTE 2: I'm assuming all those dates are filled in and not Null, otherwise you'll need to put NZ() around each of the ExDatex fields.