Normalizing an existing MS Access Database

Question

I have one large access database that I need to normalize into five tables and a lookup table. I understand the theory behind normalization and have already sketched out the look of the tables but I am lost on how to transform my table to get the database normalized. The table analyzers doesn't offer the the breakdown that I want.

Answer 1

Can queries, particularly Union queries, offer a solution? Where are you seeing a problem?

Answer 2

If you post the schema, I bet you'll get a lot of very specific good advice.

Answer 3

The database is a pretty typical database with nothing special to distinguish it from others.

The database consists of one table with:

company name, addess, telephone etc. contact person with the typical related fields

This will basically serve as a marketing database and I will need to keep track of events, business correspondence and the like. I'm just lost on how to keep the relationships intact.

Answer 4

Do you mean relationships in the relationships window? These can easily be rebuilt. Or do you mean key fields etc? This can sometimes be difficult and may involve intermediate tables. Each case is different. As doofledorfer said, you are likely to get more specific advice if you post schemas.

Answer 5

If you have a single table, add an Autonumber field to it.

Then create your other tables, and use the Autonumber value from the original single table as the foreign key to join them back to the original data.

If you had tblPerson:

  tblPerson
  LastName, FirstName, WorkPhone, HomePhone

and you wanted to break it down, add PersonID autonumber and then create a phone table:

  tblPhone
  PhoneID, PersonID, PhoneNumber, Type

Then you'd append data from tblPerson for the appropriate fields:

  INSERT INTO tblPhone (PersonID, PhoneNumber, Type)
  SELECT tblPerson.PersonID, tblPerson.WorkPhone, "Work"
  FROM tblPerson
  WHERE tblPerson.WorkPhone Is Not Null;

and then you'd run another query for the home phone:

  INSERT INTO tblPhone (PersonID, PhoneNumber, Type)
  SELECT tblPerson.PersonID, tblPerson.HomePhone, "Home"
  FROM tblPerson
  WHERE tblPerson.HomePhone Is Not Null;

Someone suggested a UNION query, which you'd have to save as you can't have a UNION query as a subselect in Jet SQL. The saved query would look something like this:

  SELECT tblPerson.PersonID, tblPerson.WorkPhone, "Work" As Type
  FROM tblPerson
  WHERE tblPerson.WorkPhone Is Not Null
  UNION ALL 
  SELECT tblPerson.PersonID, tblPerson.HomePhone, "Home" As Type
  FROM tblPerson
  WHERE tblPerson.HomePhone Is Not Null;

If you saved that as qryPhones, you'd then append qryPhones with this SQL:

  INSERT INTO tblPhone (PersonID, PhoneNumber, Type)
  SELECT qryPhones.PersonID, qryPhones.WorkPhone, qryPhones.Type
  FROM qryPhones;

Obviously, this is just the simplest example. You'd do the same for all the fields. The key is that you have to create a PK value for your source table that will tie all the derived records back to the original table.