Action Queries

  • Thread starter Thread starter Emily
  • Start date Start date
E

Emily

I am starting a database for employee health insurance.
All the data I need is already in an Excel spreadsheet. I
imported the excel sheet into access with no problem.
However, the excel sheet had last and 1st names in the
same field. I need the last and 1st names to be in
separate fields. Can the current name field be split into
2 fields. I hate to re-enter hundreds of names in a new
table if I don't have to.

Thanks for any guidance on this matter

Emily
 
The field can't be split into two but you don't have to re-
enter data either.

First, you need to create two new fields in your table.
One for first name and one for last name.

Then, create an update query and in the "Update To" row of
your query grid under your last name field put:

Right([Name],Len([Name])-InStr([Name]," "))

in the "Update To" row of your query grid under your first
name field put:

Left([Name],InStr([Name]," "))

This is assuming your "Name" field is FirstName LastName.
If your field is formatted LastName, FirstName you'll have
to change your funcions around some.

Look up the folowing functions to see how this is
acomplished:

Right()
Left()
Len()
Instr()

HTH
 
Emily:

What I would suggest doing is to open up the original spreadsheet that you
had and create two functions inside the function bar (equals bar at the
top):

A B C
1 Full Name FirstName LastName
2 Jim Jawn Jim Jawn

B2's Formula would look like this: =LEFT(A2, FIND(" ", A2)-1)
C2's Formula would look like this: =RIGHT(A2, FIND(" ", A2)+1)

You add and subtract 1 to account for the space. Once you get that, you can
just drag the formula down the column, copy the cells, the paste special >>
values... Once you do that, you can just reimport.

You can also do a search in excel for help on the left, right and find
functions. Alternatively, you could do something in access, but it would be
overkill (although similar) for what you need. Hope that helps.

Jim Jawn
 
Also, if you are going to fix the data in excel and then import, you might take
a look at using the menu item Data:Text to Columns.

Add a new column to the right of the names
Select the all the names
Select Data: Text to Columns and use the wizard to split the data
If it works, save the revised spreadsheet and reimport the data into Access
 
Back
Top