Linking problem

  • Thread starter Thread starter Bruce Rodtnick
  • Start date Start date
B

Bruce Rodtnick

I can't seem to figure this one out:

I have three tables:
VETERANS - Contains records of WWII veterans
TAPES - The tapes we've used to record the veterans
CLIPS - Individual soundbites from the tapes

Each veteran has a least one tape.
Each tape has numerous clips.

BUT each tape can contain more than one veteran.

I have a form called VETERANS with a tab called TAPES. In that tab I
have a subform called tapes and in that subform I have another subform
called clips. When I change tapes, CLIPS should show the clips on that
tape. When I change the veteran the tapes should correspond to that
veteran.

My problem lies on how to link one veteran with more than one tape AND
one tape with more than one veteran.

Can you help me?

Bruce Rodtnick
 
---------- Bruce Rodtnick said:
I have three tables:
VETERANS - Contains records of WWII veterans
TAPES - The tapes we've used to record the veterans
CLIPS - Individual soundbites from the tapes

Each veteran has a least one tape.
Each tape has numerous clips.

BUT each tape can contain more than one veteran.
Click to expand...


Bruce,

the keywords are many-to-many relationship (AKA n:m) and link table.
In order to link the veterans with the tapes, you need a 3d table
containing the combinations of veterans with tapes. The structure
would be:

tblVeteransTapes
ID (primary key, autonumber)
VeteranID (foreign key, equals the primary key from VETERANS -
hopefully an autonumber)
TapeID (foreign key, equals the primary key from TAPES - hopefully an
autonumber)
(eventually other info related to the specific combination of veteran
and tape, for ex length of interview of veteran X on tape Y)

The fields VeteranID and TapeID would be Long integer, indexed,
allowing duplicates.

You also would have to set up relationships with relational integrity
between the tables:
VETERANS to tblVeteransTapes: 1-to-many
TAPES to tblVeteransTapes: 1-to-many

The subform on tab TAPES would be based on this link table. To
display info from TAPES you could use combos and unbound text boxes.

OK, so these are the big steps. If you need further help, post back
and please write the Access version you're using.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top