Duplicate or Divde help

S

SJW

I have a dat base of contacts and some are listed as husband and wife. We
would like to divide those into two separate records with different CID's. It
would be nice if there were a way to not have to go in and re enter all of
the information like address, phone, email etc. Is this possible?
How do I accomplish this?
Thanks for any help.
 
W

Wayne-I-M

No database can regonise the difference between
Billy Bob Jones
and
Billy Bob Jones

But if you have something in the record that say this is 2 people
Billy Jones
and Bob Jones

then yes you can do this

You need to give more information about your table structure and how 2
people in a record are different from 1
Is there
Mrs
Mr
Mrs and Mr
etc
etc

You need "something" to act as criteria
 
S

SJW

In the data base it just says Billy & Bob Jones

Wayne-I-M said:
No database can regonise the difference between
Billy Bob Jones
and
Billy Bob Jones

But if you have something in the record that say this is 2 people
Billy Jones
and Bob Jones

then yes you can do this

You need to give more information about your table structure and how 2
people in a record are different from 1
Is there
Mrs
Mr
Mrs and Mr
etc
etc

You need "something" to act as criteria
Click to expand...
 
W

Wayne-I-M

Is there an "&" in all records you want to split in that field and none in
the other records (the ones you don't want to split)
 
W

Wayne-I-M

Ok - you "could" right some sql that would run a query that would do all of
this for you in one go - BUT if it was me, I wouldn't - do it in 2 halfs

If you lose half of your data (very easy to do) you would be a little sad ??

So do it by hand (sort of)

Create a copy of your database
Copy your table containing the people's detail (structure only)
Create a new query
Double click all the fields so you have all the data in the table showing in
the query
In the Name column put this in the criteria row Like "* & *"
UnClick the Name column show it does not show in normal view
Create a new calculated column
Put this in
NewName:
Right$([TableName]![NameField],Len([TableName]![NameField])-InStr(1,[TableName]![NameField],"&")-1)

Change the query to an append query (select the new table you just made from
the drop down) and Append To row should fill in for you except for the
calculated column (NewName). In the Append To row select the [name field] in
the new table.

Click append.

Next change the calculated to this
NewName:
Left$([TableName]![NameField],Len([TableName]![NameField])-InStr(1,[TableName]![NameField],"&")-1)

Note the Right has changed to Left

Run the append again and go to your new table and check that's what you
want. If not post another question with what's gone wrong :)

If you are qwrting it in sql it will look something like this (with only the
name and ID)

SELECT TableName.ID,
Right$([TableName]![NameField],Len([TableName]![NameField])-InStr(1,[TableName]![NameField],"&")-1) AS NewName
FROM TableName;


Good Luck
 
W

Wayne-I-M

ooops

forgot the last bit

After you have all the couples in the new table just remove the Like "* & *"

and change it to Not Like "* & *"

Remove the calculated column and click the show on name field and select the
name field on the new table on the Append To.

This will bring in all the non-couples to the new tables

Sorry about forgeting - I am very old so it's OK
 
Top