Why does another instance of the primary key table get created automatically?

[Access_Novice]

Screen shot attached. I am working with the tables 'tblGymnast' and 'tblAllergies.' Any gymnast may enter up to five different allergies if any at all. I linked the Primary Key, AllergyID in tblAllergies to Allergy1 of tblGymnast. So far no problem. But then I attempted to link AllergyID in tblAllergies to Allergy2 of tblGymnast and automatically a new table, tblAllergies_1 was created.



Why was this created?

Wouldn't I have to link AllergyID to Allergy2 of tblGymnast so that a second allergy can be entered? I was going to do Allergy3, but I got this other table created. How can I link all 5 Allergy fields in tblGymnast to the primary key in tblAllergies?

[June7]

Because this is linking the same table to multiple fields. Pull in tblAllergies table 5 times.

What is the second link on Allergy2 field that is outside the image?

Multiple like fields is not a normalized structure. If you try to filter records for all gymnasts with penicillin allergy, have to search all 5 fields. Also, is 5 fields enough? What about the exception who has 7 allergies? A normalized, related table would allow for any number allergies.

[Access_Novice]

What is the second link on Allergy2 field that is outside the image?

I just noticed that. Allergy2 is linked to another table that was automatically created called tblAllergies_2.

Multiple like fields is not a normalized structure. If you try to filter records for all gymnasts with penicillin allergy, have to search all 5 fields. Also, is 5 fields enough? What about the exception who has 7 allergies? A normalized, related table would allow for any number allergies.

If a gymnast had 7 allergies, wouldn't they need 7 fields to store 7 allergies?

[June7]

7 fields unless you use a related normalized table, like:

GymnastID	AllergyID
1	2
1	5
4	8
6	3
6	2