trouble establishing a subform connection (relationship)

Leo

Registered User.
Local time
Today, 18:41
Joined
Jul 12, 2001
Messages
33
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.:confused: Also, when I look at my relation diagram – does each table have to relate to another? Can I have two separate relationships indicated?
 
You have a many-to-many relationship which requires three tables to implement. The structure should be more like this:

tblDoctor (Table)
DoctorID (autonumber primary key)
DoctorFirstName
DoctorLastName
DoctorAddress
DoctorCity
DoctorState

tblSpecialty (Table)
SpecialtyID (autonumber primary Key)
Specialty

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

Notice that it is the ID fields that are used to make the relationships. NOT the text fields. I also removed all the spaces from your names since spaces and special characters should never be used in the names of tables or columns or other objects for that matter. Rather than using ID as the name of the autonumber in each table, it is better practice to give the ID a meaningful name so that you can actually see how the tables are related just by looking at column names.
 
Last edited:
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!
 
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???
 
I'm glad you worked out the name issue. It was a typo and I corrected it. If you are having trouble with the subform, make sure that the master/child links are properly set. you might want to post the database if you still can't get it working. Compact and then zip the .mdb prior to posting.
 

Users who are viewing this thread

Back
Top Bottom