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:
=====
Code:
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:
=====
Code:
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:
=====
Code:
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:
Code:
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?
Code:
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));