SPLIT INFO

Discussion in 'Microsoft Access Queries' started by Guest, Nov 8, 2006.

  1. Guest

    Guest Guest

    I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS IN
    ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO CELLS

    THANKS IN ADVANCE FOR YOUR HELP

    SJW
     
    Guest, Nov 8, 2006
    #1
    1. Advertisements

  2. Guest

    Rick B Guest

    Please stop shouting at us (turn off your caps lock).

    If you will search, you'll find numerous previous posts that address this
    topic. Here is a portion of one of those posts...

    You can add a new column to your query for the first name, and a column for
    the last name. You can then use the values from the query in your forms,
    reports, and other queries. I'd probably make this into an update query and
    clean up my table and maintain the two fields from this point forward and
    delete the combined field.

    FName: Left([Name],InStr([Name],",")-1)

    LName: Mid([Name],InStr([Name],",")+1)


    --
    Rick B




    "SJW" <> wrote in message
    news:...
    >I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS
    >IN
    > ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
    > CELLS
    >
    > THANKS IN ADVANCE FOR YOUR HELP
    >
    > SJW
     
    Rick B, Nov 8, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Add FirstName and LastName fields to the table and then run an update query
    to populate them with values parsed from the existing field, assumed to be
    called FullName in this example, e.g.

    UPDATE YourTable
    SET FirstName = MID(Fullname,INSTR(FullName," ")+1),
    LastName = LEFT(FullName, INSTR(FullName,",")-1);

    This assumes all the values in the FullName field are in exactly the same
    format as in your example , i.e. the last name followed by a comma, then a
    space, then the first name.

    Remember that if table or column names have spaces or other special
    characters in them you need to wrap them in square brackets [like this].

    BTW spreadsheets have cells, database tables have rows and columns (aka
    rather inaccurately as records and fields). A database is a very different
    animal from a spreadsheet.

    Ken Sheridan
    Stafford, England

    "SJW" wrote:

    > I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS IN
    > ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO CELLS
    >
    > THANKS IN ADVANCE FOR YOUR HELP
    >
    > SJW
     
    Guest, Nov 8, 2006
    #3
  4. Guest

    Guest Guest

    RICK, THANKS FOR YOUR HELP. ALL CAPS FOR GUYS THAST ARE 70 AND DON;T SEE SO
    WELL SORRY TO OFFEND YOU

    SJW

    "Rick B" wrote:

    > Please stop shouting at us (turn off your caps lock).
    >
    > If you will search, you'll find numerous previous posts that address this
    > topic. Here is a portion of one of those posts...
    >
    > You can add a new column to your query for the first name, and a column for
    > the last name. You can then use the values from the query in your forms,
    > reports, and other queries. I'd probably make this into an update query and
    > clean up my table and maintain the two fields from this point forward and
    > delete the combined field.
    >
    > FName: Left([Name],InStr([Name],",")-1)
    >
    > LName: Mid([Name],InStr([Name],",")+1)
    >
    >
    > --
    > Rick B
    >
    >
    >
    >
    > "SJW" <> wrote in message
    > news:...
    > >I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS
    > >IN
    > > ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
    > > CELLS
    > >
    > > THANKS IN ADVANCE FOR YOUR HELP
    > >
    > > SJW

    >
    >
    >
     
    Guest, Nov 8, 2006
    #4
  5. Guest

    Rick B Guest

    NO PROBLEM

    :)

    --
    Rick B



    "SJW" <> wrote in message
    news:...
    > RICK, THANKS FOR YOUR HELP. ALL CAPS FOR GUYS THAST ARE 70 AND DON;T SEE
    > SO
    > WELL SORRY TO OFFEND YOU
    >
    > SJW
    >
    > "Rick B" wrote:
    >
    >> Please stop shouting at us (turn off your caps lock).
    >>
    >> If you will search, you'll find numerous previous posts that address this
    >> topic. Here is a portion of one of those posts...
    >>
    >> You can add a new column to your query for the first name, and a column
    >> for
    >> the last name. You can then use the values from the query in your forms,
    >> reports, and other queries. I'd probably make this into an update query
    >> and
    >> clean up my table and maintain the two fields from this point forward and
    >> delete the combined field.
    >>
    >> FName: Left([Name],InStr([Name],",")-1)
    >>
    >> LName: Mid([Name],InStr([Name],",")+1)
    >>
    >>
    >> --
    >> Rick B
    >>
    >>
    >>
    >>
    >> "SJW" <> wrote in message
    >> news:...
    >> >I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME
    >> >IS
    >> >IN
    >> > ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
    >> > CELLS
    >> >
    >> > THANKS IN ADVANCE FOR YOUR HELP
    >> >
    >> > SJW

    >>
    >>
    >>
     
    Rick B, Nov 8, 2006
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. R W Pearson

    Fields from split table do not appear in Form View in Access 2000

    R W Pearson, Jul 25, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    499
    Van T. Dinh
    Jul 26, 2003
  2. Stranger

    Split info in one field into two fields?

    Stranger, Sep 6, 2004, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    224
    Stranger
    Sep 6, 2004
  3. Guest

    Can a query find info X numbr of rows from target info?

    Guest, Nov 23, 2005, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    231
    Guest
    Nov 29, 2005
  4. Guest
    Replies:
    3
    Views:
    228
    Guest
    Aug 15, 2006
  5. Michelle
    Replies:
    3
    Views:
    1,050
    Michelle
    May 31, 2009
Loading...

Share This Page