Search code examples
sqlms-accessjet

Test if field from one table matches field from another Access 2007

SELECT Visits.KHA_ID, tblKentuckyCounties.countyName, tblHospitals.HospitalName, hospitalCounties.countyName
FROM (HospitalType INNER JOIN (hospitalCounties 
INNER JOIN 
tblHospitals ON hospitalCounties.countyID = tblHospitals.countyFK) ON HospitalType.HospitalTypeID = tblHospitals.HospitalTypeFK) 
INNER JOIN 
(tblKentuckyCounties INNER JOIN Visits ON tblKentuckyCounties.countyID = Visits.PAT_COUN) 
ON tblHospitals.Hosp_ID = Visits.HospitalID;

This is a query that will return the county a patient is from, and the hospital in which they were seen. Works fine. However, how does one make it so that the only returned results are where the counties are different? I assume this is where the 'where' keyword would be used?

Solution

  • Assuming ...

    1. tblKentuckyCounties.countyName is "the county a patient is from"
    2. hospitalCounties.countyName is county of "the hospital in which they were seen"

    ... add a WHERE clause ...

    WHERE tblKentuckyCounties.countyName <> hospitalCounties.countyName