Need help creating a MS Access query using Left Join

pcalabria
pcalabria used Ask the Experts™
on
I need help creating a recordset when two tables are joined with left join.

The following query displays all the records in Parts4Sales and the matching ComponentMaster record ID.  

While I can see records which do not match a record in the ComponentMaster table, because the ID appears as blank, I can not selected them.


Here's my query:

SELECT Parts4Sales.PartID, ComponentMaster.ID
FROM Parts4Sales LEFT JOIN Component Master ON (Parts4Sales.SearchNumber = ComponentMaster.SearchNumber) AND (Parts4Sales.LineID = ComponentMaster.LineID);


My plan is to create a recordset using a query that includes only records without a match in the ComponentMaster table, and then loop through the recordset running code to create a matching ComponentMaster record.

Please help!  THanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Distinguished Expert 2017
Commented:
You need to add a WHERE clause

Where ComponentMaster.SearchNumber Is Null OR ComponentMaster.LineID is Null
pcalabria

Author

Commented:
I thought I had tried that before posting... but apparently made a mistake.

Thanks again, as always, Pat!
PatHartman
Distinguished Expert 2017

Commented:
You're welcome:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial