Does anybody know why I can't union more than 2 tables? What I want is a complete list of alarms, their names, and their current state, in a single query. The UNION works for 2 (e.g. CiDigitalAlarm UNION CiTimestampedAlarm) but not if you add more tables. Seems like this should work - I can't find any restriction in the Jet or Access documentation saying it should not.
SELECT Id, Name, AlarmState
FROM (
SELECT Id, Name, AlarmState FROM CiDigitalAlarm
UNION ALL SELECT Id, Name, AlarmState FROM CiTimestampedAlarm
UNION ALL SELECT Id, Name, AlarmState FROM CiDigitalAlarm
UNION ALL SELECT Id, Name, AlarmState FROM CiDigitalAlarm
)
