Create a Simple Database in Microsoft Access: A Worked Example

Most guides to building an Access database describe the process in the abstract: plan your entities, define your fields, establish your relationships. Then you open Access and still do not know what to click.

So this one builds something specific. By the end you will have a working database that tracks customers and their orders — two tables, one relationship, a form to enter data and a query to answer a question. It takes about half an hour, and the pattern behind it covers a surprising share of what small businesses actually need.

Before you open Access: decide what a row is

This is the whole of the planning stage, and it is the step people skip.

Write down the things your database is about. Not the columns — the things. Customers. Orders. Products. Each of those becomes a table, and one row in that table is one of that thing: one customer, one order.

The test that catches almost every beginner's mistake: if a field would sometimes need to hold more than one value, it belongs in its own table. A customer does not have "an order"; they have however many orders they have. So orders are not columns on the customer row — they are their own table, and each order points back at a customer.

That is the difference between a database and a spreadsheet, and it is the reason a database keeps working when the spreadsheet stops.

Step 1: create the database file

Open Access and choose Blank database. Name it and pick where it goes before you click Create — Access writes the file immediately, unlike Word or Excel, so there is no "save as" moment later.

A word on where you put it: if more than one person will use this, it needs to be on a shared network drive, not synced through OneDrive or Dropbox. File-sync services and Access databases corrupt each other. This is one of the most common causes of a ruined Access file.

Access opens with a new table called Table1 in Datasheet view. Close it without saving. Datasheet view invites you to type data straight in, which lets Access guess your field types — and it guesses badly.

Step 2: build the Customers table properly

Create → Table Design. This is the view you want: it asks for field names and types before any data exists.

Enter these:

• CustomerID — AutoNumber. Right-click it and choose Primary Key.
• CompanyName — Short Text
• ContactName — Short Text
• Email — Short Text
• Phone — Short Text
• Postcode — Short Text

Save it as Customers.

Three things worth knowing about those choices:

The primary key is what makes each row uniquely identifiable, and AutoNumber is the right choice for it. Do not use the company name — two customers can share one, and names get corrected. Do not use anything meaningful at all: the moment a key carries meaning, someone will need to change it, and changing a key is how relationships break.

Phone and postcode are text, not numbers. Number fields drop leading zeros, so an 01 dialling code becomes 1. Number is for things you would do arithmetic on. You never add two phone numbers together.

Do not build a lookup at table level. Access offers a "Lookup Wizard" data type. Skip it. It stores one value and displays another, which makes queries confusing and migrations painful later. Lookups belong on the form, as combo boxes.

Step 3: build the Orders table and connect it

Same route — Create → Table Design:

• OrderID — AutoNumber, primary key
• CustomerID — Number, Long Integer
• OrderDate — Date/Time
• Description — Short Text
• Amount — Currency
• Paid — Yes/No

Save it as Orders.

That CustomerID field is the join. It holds the ID of the customer the order belongs to. It is a Number rather than an AutoNumber because it is storing a value that was generated in the other table.

Use Currency for money, never Number with two decimal places. Currency stores exact decimal values; the Number type's Double setting stores approximations, and approximations added up a thousand times stop matching the invoices.

Step 4: create the relationship

Database Tools → Relationships. Add both tables, then drag CustomerID from Customers onto CustomerID in Orders.

A dialog opens. Tick Enforce Referential Integrity. This is the important click on the whole page: it stops an order being saved against a customer that does not exist, and stops a customer being deleted while they still have orders. Without it the relationship is decorative.

Click Create. You should see a line joining the tables, marked 1 at the Customers end and ∞ at the Orders end — one customer, many orders. That symbol is confirmation the structure is right.

Step 5: a form people can actually use

Do not enter data into tables. It is unforgiving, and it is how bad data gets in.

Select the Customers table in the left-hand pane, then Create → Form. Access builds a form showing one customer at a time — and because you enforced the relationship, it adds a subform underneath listing that customer's orders. You can now add a customer and their orders in one place, and Access fills in the link between them for you.

That is the payoff for doing steps 2 to 4 properly. Get the structure right and Access builds most of the interface itself.

Before handing it to anyone, set the tab order to match how the paperwork reads, and add validation on the table where it applies: mark essential fields Required, set an order date default of =Date(), and add a validation rule such as >0 on Amount with validation text that says what to do about it.

Step 6: ask it a question

A database that only stores is a filing cabinet. Ask it something.

Create → Query Design. Add both tables — the join line appears automatically, because you defined the relationship. Drag CompanyName from Customers, and OrderDate, Description and Amount from Orders.

In the Criteria row under Paid, type False. Run it. That is every unpaid order with the customer's name against it, and it will be right every time you run it, which is more than can be said for the equivalent spreadsheet filter.

Save the query, then select it and choose Create → Report for something printable.

The mistakes that cost the most

• One big table. Customers and orders in one sheet means the customer's address is repeated on every order, and correcting it means correcting it everywhere. This is the spreadsheet habit that Access exists to break.
• Meaningful primary keys. Use AutoNumber. Anything meaningful will eventually need editing.
• Numbers for phone numbers and postcodes. Leading zeros disappear.
• Double instead of Currency for money. Rounding errors that only surface in totals.
• Skipping referential integrity. Orphaned records accumulate quietly and every report is then slightly wrong.
• Typing into tables. Build the form. It takes one click.
• Never splitting the database. The moment a second person uses it, split it — Database Tools → Access Database puts the tables in a back-end file on the server and gives each person their own front end. Sharing one file between several people is the single biggest cause of corruption.

Where this stops working

What you have built will serve a small team for years. It will not grow past the format's own limits: 2GB per file, roughly ten concurrent users in practice, no browser or mobile access since Access Web Apps were discontinued in 2018, and no meaningful way to reach it from outside the office.

If those are the walls you are hitting — people needing it from home, too many at once, or a file creeping towards the size cap — no amount of tuning moves them. That is the point where the usual answer is rebuilding as a web application.

Encouragingly, the work above is not wasted when that happens. The table structure, the relationships and the validation rules are the design, and they transfer almost directly. Working out what the data is really shaped like is the hard part, and it is the part you have just done.

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.