Fixing Normalization Errors in Access

Access Archon Column #229 – Fixing Normalization Errors

Access versions: 2002-2013

Level: Intermediate


Introduction

Sometimes when you take over an Access database created by an amateur (or at least, someone with a minimal understanding of normalization), you will find data such as employee names stored in a Text field in the Orders table. If the employee names are not typed exactly the same every time, this can lead to problems, such as having 4 or 5 different groups on a report intended to show how many orders each employee had. This article shows how to fix such problems.


The Problem

There are six salespersons in the sample company:















Robert Lambert

Maria Lopez

Anne Marie Wilkins

Luis Hernandez Jr.

Abram Jones

Susanna Jones

But if you look at the Orders by Salesperson report, you will see many more groups, representing the different ways each salesperson’s name has been typed into the Employee field in tblOrders. In the first few pages of the report, we see groups for Abe, Abe Jones, Abraham Jones, Abram, Abram Jones, and AJ. But these are all the same person, so all these orders should be in a single group. The other employee names are similarly represented by multiple groups, one for each variant of the employee’s name typed into the Employee field. In some cases, it isn’t clear which employee is being referenced, for example the “Anna Maria Lopez” group. This could be either Anne Marie Wilkins or Maria Lopez – apparently the person who entered this name was confused between these two employees.


The Fix

If you don’t have a handy list of correct employee names, the first step is to prepare a document listing all the variations of employee names. I like to do this in a query which I then export to an Excel workbook, so the client can review all the discrepancies and indicate which is correct. qryEmployeeNames displays the employee names, with Unique Values set to Yes:

http://img.office-watch.com/waw/AW%20229-A.png image from Fixing Normalization Errors in Access at Office-Watch.com

Figure A. A query displaying variants of employee names

To export the query to Excel, right-click its name in the navigation pane and select Export, then Excel in the submenu:

http://img.office-watch.com/waw/AW%20229-B.png image from Fixing Normalization Errors in Access at Office-Watch.com

Windows 10 from people 'in the know'

A detailed and independent look at Windows 10, especially written for the many people who use Microsoft Office.

Fully up-to-date with coverage of the Anniversary 2016 major update of Windows 10.

This 670 page book shows you important features and details for all serious Windows 10 users.

Figure B. Exporting the query with duplicate names to Excel

Select the workbook format and output file name and path in the Export – Excel Spreadsheet dialog. Send the resulting workbook to the client (or someone who is familiar with the employee names), so the correct name for each employee can be marked.

When you get back the marked-up workbook, you can create a lookup table of employee names, with fields for FirstName, MiddleName, LastName and Suffix, so that (if needed) the employee names can be sorted either first name first or last name first (they are sorted last name first in the row source of the cboEmployeeID combo box on frmOrdersFixed). The table should also have an AutoNumber EmployeeID field. This is the field that will be used for linking to tblOrders.

The next step is to add an EmployeeID field to tblOrders (leave the Text Employee field in for now; it needs to remain until the updating is done). Then, create an make-table query based on qryEmployeeNames, with an extra EmployeeID field (defined as EmployeeID: 0). The query is called qmakEmployeeNames, and the resulting table is tmakEmployeeNames (I use the “tmak” prefix for tables created by make-table queries, so they can be distinguished from other tables).

With tmakEmployeeNames and tlkpEmployees open side-by-side, update the EmployeeID field in tmakEmployeeNames with the correct ID from tlkpEmployees for each variation of employee name:

http://img.office-watch.com/waw/AW%20229-C.png image from Fixing Normalization Errors in Access at Office-Watch.com

Figure C. Updating the EmployeeID field in tmakEmployeeNames

Next, make another update query to update the EmployeeID field in tblOrders, with tblOrders and tmakEmployeeNames linked by the Employee Text field:

http://img.office-watch.com/waw/AW%20229-D.png image from Fixing Normalization Errors in Access at Office-Watch.com

Figure D. An update query to update EmployeeID in tblOrders

You can now link tlkpEmployees to tblOrders with a one-to-many link in the Relationships diagram:

http://img.office-watch.com/waw/AW%20229-E.png image from Fixing Normalization Errors in Access at Office-Watch.com

Figure E. The Relationships diagram with a one-to-many link on EmployeeID

qryNorthwindAllFixed has a link between tblOrders and tlkpEmployees on EmployeeID, for proper normalization, and a field creating a concatenated Salesperson name for use on the report. Now, in rptOrdersBySalespersonFixed (based on qryNorthwindAllFixed) there is only one group for each salesperson.

After running the update query and checking the report, you can delete the Employee Text field from tblOrders.


References

The code in the sample database does not need any special references.


Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch229.zip, which is the last entry in the table of Access Archon columns for Access Watch.











Document Name

Document Type

Place in

Fixing Normalization Errors (AA 229).mdb

Access 2002-2003 database (can also be used in higher versions of Access)

Wherever you want