Unanswered: Replacing field values that match another table
First of all, please excuse my inferior knowledge...
I am trying to clean up a database that has not exactly been created nicely An example would be that for certain fields the value is one from a list -- i would have used, and will now want to convert it to, the situation that there is to be a separate table with the possible values, which has a relation to the main table (as opposed to typing the same value every time).
IOW, a query SELECT DISTINCT Field1, COUNT(*) FROM Table1 GROUP BY Field1 would generate something like Value1 - 1234; Value2 - 2345; Value3 - 3456...
Now, to get these into a new table I have used SELECT DISTINCT Field1 INTO tblField1 FROM Table1. I added an ID field (autonumber) to this table and added a NewField1 (numeric) field to Table1. This NewField1 should be filled with tblField1.ID iff tblField1.Field1 matches Table1.Field1
(I really hope I am making myself clear - English is not my native language...)
So in short, I've created the new, separate table, but now i need to fill the main table with the ID from that new table. Does anyone have any pointers for me?