Hi all,
I have built a Union query which compiles 7 predecessor queries together. In the results table, one of the fields is returning results which are incorrect.. They are not the wrong record, they are cell contents which are not entered at all in the original table which the queries draw data from. All the 7 predecessor queries show the cell contents correctly. All the other fields from the union query results are accurate too. I cannot figure out what I have done wrong to create this. The Union query code is below, just for two of the queries, there was not enough space for all 7 (sorry it is a long one). The Field generating strange results is Tbl_Qualifications.[TAFECode]
Example of what the query is generating is, Correct result: TP01113, Produced Result: 172
It makes no sense
SELECT Tbl_VETIDA.VETIDA, Tbl_VETIDA.[Audit date], Tbl_VETIDA.[Audit Outcome], Tbl_VETIDA.[Workflow Status], Tbl_Qualifications.[TAFECode], Tbl_Qualifications.[National Code], Tbl_Qualifications.[Qualification Title], Tbl_Qualifications.[Business Unit], Tbl_Qualifications.[Activity Area], Tbl_Qualifications.[Due Date for MAR/ARC Reporting], Tbl_Qualifications.[Written Direction Due Date], Tbl_Qualifications.[Condition Status], Tbl_Qualifications.[Condition Completed Date], Tbl_Qualifications.[1-1], Tbl_Qualifications.[1-2], Tbl_Qualifications.[1-3], Tbl_Qualifications.[1-4], Tbl_Qualifications.[1-5], Tbl_Qualifications.[1-6], Tbl_Qualifications.[1-7], Tbl_Qualifications.[1-8], Tbl_Qualifications.[1-9], Tbl_Qualifications.[1-10], Tbl_Qualifications.[1-11], Tbl_Qualifications.[1-12], Tbl_Qualifications.[1-13], Tbl_Qualifications.[1-14], Tbl_Qualifications.[1-15], Tbl_Qualifications.[1-16], Tbl_Qualifications.[1-17], Tbl_Qualifications.[1-18], Tbl_Qualifications.[1-19], Tbl_Qualifications.[1-20], Tbl_Qualifications.[1-22], Tbl_Qualifications.[1-23], Tbl_Qualifications.[1-24], Tbl_Qualifications.[1-25], Tbl_Qualifications.[1-26], Tbl_Qualifications.[2-1], Tbl_Qualifications.[2-2], Tbl_Qualifications.[2-3], Tbl_Qualifications.[2-4], Tbl_Qualifications.[3-1], Tbl_Qualifications.[3-2], Tbl_Qualifications.[3-3], Tbl_Qualifications.[3-4], Tbl_Qualifications.[3-5], Tbl_Qualifications.[3-6], Tbl_Qualifications.[4-1], Tbl_Qualifications.[5-1], Tbl_Qualifications.[5-2], Tbl_Qualifications.[5-3], Tbl_Qualifications.[5-4], Tbl_Qualifications.[6-1], Tbl_Qualifications.[6-2], Tbl_Qualifications.[6-3], Tbl_Qualifications.[6-4], Tbl_Qualifications.[6-5], Tbl_Qualifications.[6-6], Tbl_Qualifications.[7-1], Tbl_Qualifications.[7-2], Tbl_Qualifications.[7-3], Tbl_Qualifications.[7-4], Tbl_Qualifications.[7-5], Tbl_Qualifications.[8-1], Tbl_Qualifications.[8-2], Tbl_Qualifications.[8-3], Tbl_Qualifications.[8-4], Tbl_Qualifications.[8-5], Tbl_Qualifications.[8-6]
FROM Tbl_VETIDA LEFT JOIN Tbl_Qualifications ON Tbl_VETIDA.VETIDA = Tbl_Qualifications.VETIDA
WHERE (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-1])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-2])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-3])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-4])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-5])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-6])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-7])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-8])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-9])=True));
UNION
SELECT Tbl_VETIDA.VETIDA, Tbl_VETIDA.[Audit date], Tbl_VETIDA.[Audit Outcome], Tbl_VETIDA.[Workflow Status], Tbl_Qualifications.[TAFECode], Tbl_Qualifications.[National Code], Tbl_Qualifications.[Qualification Title], Tbl_Qualifications.[Business Unit], Tbl_Qualifications.[Activity Area], Tbl_Qualifications.[Due Date for MAR/ARC Reporting], Tbl_Qualifications.[Written Direction Due Date], Tbl_Qualifications.[Condition Status], Tbl_Qualifications.[Condition Completed Date], Tbl_Qualifications.[1-1], Tbl_Qualifications.[1-2], Tbl_Qualifications.[1-3], Tbl_Qualifications.[1-4], Tbl_Qualifications.[1-5], Tbl_Qualifications.[1-6], Tbl_Qualifications.[1-7], Tbl_Qualifications.[1-8], Tbl_Qualifications.[1-9], Tbl_Qualifications.[1-10], Tbl_Qualifications.[1-11], Tbl_Qualifications.[1-12], Tbl_Qualifications.[1-13], Tbl_Qualifications.[1-14], Tbl_Qualifications.[1-15], Tbl_Qualifications.[1-16], Tbl_Qualifications.[1-17], Tbl_Qualifications.[1-18], Tbl_Qualifications.[1-19], Tbl_Qualifications.[1-20], Tbl_Qualifications.[1-22], Tbl_Qualifications.[1-23], Tbl_Qualifications.[1-24], Tbl_Qualifications.[1-25], Tbl_Qualifications.[1-26], Tbl_Qualifications.[2-1], Tbl_Qualifications.[2-2], Tbl_Qualifications.[2-3], Tbl_Qualifications.[2-4], Tbl_Qualifications.[3-1], Tbl_Qualifications.[3-2], Tbl_Qualifications.[3-3], Tbl_Qualifications.[3-4], Tbl_Qualifications.[3-5], Tbl_Qualifications.[3-6], Tbl_Qualifications.[4-1], Tbl_Qualifications.[5-1], Tbl_Qualifications.[5-2], Tbl_Qualifications.[5-3], Tbl_Qualifications.[5-4], Tbl_Qualifications.[6-1], Tbl_Qualifications.[6-2], Tbl_Qualifications.[6-3], Tbl_Qualifications.[6-4], Tbl_Qualifications.[6-5], Tbl_Qualifications.[6-6], Tbl_Qualifications.[7-1], Tbl_Qualifications.[7-2], Tbl_Qualifications.[7-3], Tbl_Qualifications.[7-4], Tbl_Qualifications.[7-5], Tbl_Qualifications.[8-1], Tbl_Qualifications.[8-2], Tbl_Qualifications.[8-3], Tbl_Qualifications.[8-4], Tbl_Qualifications.[8-5], Tbl_Qualifications.[8-6]
FROM Tbl_VETIDA LEFT JOIN Tbl_Qualifications ON Tbl_VETIDA.VETIDA = Tbl_Qualifications.VETIDA
WHERE (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-10])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-11])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-12])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-13])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-14])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-15])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-16])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-17])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-18])=True));
I have built a Union query which compiles 7 predecessor queries together. In the results table, one of the fields is returning results which are incorrect.. They are not the wrong record, they are cell contents which are not entered at all in the original table which the queries draw data from. All the 7 predecessor queries show the cell contents correctly. All the other fields from the union query results are accurate too. I cannot figure out what I have done wrong to create this. The Union query code is below, just for two of the queries, there was not enough space for all 7 (sorry it is a long one). The Field generating strange results is Tbl_Qualifications.[TAFECode]
Example of what the query is generating is, Correct result: TP01113, Produced Result: 172
It makes no sense
SELECT Tbl_VETIDA.VETIDA, Tbl_VETIDA.[Audit date], Tbl_VETIDA.[Audit Outcome], Tbl_VETIDA.[Workflow Status], Tbl_Qualifications.[TAFECode], Tbl_Qualifications.[National Code], Tbl_Qualifications.[Qualification Title], Tbl_Qualifications.[Business Unit], Tbl_Qualifications.[Activity Area], Tbl_Qualifications.[Due Date for MAR/ARC Reporting], Tbl_Qualifications.[Written Direction Due Date], Tbl_Qualifications.[Condition Status], Tbl_Qualifications.[Condition Completed Date], Tbl_Qualifications.[1-1], Tbl_Qualifications.[1-2], Tbl_Qualifications.[1-3], Tbl_Qualifications.[1-4], Tbl_Qualifications.[1-5], Tbl_Qualifications.[1-6], Tbl_Qualifications.[1-7], Tbl_Qualifications.[1-8], Tbl_Qualifications.[1-9], Tbl_Qualifications.[1-10], Tbl_Qualifications.[1-11], Tbl_Qualifications.[1-12], Tbl_Qualifications.[1-13], Tbl_Qualifications.[1-14], Tbl_Qualifications.[1-15], Tbl_Qualifications.[1-16], Tbl_Qualifications.[1-17], Tbl_Qualifications.[1-18], Tbl_Qualifications.[1-19], Tbl_Qualifications.[1-20], Tbl_Qualifications.[1-22], Tbl_Qualifications.[1-23], Tbl_Qualifications.[1-24], Tbl_Qualifications.[1-25], Tbl_Qualifications.[1-26], Tbl_Qualifications.[2-1], Tbl_Qualifications.[2-2], Tbl_Qualifications.[2-3], Tbl_Qualifications.[2-4], Tbl_Qualifications.[3-1], Tbl_Qualifications.[3-2], Tbl_Qualifications.[3-3], Tbl_Qualifications.[3-4], Tbl_Qualifications.[3-5], Tbl_Qualifications.[3-6], Tbl_Qualifications.[4-1], Tbl_Qualifications.[5-1], Tbl_Qualifications.[5-2], Tbl_Qualifications.[5-3], Tbl_Qualifications.[5-4], Tbl_Qualifications.[6-1], Tbl_Qualifications.[6-2], Tbl_Qualifications.[6-3], Tbl_Qualifications.[6-4], Tbl_Qualifications.[6-5], Tbl_Qualifications.[6-6], Tbl_Qualifications.[7-1], Tbl_Qualifications.[7-2], Tbl_Qualifications.[7-3], Tbl_Qualifications.[7-4], Tbl_Qualifications.[7-5], Tbl_Qualifications.[8-1], Tbl_Qualifications.[8-2], Tbl_Qualifications.[8-3], Tbl_Qualifications.[8-4], Tbl_Qualifications.[8-5], Tbl_Qualifications.[8-6]
FROM Tbl_VETIDA LEFT JOIN Tbl_Qualifications ON Tbl_VETIDA.VETIDA = Tbl_Qualifications.VETIDA
WHERE (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-1])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-2])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-3])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-4])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-5])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-6])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-7])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-8])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-9])=True));
UNION
SELECT Tbl_VETIDA.VETIDA, Tbl_VETIDA.[Audit date], Tbl_VETIDA.[Audit Outcome], Tbl_VETIDA.[Workflow Status], Tbl_Qualifications.[TAFECode], Tbl_Qualifications.[National Code], Tbl_Qualifications.[Qualification Title], Tbl_Qualifications.[Business Unit], Tbl_Qualifications.[Activity Area], Tbl_Qualifications.[Due Date for MAR/ARC Reporting], Tbl_Qualifications.[Written Direction Due Date], Tbl_Qualifications.[Condition Status], Tbl_Qualifications.[Condition Completed Date], Tbl_Qualifications.[1-1], Tbl_Qualifications.[1-2], Tbl_Qualifications.[1-3], Tbl_Qualifications.[1-4], Tbl_Qualifications.[1-5], Tbl_Qualifications.[1-6], Tbl_Qualifications.[1-7], Tbl_Qualifications.[1-8], Tbl_Qualifications.[1-9], Tbl_Qualifications.[1-10], Tbl_Qualifications.[1-11], Tbl_Qualifications.[1-12], Tbl_Qualifications.[1-13], Tbl_Qualifications.[1-14], Tbl_Qualifications.[1-15], Tbl_Qualifications.[1-16], Tbl_Qualifications.[1-17], Tbl_Qualifications.[1-18], Tbl_Qualifications.[1-19], Tbl_Qualifications.[1-20], Tbl_Qualifications.[1-22], Tbl_Qualifications.[1-23], Tbl_Qualifications.[1-24], Tbl_Qualifications.[1-25], Tbl_Qualifications.[1-26], Tbl_Qualifications.[2-1], Tbl_Qualifications.[2-2], Tbl_Qualifications.[2-3], Tbl_Qualifications.[2-4], Tbl_Qualifications.[3-1], Tbl_Qualifications.[3-2], Tbl_Qualifications.[3-3], Tbl_Qualifications.[3-4], Tbl_Qualifications.[3-5], Tbl_Qualifications.[3-6], Tbl_Qualifications.[4-1], Tbl_Qualifications.[5-1], Tbl_Qualifications.[5-2], Tbl_Qualifications.[5-3], Tbl_Qualifications.[5-4], Tbl_Qualifications.[6-1], Tbl_Qualifications.[6-2], Tbl_Qualifications.[6-3], Tbl_Qualifications.[6-4], Tbl_Qualifications.[6-5], Tbl_Qualifications.[6-6], Tbl_Qualifications.[7-1], Tbl_Qualifications.[7-2], Tbl_Qualifications.[7-3], Tbl_Qualifications.[7-4], Tbl_Qualifications.[7-5], Tbl_Qualifications.[8-1], Tbl_Qualifications.[8-2], Tbl_Qualifications.[8-3], Tbl_Qualifications.[8-4], Tbl_Qualifications.[8-5], Tbl_Qualifications.[8-6]
FROM Tbl_VETIDA LEFT JOIN Tbl_Qualifications ON Tbl_VETIDA.VETIDA = Tbl_Qualifications.VETIDA
WHERE (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-10])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-11])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-12])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-13])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-14])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-15])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-16])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-17])=True)) OR (((Tbl_Qualifications.[Condition Status])="Active") AND ((Tbl_Qualifications.[Condition Completed Date]) Is Null) AND ((Tbl_Qualifications.[1-18])=True));