Dividing fields

[Chris D]

I've just imported a substantial database from Excel and Access has merged 4
of my fields into a single field.

Originally, the data was entered as the following: Salutation (Mr., Mrs.)
[Field1], First Name [Field2], Middle Initial [Field3], and Last Name
[Field4]. Now, however, the 4 fields have been merged into a single field
containing the full name.

I'd like to know if there is any possible way to divide the single field
into the 4 seperate fields they had initially been divided to when the data
was first entered into Excel? I would appreciate any help anyone has to
offer.

 

[Rick B]

I'd try that import again. Access will not just randomly merge a bunch of
fields on import. If it did, it would be a miracle for it to just happen to
figure out that those four fields could be combined to make up a name.

Make sure it is in four fields in Excel, and try importing it again.

 

[Chris D]

You are absolutely right. I was mistaken. The original Excel document used
the full name field instead of the 4 different ones that I had initially
thought had been used. I didn't have the original to look off of, so I just
assumed that that was what had happened.

Now, is there any possible way for me to import the Excel document into
Access and divide the full name field in Excel into 4 different fields in
Access? Is there a way to divide them at the spaces?

Currently, the field contains this: Mr. John A. Smith. Instead of
importing the information as a single field, is it possible to import it as 4
different fields? I'd like it to resemble something like this: Mr. | John |
A | Smith.

Is it possible? Sorry for the confusion. Thanks again.

I'd try that import again. Access will not just randomly merge a bunch of
fields on import. If it did, it would be a miracle for it to just happen to
figure out that those four fields could be combined to make up a name.

Make sure it is in four fields in Excel, and try importing it again.


--
Rick B

I've just imported a substantial database from Excel and Access has merged 4
of my fields into a single field.

Originally, the data was entered as the following: Salutation (Mr., Mrs.)
[Field1], First Name [Field2], Middle Initial [Field3], and Last Name
[Field4]. Now, however, the 4 fields have been merged into a single field
containing the full name.

I'd like to know if there is any possible way to divide the single field
into the 4 seperate fields they had initially been divided to when the data
was first entered into Excel? I would appreciate any help anyone has to
offer.

 

[Rick B]

You can try, but what if the salutation is blank in a record? The variables
are great, and it is unlikely you'll be able to get them all correct...

What if the First name is Mary Ann and the Middle name is something else?
Your break would be all off.

What if the last name has a space? Mac Allister?

What if there is no middle name/initial?

My name is in the format of John D Jones III

Is my last name Jones III? Jones? III?

What about Jr.? Sr.? etc.

Do you have an "Mr. and Mrs." in the salutation?



Lost of variables.

How to break apart a name field is addressed pretty often. Do a search and
read the previous posts. It will tell you how to use INSTR and MID
functions to break apart the field in an update query.

Good Luck.


--
Rick B

You are absolutely right. I was mistaken. The original Excel document used
the full name field instead of the 4 different ones that I had initially
thought had been used. I didn't have the original to look off of, so I just
assumed that that was what had happened.

Now, is there any possible way for me to import the Excel document into
Access and divide the full name field in Excel into 4 different fields in
Access? Is there a way to divide them at the spaces?

Currently, the field contains this: Mr. John A. Smith. Instead of
importing the information as a single field, is it possible to import it as 4
different fields? I'd like it to resemble something like this: Mr. | John |
A | Smith.

Is it possible? Sorry for the confusion. Thanks again.

I'd try that import again. Access will not just randomly merge a bunch of
fields on import. If it did, it would be a miracle for it to just happen to
figure out that those four fields could be combined to make up a name.

Make sure it is in four fields in Excel, and try importing it again.


--
Rick B

I've just imported a substantial database from Excel and Access has

merged
4

of my fields into a single field.

Originally, the data was entered as the following: Salutation (Mr., Mrs.)
[Field1], First Name [Field2], Middle Initial [Field3], and Last Name
[Field4]. Now, however, the 4 fields have been merged into a single field
containing the full name.

I'd like to know if there is any possible way to divide the single field
into the 4 seperate fields they had initially been divided to when the data
was first entered into Excel? I would appreciate any help anyone has to
offer.

 

[Chris D]

Thanks for the timely responses. I'll do my best to look through previous
posts to find exactly what I'm looking for. The biggest problem I have is
the terminology while using Access and Excel. Your posts cleared up some
things for me. Thanks again for your time.

You can try, but what if the salutation is blank in a record? The variables
are great, and it is unlikely you'll be able to get them all correct...

What if the First name is Mary Ann and the Middle name is something else?
Your break would be all off.

