Another Query Question

[TheScullster]

Hi all

With the help of Ken Sheridan, I feel in touching distance of a solution to
my current application issues.

I have a union query which returns results on links made between accounts.
Each entry represents a defined link between 2 accounts - there is a remarks
column (not shown) associated to the link describing the reason behind it.
There are 2 fields in the union query: link-id and ac-id.

So the output of the union query looks like:

link-id ac-id
1 1647
2 1639
3 1681
4 1677
5 1681
1 1677
2 97
3 1661
4 1618
5 1682

A further query is run on the union query with a user input ac-id - this
lists link-id 1 & 4 for ac-id value 1677.

I now need to requery the union query to find the "other side" and the
linked accounts i.e. for 1 & 4 the linked accounts associated with 1677 are
1647 and 1618 respectively.

This is where I am stuck!
How do I re-query the union query with results from yet another query?

TIA


Phil

 

[KenSheridan via AccessMonster.com]

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.

Ken Sheridan
Stafford, England

 

[TheScullster]

"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

 

[Ken Sheridan]

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

"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


.

 

[TheScullster]

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

Thanks again Ken

I had managed to build a query or series of queries that returned both sides
of the search successfully.
The bit I was missing was the where clause.

With your help I feel I have made real progress.

Phil