Look Up Tables and Filter Results

[starkeymd]

Hi guys,

Sorry but I'm a complete novice at this so please bear with me!

I've created a table (Films). One of the fields is linked to another table (People). The table 'People' is a list of people with a categorisation next to them (e.g. actor, director etc)

Is there a way that I can create a field called 'Actors' in the table 'Films' and for the drop down only to display those people who have been categorised as an actor?

Hope this makes sense. Many thanks



Mark

[June7]

You need 3 tables because these entities have many-to-many relationship.

People
PeopleID
FirstName
LastName

Films
FilmID
DirectorID (foreign key from People, assumes film has one director, however, I know there are exceptions)
FilmName

FilmsActors
ID (primary key)
PeopleID (foreign key)
FilmID (foreign key)

How do you want to handle people who are actor and director? This may be a case that justifies Yes/No columns in People table, one for Actor and one for Director. Use these two columns to filter combobox RowSource for Director and Actor.