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.

 

[Katherine]

Thanks! I've still got a few questions.

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.

How does this work? Each event can only have one nation that wins Gold in
it, which is the type of relationship I'm trying to build.

Secondly: is there a way I can work this out without using IDs? The name of
each Nation and each Event is unique (I made sure of it), so can't I use
those as PKs instead of using IDs?

For the other database: it includes three relevant tables and is meant to
keep track of scientific samples. One table keeps track of the dates and
times of different stages in sample processing; another keeps track of large
amounts of information about the nature and characteristics of the samples;
and one gives details about the results from the samples. I have made a
one-to-one relationship between the Sample ID# in the Sample Information
table and the Sample Results table, and another one-to-one relationship
between the ID in the Sample Information table and the one in the Processing
Dates table. I would like to be able to add a record to the Sample
Information table, and have its ID number show up in the other tables
automatically. Instead, I can't add any records to the Sample Information
table because of a Catch-22 that says I can't add any record to one table
unless it already exists in the others.

Finally, is there a way to make a + sign appear in BOTH tables in a
one-to-one relationship, so you can go to either and view a relevant recod
from the other?

Thanks,

Katherine