Query to show records EXCEPT those with matching fields (1 Viewer)

[nortonm]

Hi; I have created a DB that handles Student Placement Concerns. Some requests come through on an MS Form, but not all (some by email etc).

I append the back end of the form to the main tblPlaceConcALL (All placement concerns) by way of the mediating table tblConcernFormSubmissions, which is populated directly by the MS Forms excel also using an append.

The MS Form submissions need correcting and detail adding before they can be submitted to the main table tblPlaceConcALL.

I want to display all the records in tblConcernFormSubmissions that HAVEN'T got a matching number in the tblPlaceConcALL. This is so the DB users can view/identify the Submissions that HAVEN'T yet been processed and submitted to the tblPlaceConcALL table.

They can then be shown in a report, and individual records corrected and added to, then submitted from the form to tblPlaceConcALL.

I'm making a mess of it, and can't work out the relationship I need between the two tables to do this. It's harder than I thought, or likely I'm just having a dumb day. I've attached the design view of the query i'm using.

I've used Joins where both are =, Join showing all from tblConcernFormSubmissions and only those from tblPlaceConcALL where fields are equal, but I can't get my head around joining all from tblConcernFormSubmission EXCEPT where the fields are equal!

 

[Gasman]

Use the Find Unmatched Query Wizard?

 

[GPGeorge]

Hi; I have created a DB that handles Student Placement Concerns. Some requests come through on an MS Form, but not all (some by email etc).

I append the back end of the form to the main tblPlaceConcALL (All placement concerns) by way of the mediating table tblConcernFormSubmissions, which is populated directly by the MS Forms excel also using an append.

The MS Form submissions need correcting and detail adding before they can be submitted to the main table tblPlaceConcALL.

I want to display all the records in tblConcernFormSubmissions that HAVEN'T got a matching number in the tblPlaceConcALL. This is so the DB users can view/identify the Submissions that HAVEN'T yet been processed and submitted to the tblPlaceConcALL table.

They can then be shown in a report, and individual records corrected and added to, then submitted from the form to tblPlaceConcALL.

I'm making a mess of it, and can't work out the relationship I need between the two tables to do this. It's harder than I thought, or likely I'm just having a dumb day. I've attached the design view of the query i'm using.

I've used Joins where both are =, Join showing all from tblConcernFormSubmissions and only those from tblPlaceConcALL where fields are equal, but I can't get my head around joining all from tblConcernFormSubmission EXCEPT where the fields are equal!

This is what has been called a "frustrated outer join", a term I find amusing and apt.

Change the join to a Left Outer Join, showing all records from tblConcernFormSubmissions and only matching records from tblPlaceConcALL.

Add as a criteria: tblPlaceConcALL.ConcernID Is Null

That will return records from tblConcernFormSubmissions which do not have corresponding records in tblPlaceConcALL

BTW, when sharing SQL problems in forum posts, it's almost always better to switch to SQL view and copy and paste the actual SQL rather than a screenshot of the QBE grid.

 

[nortonm]

Thanks very much George! I had a feeling i'd seen an IsNull entry on something, but I couldn't work out what to do with it. That's excellent, thanks again!!