Access cannot display a union query in design view. But it is certainly
possible to create a union query based on linked tables, regardless of the
lack of a graphical design view for union queries. If you can describe what
it is you're trying to do with the union query, and what the problem is that
you're encountering, perhaps someone might be able to suggest a solution.
My report lists people and various data elements which show at what stage in
an application process they are. For each person, I also want to display a
list of their interviews, and this is the element for which I am trying to
use a sub-report. I have written the query as a pass-through and it works
fine as a stand-alone query, but of course is no good as a source for the
sub-report. I am using a union because the data is coming from two different
sets of tables.
My difficulty is in converting the code from Oracle SQL to Access SQL, as I
generally use pass-throughs and never need to write Access-compatible SQL.
The pass-through SQL is as follows:
select
'NEW' record_type,
red.interview_pu_id pu_id,
res.session_code session_code,
to_char(res.startdate,'dd/mm/yyyy') "Date",
re.user_1 "Type",
itd.fes_long_description "Type Desc",
red.interview_status int_status,
isd.fes_long_description "Status"
from
register_events re
left join register_event_slots res
on re.id = res.register_event_id
left join register_event_details red
on re.id = red.register_event_id
left join verifiers isd
on red.interview_status = isd.low_value
and isd.rv_domain = 'INTERVIEW_STATUS'
left join verifiers itd
on re.user_1 = itd.low_value
and itd.rv_domain = 'U_EVENT_SUBTYPE'
where
re.event_type = 'I'
and red.object_type = 'L'
and red.interview_pu_id is not null
union
select
'OLD',
ed.people_units_id,
ed.session_code,
to_char(ed.event_start,'dd/mm/yyyy'),
e.user_1,
et.fes_long_description,
e.event_status,
es.fes_long_description
from
event_details ed
left join events e
on ed.event_number = e.event_number
left join verifiers es
on e.event_status = es.low_value
and es.rv_domain = 'EVENT_STATUS'
left join verifiers et
on e.user_1 = et.low_value
and et.rv_domain = 'U_EVENT_SUBTYPE'
where
e.event_type = 'I'
and ed.type = 'PERSON'
and ed.people_units_id is not null
Any syntax pointers would be appreciated.
