Help with Normalizing table

I am upgrading an Access DB to SQL Server. I have a table in the database
that looks like this,
tblContacts
RegNo - Primary key
Name
Relationship
Phone
WorkPhone
Name2
Relationship2
Phone2
WorkPhone2
Name3
Relationship3
Phone3
WorkPhone3
Name4
Relationship4
Phone4
WorkPhone4
Name5
Relationship5
Phone5
WorkPhone5
Name6
Relationship6
Phone6
WorkPhone6
I have made a new table, Contacts that looks like this,
ContactID - autoincrementing INT
RegNo
Name
Relationship
Phone
WorkPhone
How can I go about getting the data from tblContacts to Contacts?
Thanks,
DrewNow that I think about it... my subject is off somewhat... I have already
normalized the table, I just need to get the data in there now.
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am upgrading an Access DB to SQL Server. I have a table in the database
>that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>|||Use six "select" statements (one for each group) connected by "union all".
insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
select RegNo, Name, Relationship, Phone, WorkPhone
from tblContacts
union all
select RegNo, Name2, Relationship2, Phone2, WorkPhone2
from tblContacts
where
Name2 is not null
and Relationship2 is not null
from tblContacts
union all
select RegNo, Name3, Relationship3, Phone3, WorkPhone3
from tblContacts
where
Name3 is not null
and Relationship3 is not null
from tblContacts
...
AMB
"Drew" wrote:

> I am upgrading an Access DB to SQL Server. I have a table in the database
> that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>
>|||Drew
CREATE TABLE People
(
PeopleID NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
Birthdate DATETIME NOT NULL
)
What does relatioship column mean?
CREATE TABLE Contacts
(
People_Cont NOT NULL PRIMARY KEY,
Contact_Name VARCHAR(50) NOT NULL,
Email VARCHAR(20) NULL
)
If the relatioship between these tables are many-to many so create a
"JUNCTION" table which will contain People_ID and People_Cont as Primary
KEY ( since I don't know your business reqirements , I can only guess)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am upgrading an Access DB to SQL Server. I have a table in the database
>that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>|||Uri,
The table is used to hold contact information for our clients. The
relationship column holds the relationship of the client to the contact.
I.E. Mother, Father, Brother, Guardian, etc.
Thanks,
Drew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:erW5Lhj2FHA.3600@.TK2MSFTNGP12.phx.gbl...
> Drew
> CREATE TABLE People
> (
> PeopleID NOT NULL PRIMARY KEY,
> [Name] VARCHAR(50) NOT NULL,
> Address VARCHAR(100) NOT NULL,
> Birthdate DATETIME NOT NULL
> )
>
> What does relatioship column mean?
>
> CREATE TABLE Contacts
> (
> People_Cont NOT NULL PRIMARY KEY,
> Contact_Name VARCHAR(50) NOT NULL,
> Email VARCHAR(20) NULL
> )
> If the relatioship between these tables are many-to many so create a
> "JUNCTION" table which will contain People_ID and People_Cont as Primary
> KEY ( since I don't know your business reqirements , I can only guess)
>
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>|||One popular approach is to do:
SELECT RegNo, Name,
CASE n WHEN 1 THEN Relationship
WHEN 2 THEN Relationship2
..
WHEN 6 THEN Relationship6
END AS "Relationship",
CASE n WHEN 1 THEN Phone
WHEN 2 THEN Phone2
..
WHEN 6 THEN Phone6
END AS "Phone",
CASE n WHEN 1 THEN WorkPhone
WHEN 2 THEN WorkPhone2
..
WHEN 6 THEN WorkPhone6
END AS "WorkPhone"
FROM tbl, ( SELECT 1 UNION
SELECT 2 UNION
..
SELECT 6 ) D ( n ) ;
After thoroughly reviewing your data model, you might want to decide whether
an additional identifier is required.
Anith|||It worked the charm! Thanks a bunch for your input!
I did have to make some changes,
insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
select RegNo, Name, Relationship, Phone, WorkPhone
from tblContacts
union all
select RegNo, Name2, Relationship2, Phone2, WorkPhone2
from tblContacts
where
Name2 is not null
and Relationship2 is not null
union all
select RegNo, Name3, Relationship3, Phone3, WorkPhone3
from tblContacts
where
Name3 is not null
and Relationship3 is not null
...
Thanks,
Drew
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2C717449-94D9-4A44-9943-F895BB991247@.microsoft.com...
> Use six "select" statements (one for each group) connected by "union all".
> insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
> select RegNo, Name, Relationship, Phone, WorkPhone
> from tblContacts
> union all
> select RegNo, Name2, Relationship2, Phone2, WorkPhone2
> from tblContacts
> where
> Name2 is not null
> and Relationship2 is not null
> from tblContacts
> union all
> select RegNo, Name3, Relationship3, Phone3, WorkPhone3
> from tblContacts
> where
> Name3 is not null
> and Relationship3 is not null
> from tblContacts
> ...
>
> AMB
>
> "Drew" wrote:
>|||"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
> I am upgrading an Access DB to SQL Server. I have a table in the
database
> that looks like this,
<snip>

> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
>
Drew,
The above table has both Phone and WorkPhone columns. This is not
1NF, as this represents repeating columns for the same type of
information (a phone number).
Contacts
ContactID
RegNo
Name
Relationship
Phones
PhoneID
ContactID
PhoneNumber
PhoneType
Sincerely,
Chris O.