Intersection Tables - How Should I use?

[Paul Maher]

Im writing a database to capture info on my video tapes.

I'm new to this, but so far sucessfully designed tables/forms to link my Tape Cassette Details to Programmes (1 to many). Now I want to take this to the next level and to associate Actors/Actresses with Programmes.

As there are Many Programmes and Many Actors, I have built an Intersection Table with the Primary Key from the Programmes Table and Primary Key from the Actors Table.

From my Programmes Form and My Actors Form I want a way to associate one with the other (hence the intersection).

Trouble is, I dont know how to use the Intersection Table (or at least how to update it).

I think I need to Design a Form with the Programme as the Parent Record and a SubForm with Actor as Child Record. But how do i update the Intersection Table?

If you can grasp where i'm coming from and have any ideas I'd be grateful.

Thanks Paul

 

[jimbrooking]

Paul,

Your problem isn't as complex as it may seem. In this situation, I recommend looking at the intersection table in Design View, and changing the Lookup for both fields to a combo box.

I shall assume both the tblProgrammes and tblActors has an ID (Autonumber) field as its first, and some sort of name (call it Name) as its second field.

The Intersection Table, by the way, needs to have the two fields (Programme, Actor) defined as Number, with Field Size set to Long Integer, as both will be accommodating an Autonumber field.

- In the Intersection Table's design view, click the Programmes field.
- Then click the Lookup tab at the bottom of the design window
- Change the Display Control to a Combo Box
- For its Row Source enter
SELECT * FROM tblProgrammes ORDER BY Name
- Set Bound Column to 1 (the default), Column Count to 2, and
Column Widths to 0; (0 for the 1st column, take default widths for the rest).

Repeat the process for the Actors field (the Row Source is, of course, tblActors).

Now save the table and look at in datasheet view: both fields have combo boxes, and the combo box for each shows the Programme or the Actor, respectively.


Now, you can design a form with the recordsource being the intersection table. When you drag the fields from the Field List they will cone in as combo boxes, and new records will accept only previously defined Programmes and Actors.

Jim

 

[Paul Maher]

Jim,

Thanks for this. I've tried it out and it works.

Appreciate your help.

Paul