View Single Post
  #2  
Old March 29th, 2010, 11:13 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Another Query Question

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 wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201003/1