Splitting data

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

Re: Splitting data

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" wrote in message
news:83281567-6457-4cc0-9749-5f7704715933@n22g2000prh.google groups.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

Re: Splitting data

"zazu" wrote in message
news:83281567-6457-4cc0-9749-5f7704715933@n22g2000prh.google groups.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;

Re: Splitting data

On Jan 5, 3:52=A0pm, "Allen Browne" wrote:
> 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=
:
> =A0 =A0http://allenbrowne.com/func-10.html
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "zazu" wrote in message
>
> news:83281567-6457-4cc0-9749-5f7704715933@n22g2000prh.google groups.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