Using another table in Where clause

[mrgreen]

This probably sounds stupid but I need to ask because well, I'm stupid. I have created a separate table of partnumbers that DO NOT get included in a SELECT Query for another table (tblmain). How can I use this table in my WHERE clause? Below is my SQL statement and the table with the partnumbers is tblreportpartnumbers.

strSQL = "SELECT SUM([Yield]) AS [My Sum] FROM tblmain"
strSQL = strSQL & " WHERE(([Area]='Cell 1 Supfina' AND "
strSQL = strSQL & "([Date entered]=#" & dtmdate & "#"
strSQL = strSQL & " AND([Part Number]=???????)))"
strSQL = strSQL & " Group By [Date entered];"

Any help would be wonderfulllll

Thanks A lot

 

[mikebaldam]

inner - outer joins

It depends on how the tables are linked etc...

by far the easiest way to do this is to create a query using the query wizard and add more than one table to the query... then click the View / Design /SQL button to get to SQL view and then use that SQL code to modify your SQL variable accordingly...

 

[RuralGuy]

Look at the Not IN clause.

 

[mrgreen]

Hello All.

Thanks for the replies. Below is what I've come up with.

strSQL = "SELECT Sum(tblmain.Yield) AS [Not CP]"
strSQL = strSQL & " FROM tblreportpartnumbers RIGHT JOIN tblmain "
strSQL = strSQL & "ON tblreportpartnumbers.[Part Number] = tblmain.[Part Number] "
strSQL = strSQL & "WHERE (((tblreportpartnumbers.[Part Number]) Is Null) AND ((tblmain.Area)='Cell 1 Supfina')) "
strSQL = strSQL & "GROUP BY tblmain.[Date entered] "
strSQL = strSQL & "HAVING (((tblmain.[Date entered])=#" & dtmdate & "#));"

Thanks for the suggestions and I apologize for not replying back in a timely manner.