I have 2 tables in Microsoft Access.
ID Val1 Val2 Val3 1 a b c 2 e f g 3 h i j
ID Val Chosen 1 a 2 f 3 j
In the Val Chosen field, I want Access to create a listbox in Val Chosen that contains the values in Val1, Val2, Val3 for the respective id. For example, when the user clicks the Val Chosen listbox for the record with ID 1, it should contain "a", "b", and "c". The Val Chosen listbox for the record with ID 2 should contain "e", "f", and "g".
In design mode for "Table 2", the display control is set to list box. The following query is used as a row source and incorrectly returns all possible values.
SELECT Val1 FROM Table1 UNION SELECT Val2 FROM Table1 UNION SELECT Val3 FROM Table1
The actual database has many records so I would prefer to preserve the structure of the database if possible.