1

I have a database in Access that I am trying to fix (actually, I'm working on a copy of the original).

There are two tables, one with data input errors, and a second that is a copy of those errors, but fixed, if that makes sense. However the second table only contains 3 out of 14-ish fields, and the primary key is not among them.

I would like to update the first table with the information from the second, searching for records to update based on similarity between the Title field in both. The second has had some typos fixed throughout as well...

I know some SQL but not much.

0

Without the primary key, fixing the data with an update statement is likely to get very messy. You're most likely better off getting a fresh export of the original data with the PK included, run the process/es that you did to clean up the data, then run an update statement. And then put constraints on the table to prevent the errors from occurring again.

The update statement should be fairly simple though. I don't know the full syntax for Access, but here's the general SQL statement I'd use on SQL Server:

update TableA
set FieldA = source.FieldA
from TableA destination
inner join TableB source
on destination.PK_ID = source.PK_ID
  • @CarenRose yeah it sucks when you get so far and then realize that you're missing vital data. but it's always best to do it this way so that you can 100% ensure data quality. – DForck42 Feb 6 '15 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.