Please help with simple query (only 2 tables!)

[Falkor07]

I'm using Access 2000. I have 2 tables in my query: tblPlace and tblConnection. Using a form and the Connection table I can link 2 places together from the Place table and present it in a query; for example:

SELECT tblPlace.Place_ID, tblPlace.Name, tblPlace_1.Place_ID, tblPlace_1.Name
FROM tblPlace AS tblPlace_1 INNER JOIN (tblPlace INNER JOIN tblConnection ON tblPlace.Place_ID = tblConnection.Place1) ON tblPlace_1.Place_ID = tblConnection.Place2
WHERE (((tblPlace.Place_ID)=13) AND ((tblPlace.Type)="Street") AND ((tblPlace_1.Type)="Street")) OR (((tblPlace_1.Place_ID)=13) AND ((tblPlace.Type)="Street") AND ((tblPlace_1.Type)="Street"));

Here I want to find out which other streets link to street 13 (Perry Hill). Running the query shows 5 different connections:

I need to somehow combine both sets of IDs and street names (except Perry Hill), so that I end up with a filtered list in alphabetical order:
17 Bell Green
38 Castlands Road
12 Catford Hill
14 Elm Lane
16 Perry Rise
Is there any way this can be done so that I end up with only the above data? Any help would be much appreciated!

 

[pbaldy]

This would probably work:

SELECT Place1
FROM tblConnection
WHERE Place2 = 13
UNION ALL
SELECT Place2
FROM tblConnection
WHERE Place1 = 13

You could add the other table to get the names and an ORDER BY to alphabetize, but that's the shell of it.

 

[Falkor07]

Nice one, Paul!

Many thanks for your quick reply! I got it to work based on your shell--amazing!! I never knew about that UNION ALL function... thanks again.

SELECT  tblConnection.Place1, tblPlace.Type, tblPlace.Name
FROM tblPlace INNER JOIN tblConnection ON tblPlace.Place_ID = tblConnection.Place1
WHERE (((tblConnection.Place2)=13) AND ((tblPlace.Type)="Street"))
UNION ALL 
SELECT tblConnection.Place2, tblPlace.Type, tblPlace.Name
FROM tblPlace INNER JOIN tblConnection ON tblPlace.Place_ID = tblConnection.Place2
WHERE (((tblConnection.Place1)=13) AND ((tblPlace.Type)="Street"))
ORDER BY Name