Lookup Table

[Ghagg]

I have a Main table with over 100k existing records, and one field
(ClassName) has many repeat entrees, so i want to creat a lookup table for
that repeat field. The lookup table should have a primary key field named
ClassID and a second field named ClassName.

In the Main Table i added a field called ClassID with number as a field
type. When i try to create a lookup table, i expect to see the ClassID
field filled with numbers corresponding to the name in the Lookup table.
I'm doing someting wrong, because i do not get these numbers in the Main
Table.

Can you Help, maybe an example to go to?


Thanks

GH

 

[Klatuu]

The usual way to do this is with a Combo Box on the form.
The Combo box should have as it's row source, the lookup table. Make the
numeric primary key field the first column, and the text the second column.
For your combo, set these properties:
Column Count = 2
Bound Column = 1
Column Widths 0"; 2" (The 0 will make the number column hidden. Make the
2 whatever length you need to display the text.
Control Source = The name of the field in the main table that carried the
number.

Now, when you select a row from the combo, the user will see only the text,
but the numeric column will be stored in the table.

With that many records in your table, it would be a lot of manual updating.
Since it is a new field, it will have a Null value. If you have any way of
determining which record should have which Class, an update query could work;
otherwise, someone has a lot of manual entry to do.