Microsoft Access Programmer Chicago, Illinois

Microsoft Access Database Normalization

Call MS Access Solutions at (323) 285-0939 for a FREE consultation.

The material below originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission. There may be references to "Figures" or "Chapters" that are not reprintable and are not used on this web page.

Normalization Made Easy

Normalization is a fancy term for the process of testing your table design against a series of rules that ensure that your application will operate as efficiently as possible. These rules are based on set theory and were originally proposed by Dr. E. F. Codd. Although you could spend years studying normalization, its main objective is an application that runs efficiently with as little data manipulation and coding as possible. Chapter 3 covers normalization and database design in detail.

Six Normalization Rules

1. Fields should be atomic, meaning each piece of data should be broken down as much as possible. For example, instead of creating a field called Name, you would create two fields: one for the first name and the other for the last name. This method makes the data much easier to work with. If you need to sort or search by first name separately from the last name, for example, you can do so without extra effort.


2. Each record should contain a unique identifier so that you have a way of safely identifying the record. For example, if you're changing customer information, you can make sure you're changing the information associated with the correct customer. We refer to this unique identifier as a primary key.


3. The primary key is a field or fields that uniquely identify the record. Sometimes you can assign a natural primary key. For example, the Social Security number in an employee table should serve to uniquely identify that employee to the system. At other times, you might need to create a primary key. Because two customers could have the same name, for example, the customer name might not uniquely identify the customer to the system. You might need to create a field that would contain a unique identifier for the customer, such as a customer ID.


4. A primary key should be short, stable, and simple. Short means it should be small (not a 50-character field). A Long Integer is perfect as a primary key. Stable means the primary key should be a field whose value rarely, if ever, changes. For example, although a customer ID would rarely change, a company name is much more likely to change. Simple means it should be easy for a user to work with.


5. Every field in a table should supply additional information about the record that the primary key serves to identify. For example, every field in the customer table describes the customer with a particular customer ID.


6. Information in the table shouldn't appear in more than one place. For example, a particular particular customer name shouldn't appear in more than one record.

A Non-Normalized Database

Take a look at an example. The datasheet shown in the figure is an example of a table that hasn't been normalized. Notice that the CustInfo field is repeated for each order, so if the customer address changes, it has to be changed in every order assigned to that customer. In other words, the CustInfo field is not atomic. If you want to sort by city, you're out of luck, because the city is in the middle of the CustInfo field. If the name of an inventory item changes, you need to make the change in every record where that inventory item was ordered. Probably the worst problem in this example involves items ordered. With this design, you must create four fields for each item the customer orders: name, supplier, quantity, and price. This design would make it extremely difficult to build sales reports and other reports your users need to effectively run the business.

A Normalized Database

The normalized figure shows the same data normalized. Notice that I've broken it out into several different tables: tblCustomers, tblOrders, tblOrderDetails, and tblSuppliers. The tblCustomers table contains data that relates only to a specific customer.

I have uniquely identified each record by a contrived CustID field, which I use to relate the orders table, tblOrders, to tblCustomers. The tblOrders table contains only information that applies to the entire order, rather than to a particular item that the customer ordered. This table contains the CustID of the customer who placed the order and the date of the order, and I've related it to the tblOrderDetails table based on the OrderID. The tblOrderDetails table holds information about each item ordered for a particular OrderID.

There's no limit to the potential number of items that the user can place on an order. The user can add as many items to the order as needed, simply by adding more records to the tblOrderDetails table. Finally, I placed the supplier information in a separate table, tblSuppliers, so that if any of the supplier information changes, the user has to change it in only one place.

At MS Access Solutions - We Normalize Your Microsoft Access Data

Our Microsoft Access Programmer services will normalize your data. Once normalized, we will develop forms, queries, macros, reports, and Visual Basic for Applications (VBA) code. Our programmer service will ensure your Microsoft Access database will operate smoothly and consistently, giving you the information you need for important business decisions.

Author Attribution

The preceding material originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development. and is reprinted here with the author's permission.

---

Expert Microsoft Access Programmer For Chicago, IL

When you need a Microsoft Access programmer for your Chicago, Illinois business or oraganization, call MS Access Solutions at (323)285-0939. We have over 25 years experience as a Microsoft Access programmer. We create Access database applications for all sectors, including hospitals, government agencies, the U.S. military, universities school districts & junior colleges, agriculture & landscaping, human resources companies, and insurance companies. We work with the most advanced and complex Microsoft Access database issues plus Microsoft SQL Server database programming for your business. We also work with smaller projects, like fixing damaged Access database forms, inaccurate Access queries, slow Access processing, MS Access reports, Access macros, and Vistual Basic for Applications (VBA) code. One of the more common programming activities we handle are connecting Microsoft Excel spreadsheets to Microsoft Acces and migrating Excel spreadsheets into MS Access. We are also experts at upgrading updating out-of-date Microft Access versions to the latest Access version.

