Tutorial 3: How to create linked tables on an existing table so the data can be more normalized?

I want to talk about what normalization is first before we go into great detail. According to Microsoft, normalization is is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. To understand what Microsoft is saying, imagine you have a database with one table (members) and one of the fields is city. Let's say that you only deal with 5 cities, but do you want the database to be recording the city every time you record an entry? That is really a waste of hard drive resources because instead of recording city with every record, we could just create a table called Cities and have it mapped to the members table. This means instead of recording the long string associated with city, we are only recording an integer value that maps back to the city table that will define the relationship. This is First Normal Form and for further understanding of normalization and its different forms, please go to: Microsoft. The other benefit to using normalization is that it creates a foreign key constraint so any value that is entered into city must also exist in the cities table. This keeps the data uniform and will maintain integrity so you will not see 10 different spellings of a city. Some degree of normalization should always be used in order to provide data integrity, increased resources, and increased speed of the database.

Now that you have a general idea of what normalization is, we are now going to take a look at how to normalize a field in an existing database. The database we are using is the same one from the database project so it just has the one table "members" and this table has information such as name, city, address, and so on of each member. Since city is repeated so often, this is a good candidate for normalization. Our goal is to create a separate table called Cities and a new members table where city is not being recorded, but rather just a lookup record that will do the mapping. Microsoft Access has a tool that helps with this kind of manipulation so that is what we are going to use today.



First of all. go ahead and click on database tools and then click on analyze table:

Click through till it asks you what table you want to use:

Make sure you select members and click next. The next screen will ask whether or not you want to decide what fields belong on what table. Pick the option that says, "No, I want to decide." Now you will get to a screen that looks like below:

Here, go ahead and select city. Then left click and drag it to the right open space. It will then ask you what you want to name the table, so name it "Cities." This is what you should see after you complete this step:

Click next, and then it will ask you if you need to make any changes to any of the city names. You do not, so click next. Then it will ask you if you want to create a query that looks like the old table. I recommend doing so because this will basically work just like the old members table so if there were any forms or reports based off of it, they would continue to work. Please look below:

Click finish and now it is all done. Now here is what you should have:

This now created a new table called Cities that has a relationship to Table 1 through a look up field. So now when you look at the cities table, you will see each city name and then the corresponding member information where each person matched that city:

Now, further manipulation will need to be done for the forms in order to use this approach, but this gets you the basic configuration of a table along with a related table.



I highly recommend visiting Microsoft and Access forums located at Access Forums for further information on Access and related tables.


**Pictures are self created content of Microsoft Access by Richard Ecker, 2011.
**Access Forums is a free public site where users can address all question related to Access.