Union Query returning Incorrect Cell Contents (1 Viewer)

HCameron

New member
Local time
Tomorrow, 01:06
Joined
Feb 15, 2022
Messages
1
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

Well-known member
Local time
Today, 11:36
Joined
Apr 9, 2015
Messages
4,120
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

Active member
Local time
Today, 18:36
Joined
Jan 28, 2022
Messages
294
Code:
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);
 

mike60smart

Registered User.
Local time
Today, 16:36
Joined
Aug 6, 2017
Messages
1,235
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

Active member
Local time
Today, 18:36
Joined
Jan 28, 2022
Messages
294
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

Registered User.
Local time
Today, 16:36
Joined
Aug 6, 2017
Messages
1,235
Can you upload a screenshot of your Relationships Windows to show all tables?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:36
Joined
Feb 19, 2002
Messages
36,868
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
 

Users who are viewing this thread

Top Bottom