Creating subqueries in SQL view

[AmyM]

I am stuck and in need of help with an access query. I am supposed to create a query that selects all the fields from a table for all the products that weigh more than 100 pounds. Then, switch to SQL view add a subquery that selects all products whose color is green.

I have the query built, now I just need help on the SQL and adding a subquery. This is what I have:

SELECT tblProduct.[ProductID], tblProduct.[CompanyID], tblProduct.[ProductType], tblProduct.[Price], tblProduct.[Unit], tblProduct.[Color], tblProduct.[Material], tblProduct.[Size], tblProduct.[WeightInLbs], tblProduct.[DiscountOffered]


FROM tblProduct
WHERE (((tblProduct.[WeightInLbs])>100))
AND Color Green
(Select Color
FROM tblProduct
WHERE Color="Green");

The problem is the color. What is wrong with my statement about the color of the product?

[ajetrumpet]

it should be:

SELECT tblProduct.[ProductID], tblProduct.[CompanyID], tblProduct.[ProductType],
tblProduct.[Price], tblProduct.[Unit], tblProduct.[Color], tblProduct.[Material], 
tblProduct.[Size], tblProduct.[WeightInLbs], tblProduct.[DiscountOffered]
FROM tblProduct
WHERE (((tblProduct.[WeightInLbs])>100))
AND tblProduct.Color IN 
(Select tblProduct.Color
FROM tblProduct
WHERE tblProduct.Color="Green");

however, if the colors are in a different table, the query will be different.

furthermore, if you're a student and the product colors are ALSO in the products table, tell your instructor that this assignment task is aimed towards teaching you the wrong type of query skills.

[AmyM]

Thank you! It worked perfect! I can see what i was missing...