trouble establishing a subform connection (relationship)

[Leo]

I am having trouble establishing a subform connection (relationship) to a main form. I am using Access 2003.

I am creating a medical record database where I already have one form / subform relationship established that works. However, I created another form where I want to indicate the Physician(s) information within the main form and his specialties within a subform. I need to use the subform because one physician can have more than one specialty.

My fields within two tables are:
Doctor (Table)
ID
Doctor First Name (Key)
Doctor Last Name (Key)
Doctor Address
Doctor City
Doctor State
Specialty (Related Field)

Doctor Specialty (Table)
ID (Key)
Doctor First Name
Doctor Last Name
Specialty (Related Field)

The problem
I can’t establish the relationship. Also, when I look at my relation diagram – does each table have to relate to another? Can I have two separate relationships indicated?

 

[patrickcre8]

Your table structure is wrong.
Name should not be a key

 

[Leo]

My form / subform is still not working

My form / subform is still not working.

However, when I created the third table (tblDoctorSpecialty) – I was not able to create each of the fields. I managed to create:

tblDoctorSpecialty (Table)
SpecialtyID (autonumber primary Key)
DoctorID (foreign key to tblDoctor)

I could not create the field SpecialtyID (foreign key to tblSpecialty) – Access told me that I already had a field with that name.

Thank you for your help!

 

[Leo]

Is there another method I can use to get the same end result?

Is there another method I can use to get the same end result? I simply want a field that I can have multiple entries – which I was attempting to make with a subform for Specialty within my Doctor database. However, I am not getting the results I want.

From the previous suggestion – I simply went back and altered the SpecialtyID field to state DoctorSpecialtyID. However, when I created my subform I was not able to make multiple entries and have them associated with one physician.

Within the same database I do have a successful working form / subform relationship between my patients and patient visitation forms.

I can’t see what I am doing wrong???