non matching query wizard doesnt seem to give correct result

[GrahamGC]

I have 2 tables - T-Class info, and T-entries. Field Class no is in both tables, but I want to find the records where class no does not appear in the entries table. The query the wizard has created (SQL below) shows a lot of correct records, but misses some, and , but includes others that should not be there.
SQL is: SELECT [T-Class info].[Class No]
FROM [T-Class info] LEFT JOIN [T-Entries] ON [T-Class info].[Class No] = [T-Entries].[Class No]
WHERE ((([T-Entries].[Class No]) Is Null));
Ideas?

[June7]

Is Class No a text field? Could field have empty string instead of be Null?

If you want to provide db for analysis, follow instructions at bottom of my post.

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

[CJ_London]

think you need to provide some example data of those missed or should not be included i.e. what's in the class table and what's in the entries table that causes you to think this.

[GrahamGC]

Is Class No a text field? Could field have empty string instead of be Null?

If you want to provide db for analysis, follow instructions at bottom of my post.

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

ClassNo (spaces removed) is a number field. File attached.
Note that ClassNo 30, 50, 52, etc are in the T-Entries table, so should not come out in the 'Q-T-Class info Without Matching T-Entries -2' query. Also ClassNo 24, 25, 26, etc are not in the T-Entries table, so should be in the query result.

Copy of Spring Show.zip

[orange]

Have these tables or data within been manipulated or obtained from some other application?
When I run a query to show ClassNo that are equal in both tables. I see strange/unexpected results.

Query1 sql

SELECT [T-Class info].ClassNo
, [T-Entries].ClassNo
FROM [T-Entries] INNER JOIN [T-Class info] ON
 [T-Entries].ClassNo = [T-Class info].ClassNo;

Part of result set.



I did check your EntrantNo in your tables and they are as expected/matching.

It appears to be some sort of (data) corruption with your ClassNo????

UPDATE: CJ has identified the issue in post #6. I don't use field lookups and didn't check--my bad.

[CJ_London]

it is because you are using lookups in your table design.

In T-Entries you are storing the class ID, not classNo. But because of the lookup you are seeing classNo. General not a good idea to have lookups in table design - or anything that hides the actual data such as formatting. Causes lots of confusion such as this

Modify your query to link

ON [T-Class info].ID = [T-Entries].ClassNo