Building Your First Database in Microsoft Access: A Practical Guide

Introduction

Microsoft Access is the most misunderstood application in the Office suite. Many users treat it as a more complex spreadsheet, others avoid it entirely because it looks intimidating, and some do not even know it exists. This is a missed opportunity — Access is one of the most practical tools for managing structured data that does not yet justify a full SQL Server database or cloud CRM platform.

If you manage client records, stock inventories, project logs, booking systems, or any data that is currently living in a disorganised collection of spreadsheets, Access can replace that with a proper relational database — one with consistent data entry, no duplicate records, and the ability to generate reports and queries in seconds.

This guide walks through building a practical Access database from scratch, covering tables, relationships, forms, and queries. We will use a simple customer order database as our working example.

What Access Does (and Does Not Do)

Access is a desktop relational database management system. It stores data in structured tables, enforces relationships between those tables, and provides tools for entering, querying, and reporting on that data — all without requiring SQL knowledge, though SQL is available for advanced users.

Access works well for:

• Databases with up to around 2 GB of data (roughly 100,000+ records in most use cases)
• Single-user or small multi-user (up to around 10 simultaneous users) environments
• Replacing complex, fragile Excel workbooks with a proper data structure
• Rapid prototyping of database applications before investing in enterprise software

Access is not suitable for:

• High-volume transaction systems with hundreds of concurrent users
• Web-facing applications (though Access can serve as a backend for some web tools)
• Datasets exceeding several hundred thousand records (performance degrades)

Access is only available in the Windows editions of Office, specifically in the Professional Plus and higher tiers. It is included in Office 2024 Professional Plus.

Core Concepts Before You Start

Tables

Tables are where data is stored. Each table represents a distinct entity — a customer, an order, a product. Each row in a table is one record; each column is one attribute (field) of that record.

Relationships

Relationships connect tables together using matching fields. In a customer orders database, the Orders table references the Customers table using a CustomerID field. This means you store the customer’s details once (in the Customers table) and reference them repeatedly in the Orders table — this is the core principle of relational database design, called normalisation.

Queries

Queries extract, filter, sort, and calculate data from one or more tables. They do not modify the stored data — they produce a dynamic view of it based on criteria you define.

Forms

Forms provide a user-friendly interface for data entry. Rather than entering data directly into a table’s grid view (which looks like a spreadsheet and provides no guidance or validation), a form presents fields in a logical layout with labels, dropdown menus, and validation rules.

Reports

Reports produce formatted, printable or exportable summaries of your data — grouped by category, subtotalled, or filtered to a specific date range.

Step 1: Plan Your Database Structure

Before opening Access, sketch your table structure on paper. For a simple order management database, you need three tables:

• Customers: CustomerID (primary key), FirstName, LastName, Email, Phone, Address, City, Postcode
• Products: ProductID (primary key), ProductName, Description, UnitPrice, StockQuantity
• Orders: OrderID (primary key), CustomerID (foreign key → Customers), OrderDate, Status, TotalValue
• OrderItems: ItemID (primary key), OrderID (foreign key → Orders), ProductID (foreign key → Products), Quantity, UnitPrice

Note that Orders and Products are connected through the OrderItems table. This junction table is necessary because one order can contain multiple products, and one product can appear in many orders — a many-to-many relationship that requires an intermediate table.

Step 2: Create a New Database

1. Open Microsoft Access and click Blank Database
2. Name your file (e.g., OrderManagement.accdb) and choose a save location. The .accdb format is the standard Access 2007+ format.
3. Click Create. Access creates the database and opens a blank table in Datasheet view.

Step 3: Create Your Tables in Design View

Design View gives you full control over field types and properties. Close the default blank table without saving it, then:

1. On the Create tab, click Table Design
2. The Design View shows three columns: Field Name, Data Type, and Description

For the Customers table, enter the following fields:

To set CustomerID as the primary key: click the CustomerID row, then click the Primary Key button in the ribbon (it looks like a golden key). Save the table and name it Customers.

Repeat this process for Products, Orders, and OrderItems, using the field structures defined in Step 1. For the Orders table, set the OrderDate field as Date/Time, Status as Short Text with a default value of "Pending", and TotalValue as Currency.

Step 4: Define Relationships

Once your tables are created, define the relationships between them:

1. Go to Database Tools → Relationships
2. Click Show Table and add all four tables to the Relationships window
3. Drag the CustomerID field from the Customers table and drop it onto the CustomerID field in the Orders table. The Edit Relationships dialogue appears.
4. Tick Enforce Referential Integrity. This prevents orders from being created for customers that do not exist, and prevents customer records from being deleted if they have associated orders.
5. Click Create. A line appears between the two tables showing the relationship type (one-to-many: one customer can have many orders).
6. Repeat for Orders → OrderItems (using OrderID) and Products → OrderItems (using ProductID).

Save the relationships layout. This is one of the most important steps in database design — properly enforced relationships prevent data integrity problems that are difficult to fix after a database has been in use for some time.

Step 5: Create a Data Entry Form

Forms make data entry cleaner and less error-prone. To create a form for entering new customers:

1. In the left-hand Navigation Pane, click the Customers table to select it
2. On the Create tab, click Form. Access generates a basic form automatically.
3. Switch to Layout View to adjust the arrangement of fields
4. For a more customised form, use Form Design view, which gives you precise control over positioning, font, colours, and control properties

Add validation to key fields: click the Email field in Form Design view, open the property sheet (F4), and under the Data tab, set a Validation Rule such as Like "*@*.*" and a Validation Text message like "Please enter a valid email address."

Step 6: Build a Query

Queries are how you extract useful information from your database. To find all orders placed in the last 30 days:

1. On the Create tab, click Query Design
2. Add the Orders and Customers tables
3. Double-click to add fields: FirstName, LastName, OrderDate, Status, TotalValue
4. In the Criteria row under OrderDate, enter: >=Date()-30
5. Click Run (the exclamation mark button) to see the results

Save the query with a descriptive name (e.g., OrdersLast30Days). You can run it at any time from the Navigation Pane to see current data.

Step 7: Create a Report

Reports turn query results into formatted documents. Select the OrdersLast30Days query in the Navigation Pane, then go to Create → Report. Access generates a basic report. Use Layout View to adjust column widths, add a title, include grouping (e.g., by Status), and add summary totals (right-click the TotalValue column and select Total → Sum).

Next Steps

With your basic database structure in place, you can extend it with lookup tables (a separate Status table with a dropdown for the Status field), a main navigation form using Access macros, and automated reports that export to PDF. Access’s documentation and the built-in templates are good starting points for common use cases such as contact management, asset tracking, and event management.

Access is exclusively available in the Professional Plus editions of Office. Office 2024 Professional Plus for Windows includes Access 2024 alongside the full suite at £29.99.