How can I clean up my data?

[Guest]

I imported a spreadsheet from Excel that was used for employee names. The format was Last, First with all names typed into one column. Someone gave me a code to separate the last and first names into two columns, and now here's my problem:
The beginning character is a space (since all characters to the right of the comma were moved to the first name column) and the middle initial (along with its separating space) needs to be removed for automatic lookup purposes. The idea is to have one exact name in the First Name column, no spaces or initials.

 

[Luiz Cláudio C. V. Rocha [MVP]]

Hi,

you can try Trim or LTrim function to remove the space.

Luiz Cláudio C. V. Rocha
São Paulo - Brazil

I imported a spreadsheet from Excel that was used for employee names. The

format was Last, First with all names typed into one column. Someone gave
me a code to separate the last and first names into two columns, and now
here's my problem:

The beginning character is a space (since all characters to the right of

the comma were moved to the first name column) and the middle initial (along
with its separating space) needs to be removed for automatic lookup
purposes. The idea is to have one exact name in the First Name column, no
spaces or initials.

 

[John Vinson]

I imported a spreadsheet from Excel that was used for employee names. The format was Last, First with all names typed into one column. Someone gave me a code to separate the last and first names into two columns, and now here's my problem:
The beginning character is a space (since all characters to the right of the comma were moved to the first name column) and the middle initial (along with its separating space) needs to be removed for automatic lookup purposes. The idea is to have one exact name in the First Name column, no spaces or initials.

Update the field First to Trim([First]), and the same for Last.

I'm not clear where the middle initial is - could you post an example?
Do you want to keep the initial somewhere else, so you can distinguish
Robert L. Jones from Robert J. Jones?

 

[John Vinson]

Update the field First to Trim([First]), and the same for Last.
--How do I update the field?

Create a new Query based on the table. Change it to an UPDATE query
using the Query menu item or the query-type icon. You'll get a new
line in the query grid labeled Update To - on it put

Trim([First])

under First to trim off leading (or trailing, though that's not likely
to be there) blanks.

Do you want to keep the initial somewhere else, so you can distinguish
Robert L. Jones from Robert J. Jones?

The original entry was "SIMPSON, JASON S" in the name column. (They were in all caps if that could be corrected, no period after MI which is fine)

You can update to

StrConv([First], 3)

or do both in one swell foop by updating to

StrConv(Trim([First]), 3)

Then someone gave me a code that erased the comma and separated the two names into columns. Seems to have worked successfully for nearly every entry, with the exception of the problem I'm having now.

" ROBERT J" That is the entire entry in first name, starting with a space. Maybe I can erase only single letter entries after the first name for the sake of preserving "Anna Maria" vs. repairing "John J".

If you indeed just want to irreversibly discard the J, use a SEPARATE
update query. Use a criterion on First of

LIKE "* ?"

to find only records where there the name ends in a blank followed by
a single alpha character. Update the field to

Left([First], Len([First]) - 2)

to remove the last two characters - the blank and the initial.

My expectation is that we can eventually have customers enter applications into our computers so I don't want to get too in depth* to a system just before it gets upgraded. But meanwhile their main stock of clientel is on paper. I can distinguish records with the SSN and DOB without middle name, so I feel safe deleting it, and few of the applicants listed a middle name. But there is a hidden column named 'middle' accomodating 'first' and 'last' if you think I should save the MI. A 'code' would be terrific, as well as where to enter it.

If you have a [Middle] field, in the same query where you're updating
the First field, you can update the [Middle] field to

Right([First], 1)

*But if I ask you some far out questions it's because I'm learning in which case I can't get too in-depth

You only learn by asking! <g>

 

[Guest]

I need to do the same thing. Can you share the code to separate the name into
individual fields, please?

Thanks in Advance
MW

 

[kerrpmb]

It is just a mechanical way of creating accounting paperwork. Please be more
specific. It has nothing to do with creating financial for paychecks that is
for Microsoft Dynamics CRM or one of the other softwares. Thank you.