Updating table from excel

[reglarh]

I have an excel spreadsheet from a bank, from which I can extract membership renewal payments.

My task is to update the membership table with dates and amounts paid.

What would be the best approach to this task?

 

[Ranman256]

Save the excel file as a generic name,like file2import.xls.
Link the file as a table.
Make 2 queries,
1 query to add new records that don't exist.
1 query to update existing items (if needed).
Put the queries in a macro.

So the workflow would be:
1.Get new XL file,save and overwrite file2import.xls.
2. Run macro.
Done.

 

[Galaxiom]

1 query to add new records that don't exist.
1 query to update existing items (if needed).

The Insert query is not necessary because it can all be done with the Update query.

Outer join from the source table to the destination table on the key fields. Existing records will be updated and new records will be inserted.

 

[reglarh]

Thanks guys!

All working without vba.