Setting a value in a Lookup field based on a field

[roguetexan]

• 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_D B INNER JOIN tblPurpose
ON Context_Tasks_D B.Purpose = tblPurpose.Purp ose
SET Context_Tasks_D B.PurposeNew = tblPurpose.Purp ose;

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.

Suggestions?

[ADezii]

1. Create a Field in tblPurpose named PurposeID (Auto Number/Primary Key). It will auto-populate once you close the Table.
2. Make sure the newly created Field in Context_Tasks_D B, PurposeNew, has its Data Type to NUMBER/LONG.
3. Run the following Update Query:
   

   UPDATE Context_Tasks_DB INNER JOIN tblPurpose ON Context_Tasks_DB.[Termed Purpose]=tblPurpose.Purpose SET Context_Tasks_DB.PurposeNew = [PurposeID];

4. The PurposeNew Field will now contain Numbers relating to the PurposeID Field in tblPurpose.
5. You can now create a 1 to MANY Relationship between the Tables, namely:
   

   tblPurpose.[PurposeID]{1} ==> Context_Tasks_DB.PurposeNew{MANY}

6. The [Termed Purpose] Field in Context_Tasks_D B can now be Deleted.

[roguetexan]

that worked! Thanks

[ADezii]

You are quite welcome.