What Is a Relational Database in Microsoft Access?

A relational database is a database that stores information in multiple tables and connects those tables using relationships. In Microsoft Access, the goal is to organize data so that you store each fact once, avoid duplicates, and can still answer real business questions with queries, forms, and reports.

If you build a simple “phone book” table, everything fits in one list: name, phone number, address. But most real scenarios contain repeating groups and one-to-many patterns. For example:

• One client can have many projects.
• One order can contain many line items.
• One employee can submit many timesheets.

If you try to put all of that into one table, you either:

• repeat the same client information on every project row, or
• create multiple “Project1 / Project2 / Project3” columns, which becomes unmanageable.

A relational design solves this by keeping clients in one table and projects in another table, then linking them.

Suppose you run a small consulting business:

• You need a list of clients and their contact details.
• You also need a list of projects you do for those clients.
• Each project belongs to exactly one client, but a client may have many projects.

This is a classic one-to-many relationship: one client → many projects.

In Access, the “client card” becomes a Clients table, and each “project card” becomes a row in a Projects table. The relationship is created by storing the client’s primary key in the Projects table as a foreign key.

The best practice is to use a numeric primary key (AutoNumber) for each table, then store that key in related tables. This avoids problems caused by name changes, duplicate names, and inconsistent spelling.

• Clients table: one row per client
• Projects table: one row per project (includes a ClientID foreign key)

CREATE TABLE Clients (
  ClientID AUTOINCREMENT CONSTRAINT PK_Clients PRIMARY KEY,
  ClientName TEXT(255) NOT NULL,
  AddressLine TEXT(255),
  City TEXT(100),
  StateProv TEXT(50),
  PostalCode TEXT(20)
);

CREATE TABLE Projects (
  ProjectID AUTOINCREMENT CONSTRAINT PK_Projects PRIMARY KEY,
  ProjectName TEXT(255) NOT NULL,
  DueDate DATETIME,
  EstimatedHours LONG,
  ClientID LONG NOT NULL,
  CONSTRAINT FK_Projects_Clients
    FOREIGN KEY (ClientID) REFERENCES Clients (ClientID)
);

Once tables are related, Access can combine them in useful ways:

• Queries can show projects with their client names.
• Forms can use a main form (Client) with a subform (Projects).
• Reports can group projects under each client.

Most importantly, you update client information in one place—without needing to update many rows.

SELECT
  c.ClientName,
  p.ProjectName,
  p.DueDate,
  p.EstimatedHours
FROM
  Clients AS c
  INNER JOIN Projects AS p
    ON c.ClientID = p.ClientID
ORDER BY
  c.ClientName,
  p.DueDate;

Older Access examples sometimes relate tables using a name field (for example, storing the client name inside the Projects table). That approach works for demonstrations, but it is not recommended for real systems because names can change and may not be unique.

Best practice in Access is:

• Use AutoNumber as the primary key.
• Store that numeric key as the foreign key in related tables.
• Enforce referential integrity in the Relationships window.

A relational database in Access uses multiple tables to store related information without duplication. In the client/project example:

• Clients stores client facts once.
• Projects stores project facts once.
• Projects.ClientID links each project back to its client.

This design supports reliable queries, clean forms, and accurate reports as your database grows. The next lesson will show additional examples of data that naturally belongs in multiple related tables.