An experienced database designer knows there are certain rules when deciding what information to put in one table. People paid to clean up after them will know the huge problems mistakes often caused by sloppy design can cause. Take for example, a sales database. Each sale has some sale information such as sale date, sales person and sale value. Also recorded for each sale is the customer name and address. Now a novice user who has had little on or no training may well decide to put everything in the one table. This means that each time a sale is made the user must manually enter the full customer and salesperson details regardless of how many time this has been done before.

This can be time consuming and tedious and creates needless repetition. User error may occur since a user may mistype a customer name for example. The first time the customer may be entered as 'Hinchfield Ltd.' The second time they may be entered as 'Hinchfield Limited.' The third time a spelling mistake occurs and the customer is entered as 'Linchfield Ltd.' Consider how this would affect a report based on the sales data. Perhaps if a customer has spent more than three hundred pounds in the current year then they earn a special discount for the remainder of the year. A report is run to check. This customer has three orders with a total sales value of four hundred pounds and therefore they do qualify for the special offer. Unfortunately the report based on total sales for each customer recognises three separate customers since it is grouped on customer name. This means that there are apparently three customers, each with a total sales value of less than three hundred pounds each. What a mess!

I used to love such chaos since, as an expert in database cleansing, I was paid huge amounts of cash to go through and sort it all out before helping with the re-design of the database to prevent such errors occurring in the future. Darn it all though because Access has given the world a tool which can help them sort it without paying for my services. Probably why I now spend my time writing nostalgic articles! So how can you use the Analyze Table tool to help sort out the mess?

Open the database and go to the Analyze section on the Database Tools tab. Select the Analyze Table tool. A wizard will appear and the first two tabs explain what will happen. The best thing is that the original table is not altered. Instead, the tool automatically creates separate tables which can be renamed later. The next tab allows you to select which table you want to analyse. In this example we would select our sales table. Click next and then select 'Yes, let the wizard decide.' The magic is on the next page which will display a relationship diagram showing how it will split the table up into other tables. How is it deciding what to do?

In this example it will look at the sales table. Chances are there are a limited amount of sales people and they have more than one sale each. As soon as Access recognises that the same information is repeated in the database it will create a new table to hold it. It also adds foreign keys and relationships to link the tables automatically. This means that the sale in the original table will be automatically linked to the correct sales person since it uses primary and foreign keys to link them. In our example we are assuming that repeat customers happen and the customers are also moved into a new table with their address details.

This is a good time to rename the tables to prevent confusion later. In the top right of the screen under the light bulb button is the Rename Tables button. Click this and rename the tables. Make sure you use appropriate and meaningful names. For the new customer table call it something like 'Table_Customer.' If you suddenly end up with a load of tables and you have no idea what they are then this will be extremely confusing especially when attempting to link forms etc. later. You can forget documentation as it won't mean a thing. The next step is to make sure that the Primary Keys are correct. For the new customer table, has access automatically given a unique key to it so that all customers will be identified?

The next is to click OK and see what happens. The old table will be renamed. The data is automatically moved into new tables and a query is generated to give exactly the same results as the original table had so you can base forms on it if you want to. Check the data. In the current example it may be that only the customer address was moved and not the other data. Assuming you delete everything and go back to the start you can try again and this time manually tell Access what to put in what tables since you will have a good idea from the automatic process you just witnessed. On the diagram window simply click and drag a field to create a new table such as the CustomerName. Next, click and drag other relevant fields into the new table such as CustomerAddress.

When completing the steps manually the next window will look at any typographical errors. This means if you have two addresses that are very similar then Access will show them to you and give you the option to correct them if they are in fact misspelled. This takes care of the spelling mistakes causing reporting problems we discussed earlier. Rather than having to type it, Access presents a list box with the alternative you can choose. This essentially lets you automatically merge fields together saving huge amounts of time on manual data cleansing. I just want to point out how mind blowing that is for people who have not been faced with huge piles of duplicate data they had to cleanse.

You should now have a database correctly split into logical table containing correct data with no duplicate information and all done without the use of externally purchased software or personnel. It is worth experimenting with this tool to explore how it can help improve your database. Once this process is completed then other parts of your database can be improved easily. As an example, a form to enter the sales could now contain a drop down list containing all sales people to choose the correct one rather than having to manually type it. You could save the users valuable time and effort thus hugely increasing their productivity. Worth a shot by anyone's standards!