Iain Young

Hi folks, I've got a quick MS Access question and was wondering if anyone out there can point me in the right direction.

Basically I have two tables, Movies and Actors. In the Movies table there is a field called Actors which I want to populate with values from the Actors table. I can create a relationship ok for a single item (i.e each movie entry can have 1 actor), but what I actually want to do is to have the actors field contain multiple references to the actors table.

My first thought would be to create a relationships table which would list every movie to actor relationship, remove the actors field from the Movies table, and handle everything in the form. This should work ok but it seems a bit messy, so I was wondering if anyone had any other ideas of how I should approach this?

Thanks,
Iain

chiark

Common problem in referential databases.

To represent a many:many relationship you need to use a separate table to encapsulate the relatonships.

So if you've got

Table Movie: MovieID, Name, other guff.
Table Actor: ActorID, name, other guff...

you need at a minimum
Table MovieActor: MovieID,ActorID

In the movieactor table you put entries that show what actor is in what movie. You can give it a primary key, or you can use MovieID and ActorID as a composite key... Though that could create problems.

What you might want to do is have:

Table MovieActor: MAID, MovieID, ActorID, ActorRole

That would allow you to capture what role someone played in the movie, and also would allow you to store data when the same actor had two roles in a single movie.

Cheers,
Nick.

chiark

...you already knew that, didn't you? D'oh!

You may want to look at using subforms to capture the data. Use lists with a lookup on the actor table to capture the data into the subform...

SJ_Skyline

Many to many relationships are BAD!!

you need something like the following:

tblMovies
- intMovieID
- strMovieName

tblMoviesJunctionActors
- intMovieID
- intActorID

tblActors
- intActorID
- strActorName

tblMovies has a 1 to many relationship with tblMoviesJunctionActors

tblActors has a 1 to many relationship with tblMoviesJunctionActors


hope this helps!

Rich

Iain Young

Thanks Nick.

I thought I'd end up having to do that, I just thought I'd check before getting started (I didn't know whether access had any fancy stuff to make this easier, as I've only used mysql up to now). I'll take a look at the subforms...

All this just to get my dvd collection list onto my Ipaq

Cheers,
Iain

chiark

just store the dvd title and have a big button that links to to IMDB.com

Iain Young

Thanks Rich, that was the sort of thing I was thinking about.

I think I know what I'm going to do now....

Cheers,
Iain

Iain Young

That's cheating

I would do it, but I can't link to the internet whilst standing in mvc / hmv wondering if I've already got that film / version of that film or not (I've got a few). Besides, I enjoy the challenge

Cheers,
Iain

TopBanana

Yes, you need to use a third link table