Return list of emp # NOT in a query, Access 2007

Question

have a table of the list of problems people can have. These people are id'd by a number. These people can have any number of problems (usually 5-10). I want to get a list of the people that tobacco DOES NOT appear as a problem for them. My solution was going to be to make a query of the people that DO have tobacco problems, and then query that query with a statement to return the numbers that are NOT in the tobacco query. Is this easily done? Is this close? query to get list of people who DO have tobacco problems SELECT DISTINCT Person.PersonID FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County WHERE ((Not (tblComorbidity.comorbidityexplanation)="tobacco")); query I want to return the people NOT in the first query SELECT Person.PersonID AS Expr1, [Tobacco Query].PersonID FROM [Tobacco Query] INNER JOIN Person ON [Tobacco Query].PersonID = Person.PersonID; JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

I am not fully savvy on the Access SQL dialect, but what you want is a LEFT JOIN, not an INNER JOIN. A LEFT JOIN is like an INNER JOIN in that it will give you all rows from the left-hand side of the join (in this case, the Person table), joined to matching rows on the right-hand side of the join. But a LEFT JOIN will also give you the rows from the left-hand side that don't have matching rows on the right-hand side. For that subset of rows, the right-hand side columns will have NULL values. So you should be able to write: SELECT Person.PersonID AS Expr1, [Tobacco Query].PersonID FROM Person LEFT JOIN [Tobacco Query] ON [Tobacco Query].PersonID = Person.PersonID WHERE [Tobacco Query].PersonID IS NULL; That will eliminate every record except the ones for which you couldn't find a matching record in [Tobacco Query], which should be the ones you're looking for. Edit: Had the Person table on the right at first... with a LEFT JOIN it should be on the left, since that's the table you want the data from.