Access import from Excel - Append & Update simultaneously

G

Guest

We are importing data from an Excel spreadsheet into an existing Access table
and need to know if it can update existing rows and append new rows at the
same time, by primary key field. Or, if the import can not do both at the
same then if we import the spreadsheet into blank temp table can we then use
an append query and update query at the same time.

We are importing monthly sales data from our accounting system, through an
Excel spreadsheet, into a yearly sales table in Access. Our primary key in
the table is account number. If the sales spreadsheet has a row for an
account number that already exists in the table (we've had previous business
with that customer this year), then we need to update the table with the new
spreadsheet data. If the sales spreadsheet has a row for an account number
that does not yet exist in the table (new business), then we need to create
or append a new row in the table.

If you run an append query, what happens with the duplicate primary key
rows? If you run an update query, what happens to primary key rows that
don't match? We may also have to deal with cascading the primary key updates
to other tables. Can both an update and an append be done from the same
spreadsheet, or table?

Thanks for any assistance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top