Table Relationships Difficulties

[Katherine]

I am just learning to use Access 2003 and understand the basics. To practice
with it, I made a database for the Olympics results with two tables - one
listing which nations won which events (Fields: Sport, Event, Gold, Silver,
Bronze, Event Date) with Event as the primary key; the other recording the
medal standings (Field: Nation, #Gold, #Silver, #Bronze, #Total) with Nation
as the primary key. I want to be able to click on a nation in the medal
standings and see which events they won, so I made a one-to-may relationship
between Nation in the medal standings table and each of Gold, Silver and
Bronze in the Events table.

However, when I click the + sign next to a country in the Medal Standings
table, all I get is a black record showing the Sport, Event, and Date fields.
How do I fix this?

Another problem I have encountered is with a second database where I have
tables with a one-to-one relationship. When I create a new record in one
table, I want it to be created in the other table as well; instead, Access
won't let me create new records in any table unless corresponding ones
already exist in the others. How do I make it work?

 

[Beetle]

Your table structure is wrong. For the purposes of this post I am
going to assume that you only want to track medal winners and that
you only want to track Nations, not individual participants.

You have two many-to-many relationships here. First, a Nation can
participate in many Events, and an Event can have many participants
(Nations). Second, a Nation can win many Medals, and any given type
of Medal can be won by many Nations. Therefore, you need a junction
table to define the relationship. An example structure would be;

(PK = Primary Key, FK = Foreign Key)

tblNations
********
NationID (PK)
NationName

tblEvents
********
EventID (PK)
EventName

tblMedals
********
MedalID (PK)
MedalDescription (Gold, Silver, Bronze)

tblEventResults (the junction table)
*************
EventID (FK to tbl Events)
NationID (Fk to tblNations)
MedalID (Fk to tblMedals)
(the three fields in this table would be a combined PK)

You would then use Forms/Subforms for data entry and queries to display
the information in various formats.

As far as your other db with the one-to-one relationship problem, 1:1
relationships are typically used when you are sub-typing or sub-classing.
They are sometimes used incorrectly. If you want to post some more info
about the structure of that db, someone may be able to offer more
specific advice.