Importing Data into Access from Excel

[benkingery]

I have a new Column on a table that I need to start filling in the data for existing records within my table. In order to determine exactly which records need a value for this column, I exported a query that specified which records meet the criteria of needing a value for the new column. I collected all the relevant data into excel. So I have 2 columns, 1 is the primary key of the table, and the 2nd is the New Column populated with the values I want to import into my database.

If I use the "File"-->"Get External Data"-->"Import" function, it doesn't allow me to select what items I want to import, but rather tries to create new records based on the information on the sheet. For obvious reasons, it will not import because of key violations, etc.

I imported the info into a temp table that I can use to create a query to show 3 fields (1-primary key of table, 2-New Column of table, 3-New Column of temp table). From there I can copy and paste items from the query results into the New Column in the original table, but I want to know if anyone knows how to update the data quicker.

Any thoughts are greatly appreciated

 

[CEH]

You should be able to import the way youwant from Excel...BUT...since you already have it in a temp table in your DB look into an "Update query" or "Append query"...... Might give you what your looking for.

 

[FoFa]

BACKUP your database.

Use and UPDATE query.

Create a query with the two tables, better to use the main table first, but does not matter.
Join the two tables on the primary key
MENU = QUERY - UPDATE
Pull down the column you want to update (recives the data) from the main table.
In the UPDATE TOO area, place the [tablename].[columnname] of the joined table.
Save the query.
close the query.
run the query.

 

[benkingery]

Thank you. This works perfectly.