Yep... many to many...

[rlinsurf]

Ok. I've never done a many to many before. Here's my situation. I have two databases (this is from an older version I'm now trying to get to work together).

The first is a songs database, with a composers songs that he did for various films, and other purposes, in the 30's and 40's.

Then I have the films database. In each film there are one to several of his songs. While in Songs, the song can either be in or not in a film.

What I'd like is to set up a button in each file which would find all the records related to each. So in Songs, the button would find the film a particular song is in. That's the easy part, and so that's now done.

But to go from Film, I have one field which contains all the various songs separated by semi-colons. What I'd like is to have a button which would find all those songs and display them in the Songs database.

Is there a way to do this?

Thanks

All My Best,

Jeffrey

[rockman]

To engineer a Many-to-Many relationship you have your two tables and then a third "linking" table.

1. Songs table

2. Movies table

3. Linking table

The linking table is structured:

Field #1: SongID

Field #2: MovieID

Therefore in the linking table you have many records with each record linking a song to a movie. A movie may have only one song (and therefore would be represented in the linking table once), or if the movie had 5 songs there would be 5 records in the linking table whose MovieID would represent that movie.

This is very flexible as it would even allow a SongID to appear in 2 or more records indicating (in the off chance) that a particular song was actually in more than 1 movie.

HTH,

Jeff

[rlinsurf]

Ah... got it.

Thank you :

All My Best,

Jeffrey