Query to separate names (2000)

Topic

New Reply

Hi!

I’m working on a database where the user has been entering names as first name/ last name all in one field vs separating them out. They now need to have the names separated. I would like to use a query to do this however am not sure what to put in. I know how to join twofields as one but can’t do the reverse. The field as it stands now is [EmployeeName] and the format is first name last name or [Jane Doe]. I need a field for [LastName] and a field for [FirstName]. I do not want to disrupt or change the original infomation.

Thanks!

Leesha

Reply | Quote

Replies

check out the thread beginning in post 302777 . You can either use this in a select query or you can use it in an update query to write the data back to two separate newly created columns in the table.

best,
Karl

Reply | Quote

Hi Karl,

I tried Han’s first approach with the query as this is what I was looking for but got nothing in the columns. I did not get any error messages as in parameter values when I substituted my field name [Recommended Therapist] for the the one in the example. This is my formula:

FirstName: Left([Recommended Therapist],InStr([Recommended Therapist],” “)-1)
and
LastName: Mid([Recommended Therapist],InStr([Recommended Therapist],” “)+1)

Any ideas on why neither column is returning a value?

Thanks,
Leesha

Reply | Quote

You might try putting the InStr as a separate expression in a column to the left of the First and LastName columns so you can see what is being returned – it may be that you have spaces that preceed the combined Name field – you may need an LTrim function if that is the case.

FYI, the followon post by SteveH suggesting you use Excel is what I use for most situations like this. Names aren’t terribly regular, so you get some with middle names or intials, and you also have Last Names that have spaces such as Van Meiter, and de Lahoya. Excel works well in terms of letting you see the irregular ones and correct them, and then reimport the data in a new table.

Reply | Quote

Oh God Wendell, you give me more credit than I deserve to be able to follow what you suggested. Would you mind posting an example?

Re the Excel suggestion, that would be my first choice, however the names need to go into merge fields in reports and the end user wants the simplest means possible and exporting to and using excel would not be there choice.

Leesha

Reply | Quote

Create a column in your query:

PosOfSpace: InStr([Recommended Therapist]," ")

What is the result if you switch to datasheet view? If this column displays zeros, the character between the first and last name is not an ordinary space.

Reply | Quote

Hi Hans,

It displays a single numbers, no zero’s, no names. I’m not sure what the number corresponds to.

Leesha

Reply | Quote

It looks like Hans has solved your problem, and he did pretty much as I suggested.

However, you should understand the Excel alternative, as it has a number of benefits with irregular names – if you get more than say, 10,000 names, you definitely need the programatic approach, but for smaller numbers of names, I actually find Excel to be quicker and more accurate. The steps are as follows:

• Copy the table, or at least the primary key and the name fields and paste them into an Excel workbook,
• Use the Data / Text to Columns to break the name into columns,
• correct the obvious errors by recombining Last Names that contain spaces, or suffixes such as Jr. or III, and
• Import the resulting data back into Access or link to it and run an update query to populate the new FirstName and LastName fields.[/list] Hope this is a bit clearer. We routinely have about 6 fields that we use for databases; First, Middle, Last, Prefix, Suffix and AKA-Nickname. In some cases we also use a special mailing label field where an individual wants some variation of his name used on mailings.

Reply | Quote

That’s fine. This column displays the position of the space in the recommended therapist field. For example, in “Will Hoffman” the space comes after 4 letters, i.e. the space is in the 5th position. Other examples:

Jim Jones	4
Edna Evans	5
Caroline Tenson	9

Now add this column to the right of the PosOfSpace column:

FirstPart: Left([Recommended Therapist],[PosOfSpace]-1)

Does this display anything?

Reply | Quote

Yes! Now it gives me the first name!!! Now all I need is the last name. God I appreciate this!

Leesha

Reply | Quote

Great! Now the next step: add a new column to the right of the ones you have:

SecondPart: Mid([Recommended Therapist],[PosOfSpace]+1)

Reply | Quote

THANK YOU!! Worked beautifully. Now to go back and study it so I can understand it and duplicate it down the line.

Leesha

Reply | Quote