If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Another Query Question
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Another Query Question
"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 |
#4
|
|||
|
|||
Another Query Question
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 . |
#5
|
|||
|
|||
Another Query Question
"Ken Sheridan" wrote 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 |
Thread Tools | |
Display Modes | |
|
|