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.