I have 2 tables in Microsoft Access.
Table 1:
ID Val1 Val2 Val3
1 a b c
2 e f g
3 h i j
Table 2:
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.
Best Answer
I think that in this case you better do this in a form. The problem with your qeury is that it lacks where clauses. For id 1 for example, the selects to union should be: SELECT Valx FROM Table1 WHERE ID=1
On a form you can do this for example by setting the row source in the on current event depending on the value of ID.
And yes, the structure of your database could need an update. Wich can be done with one simple query.