CODEWITHSUNDEEP
sqlms-access
James An
James An

Reputation: 15

Microsoft Access - How do I import data to an existing Table and update the data

simple yet, a question I can not find an answer too.

I am currently importing RAW Data from Excel to my ACCESS database. Every day, this RAW data I receive updates in status.

I have a field/key that never changes for example a Document number. However, fields like "Status" and "quantity" can change based on a given situation.

Is there a way for me import my new file into my existing data and have it update/overwrite data that matches my Document number field?

Upvotes: 1

Views: 521

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

create a unique constraint on the fields which will be unique then you can use

UPDATE
MyAccessTable A
INNER JOIN MyLinkedExcelTable X
    ON A.document_number = X.document_number
SET A.f1 = X.f1

then insert the missing ones using

INSERT INTO MyAccessTable (f1, f2, .....)
SELECT f1, f2, ....
FROM MyLinkedExcelTable X
WHERE X.document_number NOT IN (SELECT Document_number FROM MyAccessTable)

Upvotes: 1

Related Questions