Access Basics

7 Access Table Design Mistakes That Cause Big Problems

Bad table design is the root cause of most Access performance, data quality, and migration problems. Here are the seven most common mistakes — and how to fix them.

A
Anthony Passeri, MBA
••6 min read
7 Access Table Design Mistakes That Cause Big Problems

Most Access problems trace back to table design decisions made years ago. A database that seemed fine at 5,000 rows becomes painful at 100,000. Queries that worked when the team was three people break when it grows to fifteen. Reports that used to run in seconds now take minutes.

The root cause is almost always the tables. Here are the seven mistakes that cause the most damage — and what to do about them.

1. Storing Multiple Values in One Field

This is the most common and most damaging table design mistake in Access databases.

It looks like this: a Products table with a field called Categories containing values like "Electronics, Computers, Laptops". Or an Orders table with a LineItems field containing "Widget A x2, Widget B x1".

The problem: you cannot query individual values reliably. You cannot count how many products are in the Electronics category without parsing strings. You cannot join to a Categories table. You cannot enforce referential integrity. Every query that touches this field becomes a string manipulation exercise.

The fix: normalize. If a product can belong to multiple categories, create a ProductCategories junction table with ProductID and CategoryID columns. One row per relationship, every relationship queryable.

This is the first principle of relational database design, and violating it creates problems that compound over time.

2. Using Text Fields for Numbers and Dates

Access will let you store "12/31/2024" in a Short Text field. It will also let you store "$1,250.00" as text. Both choices cause problems.

Text fields sort alphabetically, not numerically or chronologically. "9/1/2024" sorts after "12/31/2023" alphabetically but before it chronologically. Date calculations (DateDiff, DateAdd) do not work on text values. Numeric comparisons (> 1000) do not work on text numbers.

The fix: use the correct data type from the start. Dates go in Date/Time fields. Currency goes in Currency fields. Integers go in Number fields with the Integer or Long Integer field size. If you have existing text fields storing dates or numbers, convert them — the pain of conversion now is far less than the pain of working around the wrong data type indefinitely.

3. No Primary Key (or the Wrong Primary Key)

Every table needs a primary key: a field or combination of fields that uniquely identifies each row. Without one, Access cannot enforce uniqueness, relationships are unreliable, and some operations (like editing records through a query) do not work.

The wrong primary key is almost as bad as no primary key. Using a name, email address, or phone number as a primary key creates problems when those values change — and they always eventually change.

The fix: use an AutoNumber field as the primary key for every table. It is automatically unique, never changes, and has no business meaning that could become incorrect. Name it CustomerID, OrderID, ProductID — whatever makes the relationship clear.

4. Repeating Groups of Columns

This one is subtle but common. It looks like this:

Orders table:
OrderID | Item1 | Qty1 | Price1 | Item2 | Qty2 | Price2 | Item3 | Qty3 | Price3

The designer anticipated that orders would have up to three line items, so they created three sets of columns. Problems: what happens when an order has four items? What if most orders have only one item — you have two empty columns on every row? How do you query "all orders containing Product X" without checking three separate columns?

The fix: create a separate OrderLineItems table with OrderID, ProductID, Quantity, and UnitPrice. One row per line item, no limit on how many an order can have, fully queryable.

5. Missing Indexes on Foreign Keys

Access automatically indexes the primary key of every table. It does not automatically index foreign keys — the fields in child tables that reference parent table primary keys.

When you join Orders to Customers on CustomerID, Access needs to find matching rows in both tables. If CustomerID in the Orders table is not indexed, Access scans every row in Orders for every customer lookup. For a large orders table, this is slow.

The fix: index every foreign key field. In the table's Design View, set the Indexed property to "Yes (Duplicates OK)" for every field that is a foreign key. This takes two minutes per table and can make queries dramatically faster.

6. Storing Calculated Values

Storing a value that can be calculated from other stored values creates a consistency problem: the stored value can become wrong if the source values change and the calculated field is not updated.

Common examples: storing FullName when you already store FirstName and LastName. Storing TotalAmount when you already store Quantity and UnitPrice. Storing Age when you already store DateOfBirth.

The fix: calculate these values in queries, not in tables. [FirstName] & " " & [LastName] in a query always reflects the current values. A stored FullName field may not. The only exception: values that are expensive to recalculate and are queried very frequently — in that case, a calculated field with careful update logic may be justified, but it is the exception, not the rule.

7. Memo Fields on Every Table

Memo (Long Text) fields are useful for storing large amounts of text — notes, descriptions, comments. But some developers add a Memo field to every table "just in case," even when it is rarely used.

Memo fields are stored separately from the main table data in Access. Selecting a Memo field forces an additional disk read for every row. Including a Memo field in a query that returns thousands of rows is significantly slower than the same query without it.

The fix: only add Memo fields when you genuinely need to store large text. If you do have Memo fields, exclude them from queries that do not need them — use SELECT CustomerID, FirstName, LastName FROM Customers instead of SELECT * FROM Customers when you do not need the Notes field.

The Migration Angle

Every one of these mistakes creates extra work in a migration. Multi-value fields need to be normalized before the data can move to SQL Server or Dataverse. Wrong data types need to be converted. Missing indexes need to be added. Repeating column groups need to be restructured into related tables.

If you are planning a migration, a table design audit is one of the most valuable things you can do before you start. Fixing these issues in Access — where you know the data and the business rules — is much easier than fixing them mid-migration when you are also learning a new platform.

And if you are not planning a migration yet, fixing these issues now will make your Access database faster, more reliable, and easier to maintain for years to come.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Want a professional review of your Access table design before a migration? Schedule a free assessment call.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#table design#normalization#data modeling#Access basics#best practices

Keep reading

More on Access Basics