importing excel data into an existing access database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my access database I have a "Full Name" field that needs to be spilt into
first name, middle name, and last name. I have created 3 fields for the new
catagories. I have also already exported all of the "Full Name" data entries
into excel and have spilt the data into 3 columns using the 'text to columns'
function.

I am now trying to export the data back to Access. I can import the data
from excel back into access however access imports the data as new entries
rather than matching it up with the existing entries so that each person has
two entries. Is there any way to import the excel data and control where
access places the data in the existing table?? Thank you in advance.
 
assuming that all the "Full Name" values are unique (no multiple Harry Frank
Smith's, for example), you can import the spreadsheet into a "temp" table.
then create a query based on the temp table, add all three fields to the
table, and create a fourth field with the three names concatenated, as

FullNameAgain: [FirstName] & " " & [MiddleName] & " " & [LastName]

make sure you set up the calculated name field to match the order of the
full name field in the original data table.

now link the real data table and the temp-table query in a second query,
linking the two tables on the full name field in each. open the query to
make sure the data matches up correctly. then turn the query into an Update
query, and update the real data table with the three separate name fields
from the temp-table query.

hth
 
Back
Top