---

You can find more information about MS Access Solutions programmer services on the Microsoft Access Programmer Portland, OR web page.

Using Access' Built In Table Analyer Wizard For Normalization

Microsoft Access includes a Table Analyzer Wizard that breaks down flat non-relational tables into structured relational formats. This tool helps users improve data integrity and cut down on redundancy even if they don't know much about database design.

You can start the wizard by opening your Access database clicking the Database Tools tab on the ribbon, and selecting Analyze Table. The Table Analyzer Wizard then looks at your chosen table for repeated data patterns, like fields with duplicate information or columns storing multiple values.

The wizard starts by asking if you want Access to choose how to split the data or if you'd like to make those choices yourself. For newcomers, the automatic option works best. Access looks at the table and spots logical data groups. Let's say customer names, addresses, and orders are all in one table. In this case, Access will recommend creating different tables for Customers and Orders connecting them through primary and foreign keys.

The wizard then makes new related tables with well-defined connections. It gives each table a primary key and adds foreign key links where necessary. It also turns repeated values into lookup tables, which helps cut down on data duplication and keeps data consistent.

After this process, Access keeps your original table by renaming it with "_Backup" at the end. This gives you a way to go back if you need to check the old version. The wizard adds the new tables to your database, ready to use for searches, data entry screens, and printouts. One of the handiest features is how the wizard can add a smaller form within your current forms. If your old table was linked to a form, the wizard offers to make a main form with smaller forms inside that show the new structure. This keeps things looking the same for users while making the data setup better behind the scenes.

The analyzer enhances performance in addition to splitting tables. Normalized data speeds up queries, minimizes update anomalies, and decreases file size. This is particularly crucial for Access databases that are shared by several users or are getting close to the 2GB limit.

As your data changes, you can repeat the procedure to guarantee continued database health. To adhere to database normalization best practices, the Table Analyzer Wizard can be used to run new tables or imported data.

The wizard is strong, but not flawless. Sometimes manual adjustments are required, particularly if your table has inconsistent data types, missing primary keys, or values that are not formatted correctly. Clean up your data by standardizing field entries and eliminating null values before executing the tool.

---

Microsoft Access Programmer Services Locations In Chicago, IL Map

Normalizing Your Database

Real-World Example Using Microsoft Access

Normalization in Microsoft Access becomes clearer when you work through a real-world example. Let's say you manage customer orders in a single table. This flat structure might include fields like Customer Name, Address, Phone, Order Date, Product Name, Quantity, and Price — all in one row. If the same customer places multiple orders, their information repeats over and over. This takes up space and increases the chance of inconsistencies.

A normalized approach would break this large table into smaller related tables. You'd create a Customers table for customer details, an Orders table for each order, and a Products table for product information. Each table has a unique ID field (a primary key), and the tables are linked using those IDs.

Access makes this easy through the Relationships tool. You connect the CustomerID field in the Customers table to the same field in the Orders table. Now, if a customer updates their phone number, you change it once — not in 15 different rows.

How Normalization Helps Your Business Or Organization

When you normalize your database, you reduce duplicate data. This makes updates simpler and reduces the chance of errors. For example, if prices change, you don't have to update every record in an orders table. You just update the price in the Products table.

Normalized databases also support cleaner reporting. When you want to run a sales report by customer, Access can pull information from all the related tables using queries. This improves performance and gives you more accurate results.

Another benefit is scalability. As your business grows and you collect more data, a well-structured database is easier to maintain. You can add new products, customers, or order types without redesigning the entire system.

When to Normalize And When Not To

Normalization is ideal when your database will be used for long-term storage, frequent updates, or detailed reporting. It's especially important for multi-user environments where consistency matters.

That said, there are times when denormalized tables are more practical. For instance, when building a report that doesn't change often, or if you're working with a small dataset used by one person, a flat table might be easier to manage.

Microsoft Access allows you to build queries and forms on top of normalized data, so your users don't have to deal with the complexity behind the scenes. You get the benefits of structure without sacrificing usability.

Final Tips For Working With Normalized Data in Access

To make normalized databases work smoothly:

• Use lookup tables for fields like states, product categories, or departments.
• Define relationships with referential integrity to prevent orphaned records.
• Avoid storing the same field in multiple places — link to it instead.
• Use forms for data entry to guide users through the structure and simplify their workflow.

Normalization doesn't have to be complex. With a few smart table designs and quality relationships, we can build an Access database that's clean, fast, and built to last.

---

Chicago, IL Map Of Area We Serve