Access 2007 query incorrectly returns zero records

Question

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"));

Answer 1

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).

Answer 2

(((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.