recursively retrieve records from 2nd table based on records from

I have two separate queries, that show the wanted results.
I tried hard but i cannot "merge" them into query/subquery.
I am not sure if subqueries are the right way to solve this.


I keep having errors or results that aren't the ones expected.
Below is the query , with simplified table/field names.

SELECT tbl1.ID FROM tbl1
(SELECT ID, field2 FROM tbl2 WHERE field2 = tbl1.ID )
ORDER BY tbl1.ID DESC;

Using words the query should

Display table1.ID ASCENDING
for each
table1.ID lookup table2.ID WHERE table2.field2 = table1.ID

What I am looking for to be displayed:

tbl1.ID tbl2.ID tbl2.field2
1 99
99 1
123 1
567 1
2 766
2 456
766 2
456 2
999 2

What I am looking for to be displayed:

tbl1.ID tbl2.ID tbl2.field2
1 99
99 1
123 1
567 1
2 766
2 456
766 2
456 2
999 2


I have two separate queries, that show the wanted results.
I tried hard but i cannot "merge" them into query/subquery.
I am not sure if subqueries are the right way to solve this.


I keep having errors or results that aren't the ones expected.
Below is the query , with simplified table/field names.

SELECT tbl1.ID FROM tbl1
(SELECT ID, field2 FROM tbl2 WHERE field2 = tbl1.ID )
ORDER BY tbl1.ID DESC;

Using words the query should

Display table1.ID ASCENDING
for each
table1.ID lookup table2.ID WHERE table2.field2 = table1.ID

What I am looking for to be displayed:

tbl1.ID tbl2.ID tbl2.field2
1 99
99 1
123 1
567 1
2 766
2 456
766 2
456 2
999 2

Not sure what you are trying to do. It might help if you posted the starting
data that is giving you the results you want.

I assume that a query like the following is NOT what you want, although it
seems to answer your verbal description of the desired results.

SELECT table1.ID, Table2.ID, Table2.Field2
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.Field2

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

elsamiro2351 wrote:
I have two separate queries, that show the wanted results.
I tried hard but i cannot "merge" them into query/subquery.
I am not sure if subqueries are the right way to solve this.


I keep having errors or results that aren't the ones expected.
Below is the query , with simplified table/field names.

SELECT tbl1.ID FROM tbl1
(SELECT ID, field2 FROM tbl2 WHERE field2 = tbl1.ID )
ORDER BY tbl1.ID DESC;

Using words the query should

Display table1.ID ASCENDING
for each
table1.ID lookup table2.ID WHERE table2.field2 = table1.ID

What I am looking for to be displayed:

tbl1.ID tbl2.ID tbl2.field2
1 99
99 1
123 1
567 1
2 766
2 456
766 2
456 2
999 2