Replacing values with an ID when importing tables in MS Access

Question

I have imported data from a monolithic csv file into MS Access. One of my fields is notes which can be pretty much anything, or be any length. Regardless, its content is often repeated across records.

So I have split each unique note into a new table and added an 'autonumber' field to serve as the primary key. All good so far.

The problem is that I now need to link the original table with the new notes one, but the original table has no knowledge of which ID should match which note, and so I am unable to replace the note with the ID.

I also cannot link the note field on the original table with the note field in the note table (nor would I want to) because the fields are 'long text'.

Answer 1

Since you can't JOIN on Memo fields in Access, you will need to create a CROSS JOIN and filter where the ID's are equal. From there, it's just an UPDATE:

UPDATE Products, Notes
SET Products.NoteID = Notes.NoteID
WHERE (Products.Notes=Notes.Notes);

sgeddes has the same idea in his code as well.