CODEWITHSUNDEEP
ms-accesscombobox
anotherfred
anotherfred

Reputation: 1438

MS Access combobox column to lookup a third table (multi-dimensional lookup?)

I have an Access table and form testResults with a field that refers to a test in the tests table by its foreign key (id). I have a combobox for inputting the test since the users obviously can't see the id: instead they see the name. This lookup is simply a two column combobox with the first column zero width.

My problem is that there are multiple tests with the same name but different teachers. Teacher is itself a foreign key from the tests table to a third table (teachers).

How can the combox on testResults show a two column list with the test name and the teacher's name? The best I can do so far is the test name and the teacher's id.

If anyone can help, thanks a lot!

Upvotes: 1

Views: 109

Answers (1)

Andre
Andre

Reputation: 27644

The RowSource of your combobox should be a query that JOINs both tables, then you can use any column you want.

Something like

SELECT tests.id, tests.test_name, teachers.teacher_name
FROM tests INNER JOIN teachers ON tests.teacher_id = teachers.id

and of course the combobox needs 3 columns.

Upvotes: 1

Related Questions