ACC: How the Table Analyzer Wizard Works
ID: Q140636
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SUMMARY
Novice: Requires knowledge of the user interface on single-user computers.
The Table Analyzer Wizard can help you create a relational database from a
set of data. By using 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.
MORE INFORMATION
The Table Analyzer Wizard can automatically analyze a non-relational
database and "normalize" it for you. Normalization is the process of taking
a single-table database and breaking it into a set of smaller, related
tables, with each table focused on a single topic or grouping of
information.
A normalized, relational database has a number of advantages over a
nonrelational one. First, updating information is faster and easier because
fewer data changes are required. Second, only the minimum information is
stored; therefore, the database is smaller in size. Finally, a relational
database keeps data consistent automatically because data is stored once.
Although the Table Analyzer Wizard can help you create a relational
database, if you have database experience, the Table Analyzer Wizard
enables you to modify any suggestions it makes. You can split up tables,
rearrange fields in tables, and create relationships between tables. You
can modify Table Analyzer Wizard decisions during every step of the
database-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 Table
Analyzer 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 a relationship that controls how the new tables
work together. This relationship enforces referential integrity (data
consistency) with cascading updates. The wizard does not automatically
add cascading deletes to the relationship 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.
REFERENCES
For more information about the Table Analyzer Wizard, search the Help Index
for "Table Analyzer," or ask the Microsoft Access 97 Office Assistant.
Keywords : kbusage WzOthr
Version : 7.0 97
Platform : WINDOWS
Issue type : kbinfo
|