Remove the Q1.ac_id column from the SELECT clause:

SELECT Q1.link_id, Q2.ac_id AS secondary_ac_id
FROM TheUnionQuery AS Q1
LEFT JOIN TheUnionQuery AS Q2
ON Q1.link_id = Q2.link_id AND Q1.ac_id Q2.ac_id
WHERE Q1.ac_id = [Enter ac_id;];

Note that I've changed the join type to a left outer join. This will ensure
that a row is returned, even if there is no secondary linked account. If you
don't want this leave it as an inner join.

Ken Sheridan
Stafford, England

"TheScullster" wrote:


"KenSheridan wrote

Phil:

If you only want to drill down one level, then something like this should
do
it:

SELECT Q1.link_id, Q1.ac_id AS primary_ac_id,
Q2.ac_id AS secondary_ac_id
FROM TheUnionQuery AS Q1
INNER JOIN TheUnionQuery AS Q2
ON Q1.link_id = Q2.link_id AND Q1.ac_id Q2.ac_id
WHERE Q1.ac_id = [Enter ac_id;];

If you want to drill down recursively to an arbitrary number of levels
then
it becomes a lot more complex.


Thanks again for your time Ken.

The query now returns all linked accounts which is good.
I have managed to pass the searched-for-account from a combo box to the
query (basic stuff perhaps but new to me).

How do I suppress the display of the account that the user has input for the
search?
Should this be done via additional criteria at the query stage - if so how?

Thanks again

Phil


.