Others

Create Many-to-Many Records in Microsoft Access with a Junction Table

Emma BrownEmma Brown Nov 22, 2026 868 views

Question details

The user needs to save multiple country or region relationships for each source record in Microsoft Access.

Create Many-to-Many Records in Access with a Junction Table
Product
Microsoft Access
Device & OS
not provided
Scenario
A main form is bound to a source table, and a subform is bound to a junction table.
Observed behavior
IDs selected in a multiselect list box are not being stored correctly in the junction table.
Before you start

Identify the primary key in the source table and the primary key for each selectable country or region. Confirm that the junction table has matching foreign-key fields and that the main form is bound to the source table.

Solution 1Recommended

Use a Continuous Subform to Add Junction Table Records

Replace the multiselect list box with a combo box on each subform row so Access saves one relationship per record without custom selection-handling code.

This approach assumes countries and regions are stored as selectable records in one lookup table. Each junction row links one source record to one lookup record and can also store relationship details such as Status.

1
Set up the junction table

Open the junction table in Design View. Add SourceID and LocationID foreign-key fields with data types compatible with their respective primary keys. If the primary keys are AutoNumber fields, use Number fields with Field Size set to Long Integer for the corresponding foreign keys. Add an optional Status field.

2
Define the relationships

Open Database Tools > Relationships, add the source, location, and junction tables, and link each primary key to its corresponding junction-table foreign key. Select Enforce Referential Integrity where the existing data permits it.

3
Create the linked subform

Create a form based on the junction table, then add it to the source main form as a subform. In the subform control's Property Sheet, set Link Master Fields to the source primary key and Link Child Fields to SourceID.

4
Configure the location combo box

Open the subform in Design View and add a combo box bound to LocationID. Set its Row Source to a query containing the location ID and display name, and set Bound Column to the ID column. Hide the ID column with Column Widths if you want users to see only the name.

5
Add one relationship per row

In the subform's Property Sheet, set Default View to Continuous Forms and Allow Additions to Yes. Open the main form in Form View, choose a source record, and use a new subform row for each country or region. Enter Status on each row if needed.

Use a Continuous Subform to Add Junction Table Records
Prevent duplicate relationships: If each source and location pair should occur only once, create a unique composite index on SourceID and LocationID in the junction table's Indexes window.
Free Microsoft Office alternative

Work with Exported Access Data in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with office files. Export Access data to Excel when you want to review or share it in WPS Spreadsheets; WPS does not replace Access junction tables or forms.

Review and organize exported country or region data in WPS Spreadsheets.Open and edit Microsoft Excel XLSX files for easier file migration.Use a familiar spreadsheet interface with common sorting and filtering tools.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the multiselect list box not create junction records automatically?

A multiselect list box can hold several selections, but those selections do not automatically become separate junction-table rows. Saving them requires code that loops through the selected items and inserts a record for each one.

How does the subform know which source record to use?

Set the subform control's Link Master Fields property to the source primary key and Link Child Fields to the junction table's SourceID. Access then links new subform rows to the current main-form record.

Can I store a status for each selected country or region?

Yes. Add a Status field to the junction table and place a bound control for it on the continuous subform. Each relationship row can then have its own status.

What if countries and regions are in separate tables?

Use foreign keys that match your data model. If each relationship represents a country-and-region combination, add separate CountryID and RegionID fields to the junction table and bind a combo box to each field on the subform.