database for my movies- question about filtering

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

me@junk.net - 23 Jul 2006 21:33 GMT

I am curious how I would do this. If I make a number of fields where I can
put in actors/actresses. I could have up to 10 for just this type, or do I
make one field for this and put in all ten names? If I have 10 different how
do I then filter since I want to search for say all movies with tom cruise
but he may be listed in any one of those 10 fields?

I hope this makes sense......

thanks

Reply to this Message

Larry Linson - 23 Jul 2006 21:51 GMT

>I am curious how I would do this. If I make a number of fields where I can
> put in actors/actresses. I could have up to 10 for just this type, or do I
> make one field for this and put in all ten names? If I have 10 different
> how
> do I then filter since I want to search for say all movies with tom cruise
> but he may be listed in any one of those 10 fields?

Use a related Performers table containing a Foreign Key to the Movie Table
and the name of the actor -- one per actor/actress. Join the Movie and
Performers Tables on the Movie Table's unique key in a Query, then search on
the Actor's name. Welcome to the world of Relational Database -- proper
design makes the search/query easy as falling off the proverbial log and
avoids the problems you so perceptively identified.

Larry Linson
Microsoft Access MVP

Reply to this Message

me@junk.net - 23 Jul 2006 22:05 GMT

it's been a while for me and access (like 5 years) so i know i join tables
where they have the same field. what do you mean by "foreign key"?

Reply to this Message

Larry Linson - 24 Jul 2006 05:38 GMT

> it's been a while for me and access
> (like 5 years) so i know i join tables
> where they have the same field. what
> do you mean by "foreign key"?

Foreign Key -- a field which contains the value of a Key field in another
Table to allow joining the Records. E.G., if the unique key Field
identifying the Record in the Movie Table is MovieID, you would have a copy
of the MovieID field in a Table listing ActorsInMovies, along with another
Field which, alone or in conjunction with the MovieID (foreign key) Field,
will uniquely identify the Record in the ActorsInMovies Table.

Larry Linson
Microsoft Access MVP

Reply to this Message

Steve Hayes - 24 Jul 2006 06:49 GMT

>I am curious how I would do this. If I make a number of fields where I can
>put in actors/actresses. I could have up to 10 for just this type, or do I
>make one field for this and put in all ten names? If I have 10 different how
>do I then filter since I want to search for say all movies with tom cruise
>but he may be listed in any one of those 10 fields?

You would make a many-to-many relationship.

One table for films
One table for the actors
And a linking table to show which actors played in which films.

Then you could create queries to show which films an actor played in, or which
actos played in a particular film.

Signature

Steve Hayes from Tshwane, South Africa
http://people.tribe.net/hayesstw
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

Reply to this Message