From Flat Files to Relational Power: Creating Structured Access Tables from Your Excel Workbooks

 

Excel is well known for quick and easy data analysis. However, many workbooks end up as flat files. It is common to have one large file that contains all the information, with the same data repeated multiple times. Flat files, such as single-sheet Excel spreadsheets, often contain redundant or denormalized data, making them inefficient for complex queries, data integrity, and scalability. Microsoft Access can transform that same data into a relational database with separate structured tables connected by keys, allowing you to store each “thing” once and relate everything properly.

In this tutorial, we will go from flat files to relational power by creating structured Access tables from your Excel workbooks. You will learn how to convert a typical Excel flat file into a well-designed relational Access database.

Understanding the Problem with Flat Files

A flat file is a single data table or sheet that stores every piece of information about every entity in one large grid. Excel workbooks often start this way: one sheet, hundreds of columns, and data repeated across thousands of rows. Suppose you have sales data in Excel with columns such as OrderID, OrderDate, CustomerName, CustomerCity, ProductName, Quantity, UnitPrice, SalesAmount, Cost, and Profit.

The three anomalies that destroy data integrity:

• Update anomaly: The same customer appears on multiple rows. If their phone number changes, you must update every row. If you miss one, your database contains contradictory information
• Insert anomaly: You cannot record a new customer until they place an order because there is nowhere to store a customer who has no orders yet
• Delete anomaly: If you delete order 1003, you permanently lose all information about that customer

Step 1: Preparing Your Excel Workbook

Before importing, clean and organize your data to avoid errors in Access.

• Ensure the first row contains clear column headers (no merged cells or blanks)
• Remove any blank rows or irrelevant data
• Check data types:
  • Format dates as Date, numbers as Number, and currency as Currency
• Remove duplicates
• Split combined fields if required (for example, separate full names into first and last names)

Identifying Normalization Opportunities:

In a flat file, data is often repeated (for example, the same customer appears multiple times). Plan to split the data into separate entities:

• Customers: Unique customer information
• Products: Unique product details
• Orders: Transaction details linking customers and products

Close and save the Excel file. If your dataset is very large, consider splitting it into multiple sheets for easier import.

Tip: If your workbook has multiple sheets, treat each as a potential table source.

Step 2: Importing Excel Data into Microsoft Access

Creating a New Access Database:

• Open Microsoft Access
• Click Blank desktop database and provide a name
• Browse to your desired location and save the file
• Click Create, which opens a new database with a default blank table

Access databases use the .accdb file extension by default.

Importing Excel Data into Access:

Import your flat file as a starting point, then refine it.

• Go to the External Data tab >> select New Data Source >> select From File >> select Excel

• Select Import the source data into a new table in the current database
• Browse and select the Excel file
• Click OK

• Choose the sheet Sales >> click Next

• Check First Row Contains Column Headings
• Click Next

• Review data types (especially dates and currency)
• Click Next

• Let Access add a primary key or choose one (for example, select OrderID if it is unique)
• Click Next

• Name the table ImportedSales >> click Finish

You now have a single table in Access mirroring your flat Excel sheet. However, it is still “flat.” Next, we will normalize it.

Step 3: Normalizing Data into Multiple Tables

Normalization reduces redundancy by splitting data into related tables. We will aim for at least Second Normal Form (2NF), which removes partial dependencies. Below are multiple ways to create tables; you can choose the method you prefer.

Normalization basics:

• Each table stores one type of entity
• Each row is unique
• No repeating groups
• Use IDs to connect tables

Creating the Customers Table Using an SQL Query

Create a new table using a query that extracts distinct customers.

• Go to the Create tab >> select SQL Query
• Paste the following SQL query

SELECT DISTINCT
ImportedSales.CustomerName,
ImportedSales.CustomerEmail,
ImportedSales.Region
INTO Customers
FROM ImportedSales
WHERE ImportedSales.CustomerName IS NOT NULL;

• Click Run (!) and then click Yes

• The Customers table is now populated from the query
• Right-click the Customers table >> select Design View

• Add CustomerID as an AutoNumber field
• Set it as the Primary Key
• Keep CustomerName, CustomerEmail, and Region
• Save the table

You can create the Products and Orders tables by following a similar process.

Products table

SELECT DISTINCT
ImportedSales.ProductName,
ImportedSales.Category,
ImportedSales.UnitPrice,
ImportedSales.Cost
INTO Products
FROM ImportedSales
WHERE ImportedSales.ProductName IS NOT NULL;

Creating a Table Using Query Design

Use Query Design to extract unique data from the ImportedSales table.

• Go to the Create tab >> select Query Design
• Add the ImportedSales table from the Add Tables pane
• Select fields from the dropdown or drag the following to the query grid:
  • ProductName, Category, and UnitPrice

