SQL CASE Statement using an intermediate table in Ms Access

Problem

We need to create a SQL CASE Statement which is not currently supported in Ms Access. This time we will use an intermediate table to create the intervals.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In our example for the column Number we want to create intervals of 100 from 0 to >1000. We will use the interval notation: parentheses and/or brackets are used to show whether the endpoints are excluded or included respectively.

Solution

1. Create an intermediate table with the intervals

2. Join both tables based on the lower and upper bounds of the intervals.

SELECT Table.Number, intervals.Interval
FROM [Table] INNER JOIN intervals ON (Table.Number > intervals.lower) AND (Table.Number <= intervals.upper);

Related posts

• SQL CASE Statement in Ms Access