Query crosses 3 tables

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have 3 tables. tblPatient contains ID, Name, Age....., tblAdmission
contains ID, PatientID, AdmissionDate....., tblCertificate contains ID,
PatientID, AdmissionDate.....

How to combine the following 2 queries into one that crosses 3 tables?
(Please note that the WHERE clause in the second query has error by itself).
Thank you.

SELECT tblPatient.*, tblCertificate.*
FROM tblPatient INNER JOIN tblCertificate
ON tblPatient.ID = tblCertificate.PatientID
WHERE tblCertificate.ID = Me!txtID

SELECT tblPatient.*, tblAdmission.*
FROM tblPatient INNER JOIN tblAdmission
ON tblPatient.ID = tblAdmission.PatientID
WHERE tblAdmission.AdmissionDate = tblCertificate.Admission
 
You need parenthesis, something like:

SELECT tblPatient.*, tblCertificate.*, tblAdmission.*
FROM ( tblPatient INNER JOIN tblCertificate
ON tblPatient.ID = tblCertificate.PatientID )
INNER JOIN tblAdmission
ON tblPatient.ID = tblAdmission.PatientID
AND tblAdmission.AdmissionDate = tblCertificate.Admission
WHERE tblCertificate.ID = Me!txtID


Alternatively, you can use the graphical designer and simply make the three
"links" .



Vanderghast, Access MVP
 
Back
Top