* I have over 1000 records in a table (Context_Tasks_DB), one field of which (termed Purpose) should really be a Lookup to another table (tblPurpose), but currently simply has text (only 30 unique text values are in that field and are therefore repeated...).
* I have created a table called tblPurpose with the desired, unique, lookup values (30 unique values).
* I have created a NEW field called PurposeNew that references the Lookup table.
I would like to "set" the value in PurposeNew from tblPurpose based on the text value in Purpose.
Currently my SQL query looks like this:
UPDATE Context_Tasks_DB INNER JOIN tblPurpose
ON Context_Tasks_DB.Purpose = tblPurpose.Purpose
SET Context_Tasks_DB.PurposeNew = tblPurpose.Purpose;
The above only creates a Query output of blank values and does not SET the Lookup field in PurposeNew at all.
The objective is to convert my "flat" field into a list/lookup and essentially "import" the flat file into a DB appropriate format. After this is done, I will delete that flat file field as this is essentially a one time only import.