Access Database Relationships and Queries: A Guide to Relational Data Management

Why Relationships Are the Foundation of Useful Databases

A Microsoft Access database that stores all its data in a single table is not a relational database — it is a glorified spreadsheet. The power of Access, and of relational databases generally, comes from distributing data across multiple linked tables and using relationships to connect them. This design eliminates data duplication, maintains consistency, and makes it possible to ask complex questions of your data through queries that would be impossible in a flat-file structure.

This guide covers database normalisation principles, how to implement relationships correctly in Access, referential integrity, and the query techniques — from simple single-table queries to multi-table joins and calculated fields — that turn raw relational data into actionable information. Access is included in Office 2024 Professional Plus (€34.99) and Office 2019 Professional Plus (€26.99) for Windows. It is not included in Home and Business editions.

Understanding Normalisation: Why You Need Multiple Tables

Consider a table that tracks customer orders with these fields: OrderID, CustomerName, CustomerEmail, CustomerAddress, ProductName, ProductPrice, Quantity, OrderDate. This design has serious problems:

• Data duplication — Every order from the same customer repeats their name, email, and address. If the customer moves, you must update every record they appear in.
• Update anomalies — If a product’s price changes, you need to update it in every order row containing that product — or risk inconsistency.
• Deletion anomalies — Deleting the last order from a customer loses all customer information. Deleting the last order for a product loses the product information.

Normalisation solves these problems by separating data into purpose-specific tables: a Customers table, a Products table, and an Orders table that references both. The first three normal forms (1NF, 2NF, 3NF) cover most practical business database design.

First Normal Form (1NF)

Each field contains a single, atomic value. No repeating groups. Every record must have a unique identifier (primary key). Problem: a field called “Products Ordered” containing “Widget, Gadget, Gizmo” violates 1NF — each product needs its own row.

Second Normal Form (2NF)

All non-key fields depend on the entire primary key, not just part of it. This applies to tables with composite primary keys (a key made of multiple fields). Problem: in an OrderItems table with a composite key of OrderID + ProductID, storing ProductName violates 2NF because ProductName depends only on ProductID, not the full composite key.

Third Normal Form (3NF)

No non-key field depends on another non-key field. Problem: storing both PostCode and City in the same table creates a transitive dependency — City depends on PostCode, not on the primary key. City should be looked up from PostCode in a separate reference table.

Designing the Relationships View in Access

Access’s Relationships window is where you define and visualise the connections between tables. Open it from the Database Tools tab > Relationships.

Adding Tables to the Relationships View

1. Click Show Table in the Relationships tab that appears.
2. Double-click each table you want to include in the diagram.
3. Close the Show Table dialog. The tables appear as boxes with their field names listed.

Creating a Relationship

To link the Orders table to the Customers table:

1. Click and drag the CustomerID field from the Customers table and drop it onto the CustomerID field in the Orders table.
2. The Edit Relationships dialog appears, confirming the linked fields and showing the relationship type (One-To-Many in this case — one customer can have many orders).
3. Tick Enforce Referential Integrity.
4. Optionally tick Cascade Update Related Fields and/or Cascade Delete Related Records depending on your requirements.
5. Click Create.

A line appears between the tables with “1” at the Customers end and “∞” (infinity) at the Orders end, indicating a one-to-many relationship.

The Three Types of Relationships

One-to-Many

The most common relationship type. One record in the parent table relates to multiple records in the child table. Examples:

• One Customer → Many Orders
• One Product Category → Many Products
• One Employee → Many Timesheets

Implemented by placing the primary key of the “one” table as a foreign key in the “many” table.

One-to-One

Each record in one table relates to exactly one record in another. Used to split a wide table into two — often for security (putting sensitive fields in a separate table with tighter access controls) or performance (keeping a large memo field in a separate table). Both tables share the same primary key value.

Many-to-Many

Multiple records in Table A relate to multiple records in Table B. This cannot be implemented directly in a relational database — it requires a junction table (also called a bridge or linking table). Example: Students and Courses have a many-to-many relationship (each student takes multiple courses; each course has multiple students). The junction table Enrolments contains StudentID and CourseID as a composite primary key, creating two one-to-many relationships.

Referential Integrity and Cascade Options

Referential integrity enforces the validity of relationships — it prevents orphaned records (child records with no matching parent). With referential integrity enforced:

• You cannot add an order record with a CustomerID that does not exist in the Customers table.
• You cannot delete a customer record that has existing orders.
• You cannot change a customer’s CustomerID if orders exist with that ID.

The cascade options modify this behaviour:

• Cascade Update Related Fields — If a customer’s CustomerID changes, all related order records are automatically updated to the new ID. Rarely needed if you use system-generated numeric IDs (which should never change), but useful for natural keys.
• Cascade Delete Related Records — If a customer is deleted, all their orders are automatically deleted too. Use with caution — this can delete significant quantities of related data irreversibly.

Building Queries: The Power of Relational Data

With relationships in place, Access queries can pull together data from multiple tables in ways that would be impossible in a flat-file spreadsheet. Open the query designer from Create > Query Design.