What if the last name has a space? Mac Allister?

What if there is no middle name/initial?

My name is in the format of John D Jones III

Is my last name Jones III? Jones? III?

What about Jr.? Sr.? etc.

Do you have an "Mr. and Mrs." in the salutation?



Lost of variables.

How to break apart a name field is addressed pretty often. Do a search and
read the previous posts. It will tell you how to use INSTR and MID
functions to break apart the field in an update query.

Good Luck.


--
Rick B

You are absolutely right. I was mistaken. The original Excel document used
the full name field instead of the 4 different ones that I had initially
thought had been used. I didn't have the original to look off of, so I just
assumed that that was what had happened.

Now, is there any possible way for me to import the Excel document into
Access and divide the full name field in Excel into 4 different fields in
Access? Is there a way to divide them at the spaces?

Currently, the field contains this: Mr. John A. Smith. Instead of
importing the information as a single field, is it possible to import it as 4
different fields? I'd like it to resemble something like this: Mr. | John |
A | Smith.

Is it possible? Sorry for the confusion. Thanks again.

I'd try that import again. Access will not just randomly merge a bunch of
fields on import. If it did, it would be a miracle for it to just happen to
figure out that those four fields could be combined to make up a name.

Make sure it is in four fields in Excel, and try importing it again.


--
Rick B



I've just imported a substantial database from Excel and Access has merged
4
of my fields into a single field.

Originally, the data was entered as the following: Salutation (Mr., Mrs.)
[Field1], First Name [Field2], Middle Initial [Field3], and Last Name
[Field4]. Now, however, the 4 fields have been merged into a single field
containing the full name.

I'd like to know if there is any possible way to divide the single field
into the 4 seperate fields they had initially been divided to when the
data
was first entered into Excel? I would appreciate any help anyone has to
offer.

 

[George Nicholson]

While it might be technically possible to do what you want in Access, it
would still involve a lot of manual work.

If you have the data in an Excel file, you might start with Data>"Text to
Columns" (using space as a delimiter) in Excel and edit the data until you
have it separated the way you want (probably a lot of Inserting & Deleting
cells to "move" data left or right so it "lines up"). Then import it into
Access again. Either way you will probably still need to do a lot of manual
editing but that is often quite a bit easier in Excel than Access for jobs
like this.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Thanks for the timely responses. I'll do my best to look through previous
posts to find exactly what I'm looking for. The biggest problem I have is
the terminology while using Access and Excel. Your posts cleared up some
things for me. Thanks again for your time.

You can try, but what if the salutation is blank in a record? The
variables
are great, and it is unlikely you'll be able to get them all correct...

What if the First name is Mary Ann and the Middle name is something else?
Your break would be all off.

What if the last name has a space? Mac Allister?

What if there is no middle name/initial?

My name is in the format of John D Jones III

Is my last name Jones III? Jones? III?

What about Jr.? Sr.? etc.

Do you have an "Mr. and Mrs." in the salutation?



Lost of variables.

How to break apart a name field is addressed pretty often. Do a search
and
read the previous posts. It will tell you how to use INSTR and MID
functions to break apart the field in an update query.

Good Luck.


--
Rick B

You are absolutely right. I was mistaken. The original Excel document used
the full name field instead of the 4 different ones that I had
initially
thought had been used. I didn't have the original to look off of, so I just
assumed that that was what had happened.

Now, is there any possible way for me to import the Excel document into
Access and divide the full name field in Excel into 4 different fields
in
Access? Is there a way to divide them at the spaces?

Currently, the field contains this: Mr. John A. Smith. Instead of
importing the information as a single field, is it possible to import
it as 4
different fields? I'd like it to resemble something like this: Mr. |
John |
A | Smith.

Is it possible? Sorry for the confusion. Thanks again.

:

I'd try that import again. Access will not just randomly merge a
bunch of
fields on import. If it did, it would be a miracle for it to just happen to
figure out that those four fields could be combined to make up a
name.

Make sure it is in four fields in Excel, and try importing it again.


--
Rick B



I've just imported a substantial database from Excel and Access has merged
4
of my fields into a single field.

Originally, the data was entered as the following: Salutation (Mr., Mrs.)
[Field1], First Name [Field2], Middle Initial [Field3], and Last
Name
[Field4]. Now, however, the 4 fields have been merged into a
single field
containing the full name.

I'd like to know if there is any possible way to divide the single field
into the 4 seperate fields they had initially been divided to when
the
data
was first entered into Excel? I would appreciate any help anyone
has to
offer.