Separating table, maintaining link without losing data?

[Jason]

I have been asked to take our current database table and break it into as
many as three different tables. The catch is that they will have
parent-to-chld relationships and I need to delete some duplicate entries in
the first table and make sure that the linked records in the second table
link to the single parent.

I provide technical support for a plastic fittings/valves/irrigation/etc
company and we track each call with the customer's contact information and
the reason for the call. All of this is collected in one table, TSContacts.
We have almost a year worth of entries, roughly 10,000 records. The problem
is that we recieve calls from the same contacts many times, so their
information gets entered over and over again. Additionally, some issues
cannot be addressed in the one call and several calls or transactions may
take place before the issue is resolved.

ContactID Name PhoneNumber Product Problem Comments
10025 John Doe 123-555-1234 Drip broken timer
malfunction
10026 John Doe 123-555-1234 box broken side of
box cracked
10027 John Doe 123-555-1234 Valve broken valve
leak at thread
10028 John Doe 123-555-1234 Valve broken more of
same valve 10029 John Doe 123-555-1234 Valve broken
more of same valve


What we would like to do with this data is make the contact info the parent
to the call info and make the call info parent to the comments (to track
progress and a date field would be added). Each Contact could have several
calls linked to it and each call could have several comments linked to it.
So it would look something like

10025 John Doe 123-555-1234
Drip
broken

timer malfunction
box
broken

side of box cracked
Valve
broken

valve leak at thread

more of same valve

more of same valve

All valves replaced

Now, I have figured out a way, albeit tedious, to separate the tables and
add autonumber primary keys to create one-to-may relationships and make
everything work (in my head at least), but I would need to be able to paste
all of the contact records into a table with the ContactID field set to
accept the numbers I give it to maintain the link with all associated call
records and then pick up autonumber from the largest number. So far, what
I've read is that this cannot be done. Is there any way to make this
transition without losing old call info?

 

[Ken Sheridan]

You can decompose the table relatively easily, but only if a number of
assumptions hold true:

1. The contact names have been entered absolutely consistently, e.g. always
John Doe, but not John Doe In some rows and J Doe in others.

2. No two contacts have the same name.

3. No customer has made more than one call relating to the same
product/problem where these calls in fact represent separate instances of the
problem.

From experience I'd be sceptical about each of these holding true ( I once
found three versions of my own name in one database), but if they do then the
sequence of 'append' queries would be:

1. Append distinct values of the contact data to a new Contacts table,
which has an autonumber ContactID column as its primary key:

INSERT INTO Contacts(ContactName, PhoneNumber)
SELECT DISTINCT [Name], PhoneNumber
FROM TSContacts;

2. Append call data into a new Calls table, which has an autonumber CallID
column as its primary key:

INSERT INTO Calls(Product, Problem, ContactID)
SELECT DISTINCT Product, Problem, ContactID
FROM TSContacts INNER JOIN Contacts
ON TSContacts.[Name] = Contacts.ContactName;

3. Append comments data into new Comments table. This table can have a
CommentID autonumber primary key:

INSERT INTO Comments(Comment, CallID)
SELECT Comments, CallID
FROM (TSContacts INNER JOIN Contacts
ON TSContacts.[Name] = Contacts.ContactName)
INNER JOIN Calls ON Calls.ContactID = Contacts.ContactID;

Note that I've used ContactName rather than Name as the column name in
Contacts; 'Name' is the name of a built in property of many types of object
in Access, so is best avoided as a column name. I've also used Comment
rather than Comments as the column name in the Comments table, favouring the
convention that table names should wherever possible be plural or collective
nouns (as a table represents a set), while column names should wherever
possible be singular names (as a column represents an attribute).

Ken Sheridan
Stafford, England

 

[Peter Hibbs]

Jason,

Export your table to a .csv file using the built-in export wizard and
then use the Excel to Access Converter Utility program at :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d
to import the data into your new database which will separate the data
into your three tables, create the primary and foreign key fields
automatically and remove any duplicate records in the primary table
(the John Doe records) as well as a few other things.

HTH

Peter Hibbs.

 

[Jason]

Unfortunately none of the stipulations hold true. I could go through and
verify spelling on duplicate names by sorting by the phone number, and that
might fix #1. We deal with all levels of customers from home owners,
contractors, distributors, architects/engineers. Many times we only get the
first name, especially with homeowners or impatient contractors. Sometimes
just the last name from the elderly home owners who insist you call them Mr
or Mrs so and so.

