Update specific records only

[antrawson]

Hi all,
I did have an account however I forgot my password and it was my old employers email address *doh*

Anyway, I am looking for some help please on access

I have a table which has a load of columns. There is one column with a unique field and one that has quantity.

How can I using a spreadsheet mass update the records eg I want it to update the quantity field only but for each record with that identifier

It would be easy in excel however the lines change and as such the info on row 244 for example will be different on the next released spreadsheet

Any help would be great
Many thanks

 

[Micron]

update the quantity field only but for each record with that identifier

What identifier? If you mean the unique table id you refer to, there is only one record, otherwise it can't be unique. I think you mean update the table quantity field using the quantity from the spreadsheet where each table id equals the id in the spreadsheet.
You have a couple of options, I guess. If the structure of the table and spreadsheet will not change, then you could use the Transferspreadsheet method to move the spreadsheet data into a table that you link to in an update query that links the tables by id and performs the update using the quantity that you transfered.

Depending on the rest of your data, you could also only transfer the data and over-write the table entirely if that makes sense. You will need a work-around because of the unique table id - maybe deleting the table data first (using transactions would be advised in case the upload fails. You would roll back the delete).

Or you could use automation to open Excel, but this would be more difficult and involve loops that either found a matching id in the spreadsheet or vice-versa

 

[antrawson]

Hi
I will add some more context as it may help

In my table in access Column B is the unique identifier, column F will have a number which is quantity. I will have a spreadsheet which has identical headers as thats in the database. I want to only update the access database with column F against the column B
Does this help a bit?
Thanks

 

[Micron]

Thanks for clarifying - I think this

I think you mean update the table quantity field using the quantity from the spreadsheet where each table id equals the id in the spreadsheet.

means the same thing. If we agree, my answer was based on that assumption so no changes there. The method you choose would be based on your knowledge/comfort level and business environment. Maybe you need to research "transferspreadsheet" to see if that would work for you. Automation is a different animal.