Relationships Between Tables in Access: Junctions, Cascades and Joins

Relationships are what separate a database from a pile of spreadsheets. They are also where most home-built Access databases go wrong, and the damage is quiet: nothing breaks on the day, the reports just start being slightly wrong and nobody can say when it started.

This guide assumes you can already build a table and join two of them. It covers the decisions that come after: many-to-many, the cascade options, and how to fix a structure that was built wrong.

The question that decides the structure

Ask it in both directions, out loud:

Can one customer have many orders? Can one order have many customers?

Yes then no gives you one-to-many, which is most relationships. The "many" side gets a field holding the other table's key — Orders holds a CustomerID.

Yes both ways gives you many-to-many, and it needs a third table. A product appears on many orders; an order contains many products.

No both ways gives you one-to-one, which is rare and usually means the two tables should be one. Legitimate uses exist — splitting off a large attachment field, or data only some records have — but check first that you are not just avoiding a wide table.

Many-to-many: the junction table

Access cannot store a many-to-many relationship directly. You create a third table between the two, and the important shift is realising that this table is a real thing in your business, not a technical artefact.

Orders and Products need an OrderLines table, and an order line is exactly what a paper invoice has one row of:

• OrderLineID — AutoNumber, primary key
• OrderID — Number, joins to Orders
• ProductID — Number, joins to Products
• Quantity — Number
• UnitPrice — Currency

Then two one-to-many relationships: Orders to OrderLines, and Products to OrderLines. The many-to-many is the result of those two, not a thing you declare.

Note UnitPrice sitting on the line rather than being read from the product. That is deliberate and it is the mistake most often made here: if the line only points at the product, then raising a price next year silently rewrites the value of every historical order. Prices that were charged belong on the transaction. Prices that are current belong on the product.

Referential integrity, and what it costs you

Ticking Enforce Referential Integrity when you create the relationship is what makes it real. Access then refuses to save an order line pointing at a product that does not exist, and refuses to delete a product that has order lines against it.

People untick it because it gets in the way. What it is getting in the way of is a mistake. Orphaned records — lines belonging to no order, orders belonging to no customer — do not announce themselves; they just quietly fall out of every report that joins the tables, and totals come up short with no error anywhere.

You cannot enforce it if the existing data already breaks the rule. Access will refuse and tell you so. That refusal is a finding, not an obstacle: it means orphans are already in there. Find them with a Find Unmatched Query (Create → Query Wizard) and deal with them before continuing.

The two cascade options

Once integrity is enforced, two tickboxes appear. They are not equivalent and should not be treated as a pair.

Cascade Update Related Fields — if a primary key changes, the change follows through to related records. Reasonable to enable, and it is also a hint that something is off: with AutoNumber keys, primary keys never change, so if you need this you probably have a meaningful key that should be a plain field instead.

Cascade Delete Related Records — deleting a record deletes everything hanging off it. Deleting one customer silently deletes every order they ever placed, with one confirmation prompt that does not say how many rows are going.

The safe rule: enable cascade delete only where the child records genuinely have no independent existence — order lines belong to their order and are meaningless without it, so that one is fine. Never enable it from customers to orders, from products to anything, or on any table you would need for historical reporting. For those, the right pattern is an Active yes/no field: mark the customer inactive, hide them from the forms, keep the history.

Join types, and the reports that come up short

Double-click the line between two tables in a query and you get three choices. The default (inner join) shows only records that match on both sides.

That default is behind a whole class of quietly wrong report. "Every customer and their total orders" built on an inner join omits every customer who has not ordered yet — and those are often exactly the customers the report was meant to surface. Choose the second option, an outer join, to keep all records from one side whether or not they match.

Fixing a database that was built without relationships

Very common: one wide table, or several tables that are related in principle but joined by typed-in names rather than keys.

The order of work that causes least pain:

1. Back up the file first. Copy it somewhere else. Not negotiable.
2. Find the duplicates in whatever field is doing the joining. "Hartley Ltd", "Hartley ltd" and "Hartley Ltd " are three customers to Access. A Find Duplicates query lists them.
3. Build the lookup table — one row per real customer, with an AutoNumber key.
4. Add the key field to the main table and populate it with an update query that matches on the cleaned name.
5. Check for anything unmatched before going further. A Find Unmatched query tells you what did not map.
6. Create the relationship and enforce integrity. If Access refuses, step 5 was not finished.
7. Only then remove the old text field, and repoint forms and reports at the new one.

Doing this on live data during working hours is how databases get damaged. Do it on a copy, verify the record counts match, then swap.

What good structure does and does not buy you

Get relationships right and a great deal follows automatically: forms build themselves with working subforms, queries join without being told how, and whole categories of data-quality problem simply cannot occur.

What it does not do is change the format's limits — 2GB per file, roughly ten concurrent users, no browser or mobile access since Access Web Apps were discontinued in 2018.

But it is the work that matters most if you ever move. A rebuild on a proper server database starts from the table structure and the relationships, so a well-normalised Access database is a design document that happens to run. A single wide table is a much longer conversation.

Our Microsoft Access services page covers both improving what you have and moving beyond it, and a free system review will tell you which one you need.