troubleshooting Question

Need help creating a MS Access query using Left Join

Avatar of pcalabria
pcalabriaFlag for United States of America asked on
Microsoft Access
3 Comments1 Solution68 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros