UNION query

[skydivetom]

Experts:



I need some assistance with a UNION query. Allow me to provide some background first.

In my database, I have three "types of entities": CFT, OPT, and WS

To group a range of queries, I use the following prefixes for these types/entities, for example:
1. "CFT" -- for this query set, queries are prefixed with a "Q202"
2. "OPT" -- for this query set, queries are prefixed with a "Q302"
2. "WS" -- for this query set, queries are prefixed with a "Q402"

Now, the following three (3) queries **successfully execute** when I run them individually. SQL are as follows:

For CFT:
=====

SELECT 
T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q202_CFT_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q202_CFT_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q202_CFT_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q202_CFT_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q202_CFT_Participation_NonAssignedStaff_Input.LastName) Is Null))
ORDER BY T01_StaffMembers.All_LastName;

For OPT:
=====

SELECT T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q302_OPT_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q302_OPT_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q302_OPT_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q302_OPT_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q302_OPT_Participation_NonAssignedStaff_Input.LastName) Is Null))
ORDER BY T01_StaffMembers.All_LastName;

For WS:
=====

SELECT T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q402_WS_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q402_WS_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q402_WS_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q402_WS_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q402_WS_Participation_NonAssignedStaff_Input.LastName) Is Null));

... so far so good!

Now, while the query structures for CFT, OPT, and WS are identical (I am quite confident), I want to **UNION** all three queries into a single query. However, I'm getting an error (see attached JGP) when attempting to execute the UNION query.

Please keep in mind, I can successfully execute the UNION query for "OPT" (type/entity #2) and "WS" (type/entity #3). It's only when I add "CPT" (type/entity #1) into the UNION query when it results in the error.

Again, below SQL (OPT and WS) UNION query works fine:

SELECT T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q302_OPT_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q302_OPT_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q302_OPT_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q302_OPT_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q302_OPT_Participation_NonAssignedStaff_Input.LastName) Is Null))
ORDER BY T01_StaffMembers.All_LastName;
UNION 
SELECT T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q402_WS_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q402_WS_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q402_WS_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q402_WS_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q402_WS_Participation_NonAssignedStaff_Input.LastName) Is Null));

However, the SQL below (CFT and OPT and WS) results in the error on the [All_LastName]. What am I missing/overlooking? How can I fix the code so that I can UNION all three individual queries?

SELECT 
T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q202_CFT_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q202_CFT_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q202_CFT_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q202_CFT_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q202_CFT_Participation_NonAssignedStaff_Input.LastName) Is Null))
ORDER BY T01_StaffMembers.All_LastName;
UNION 
SELECT T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q302_OPT_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q302_OPT_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q302_OPT_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q302_OPT_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q302_OPT_Participation_NonAssignedStaff_Input.LastName) Is Null))
ORDER BY T01_StaffMembers.All_LastName;
UNION 
SELECT T01_StaffMembers.All_LastName AS LastName
FROM (T01_StaffMembers LEFT JOIN Q402_WS_Participation_NonAssignedStaff_Input 
ON (T01_StaffMembers.All_RankTitle = Q402_WS_Participation_NonAssignedStaff_Input.Rank_Grade) 
AND (T01_StaffMembers.All_FirstName = Q402_WS_Participation_NonAssignedStaff_Input.FirstName) 
AND (T01_StaffMembers.All_LastName = Q402_WS_Participation_NonAssignedStaff_Input.LastName)) 
LEFT JOIN (T01_Organization RIGHT JOIN (T01_Billets RIGHT JOIN T00_JunctionTable_OBS 
ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) 
ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
WHERE (((T01_StaffMembers.All_LastName)<>"[Vacant]") 
AND ((T01_StaffMembers.All_Onboard)=True) 
AND ((Q402_WS_Participation_NonAssignedStaff_Input.LastName) Is Null));

[JoeM]

It looks like that field appears twice in the "ORDER BY" clause of two of the Sub Queries.
Try getting rid of the "ORDER BY" line in those Sub Queries, it isn't necessary.
There really isn't any point in having those lines there in UNION queries, since you are combining them all together.

[skydivetom]

JoeM:

Wow... a simple yet perfect solution! I would have never thought that the order would cause the issue.
UNION query works great now. Thousand thanks!



EEH

[JoeM]

You are welcome!