Query Problem...Dont know what to call it

[aqif]

Hi

I've got a table in which I have following fields plus many others:

DIAGNOSIS
---------
PatientID
PrimaryHosp
SecondaryHosp

I have a Hospital Table also

HOSPITAL
--------
HospID
HospName

In access I have linked HospID with PrimaryHosp although I also wanted to link it with SecondaryHosp but Access only allows one relationship.

Now is it possible that I could get a query like:

PatientID PrimaryHosp HospName SecondaryHosp HospName
------------------------------------------------------------------
1 2 Hospital 2 1 Hospital 1
1 3 Hospital 3 2 Hospital 2

Thanxx in advance

Cheers!
Aqif

 

[Liv Manto]

Just add the Diagnosis table, this time Access would name it with an Alias Diagnosis_1.

Link HospID to Secondary Hospital.

Make sure that the link from the HospID to the TWO Diagnosis tables are left outer join.

(Right CLick, join properties, choose "Include all Hosp ID...."

Drag the fields.

If you want to rename the field caption in query, put "NewName:" in front of the field name.