appropriate query type?

[jerbacher]

Hi everyone,

I'm not extremely fluent in queries, but I have a question. I have information in a table which is updated from an external source in the form of an excel spreadsheet. I was just wondering what type of query to use (if a query is even the thing to do) to update the information in the table with info from the spreadsheet. It would be best if the query could compare the two and only change records which have changed in the spreadsheet. Any suggestions are greatly appreciated.


Jon

 

[glynch]

There a re a lot of ways to update information from an incoming datasource. It depends on the business rules how you want to run an update. I have worked for companies that don't actually delete any data, but just archive old records (records whose data will be changed by an update), and append the changed record as an additional record in the receiving table.

But anyway a common way to handle your problem is to use two queries, one update query, and one append query. I'm assuming the Access table and the Excel spreadsheet have a primary key - foreign key relationship across one or more fields.

The append query would use an outer join on the key field(s) to identify the records in the spreadsheet that don't exist in the table.

The update query would use an inner join to find a match on the common records in the spreadsheet and the table and update all of the other fields in the table to the values in the corresponding fields in the spreadsheet.

This is a simple example, there may also be audit fields involved to track last update date, there may not be a direct key to key relationship, etc. There are a lot of circumstances which can complicate an update process. If you are not sure you are getting it right (isn't that always the case?), try this first with copies of the tables and see if you get the results you need, then run it for real when you've worked out all of the bugs.

Good Luck!