Basic Select Query

1. In the Show Table dialog, add the tables you need.
2. Access automatically shows the relationship lines between linked tables.
3. Double-click the fields you want to include in the query result. They appear as columns in the query grid below.
4. Add criteria in the Criteria row to filter results (e.g., enter >100 in the Total column to show only orders over €100).
5. Click Run (the red exclamation mark) or switch to Datasheet View to see the results.

Joining Tables in a Query

Access performs joins automatically based on the relationships you have defined. To pull customer name, product name, quantity, and order date into a single query result:

1. Add the Customers, Orders, OrderItems, and Products tables to the query.
2. Access draws the join lines based on the relationships. Verify that all tables are correctly linked.
3. Add CustomerName from Customers, ProductName from Products, Quantity from OrderItems, and OrderDate from Orders to the query grid.

The result shows one row per order item, with all related information pulled from the appropriate source table — exactly the normalisation payoff.

Types of Joins

Double-clicking the join line in a query opens the Join Properties dialog, where you can change the join type:

• Inner Join (option 1) — Default. Returns only records where matches exist in both tables. Orders with no matching customer (which referential integrity should prevent) are excluded.
• Left Outer Join (option 2) — Returns all records from the left table and matching records from the right table. Records in the left table with no matching right-table record show null values. Use this when you want to see all customers including those with no orders.
• Right Outer Join (option 3) — The reverse: all records from the right table, matching from the left.

Calculated Fields in Queries

Access queries can include fields that calculate new values from existing data rather than simply displaying stored values. In an empty field cell in the query grid, type the calculation directly:

TotalValue: [Quantity]*[UnitPrice]

The text before the colon is the new field name; the expression after calculates its value. Access supports full expression syntax including arithmetic operators, string functions (Left, Mid, Right, Len, UCase), date functions (DateDiff, Month, Year, DateAdd), and conditional logic:

AgeCategory: IIf([Age]>=65,"Senior",IIf([Age]>=18,"Adult","Junior"))

Aggregate Queries: Totals and Summaries

Aggregate queries summarise data rather than returning individual records. Click the Totals button (Σ) in the query designer to add a “Total” row to the query grid. For each field, choose how it is treated:

• Group By — Groups records by this field’s value (equivalent to SQL GROUP BY)
• Sum — Totals numeric values within each group
• Count — Counts records within each group
• Avg — Averages numeric values
• Max / Min — Highest or lowest value
• Where — Applies criteria without grouping or displaying this field

Example: to count the number of orders and total order value per customer, set CustomerName to Group By, OrderID to Count, and TotalValue (calculated field) to Sum. The query returns one row per customer with their order count and total spend — exactly the kind of summary report Access excels at producing.

Parameter Queries for Dynamic Filtering

A parameter query prompts the user to enter a filter value each time it runs, rather than having a fixed criteria value. In the Criteria row, type the prompt text in square brackets:

[Enter start date:]

When the query runs, Access shows an input box with the prompt text and uses whatever the user enters as the filter criteria. This makes a single query design reusable for any date range without opening the designer each time.

Getting Started with Access

Access is available in Office 2024 Professional Plus and Office 2021 Professional Plus at €34.99, and Office 2019 Professional Plus at €26.99. All three versions support the relational database features covered in this guide. System requirements are the same as the broader Office suite: Windows 10 or later, 4 GB RAM minimum (8 GB recommended for larger databases), and a 64-bit processor for the best performance with larger Access database files.

For databases that will grow beyond a few thousand records or require simultaneous multi-user access, consider using Access as a front-end connected to a SQL Server Express backend (free from Microsoft) rather than a standalone .accdb file. The query design skills covered here transfer directly to that environment.

Action Queries: Updating, Appending, and Deleting Data

Select queries read data; action queries modify it. Access offers four types of action queries that automate common data manipulation tasks.

Update Query

An Update query modifies existing records meeting a condition without requiring manual editing. Example: apply a 5% price increase to all products in a specific category:

UPDATE Products
SET UnitPrice = UnitPrice * 1.05
WHERE CategoryID = 3

In the query designer, switch to Update Query via Query Design > Query Type > Update. The query grid shows an “Update To” row where you enter the new value formula, and the Criteria row where you specify which records to update. Always run a Select query first with the same criteria to verify which records will be affected before running the Update.

Append Query

An Append query copies records from one table into another. This is commonly used for archiving: copying old records from an active transactions table into an archive table before deleting them from the main table. In the query designer, select Append Query and specify the destination table. Map source fields to destination fields in the “Append To” row.

Delete Query

A Delete query removes records meeting specific conditions. Delete queries are irreversible — always back up the database or run a Select query with the same criteria first. To delete all orders older than 7 years: add the Orders table, set criteria on the OrderDate field to < DateAdd("yyyy", -7, Date()), switch to Delete Query type, and run.

Make-Table Query

A Make-Table query creates a new table containing the results of a query. This is useful for creating snapshots of data at a point in time, creating working tables for complex multi-step operations, or exporting a specific subset of data as a standalone table for external use.