Import from a spreadsheet and overwright existing data

[IannWard]

Hi All


I have a table with product information consisting of half a dozen fields. We recently updated some of our remote systems and the new data was recorded on a spreadsheet. I was to import the data into my database and update some of the fields with the new info, for example:

Database table has 4 fields:
Serial Number - 98746321
Asset Tag - 00001526
Location - Our Stock
Item Description - Dell5810

Spread sheet has 3 columns:
Serial Number - 98746321
Asset Tag - 00001526
Location - 0250

As you can see, the location has changed..... and there is no item description on the spreadsheet.
If I try the usual import spreadsheet route I will get a "Duplicates" error.
How can I import the spreadsheet and have it only change the fields where the data has changed?
I'm sure this is simple to you experts but if you can help with full instructions including any code, that would be great.
There are 7500 entries to update, so will take too long to manually search each serial number in a form and make the changes there.

Many Thanks

Iann

[JoeM]

Import the data into a new, temporary table.
Then join this temporary table to your main table, joining on your key fields (Serial Number and Asset Tag, presumably) and update the fields you want using an Update Query.

[IannWard]

Hi Joe
Many thanks for your quick response.
I have created a temp table and copied in the data. I have opened a query, inserted both tables, joined the common fields and in the query design grid I have entered the destination field and table and in the 'update to' cell I entered [SerialNumbersTemp].[Serial Number]
When I run the query I get the error 'Type mismatch in expression'... Can you please help?

[IannWard]

Hi Joe

My apologies, I got it working, I joined two fields that had different data by mistake. un joined them, left the serial number joined and it worked a treat.

Thanks a million for your help.

Iann

[JoeM]

You're most welcome!

[ssanfu]

Oops, got distracted with work... was going to cancel... hit the wrong button.