Hi,

I have to create a database that records details of films. I need to make a column that has a list of actors/actresses that appear in that film. How would it be best to do this, I can't create a drop down list in design view because it would end up been the same list for each film.

I would be so grateful for your help

If this a school project or the like, then the simplest solution is to create a new table that has the PK of the film taken from the Films table (That's if you have one) then you need another field to store the name of the Actor/actress.

For Example:

Films table

Record 1
PK 01
Film Name "Forest Gump"

Record 2
PK 02
Film Name "Die Hard"


Actors Table

FK 01
Actor "Tom Hanks"
FK 02
Actor "Bruce Willis"
FK 02
Actor "Demi More"

Alright she is not in the film but you get my gist.

This effort is not normalised as many actors can appear in many films. Really you should have a many to many relationship between fims and actors and have a junction table in between them.

FYI -> PK = primary key; FK = foreign key.

somthing like this:

tblFilms
-------------
FilmID (Autonumber; PK)
Title
Year
.
.
.

tblActors
------------
ActorID (Autonumber; PK)
Name
.
.
.

tblFilms_Actors (this is the junction table)
-------------
FilmActors_ID (Autonumber; PK)
FilmID (FK; create as lookup from tblFilms)
ActorID (FK; create as lookup from tblActors)

then make a form with a subform (you have to make the subform before you can add it to the main form, i usually make my subs first).

the form to have all the film data in it, and the subform to choose which actors appeared in it. (if setup correctly, access will fill the FilmID(FK) automatically for you in the subform, once you are sure this is working, you can then hide that column to make it look better).