Separating data from one table column into two columns

[taryn]

Hello

I have a column, "GivenNames", which holds the first name and any additional (middle names) of individuals. The surnames are held in a different column

"LastName".

I need to create a query for a report which separates the data held in

"GivenNames" into two columns: one column should just have the f

irst name and the second column should have

any additional (middle names) of the individual.

Below is some sample data and how the results of the query should look:



Sample data:

RefCode	LastName	GivenNames
SMITHJ	Smith	John James
SMITHP	Smith	Paul Michael Charles
SMITHK	Smith	Karen

Desired result:

RefCode	LastName	FirstName	MiddleNames
SMITHJ	Smith	John	James
SMITHP	Smith	Paul	Michael Charles
SMITHK	Smith	Karen

I look forward to hearing from the forum.

Kind regards.

Taryn

[JoeM]

For values like "Paul Michael James", how do you know that the Middle Name is the one that has two words and not First Name?
I have seen people with multiple First Names like "Ann Marie" and "Bobby Jo".
Or don't you really care about that, and if there are more than two names, you always want the First Name to be the first word, and the Middle Names to be the rest?

If so, then use these calculated fields to get your names:
FirstName: IIf(InStr([GivenNames]," ")>0,Left([GivenNames],InStr([GivenNames]," ")-1),[GivenNames])
MiddleNames: IIf(InStr([GivenNames]," ")>0,Mid([GivenNames],InStr([GivenNames]," ")+1),"")

[taryn]

Thank you very much for your prompt reply. Query has been solved.

Kind regards.

Taryn

[JoeM]

You are welcome.