Short answer: Designing Access tables and relationships properly means creating one table per type of thing (customers, orders, products), adding a primary key to every table, placing foreign keys in related tables, and turning on referential integrity. In addition, use junction tables for many-to-many links, and avoid lookup or multi-value fields inside tables.
Solid table design represents the single most important decision in any Access database. When you get designing Access tables and relationships right, your forms, queries, and reports stay simple to build and remain accurate as data grows. Conversely, if you get the structure wrong, you spend years working around duplicate entries, sluggish queries, and reports nobody trusts.
New to Access? Start with Getting Started with Microsoft Access, first, then come back here.
Why Designing Access Tables and Relationships Matters
Poor table design drives many of the emergency rescues we handle every week:
- The same customer typed differently across a hundred separate records
- Monthly reports showing totals that never match
- Queries that crawl as your record count grows
- Legacy databases that feel impossible to document, modify, or migrate
Furthermore, clean modeling matters just as much later on. When your business outgrows Access and moves to SQL Server or Dataverse, a normalized design migrates smoothly; however, a messy one turns a straightforward migration into a complete redesign. For more details,, see Converting an Old Access Database to a Modern Platform.
Start With the Things You Track
Before opening Table Design, list the distinct things (entities) your business tracks. Each entity becomes its own dedicated table.
For example, a clean order-tracking database typically includes four core tables:
| Table | One row per | Key fields |
|---|---|---|
| tblCustomers | Customer | CustomerID, CompanyName, Phone, Email |
| tblProducts | Product | ProductID, ProductName, UnitPrice |
| tblOrders | Order | OrderID, CustomerID, OrderDate, Status |
| tblOrderDetails | Product on an order | OrderDetailID, OrderID, ProductID, Quantity, Price |
Follow one golden rule: store each fact in exactly one place. Specifically, a customer’s phone number lives only in tblCustomers, while tblOrders simply points to that customer instead of copying their contact details. Database architects call this practice normalization, and it keeps your records accurate over time.
Primary Keys and Foreign Keys
- Primary key: a field that uniquely identifies each row in a table. In Access, an AutoNumber ID (such as
CustomerID) provides the simplest and most reliable choice. Avoid using names, email addresses, or phone numbers as primary keys, because real-world details change. - Foreign key: a field in one table that stores the primary key from another table. For instance,
CustomerIDintblOrdersacts as a foreign key pointing back totblCustomers. Always set a foreign key to Number (Long Integer) so it matches the AutoNumber field it references.
The Three Types of Relationships
| Type | Example | How to build it |
|---|---|---|
| One-to-many | One customer has many orders | Put the “one” table’s primary key in the “many” table as a foreign key |
| Many-to-many | Orders contain many products; products appear on many orders | Use a junction table (tblOrderDetails) with a foreign key to each side |
| One-to-one | One employee has one confidential HR record | Share the same primary key; use rarely, usually to separate sensitive fields |
In practice, one-to-many links cover roughly 90% of business scenarios. Meanwhile, many-to-many links often trip up beginners because Access cannot join two tables directly in a many-to-many relationship. Instead, a junction table sits in the middle—and frequently stores useful transaction details of its own, such as the quantity and unit price at the time of the order.
Best Practices for Designing Access Tables and Relationships
- Go to Database Tools > Relationships.
- Add your tables to the window.
- Drag the primary key from the “one” table (for example, CustomerID in tblCustomers) onto the matching foreign key in the “many” table (CustomerID in tblOrders).
- In the Edit Relationships box, check Enforce Referential Integrity. This stops anyone from creating an order for a customer who doesn’t exist, or deleting a customer who still has orders.
- Decide on the cascade options:
- Cascade Update Related Fields: rarely needed when you use AutoNumber keys, since they don’t change.
- Cascade Delete Related Records: deleting a parent deletes its children. Use with care; it’s easy to lose data by accident.
- Select Create, then save the layout.
The Relationships window doubles as documentation: a clear diagram makes the database far easier for the next person to understand.
Indexes: Keeping Queries Fast
An index helps Access find records quickly, like the index in a book.
- Primary keys are indexed automatically.
- Index the foreign keys and any fields you often search, sort or filter on, such as OrderDate or Status.
- Don’t index everything. Each index slows saving slightly and adds file size.
To add one, open the table in Design View, select the field, and set Indexed to Yes (Duplicates OK).
Naming Conventions That Save Headaches
- No spaces or special characters: OrderDate, not Order Date or Order#.
- Consistent prefixes: tbl for tables, qry for queries, frm for forms, rpt for reports.
- Same name for the same field everywhere: CustomerID in both tables, not CustID in one.
- Avoid reserved words like Date, Name or Order as field names. They can cause errors in queries and VBA.
Clean names also make a future move to SQL Server or Dataverse much smoother.
Common Table Design Mistakes
- One giant table. Customer, order and product details all in one row means endless duplication.
- Repeating columns. Product1, Product2, Product3 means you need a related table instead.
- Lookup fields in tables. Access lets you add dropdown lookups at the table level, but they hide the real stored value and cause confusion in queries. Put dropdowns on forms instead.
- Multi-value fields. Convenient at first, but hard to query, and they don’t migrate cleanly to SQL Server. Use a junction table.
- Storing calculated values. Store Quantity and Price; calculate the total in a query.
- Relationships without referential integrity, which lets orphaned records creep in.
- Using text fields for numbers or dates, which breaks sorting and math.
When to Get Help
Table design is the foundation everything else rests on. Get expert eyes on it when:
- You’re inheriting a database and can’t tell how it’s structured (legacy system documentation)
- Queries are slow or reports don’t add up (Access maintenance and support)
- You’re preparing to move to SQL Server or Power Apps
The same principles apply on modern platforms too; see Low-Code Doesn’t Mean Easy for why data modeling still matters in Power Apps.
Not sure your tables are set up right? Book a free diagnostic and we’ll review your design before it causes problems.
Frequently Asked Questions
What is a primary key in Access?
A primary key is a field that uniquely identifies each record in a table. An AutoNumber ID field is the simplest and most reliable choice in Access.
How do I create a relationship between tables in Access?
Go to Database Tools > Relationships, add your tables, drag the primary key from one table onto the matching foreign key in the other, check Enforce Referential Integrity, and select Create.
How do I set up a many-to-many relationship in Access?
Create a junction table containing a foreign key to each of the two tables, then build a one-to-many relationship from each table to the junction table.
Should I use lookup fields in Access tables?
Generally no. Lookup fields at the table level hide the stored value and complicate queries. Use a related table and put the dropdown on your form instead.
Anthony Passeri, MBA, is the founder of Access Evolved and a certified Microsoft Access Expert with 21+ years of Access and relational database experience. He helps businesses keep critical Access databases running and modernize them to Power Platform or SQL Server. More about Anthony
Talk to an Access Expert
Not sure what this means for your database? Book a free diagnostic call. No sales pitch, just a straight conversation about what makes sense for your situation.
Phone: 623-267-5720
Email: info@accessevolved.com
Access Evolved | 19580 W Indian School Rd, Ste 105 #815, Buckeye, AZ 85396
Sources
The design guidance in this article reflects standard relational database practice and Access Evolved’s own experience rather than a single outside source.

