Normalizing an existing MS Access database
I have one large access database that I need to normalize to five tables and a lookup table. I understand the theory of normalization and have already sketched out the look of the tables, but I have lost information on how to transform the table to normalize the database. The table parsers don't offer the breakdown I want.
If you have one table, add the Autonumber field to it.
Then create your other tables and use the Autonumber value from the original seperate table as the foreign key to join them back to the original data.
If you have tblPerson:
tblPerson
LastName, FirstName, WorkPhone, HomePhone
and you want to break it, add the PersonID autonomy and then create the phone table:
tblPhone
PhoneID, PersonID, PhoneNumber, Type
Then you add data from tblPerson for the corresponding fields:
INSERT INTO tblPhone (PersonID, PhoneNumber, Type)
SELECT tblPerson.PersonID, tblPerson.WorkPhone, "Work"
FROM tblPerson
WHERE tblPerson.WorkPhone Is Not Null;
and then you run another request for your 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 that you need to keep as you cannot have a UNION query as a sub-task in Jet SQL. The saved query will 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 this as qryPhones, you would add 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 would do the same for all fields. The key is that you need to create a PK value for your source table, which will bind all derived records to the source table.
source to share
A database is a fairly typical database with nothing special to distinguish it from others.
The database consists of one table with:
company name, addition, phone number, etc. contact person with typical linked fields
This will serve mainly as a marketing database and I will need to track events, business correspondence and the like. I just lost how to keep the relationship intact.
Do you mean relationships in the relationship window? They can be easily rearranged. Or do you mean key fields, etc.? This can sometimes be difficult and may involve staging tables. Each case is different. As doofledorfer said you will probably get more specific guidance if you post schematics.
source to share