Update records on only ONE FIELD from excel data?

[stildawn]

Hi All

I have a table which has a column called "Service". I have just been informed that the data in the "Service" field for all our records is incorrect, but there is a spreadsheet that I could do a VLOOKUP on to correct the service field for the affected records.



My question is, is there a way to update just the one field in my access table from a excel sheet, updating the correct records in the table??

If that makes sense?

Cheers

[June7]

Yes, that would be an SQL action.

Do you have an established link to the spreadsheet? If so, use the link just as any table, except can't edit the spreadsheet.

The link can be queried, sorted, joined in query.

Can the table and link be joined in query?

Can use the query builder and ribbon tools to help build UPDATE query.

[stildawn]

Sorry I didn't understand any of that haha, I'm fairly new to Access...

The only spreadsheet currently is a completely random one that some other department has generated... What I was planning to do was export my entire table from Access to a new Workbook.

Then do a VLOOKUP from the Access Workbook, with the Workbook that contains the correct values for the "Service" column.

I was then going to just delete all the amended records in the Access table, and then copy and paste in the "updated records" from the spread sheet into Access...

But I thought there is probably a better cleaner way of doing this? Like is it possible to do a one time VLOOKUP in access itself with the random spreadsheet?

[June7]

The function in Access is called DLookup and might not be needed.

If this is a one time event and not a gazillion records, then the copy/paste would be quick and probably simple. Assuming there are no dependent tables related to this table that are associated by autonumber primary key.

If the spreadsheet structure is such that Access can link to it, that would be another way to go.

Of course, have to learn a little more about Access but if you are going to use Access, then better learn it, especially the basics.

Everything I previously described is fairly basic functionality.