Access Archon Column #229 – Fixing Normalization Errors
Access versions: 2002-2013
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.
There are six salespersons in the sample company:
Anne Marie Wilkins
Luis Hernandez Jr.
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.
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:
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:
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:
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:
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:
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.
The code in the sample database does not need any special references.
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.
Fixing Normalization Errors (AA 229).mdb
Access 2002-2003 database (can also be used in higher versions of Access)
Wherever you want