Search code examples
ms-accessms-access-2013lookup-tables

LookUp issue in Union query MS Access

I have two queries which are having fields Role and Description. I've created a Union query out of these two queries.

Query 1 is from table Building1 and second query from table Building2

But field Role is a combo filled by LookUp query from Base table Role and used Bound Column to display name

When I execute the queries, I am getting the results correctly whereas when I run the union, field Role giving me the RoleId instead of name

Could someone tell/explain why it behaves and what's the work around

Solution

  • Add the table Role twice to the query.

    Then join their Ids to the RoleId of Building1 and Building2 respectively.

    Something like:

    Select Building1.*, Role.Name
From Building1
Inner Join Role On Role.Id = Building1.RoleId
Union All
Select Building2.*, Role.Name 
From Building2
Inner Join Role On Role.Id = Building2.RoleId