Query crosses 3 tables

[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

 

[vanderghast]

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

 

[Jeff]

Thank you very much.