• Go to the Query Design tab >> select Make Table
• In the Query Type group, name the table Products

• Click Run (the exclamation mark icon) and select Yes
• The table will be created and populated, but you will need to add ProductID manually or via an update query

• Open the Products table in Design View
• Add ProductID as an AutoNumber field
• Set it as the Primary Key
• Save the Products table

• You can repeat a similar process for the Customers table if needed

Creating the Orders Table

• Query all orders, but replace CustomerName, CustomerEmail, and Region with CustomerID
• Replace ProductName and Category with ProductID
• Go to the Create tab >> select Table Design
• For the Orders table, define the following fields:
  • OrderDetailID (AutoNumber, Primary Key)
  • OrderID (Number, Long Integer)
  • OrderDate (Date/Time)
  • CustomerID (Number, Foreign Key)
  • ProductID (Number, Foreign Key)
  • Quantity (Number)
  • UnitPrice (Currency)
  • Discount (Number)
  • SalesAmount (Currency)
  • Cost (Currency)
  • Profit (Currency)
• Save the table as Orders

Now, use a similar procedure to populate the Orders table:

• Go to the Create tab >> select Query Design
• Add the ImportedSales, Customers, and Products tables from the Add Tables pane
• Drag the following fields to the query grid:
  • OrderID
  • OrderDate
  • CustomerID
  • ProductID
  • Quantity
  • UnitPrice
  • Discount
  • SalesAmount
  • Cost
  • Profit
• Set joins: ImportedSales with Customers
  • Join ImportedSales.CustomerName to Customers.CustomerName
  • Join ImportedSales.CustomerEmail to Customers.CustomerEmail
  • Join ImportedSales.Region to Customers.Region
• Set joins: ImportedSales with Products
  • Join ImportedSales.ProductName to Products.ProductName
  • Join ImportedSales.Category to Products.Category

• Go to the Query Design tab >> select Append
• In the Append dialog, select Orders >> click OK

• Click Run (the exclamation mark icon)
• When the warning message appears, click Yes

• The matching rows will be appended to the Orders table

If your dataset is small, you can manually copy and paste unique values into the new tables and assign IDs.

Deleting or archiving the imported table: Once the data is normalized, you can delete ImportedSales or keep it as a backup.

Step 4: Defining Relationships Between Tables

Link tables to enforce referential integrity.

• Go to the Database Tools tab >> select Relationships
• Add tables:
  • Drag Customers, Products, and Orders from the navigation pane into the window
• Creating relationships:
  • Drag Customers.CustomerID to Orders.CustomerID
  • Drag Products.ProductID to Orders.ProductID

For each relationship:

• In Relationship Design, select Edit Relationships
• Check Enforce Referential Integrity
• Optionally check:
  • Cascade Update Related Fields
  • Cascade Delete Related Records (use carefully)

• Click OK. This creates a one-to-many relationship (one customer, many orders)

• Close and save the layout

Your database is now relational, and changes in one table propagate appropriately. You have also prevented orphan records, such as order details without a corresponding order.

Step 5: Enhancing with Queries, Forms, and Reports (Optional)

To leverage your new structure:

Creating a query: View all orders with customer details:

• Go to the Create tab >> select Query Design
• Add all three tables (Access will auto-join based on defined relationships)
• Select fields such as OrderID, OrderDate, CustomerName, ProductName, and SalesAmount
• Click Run to view results

• You will see a query result showing orders with customer details

Building forms:

• Go to the Create tab >> select Form Wizard
• Select a table (for example, Orders)
• Select all fields from Available Fields
• Click Next

• Select the relevant layout options >> click Next
• Finally, click Finish

• You can now use the Orders form for easier data entry

Generating reports:

• Go to the Create tab >> select Report Wizard
• Select a query or table >> group by CustomerID
• Click Next

• Select the relevant options >> click Next
• Finally, click Finish

• This report is ideal for summaries

Common Pitfalls and Tips

• Data type mismatches: Ensure Excel and Access data types align (for example, do not import text fields as numbers)
• Large datasets: If Excel contains over one million rows, split files or use Power Query in Excel first
• Normalization levels: For simplicity, we stopped at basic normalization; advanced users can aim for Third Normal Form (3NF) or higher
• Backups: Always save versions of your Access file
• Alternative tools: If Access is unavailable, consider SQL Server Express, MySQL, or Excel’s Power Pivot for lightweight relational modeling
• Testing: Enter sample data and run queries to verify relationships (for example, deleting a customer should be restricted if referential integrity is enforced)

Conclusion

By following these steps, you can transform a flat Excel workbook into a structured relational database in Access. This approach improves data efficiency, consistency, and query performance without leaving the Microsoft ecosystem. Practice with your own datasets to refine the process, and consider exploring Third Normal Form and additional optimization techniques as your database grows.