Others

Design a Microsoft Access Database for Titles, Actors, and Directors

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

Design a normalized database that associates each title with multiple people and job types.

Design a Microsoft Access Database for Titles, Actors, and Directors
Product
Microsoft Access
Device & OS
not provided
Scenario
A title can have multiple actors, directors, or other personnel, and one person can hold multiple roles.
Observed behavior
The desired design stores personnel and roles as records rather than separate fields such as Lead Actor 1 and Lead Actor 2.
Before you start

Identify the fields used to identify titles, people, and job types, and list any existing role-specific columns in the Titles table. The example below assumes each person and job type has its own primary key.

Solution 1Recommended

Create Junction Tables and a Linked Personnel Subform

Use this normalized design to add any number of people and roles to a title without creating separate columns for each role.

The PersonJobTypes table records valid person-and-job combinations. TitlePersonnel then links each title to those combinations, allowing the same person to have different jobs on the same title.

The composite key prevents the same person-and-job combination from being added twice to one title. If you need to distinguish multiple credits for the same person and job, the key and additional credit fields will need a different design.

1
Create the main tables

In Access, select Create > Table Design. Create Titles with TitleID as its primary key, People with PersonID and PersonName, and JobTypes with JobTypeID and JobTypeName. Set PersonID and JobTypeID as the primary keys of their respective tables.

2
Create person-job combinations

In Create > Table Design, create PersonJobTypes with PersonJobTypeID as its primary key and PersonID and JobTypeID as foreign-key fields. Use Design > Indexes to create a unique index on the PersonID and JobTypeID pair so each combination is stored only once.

3
Create the title junction table

Create a TitlePersonnel table with TitleID and PersonJobTypeID as foreign-key fields. In Table Design, select both rows and choose Primary Key to make the pair a composite primary key. Alternatively, use a separate primary key and a unique index on the two foreign-key fields.

4
Set relationships

Open Database Tools > Relationships and add the tables. Link Titles.TitleID to TitlePersonnel.TitleID, PersonJobTypes.PersonJobTypeID to TitlePersonnel.PersonJobTypeID, and the People and JobTypes primary keys to their matching fields in PersonJobTypes. Select Enforce Referential Integrity for each relationship. If a primary key is AutoNumber, use Number with Field Size set to Long Integer for its matching foreign key.

5
Add a linked subform

Open the Titles form in Design View and add a subform based on TitlePersonnel. In the subform control's Property Sheet, set Link Master Fields and Link Child Fields to TitleID. Remove role-specific person fields from the Titles table once the junction records are in place.

6
Add the personnel combo box

In the subform's Design View, add a combo box bound to PersonJobTypeID. Set its Row Source to a query joining PersonJobTypes, People, and JobTypes that returns PersonJobTypeID, PersonName, and JobTypeName. Set Bound Column to 1 and Column Count to 3, then hide the ID column with Column Widths so users can select a person and job.

Create Junction Tables and a Linked Personnel Subform
Add combinations before assigning them: The combo box lists records already present in PersonJobTypes. Add a person-and-job combination there before assigning it to a title.
Free Microsoft Office alternative

Organize Exported Title and Personnel Lists in WPS Office

WPS Office is a free, lightweight alternative for working with exported title and personnel lists in a familiar spreadsheet interface. Open Excel-compatible files to review and edit your data. WPS Office does not replace Access relationships, forms, or database enforcement, so migrating those features requires a database application.

Review title, person, and job records in a familiar spreadsheet layoutOpen and edit Microsoft Excel XLS and XLSX files exported from AccessUse a free, lightweight office suite for everyday document and spreadsheet workMove exported tabular data into WPS Spreadsheets without rebuilding the list
microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use separate Actor1, Actor2, and Director fields?

Separate fields limit the number of people or roles and make searches and updates harder. A TitlePersonnel junction table stores each assignment as a record, so a title can have as many assignments as needed.

Can one person be both an actor and a director on the same title?

Yes. Create separate PersonJobTypes records for that person's actor and director jobs, then add both combinations to the title in TitlePersonnel.

How do I prevent duplicate personnel assignments?

In TitlePersonnel, make TitleID and PersonJobTypeID a composite primary key in Table Design, or add a unique index on that pair through Design > Indexes.

What should the subform combo box display?

Set its Row Source to a query joining PersonJobTypes, People, and JobTypes. Return the ID, person name, and job type; bind the combo box to the ID and hide that column so users see the names.

Can WPS Office open this Access database as a relational database?

WPS Office can work with exported spreadsheet data, but it does not replace Access tables, relationships, or forms. Export a table or query from Access to an Excel file if you only need to review or edit a list in WPS Spreadsheets.