Searching for records with data in parent table but none in daughter table

[jhmiii]

I am building a query for seeking records in a parent table with data in a particular field, but which have no records in a daughter table. I have a Compounds table and a daughter table, Suppliers. After entering a lot of data in both tables I need a query that highlights (and ideally counts) all of the records that have data in [Compounds]![CASNumber], but no data in [Suppliers]![Company]. Both tables are shown in the Query builder and an outer join has been selected: Include All records from 'Compounds' and only those records from 'Suppliers' where the joined fields are equal. Only some of the [Compounds] records have [CASNumber] entered and only some of the those have [Suppliers]![Company] entered. The query I constructed has only three fields: [Compounds]![CompoundNumber], [Compounds]![CASNumber], and [Suppliers]![Supplier]. I am sorting on [Compounds]![CompoundNumber]. I added this criteria to [Compounds]![CASNumber]:

[Compounds]![CASNumber] <> "IsNull"

. I also added the following criteria to [Suppliers]![Supplier]:

[Suppliers]![Company]="IsNull"

Switching to Datasheet View shows no records. I am guessing my criteria are somehow incorrect as this is the first time I have tried to use criteria.



I appreciate any and all suggestions provided.

[June7]

Post the attempted query SQL statement.

Try using the Find Unmatched query wizard.

[jhmiii]

Used the Find Unmatched query wizard and added "Is Not Null" as a criterion to the field that needs to be populated in the parent table. Worked like a charm! Thanks for the help Moderator. I made the problem much more difficult than it needed to be!!