Most Access databases that go wrong were never really relational. They started as one wide table copied out of a spreadsheet, and everything painful about them afterwards — duplicate customers, totals that disagree, a form that takes ten seconds to open — traces back to that.
This guide covers how relationships actually work in Access, how to set them up properly, and the mistakes that cause most of the trouble.
The idea in one sentence
Store each fact once, in the table it belongs to, and link tables together rather than repeating data.
If a customer's address appears in your orders table, you have as many versions of that address as they have orders, and they will drift apart. Put the address in a Customers table, give each customer an ID, and have orders refer to that ID. Change it once and every order sees the change.
Primary keys and foreign keys
A primary key uniquely identifies a row. Access offers an AutoNumber field by default and it is almost always the right choice — resist using something meaningful like an email address or part number, because real-world identifiers change and change breaks references.
A foreign key is the field in the other table holding that ID. Orders holds CustomerID; that is the whole mechanism.
Setting relationships up in Access
Use Database Tools → Relationships. Drag the primary key onto the matching foreign key, and in the dialog that appears:
- Enforce Referential Integrity — tick it. This stops an order existing for a customer who does not, which is the single most valuable guarantee Access gives you.
- Cascade Update Related Fields — generally safe, and largely irrelevant if you used AutoNumber keys, since they never change.
- Cascade Delete Related Records — be careful. It means deleting a customer silently deletes all their orders. Most businesses want the deletion refused instead, which is what leaving it unticked does.
Set relationships before building forms and reports. Access uses them to suggest joins, and retrofitting them to an existing database is considerably more work.
The three relationship types
One-to-many is almost everything: one customer, many orders. One product, many order lines.
Many-to-many cannot be done directly — an order has many products and a product appears on many orders. You need a third table in between (OrderLines), holding one row per product per order plus the things that belong to that pairing, like quantity and price at the time. This junction table is the piece people most often miss, and its absence is why so many home-built databases have Product1, Product2, Product3 columns.
One-to-one is rare and usually means the two tables should be one.
Mistakes that cause the most trouble
- The wide table. One table with sixty columns imported from a spreadsheet. Splitting it later is possible but far harder than starting right.
- Repeating columns. Phone1, Phone2, Phone3 means you need a Phones table. The moment you number a column, you have found a missing relationship.
- Lookup fields at table level. Access lets you attach a lookup directly to a table field. It looks convenient and hides what is really stored, confuses queries, and complicates any future migration. Build lookups on forms with a combo box instead.
- Skipping referential integrity because it rejects existing data. That rejection is the point — it is telling you there are orphaned records to fix.
- Storing calculated values. Do not keep a Total column that is Quantity times Price. Calculate it in a query or on the report, so it cannot go stale.
When Access stops being enough
A well-structured Access database will comfortably run a small business for years. The limits are not about your design — they are the format's: a 2GB file ceiling, roughly ten concurrent users before instability, and no browser or mobile access at all.
The usual first step when you outgrow it is to keep the Access front end and move the tables to SQL Server, which removes the size and concurrency limits without rebuilding anything. After that, rebuilding as a web application removes the rest.
Good news either way: a properly relational Access database is the best possible starting point for that move. The structure is already right, so the migration is mechanical rather than a redesign. A badly structured one has to be untangled first — another reason to get the relationships right now.
If you want to know which side of that line yours sits on, our Microsoft Access services page explains how we work, and a free system review reads your actual database and tells you plainly — including when the answer is to leave it where it is.
Comments
Be the first to comment on this post.
