Access 2007 query incorrectly returns zero records

I have a small database in Access that works well except for the following problem. The database is designed correctly and any the other 100 queries I have work fine. I'm trying to find the correct SQL to return the correct records. There definitely are records that match the (((tblComorbidity.comorbidityexplanation)="infection" And (tblComorbidity.comorbidityexplanation)="pressure sores")); part, but for some reason it returns nothing. Any suggestions? Thanks

    SELECT DISTINCT Person.PersonID, tblComorbidity.comorbidityexplanation
    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 
(((tblComorbidity.comorbidityexplanation)="infection" And (tblComorbidity.comorbidityexplanation)="pressure sores")开发者_运维技巧);

Your SQL is trying to get all tblComorbidity records that have an explanation of "infection" AND "pressure sores". That's not possible, since ONE tblComorbidity record has only ONE explanation.

What (I guess that) you really want is to get all Persons for which ONE tblComorbidity record with explanation = "infection" exists and ANOTHER tblComorbidity record with explanation = "pressure sores" exists. That sounds similar, but it's something different, and it needs to be done differently.

Something like the following SQL should do what you want:

SELECT DISTINCT Person.PersonID
  FROM ((((Person INNER JOIN tblComorbidityPerson AS InfPers ON Person.PersonID = InfPers.PersonID)
          INNER JOIN tblComorbidity AS Inf ON InfPers.comorbidityFK = Inf.ID)
         INNER JOIN  tblComorbidityPerson AS SorePers ON Person.PersonID = SorePers.PersonID)
        INNER JOIN tblComorbidity AS Sore ON SorePers.comorbidityFK = Sore.ID)
       INNER JOIN tblKentuckyCounties ON tblKentuckyCounties.ID = Person.County
 WHERE Inf.comorbidityexplanation = "infection"
   AND Sore.comorbidityexplanation = "pressure sores";

Basically, you need to join comorbidity twice (once for each explanation).

(((tblComorbidity.comorbidityexplanation)="infection" OR (tblComorbidity.comorbidityexplanation)="pressure sores"));

You need an OR here, not an AND. tblComorbidity.comorbidityexplanation cannot be two different values at once.

cularis is on the right track. After your where clause, you need to add

GROUP BY Person.PersonID
HAVING count(*) = 2

This will group the records in the child tables by the person, and make sure the count is 2 (one for 'infection' and one for 'pressure sores').