Categorize Records In A Query

A common request is to categorize records into different categories based on a value in a field or fields. A common solution used is to hard-code the values in a query or use VBA code to accomplish this where the best answer almost always involves using a table to define the categories.

Defining the categories in your SQL makes future category range changes difficult. Same with a coded solution. It is usually easier to define the categories in a table and use a join to dynamically categorize at query time. Here is an Employee table and AgeRange table:

tblEmployee:
  EmpID - Long (PK)
  EmpName - Text(25)
  EmpAge - Long

EmpID     EmpName       EmpAge
1         Paul          21
2         Steven        31
3         Tom           41
4         Joe           51
12        Joe           61
14        Tim           71
15        Phil          55
16        Joe           35
17        Karen         26
19        Jill          23
22        Raymond       43

tblAgeRange:
  AgeMin - Long (PK)
  AgeMax - Long
  AgeCategory - Text(50)

AgeMin    AgeMax    AgeCategory
18        25        Young Adult
26        30        Late Twenties
31        40        No Longer Young
41        50        Looking Forward To AARP
51        60        Looking Back At Twenties
61        150       Done For
            

By joining these two tables together using the EmpAge field from tblEmployee we can determine the appropriate age category:

SELECT tblAgeRange.AgeCategory, tblEmployee.*
FROM tblEmployee LEFT JOIN tblAgeRange ON (tblEmployee.EmpAge <= tblAgeRange.AgeMax) AND (tblEmployee.EmpAge >= tblAgeRange.AgeMin)
ORDER BY tblEmployee.EmpAge;
            

and get results like this:

EmpID     EmpName   AgeCategory
1         Paul      Young Adult
2         Steven    No LongerYoung
3         Tom       Looking Forward To AARP
4         Joe       Looking Back At Twenties
12        Joe       Done For
14        Tim       Done For
15        Phil      Looking Back At Twenties
16        Joe       No Longer Young
17        Karen     Late Twenties
19        Jill      Young Adult
22        Raymond   Looking Forward To AARP
            

Re-defining the minimum and maximum ages for each range are done in the tblAgeRange table and no other modification is required. An application would typically provide a configuration screen for the user to re-define category limits. Re-running the query will reflect those changes.