SQL query to return lookup of a lookup field

[StevenBee]

OK, there is a much easier way to do this, I'm sure, but I'm rather new to this and can't figure out how to perform the following without all sorts of obfuscated loops and temporary variables.

Given the tables:

TRAVELLER
--------
Traveller_ID as Autonumber
Traveller_Name as text

TRIPS
-----
Trip_ID as Autonumber
Traveller_ID as long
Place_ID as long

PLACES
------
Place_ID as Autonumber
Place_Name as text

And the obvious relationships:

[TRIPS]![Traveller_ID] <---> [TRAVELLER]![Traveller_ID]
[TRIPS]![Place_ID] <---> [PLACES]![Place_ID]

So now, I want to create 2 combobox fields on a form. When you select a traveller's name from the first combobox, the second combobox is populated with a list of all the Place Names he's been to.

What sort of SQL statement would return this result?

 

[John Big Booty]

I think you will find that a cascading Combo Box set will do just what you are looking for.

 

[StevenBee]

Thanks for the reply, John. But I do not believe that this addresses my issue.

I want the first combobox on my form to have a list of travellers' names ([TRAVELLER]![Traveller_name])

I want the second combobox on my form to have the list of place names ([PLACES]![Place_Name])

The problem I'm having is that these are only related through a third table ([TRIPS])... so when I try to make a SELECT query to populate the second combobox with places, I can only seem to get it to bring up the place ID's from the TRIPS table... for e.g.,

second_combobox.RowSource = SELECT [TRIPS]![Place_ID] FROM TRIPS WHERE [TRIPS]![Traveller_ID] = first_combobox

This will populate my second field with the ID # of the places, but I want to populate it with the names of the places from the [PLACES] table.

I suppose I could do what I'm trying to do by putting the above result into some sort of array and then populating second_combobox.RowSource with a new SQL query of the PLACES table using that temporary array... but I'm thinking there's a much easier way that I'm just not catching on to.

 

[John Big Booty]

If you can retrieve the PlaceID in your query, why can't you simply include the table with the related name in your query and return the name

 

[StevenBee]

As usual, my only limitations are sheer ignorance ;-)

So yeah, what I'm trying to do is to return the name of the place from the PLACES table (and by extension through the PLACES table the Place_ID), by matching the one piece of information I do have - the name of the traveller from the first combo-box (and, by extension through the TRAVELLERS table, the traveller_ID)... but I can only get the place name from the traveller name through the TRIPS table, and I can't seem to work out an elegant way to word my SQL statement to work with all three tables simultaneously, if that is indeed the way to best do it.

 

[John Big Booty]

Rather than trying to write the SQL directly, why not use the visual query builder? You might find it easier.