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'.

You need to `join` on some field, and in this case, it sounds like the `note` field is your only choice (unless you want to write code to loop through records and update accordingly, but I'd imagine this would be worse than the `join`). — sgeddes, Jun 19 '14 at 22:58
You can force a join with a CROSS JOIN (here: http://improvingsoftware.com/2011/03/15/how-to-join-on-memo-fields-in-microsoft-access/). Create NoteID in your original table, cross join the records as in the article, then update NoteID in the original table. You can then jettison the Note column in your original table and just join on NoteID in the future. — VBlades, Jun 19 '14 at 23:04
Following the article I have a query that has successfully joined the the two tables. How do I then update NoteID in the original table? — user3758291, Jun 19 '14 at 23:30
@VBlades 'SELECT Products.*, Notes.* FROM Products, Notes WHERE (Products.Notes=Notes.Notes);' — user3758291, Jun 20 '14 at 01:27
I posted it as an answer just so people can refer to it in the future. — VBlades, Jun 20 '14 at 01:46

sgeddes · Answer 1 · 2014-06-20T01:36:08.477

1

You'll need to use an UPDATE statement with a JOIN:

update o
set o.noteid = n.id
from original o 
  join notes n on o.notes = n.note;

SQL Fiddle Demo

If this is a Memo field and you can't use a JOIN in Access, then this trick should work as well:

update o
set o.noteid = n.id
from original o, notes n
where o.notes = n.note;

edited Jun 20 '14 at 01:36

answered Jun 19 '14 at 23:53

sgeddes

62,311
6
61
83

Access doesn't allow JOINs on Memo (long text) fields. Also, the update with INNER JOIN syntax is different that other db systems. So unfortunately, this won't work. – VBlades Jun 20 '14 at 01:00

score 1 · Accepted Answer · answered Jun 20 '14 at 01:45

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.

Replacing values with an ID when importing tables in MS Access

2 Answers2