Prev: Query Criteria
Next: adding a new customer
From: Jeff on 29 Apr 2010 04:11
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
--
Jeff
From: vanderghast on 29 Apr 2010 08:31
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" <Jeff(a)discussions.microsoft.com> wrote in message
news:67F12926-7F4A-494B-9C7E-4D2BC18402F5(a)microsoft.com...
>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
> --
> Jeff

From: Jeff on 29 Apr 2010 18:52
Thank you very much.
--
Jeff


"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" <Jeff(a)discussions.microsoft.com> wrote in message
> news:67F12926-7F4A-494B-9C7E-4D2BC18402F5(a)microsoft.com...
> >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
> > --
> > Jeff
>
 | 
Pages: 1
Prev: Query Criteria
Next: adding a new customer