I thought of another way to do this and not have to customize the
autonumber, but it is more tedious that my first idea. I will have to copy
the Contacts table I have now into a Call table and delete all non relevant
fields. I will add a CallID autonumber field, but sort by ContactID. Then I
will take the Contact table and copy it into Excel. From there I will sort
by phone number (this will be the only way to separate the contacts as many
times we have different people call from the same number, i.e distributors
and sales reps), and manually go through and change the Contact ID so that
duplicate numbers have the same ID. Then I will resort the records by the
Contact ID. My theory is that I can past the column into the Contact ID
column of the newly created Calls table. Then I will have to go through the
original contact table, sort by Phone number and delete the duplicates that
do not have the Contact ID number I decided to keep for the duplicates in
Excel. Unless there are any other ideas (or reasons my idea won't work), I
guess I'll get to work on this. I haven't had to do tedious busy work like
this since I was in the Marines. Thanks.

You can decompose the table relatively easily, but only if a number of
assumptions hold true:

1. The contact names have been entered absolutely consistently, e.g. always
John Doe, but not John Doe In some rows and J Doe in others.

2. No two contacts have the same name.

3. No customer has made more than one call relating to the same
product/problem where these calls in fact represent separate instances of the
problem.

From experience I'd be sceptical about each of these holding true ( I once
found three versions of my own name in one database), but if they do then the
sequence of 'append' queries would be:

1. Append distinct values of the contact data to a new Contacts table,
which has an autonumber ContactID column as its primary key:

INSERT INTO Contacts(ContactName, PhoneNumber)
SELECT DISTINCT [Name], PhoneNumber
FROM TSContacts;

2. Append call data into a new Calls table, which has an autonumber CallID
column as its primary key:

INSERT INTO Calls(Product, Problem, ContactID)
SELECT DISTINCT Product, Problem, ContactID
FROM TSContacts INNER JOIN Contacts
ON TSContacts.[Name] = Contacts.ContactName;

3. Append comments data into new Comments table. This table can have a
CommentID autonumber primary key:

INSERT INTO Comments(Comment, CallID)
SELECT Comments, CallID
FROM (TSContacts INNER JOIN Contacts
ON TSContacts.[Name] = Contacts.ContactName)
INNER JOIN Calls ON Calls.ContactID = Contacts.ContactID;

Note that I've used ContactName rather than Name as the column name in
Contacts; 'Name' is the name of a built in property of many types of object
in Access, so is best avoided as a column name. I've also used Comment
rather than Comments as the column name in the Comments table, favouring the
convention that table names should wherever possible be plural or collective
nouns (as a table represents a set), while column names should wherever
possible be singular names (as a column represents an attribute).

Ken Sheridan
Stafford, England

I have been asked to take our current database table and break it into as
many as three different tables. The catch is that they will have
parent-to-chld relationships and I need to delete some duplicate entries in
the first table and make sure that the linked records in the second table
link to the single parent.

I provide technical support for a plastic fittings/valves/irrigation/etc
company and we track each call with the customer's contact information and
the reason for the call. All of this is collected in one table, TSContacts.
We have almost a year worth of entries, roughly 10,000 records. The problem
is that we recieve calls from the same contacts many times, so their
information gets entered over and over again. Additionally, some issues
cannot be addressed in the one call and several calls or transactions may
take place before the issue is resolved.

ContactID Name PhoneNumber Product Problem Comments
10025 John Doe 123-555-1234 Drip broken timer
malfunction
10026 John Doe 123-555-1234 box broken side of
box cracked
10027 John Doe 123-555-1234 Valve broken valve
leak at thread
10028 John Doe 123-555-1234 Valve broken more of
same valve 10029 John Doe 123-555-1234 Valve broken
more of same valve


What we would like to do with this data is make the contact info the parent
to the call info and make the call info parent to the comments (to track
progress and a date field would be added). Each Contact could have several
calls linked to it and each call could have several comments linked to it.
So it would look something like

10025 John Doe 123-555-1234
Drip
broken

timer malfunction
box
broken

side of box cracked
Valve
broken

valve leak at thread

more of same valve

more of same valve

All valves replaced

Now, I have figured out a way, albeit tedious, to separate the tables and
add autonumber primary keys to create one-to-may relationships and make
everything work (in my head at least), but I would need to be able to paste
all of the contact records into a table with the ContactID field set to
accept the numbers I give it to maintain the link with all associated call
records and then pick up autonumber from the largest number. So far, what
I've read is that this cannot be done. Is there any way to make this
transition without losing old call info?

 

[susan roads]

HI JENNFER DEVERE...