Simple Query for multiple ands

[Lorlai]

I am trying to create a query that will only append product data if the product number does not currently exist in the master sheet, and only if that non-existing product number also has a product status of some form of "active" or "planning". I'm hitting a roadblock with access and can't make my query work. Currently my query is:

INSERT INTO [Products] ( [Product Number], [Product Name], [Product Color], [Product Status], [Product Start], [Product Finish], [Company Status], [Product Price] )
SELECT [Master Data].[Product Number], [Master Data].[Product Name], [Master Data].[Product Color], [Master Data].[Product Status], [Master Data].[Product Start], [Master Data].[Product Finish], [Master Data].[Company Status], [Master Data].[Product Price]
FROM [Products] RIGHT JOIN [Master Data] ON [Products].[Product Number] = [Master Data].[Product Number]
WHERE ((([Products].[Product Number]) Is Null));

I've tried adding the criteria for product status, but cannot get any results:

WHERE ((([Master Data].[Product Status]) Like "*Active*") AND (([Products].[Product Number]) Is Null));

I figure it must be an easy solution to pull only Product Numbers that are null and have a status of "*Active*" or "*Planning*", but I can't find anything online and can't figure out what I'm doing wrong. Any advice would be greatly appreciated!

[June7]

Don't understand. Which is the 'master sheet'.

[Lorlai]

Sorry, was working back and forth from Access to Excel! I mean the Master Data table, not master sheet.

[jwill]

Hey Lorlai,

Your statement is working for me. Are you sure you have records to match your criteria?

[Lorlai]

Here is some dummy data, maybe I'm doing something incorrectly with my logic:

Master Data Table

Product name	Product Color	Product Status	Product Start	Product Finish	Company Status	Product Price
12345	truck	red	active	now	later	good	lots
45678	car	black	in planning	now	later	good	lots
20245	bottle	red	active later	now	later	good	lots
9991	pc	silver	complete	before	now	good	lots
6692	phone	grey	complete	before	now	good	lots

Product Table

Product Number	Product name	Product Color	product status	product start	product finish	company status	product price
45678	car	black	in planning	now	later	good	lots
9991	pc	silver	complete	before	now	good	lots
6692	phone	grey	complete	before	now	good	lots
564564	frog	green	complete	before	now	good	lots
231247	dog	brown	complete	before	now	good	lots

Based on these two tables, I want my query to find Products 12345 and 20245, which both are "*active*" and are not currently in Products, and append them into the product table. My current query doesn't find anything, even though i know there are these two products. Is something wrong with my logic perhaps?

Thanks for you help!

[orange]

[Products].[Product Number] = [Master Data].[Product Number]

Your Master Data Table does not have a Product Number field according to your sample layout, but that might just be editing.

[June7]

I created tables from the sample data and ran your query (copy/paste, no changes) and it worked. Those two records were added to the Products table.

INSERT INTO Products ( [Product Number], [Product Name], [Product Color], [Product Status], [Product Start], [Product Finish], [Company Status], [Product Price] )
SELECT [Master Data].[Product Number], [Master Data].[Product Name], [Master Data].[Product Color], [Master Data].[Product Status], [Master Data].[Product Start], [Master Data].[Product Finish], [Master Data].[Company Status], [Master Data].[Product Price]
FROM Products RIGHT JOIN [Master Data] ON Products.[Product Number] = [Master Data].[Product Number]
WHERE ((([Master Data].[Product Status]) Like "*Active*") AND ((Products.[Product Number]) Is Null));

Do you want to provide your project for analysis? Follow instructions at bottom of my post.

[Lorlai]

Something must be wrong with my computer. This was not working, and neither was an import process I had created. I go away for a day, and suddenly everything works! Thank you for all of your help!

[orange]

Message to remember ---- take more vacation days!!

Glad it's working.