How do I import data and update

[focal]

Hello, I am a newer Access user and am trying to take and XLS file and import it into an existing Access file. I want to compare the records and if the record exists in the XLS file I want to replace it in the Access file.

It will have updated cost info in it. Is there an easy way to do this. Thanks

 

[THoey]

I suggest you import it into a temporary table and then run a query that will update your table, based on records in the temporary table.

To import it manually, create a copy of your table (right click on the table, copy, then save just the structure), go to File Menu -> Get External Data -> Import, and walk through the prompts. If you are going to do the import through code, look up TransferSpreadsheet in the help.

Once you get the data in, post back and we can help you through the SQL if necessary. Terry M. Hoey

 

[checkOut]

It's an old post, but can somebody help me with the update query? I have exactly this problem,

Thanx in advance,
Gerard

 

[annsolomon]

It's pretty straight forward. In the query window select Create Query in Design View. Select the table you want to update then click Query on the Menu bar and select Update Query. Add the field you want to update Field section of the grid then put whatever you want to change it to in the Update To section of the grid. (Note: You can update your field by entering a specific value in the Update To section or by entering a field from this or another table in the Update To section.) Ann

 

[jdttek]

I have tried this approach (using update query). WHat I found was, all records that exist on both the xls and the Acccess table updated OK. HOWEVER:
1. If a record no longer existed on the xls, it was not deleted on the Access table automatically.
2. If there was a new record on the xls table, it did not get transferred to the access table.
In managing a receivles portfolio for example (to update balances) it does no good if a paid off account remained on the access table or if a new account did not get transferred over.

What I did was, totally delete the records on the access table leaving structure and primary key in place) (Select all records and delete). Then import the xls table into the existing empty table.

I wld like to know if all of that is necessary or if there is a way to update exsting records PLUS create new and delete nonexistent records at the same time.

JDTTEK

 

[annsolomon]

jdttek, you're situation is a bit different. First make sure you're importing your spreadsheet into an empty temporary table. Create a Delete Query that will delete records from the old table that do not have matching records in the temporary table. Create an Append Query that appends records from the temporary table to the old table where there records in the temporary table that don't already exist in the old table. Ann

 

[nathanstevenson]

Question:
How eactly do you create an append query that appends records from a temporary table to an old table where the records in the temporary table don't exist in the old table?
And how do you increment from the last record added?

Nathan