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

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, a开发者_Python百科nd 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;

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.