Splitting data

[zazu]

We have a database of books, which includes a field "Author"

Authors are listed like:

Annabel Langbein
Alison Holst
Fred Reichelman

I would like to create a table of authors with seperate fields for
FirstName and LastName based on the table above (some 2000 + authors).
Can I run a query to split the contents of the original Author field
based on the space between the first and last name, noting that the
length of each name varies. Is it possible to do this with an
expression in a select query.

If someone could show me how, it would be much appreciated

Regards

[Allen Browne]

Yes. In the Field row in query design, you can type an expression that
parses the 2 words, breaking at the space.

Use Instr() to locate the space, Left() and Len() to get the first word,
Mid() to get the remainder.

If some entries might have multiple spaces, such as Robert Louis Stevenson,
use InstrRev() to get the last space.

If it helps, there's a ParseWord() function here that parses the n-th word:
http://allenbrowne.com/func-10.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"zazu" <mi***********@gmail.comwrote in message
news:83**********************************@n22g2000 prh.googlegroups.com...

We have a database of books, which includes a field "Author"

Authors are listed like:

Annabel Langbein
Alison Holst
Fred Reichelman

I would like to create a table of authors with seperate fields for
FirstName and LastName based on the table above (some 2000 + authors).
Can I run a query to split the contents of the original Author field
based on the space between the first and last name, noting that the
length of each name varies. Is it possible to do this with an
expression in a select query.

If someone could show me how, it would be much appreciated

Regards

[Stuart McCall]

"zazu" <mi***********@gmail.comwrote in message
news:83**********************************@n22g2000 prh.googlegroups.com...

We have a database of books, which includes a field "Author"

Authors are listed like:

Annabel Langbein
Alison Holst
Fred Reichelman

I would like to create a table of authors with seperate fields for
FirstName and LastName based on the table above (some 2000 + authors).
Can I run a query to split the contents of the original Author field
based on the space between the first and last name, noting that the
length of each name varies. Is it possible to do this with an
expression in a select query.

If someone could show me how, it would be much appreciated

Regards

So long as you're sure it's ALWAYS first name space last name, this will
create a table called NewTable with the fields split the way you want:

SELECT Left([Author],InStr(1,[Author]," ")-1) AS FirstName,
Mid([Author],InStr(1,[Author]," ")+1) AS LastName INTO NewTable
FROM Books;

[zazu]

On Jan 5, 3:52*pm, "Allen Browne" <[e-mail]:

Yes. In the Field row in query design, you can type an expression that
parses the 2 words, breaking at the space.

Use Instr() to locate the space, Left() and Len() to get the first word,
Mid() to get the remainder.

If some entries might have multiple spaces, such as Robert Louis Stevenson,
use InstrRev() to get the last space.

If it helps, there's a ParseWord() function here that parses the n-th word:
* *http://allenbrowne.com/func-10.html

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"zazu" <[e-mail] in message

news:83**********************************@n22g2000 prh.googlegroups.com...

We have a database of books, which includes a field "Author"

Authors are listed like:

Annabel Langbein
Alison Holst
Fred Reichelman

I would like to create a table of authors with seperate fields for
FirstName and LastName based on the table above (some 2000 + authors).
Can I run a query to split the contents of the original Author field
based on the space between the first and last name, noting that the
length of each name varies. Is it possible to do this with an
expression in a select query.

If someone could show me how, it would be much appreciated

Regards

Thanks. Very helpfull