I'd suggest normalising some more. Have your clinics table just clinic_id
and clinic_name. Add a doctor_clinic table that matches up doctor_ids with
clinic_ids.
Then you can go...
SELECT clinics.clinic_name,
Doctors.doctor_name,
Locations.address
FROM (
(
clinics INNER JOIN doctor_clinic
ON clinics.clinic_id = doctor_clinic.clinic_id
) INNER JOIN doctor
ON doctor_clinic.doctor_id = doctor.doctor_id
) INNER JOIN address
ON doctor.doctor_id = address.doctor_id
ORDER BY
clinics.clinic_name,
clinics.clinic_id,
Doctors.doctor_name,
Doctors.doctor_id,
Locations.address
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:bounce-cfaussie-
> [EMAIL PROTECTED] On Behalf Of Tom MacKean
> Sent: Thursday, 22 April 2004 1:54 p.m.
> To: CFAussie Mailing List
> Subject: [cfaussie] Multiple Tables (newbie question)
>
> Hi List,
>
> I have tried to get my head around this, but failed.
>
> Simply, Consider three MS Access tables...
>
> * DOCTORS - Fields: Doctor_ID, Doctor_Name
> * LOCATIONS - Fields: Doctor_ID, Address (each doctor can have
> more than one address)
> * CLINICS - Fields: Doctor_ID, Clinic_Name (each doctor can work
> at more than one clinic)
>
> What I want to do is display...
>
> Clinic Name 1
> Doctor Name 1
> - Address 1-1
> - Address 1-2
>
> Doctor Name 2
> - Address 2-1
> - Address 2-2
>
> Clinic Name 2
> Doctor Name 1 (same doc different clinic)
> - Address 1-1
> - Address 1-2
>
> Doctor Name 3
> - Address 3-1
> - Address 3-2
> - Address 3-3
>
> ...etc...
>
> Any help/suggestions most welcome...
>
> Thanks
>
> Tom
> ________________________________
>
> Tom MacKean
> Copywriter/Designer
> Phone: (02) 9229 6465
> Fax: (02) 9231 6965
> Mobile: 0414 312 803
>
> NOTICE: Medical and scientific information provided in print and
> electronically by Sydney IVF might not be relevant to your own
> circumstances and should always be discussed with your own doctor before
> you act on it. This communication is confidential and may contain
> copyright or otherwise protected information of Sydney IVF Limited or a
> third party. If you are not the intended recipient of this communication
> please immediately let us know by reply email or telephone us on +61 2
> 9221 5964, delete the communication and destroy all copies.
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to leave-cfaussie-
> [EMAIL PROTECTED]
>
> MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
> http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
