Not possible to use HAVING clause in transform sql statement?

Is it possible to use the HAVING clause with the transform statement in MS Access 2010? I keep getting “Syntax error in transform statement”

My sql is

TRANSFORM FIRST(appointment_date) As FirstOfappointment_date
SELECT rank, complete_name
FROM appointment_overview
WHERE status IS NULL OR status <> ‘Deleted’
GROUP BY rank, complete_name
HAVING COUNT(appointment_id) < 2
PIVOT type;

I tried testing the select statement by itself and it works fine

Is what I’m trying to do not supported by access?

Answer

Indeed, Having is not available when executing a Pivot.

A possible workaround is to use a subquery, and move your Having clause to the Where clause:

TRANSFORM FIRST(appointment_date) As FirstOfappointment_date
SELECT rank, complete_name
FROM appointment_overview a
WHERE (status IS NULL OR status <> 'Deleted')
AND (SELECT Count(s.appointment_id) FROM appointment_overview s WHERE (status IS NULL OR status <> 'Deleted') AND s.rank = a.rank AND s.complete_name = a.complete_name) = 2
GROUP BY rank, complete_name
PIVOT type;

Note that this may have a substantial impact on performance.