Others

How to Design an Access Marriage Table with Two Person References

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to structure a Microsoft Access database to track marriage relationships so that each person's spouse is correctly displayed, regardless of whether they are recorded as the first or second person.

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing a relational database schema for tracking people and their complex marital relationships without losing query data.
Observed behavior: Using a standard two-column relationship (Person1 and Person2) in a single marriage table omits records when running a query for a person listed as the second spouse.

Before you start

Ensure you have basic familiarity with Microsoft Access table creation, primary keys, and the Database Tools menu before modifying your relational tables.

Solution 1Recommended

Implement a Junction Table for Marriages

The most effective relational database design involves using three separate tables to link people to marriages without being constrained by sequential person ordering.

This is primarily a relational database design concept rather than a strict Access or VBA issue.

If you only join a PersonID to a Person1 field, anyone listed as Person2 is omitted from standard query results. A junction table resolves this by treating all spouses equally within the relationship, allowing queries to find either spouse seamlessly.

Create the Persons Table

Create a table named `tblPersons` containing a `PersonID` (set as the Primary Key) and other relevant personal details.

Create the Marriage Table

Create a table named `tblMarriage` containing a `MarriageID` (set as the Primary Key) and specific marriage details like the date of the event.

Set Up the Junction Table

Create a third table named `tblMarriagePerson` containing a `MarriagePersonID` (Primary Key), a `MarriageID`, and a `PersonID`.

Enforce Referential Integrity

Navigate to the 'Database Tools' tab, click 'Relationships', and enforce referential integrity between `tblPersons`, `tblMarriage`, and your new junction table.

Create the Query and Subform

Create a query that joins both people in each marriage through the junction table. Use this query to display related marriages in a subform on your main Person form.

Data Integrity Maintained: Enforcing referential integrity ensures that you cannot accidentally delete a person if they are still actively linked to a marriage record.

Free Microsoft Office alternative

Looking for a Lightweight, Reliable Office Suite?

While WPS Office does not include a database management tool like Microsoft Access, it offers a robust, free, and lightweight suite for all your document, spreadsheet, and presentation needs. Enjoy seamless compatibility with Microsoft Office formats and an intuitive, familiar user interface.

1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install WPS Office: Run the downloaded installer and follow the simple on-screen prompts to set up the software.
3. Start Creating: Launch WPS Office to immediately start creating or editing your spreadsheets, documents, and presentations with high compatibility.

Highly compatible with Microsoft Excel, Word, and PowerPoint file formats.Lightweight installation with incredibly fast loading times.Free to use with a familiar tabbed interface to boost productivity.Easily handle complex data analysis using WPS Spreadsheet instead of a database for smaller scale projects.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't a simple Person1 and Person2 column design work efficiently?

When a table relies on Person1 and Person2 columns, queries become overly complex because you must always check both columns to find a specific individual. This makes reporting, filtering, and indexing highly inefficient.

What is referential integrity in Microsoft Access?

Referential integrity is a database rule that prevents orphaned records. In a junction table setup, it ensures a marriage record cannot reference a PersonID that does not actually exist in your main tblPersons table.

Can I display the spouse's name directly on a single Person form?

Yes. By using a subform linked via the junction table query, you can configure your main Access form to display the primary person and the subform to dynamically show the corresponding spouse from the marriage relationship.

Office Quick Solutions