This article was previously published under Q208679
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).
For a Microsoft Access 2002 version of this article, see 292799.
The Table Analyzer Wizard can help you create a relational database from aset of data. When you use the Table Analyzer Wizard, you do not have to know relational database design principles. This article explains how the Table Analyzer Wizard deciphers a single-table database and proposes a way of splitting the original table into a set of related tables.
The Table Analyzer Wizard can automatically analyze a nonrelationaldatabase and "normalize" it for you. Normalization is the process of takinga single-table database and breaking it into a set of smaller, relatedtables, with each table focused on a single topic or grouping ofinformation.
A normalized relational database has a number of advantages over anonrelational one. First, updating information is faster and easier becausefewer data changes are required. Second, only the minimum information isstored; therefore, the database is smaller in size. Finally, a relationaldatabase keeps data consistent automatically because data is stored once.
Although the Table Analyzer Wizard can help you create a relationaldatabase, if you have database experience, the Table Analyzer Wizardenables you to modify any suggestions it makes. You can split up tables,rearrange fields in tables, and create relationships between tables. Youcan modify Table Analyzer Wizard decisions during every step of thedatabase-creation process.
How to Run the Table Analyzer Wizard
To run the Table Analyzer Wizard, follow these steps:
On the Tools menu, point to Analyze, and then click Table.
Follow the instructions in the Table Analyzer Wizard dialog boxes. Note that the first two dialog boxes explain what normalization is and why it is useful. The third dialog box asks if you want to manually split a database or if you want to let the wizard do it for you.
How the Table Analyzer Wizard Works
If you choose to let the Table Analyzer Wizard split a database, the TableAnalyzer Wizard runs through the following process:
The Table Analyzer Wizard starts with a single-table database, which can be any set of data created with, or imported into, Microsoft Access. The wizard then breaks the table into a set of smaller tables. Each of these smaller tables contains the minimum set of information which is grouped together.
The wizard looks for unique values that can identify a grouping of data. These unique values are labeled as primary keys for each of the groupings. If no unique value is identified, the wizard creates a primary key using an auto-incrementing long integer field. Also, the wizard creates a foreign key in related tables.
The wizard creates relationships that control how the new tables work together. These relationships enforce referential integrity (data consistency) with cascading updates. The wizard does not automatically add cascading deletes to the relationships because of the risk that you may accidentally delete large portions of data.
The wizard creates an initial proposal and asks you to confirm or change it.
If you confirm the proposal, the wizard then searches the new tables for inconsistent data (for example the same customer with two different phone numbers) and presents a list of records that you can change or accept.
Finally, you can choose to create a query that simulates the original, single-table database. The wizard first backs up the original table and renames it by appending "_OLD" to its name. Then, the wizard creates a query using the original table name. This assures that any existing forms or reports based on the original table will work with the new table structure.
For more information about the Table Analyzer Wizard, click Microsoft Access Help on the Help menu, type split a table into related tables using the table analyzer wizard in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.