Link one field to 3 (1 Viewer)

[$t3ff]

I have a database which works as an information system for the upcomming olympics.

In tblAthletes is...

• AthleteID (Primary Key)
• FirstName
• Surname
• And Various other fields not needed in this problem

In tblResults is...

• Gold
• Silver
• Bronze
• Also other field in this table

What I am aiming for is to have Gold AND Silver AND Bronze all as foreign keys of the AthleteID field.

Is there a way to do this or can another way be suggested. I am working on the 2000 version of access.

 

[Uncle Gizmo]

Shouldn't you also have a table to record the event they won the medal in?

 

[boblarson]

I think your design is slightly off. You should have:

• AthleteID (Primary Key)
• FirstName
• Surname
• And Various other fields not needed in this problem

And in tblResults

• ResultID (Primary Key)
• AthleteID (Foreign Key)
• MedalType (Foreign Key from Medals table)
• EventID (Foreign Key from Events table)

tblMedals

• MedalID (Primary Key)
• MedalDescription

tblEvents

• EventID (Primary Key)
• EventDescription

 

[$t3ff]

Thus far tblResults has...

• ResultID
• Gold
• Silver
• Bronze
• Event (From a table of events)
• Sport (From a table of sports)
• Venue (From a table of venues)

You have medal description, are you saying that in that field there would be the values gold, silver and bronze?

 

[boblarson]

Thus far tblResults has...

• ResultID
• Gold
• Silver
• Bronze
• Event (From a table of events)
• Sport (From a table of sports)
• Venue (From a table of venues)

You have medal description, are you saying that in that field there would be the values gold, silver and bronze?

You do NOT have a separate field for Gold, Silver, and Bronze. You set it up the way I showed as MedalType and you only store the ID number from the medal table which has Gold, Silver, and Bronze as the DESCRIPTIONS. So, you would store the ID which would likely be 1 for Gold, 2 for Silver, and 3 for Bronze. Then, if you need to view the descriptions anywhere you just tie the tables together via a query.

 

[$t3ff]

I now understand what you mean, thankyou for your help.