Update Table from Excel Import (1 Viewer)

[matthewnsarah07]

Hi All

I'm setting up a Dbase which will sift through scheduled works from two business areas before sending bespoke reports out to staff concerned.

The works list comes as an Excel Sheet which I have imported once to create tables within access with a Primary Key as [WorkID]

When a job is initially imported a local field [Status] will default to "New".

Now if of course I try importing the next days list with some of the previous days works and some new it will only allow the new records to be added. I need to be able to import all works and where an imported primary key matches one in the table (tblArea) it should update the rest of the record and change status to "Updated".

What is going to be the quickest way to do this - can be done at import or is there another way??

Thanks for any help

 

[Fuga]

I would solve this with queries and a temporary table linked to the excel sheet.

First, you make an update query with inner joins on the workID field (from your temporary table to the ordinary).

Second, you make an append query, probably with an outer join on the workID field (ie missmatch query).

If you use VBA, you can use docmd.transferspreadsheet to import an excel sheet if it´s not linked, then docmd.openquery to run the queries.

Fuga