Union Query returning Incorrect Cell Contents (1 Viewer)

[HCameron]

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));

 

[Ranman256]

instead of 1 massive union qry, convert each into its own qry:
Q1
Q2

then run each to see if it works correctly, then create the union qry as:
select * from Q1
union
select * from Q2

 

[SHANEMAC51]

SELECT Tbl_VETIDA.VETIDA, Tbl_VETIDA.[Audit date], Tbl_VETIDA.[Audit Outcome],
 Tbl_VETIDA.[Workflow Status], TQ.[TAFECode], TQ.[National Code], TQ.[Qualification Title],
 TQ.[Business Unit],
 TQ.[Activity Area],
 TQ.[Due Date for MAR/ARC Reporting],
 TQ.[Written Direction Due Date],
 TQ.[Condition Status],
 TQ.[Condition Completed Date],
 TQ.[1-1], TQ.[1-2], TQ.[1-3], TQ.[1-4], TQ.[1-5], TQ.[1-6], TQ.[1-7], TQ.[1-8], TQ.[1-9],
 TQ.[1-10], TQ.[1-11], TQ.[1-12], TQ.[1-13], TQ.[1-14], TQ.[1-15], TQ.[1-16], TQ.[1-17], TQ.[1-18], TQ.[1-19],
 TQ.[1-20], TQ.[1-22], TQ.[1-23], TQ.[1-24], TQ.[1-25], TQ.[1-26],
 TQ.[2-1], TQ.[2-2], TQ.[2-3], TQ.[2-4],
 TQ.[3-1], TQ.[3-2], TQ.[3-3], TQ.[3-4], TQ.[3-5], TQ.[3-6],
 TQ.[4-1],
 TQ.[5-1], TQ.[5-2], TQ.[5-3], TQ.[5-4],
 TQ.[6-1], TQ.[6-2], TQ.[6-3], TQ.[6-4], TQ.[6-5], TQ.[6-6],
 TQ.[7-1], TQ.[7-2], TQ.[7-3], TQ.[7-4], TQ.[7-5],
 TQ.[8-1], TQ.[8-2], TQ.[8-3], TQ.[8-4], TQ.[8-5], TQ.[8-6]
FROM Tbl_VETIDA
 LEFT JOIN Tbl_Qualifications TQ
 ON Tbl_VETIDA.VETIDA = TQ.VETIDA
WHERE (TQ.[Condition Status]="Active" AND TQ.[Condition Completed Date] Is Null)
 AND (TQ.[1-1]=True OR TQ.[1-2]=True OR TQ.[1-3]=True
 OR TQ.[1-4]=True OR TQ.[1-5]=True OR TQ.[1-6]=True
 OR TQ.[1-7]=True OR TQ.[1-8]=True OR TQ.[1-9]=True);

UNION

SELECT Tbl_VETIDA.VETIDA, Tbl_VETIDA.[Audit date], Tbl_VETIDA.[Audit Outcome],
 Tbl_VETIDA.[Workflow Status], TQ.[TAFECode], TQ.[National Code], TQ.[Qualification Title],
 TQ.[Business Unit],
 TQ.[Activity Area],
 TQ.[Due Date for MAR/ARC Reporting],
 TQ.[Written Direction Due Date],
 TQ.[Condition Status],
 TQ.[Condition Completed Date],
 TQ.[1-1], TQ.[1-2], TQ.[1-3], TQ.[1-4], TQ.[1-5], TQ.[1-6], TQ.[1-7], TQ.[1-8], TQ.[1-9],
 TQ.[1-10], TQ.[1-11], TQ.[1-12], TQ.[1-13], TQ.[1-14], TQ.[1-15], TQ.[1-16], TQ.[1-17], TQ.[1-18], TQ.[1-19],
 TQ.[1-20], TQ.[1-22], TQ.[1-23], TQ.[1-24], TQ.[1-25], TQ.[1-26],
 TQ.[2-1], TQ.[2-2], TQ.[2-3], TQ.[2-4],
 TQ.[3-1], TQ.[3-2], TQ.[3-3], TQ.[3-4], TQ.[3-5], TQ.[3-6],
 TQ.[4-1], TQ.[5-1], TQ.[5-2], TQ.[5-3], TQ.[5-4],
 TQ.[6-1], TQ.[6-2], TQ.[6-3], TQ.[6-4], TQ.[6-5], TQ.[6-6],
 TQ.[7-1], TQ.[7-2], TQ.[7-3], TQ.[7-4], TQ.[7-5],
 TQ.[8-1], TQ.[8-2], TQ.[8-3], TQ.[8-4], TQ.[8-5], TQ.[8-6]
FROM Tbl_VETIDA
 LEFT JOIN Tbl_Qualifications TQ
 ON Tbl_VETIDA.VETIDA = TQ.VETIDA
WHERE (TQ.[Condition Status]="Active" AND TQ.[Condition Completed Date] Is Null)
 AND (TQ.[1-10]=True OR TQ.[1-11]=True OR TQ.[1-12]=True
 OR TQ.[1-13]=True OR TQ.[1-14]=True OR TQ.[1-15]=True
 OR TQ.[1-16]=True OR TQ.[1-17]=True OR TQ.[1-18]=True);

 

[SHANEMAC51]

I have built a Union query which compiles 7 predecessor queries together.

something I doubt about your request

 

[SHANEMAC51]

Example of what the query is generating is, Correct result: TP01113, Produced Result: 172
It makes no sense

in this, apparently 172 is entered in some entry in this field

 

[mike60smart]

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));

Your tblQualifications does not look normalised.

Are you able to upload a copy of the database with no Confidential data?

 

[SHANEMAC51]

Your tblQualifications does not look normalised.

Are you able to upload a copy of the database with no Confidential data?

the table is very similar to a certain questionnaire - 60+ logical fields

 

[mike60smart]

Can you upload a screenshot of your Relationships Windows to show all tables?

 

[Pat Hartman]

If you are using a table level lookup, you will get the ID in a union query rather than the text value.

Does that make sense given your actual data? If so, you are a victim of table level lookups and are finding out first hand why we strongly recommend NEVER using them.

The best solution - remove the table level lookups. This might require changing the recordsource of a report that relies on seeing the text rather than the ID. Then create a final query that joins the union to the lookup table and pick up the text value

The inferior solution - create a final query that joins the union to the lookup table and pick up the text value but don't bother to fix the underlying problem