Update + Append Query in Access 2007

Question

I'm working on a database right now and I have a pretty specific problem that I'm trying to figure out:

I have a large master table in my database with all the info we're gathering. We're updating records in this master table based on Excel files returned to us by various team members across the company - all of the records have unique ID numbers so we know what fields in the master table to update. We are tracking who responds by updating the file name into the master table as well. I want to update this with the file name; however, if two sources give me the same data, I want to append the second file to the first file rather than replace it with an update.

The problem is, I need the query to "know" when to update and when to append. Is there some IF statement I can use - maybe Update when Null, Append when Not Null?

Fionnuala Fionnuala · Accepted Answer · 2012-09-12T17:55:13

You can refer to an Excel sheet or range in a query:

INSERT INTO Table1 ( ADate ) 
SELECT SomeDate FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$a1:a4]
WHERE SomeDate Is Not Null

This means that you can run queries based on the presence or absence of data in the Excel file.

Update + Append Query in Access 2007

1 Answers