By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Splitting data

P: n/a
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
Jan 5 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jan 5 '08 #2

P: n/a
"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;
Jan 5 '08 #3

P: n/a
On Jan 5, 3:52*pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <mike.drean...@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
Thanks. Very helpfull
Jan 5 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.