Doesn’t this sound great? The Access Table Analyzer promises to take a messy flat-file table (such as an imported spreadsheet) — with all its repetitive data — and convert it to an efficient set of relational tables. But, as the saying goes, promises made are promises broken. Unless your flat file follows some strict rules, the Table Analyzer won’t quite get it right.
A flat-file database is one in which all the data is in one file. Access is a relational database that allows data to be stored in multiple tables for more efficiency.
Sometimes you get a perfect set of relational tables, and sometimes the Table Analyzer doesn’t suggest a new table when it should or suggests a new table when it shouldn’t. Give it a try and see what happens. Best case, it works right and you’ve just saved yourself a boatload of time. Worst case, it doesn’t work right and you wasted a few minutes of your time. Most of the time, you’ll get something of value that may need tweaking. At least you saved a partial boatload of time!
The Analyzer works best with a flat-file table that contains plenty of duplicate information. For example, imagine a flat-file table for a bookstore. Each record in the table contains customer and book data. If the same customer buys six books, the table contains six separate records with the customer’s name, address, and other information duplicated in every one. Multiply that by 1,000 customers, and you have precisely the kind of flat-file mess that the Analyzer loves to solve.
With that thought in mind, here’s how to invoke the Table Analyzer Wizard:
Open your database and select the table you’d like to evaluate from the Navigation pane.
Click the Database Tools tab.
The Analyze group of buttons appears on the Ribbon.
The Analyze Group on the Ribbon.Click the Analyze Table button from the Analyze group.
The Table Analyzer Wizard dialog box appears.
Here comes the Table Analyzer.Read the first two screens if you want (they’re strictly educational); click Next after each one.
Another Table Analyzer Wizard screen appears.
Select a table to analyze.The name of the table you selected in the Navigation pane should be selected in the Tables list. If it is not, click the name of the table you’d like to convert.
Click Next.
In the dialog box that appears, the wizard asks whether you want to just let the wizard do its thing (the wizard will decide how the flat-file table should be arranged into multiple tables) or if you want to decide which fields go to what tables.
Click the Yes option (if it’s not already selected) to give the wizard full power in deciding the fate of your table, and then click Next.
If the wizard recommends that you not split your table, click the Cancel button and pat yourself on the back for a job well done. This message means that the wizard thinks your table is fine just as it is.
If the wizard does split your table, it will analyze your table and show you its findings.
The Table Analyzer makes its decision.Make sure the information from your flat-file table is grouped correctly into new tables:
If the information is grouped correctly, name the tables by double-clicking each table’s title bar and typing a new name in the resulting dialog box.
If the information is not grouped correctly, use your mouse to drag and drop fields from table to table — and then double-click each table’s title bar to rename the tables.
If you want to create a new table, drag a field into any open space between or around the existing tables. The wizard will create a new table window containing the field. Double-click the new table’s title bar to rename the table.
When you’re finished arranging and naming your tables, click Next.
The wizard automatically selects a key field for each table that it thinks needs a key field. Should the wizard select a field incorrectly as a key field, you can correct the error.
If the wizard doesn’t designate a key field properly, you can
Designate an existing field as a key field by selecting the field and clicking the Set Unique Identifier button (looks like a key).
Change a key-field designation by selecting the proper key field and then clicking the Set Unique Identifier button (looks like a key).
Add a key field by clicking the Add Generated Key button (contains a plus sign and key).
Click Next for the final step in the process.
The wizard offers to create a query that looks and acts like your original table. If you have reports and forms that work with the flat file, they’ll work with the new query.
At this point in the process, you may not see the last screen of the wizard as described in Step 12; the wizard may tell you that some of your data might be incorrect and that you should correct it. For example, if you have a Country field in your table and there are two entries such as USA and UAS, the wizard will ask you for — or suggest — a correction for the error. You definitely should correct this type of error.
Unfortunately, the wizard is often not very good at identifying such errors. (It might suggest that you change Belgium to Brazil, for example.) Take a quick peek at the data for actual errors and move on — because the suggested “corrections” are often incorrect!
Choose Yes to have the wizard create the query or No to skip query creation.
Choosing Yes creates a query that runs against the new tables. The query looks and acts like the original table. The original table is renamed with an _OLD slapped on the end, and any reports and forms use the query (rather than the original table) automatically. Choosing No generates the new tables but leaves the original table with its original name.
Click Finish to exit the wizard.
The wizard completes the process of splitting the flat-file table into a set of relational tables.
The Table Analyzer is unlikely to split a flat-file database correctly into a properly designed relational database — especially if the flat file is complicated. You’re much better off bringing the database to a qualified human and letting her properly redesign it as a relational database — or figuring out how to do that yourself!