Another Query Question
in [Getting Started]
|
From: TheScullster on 29 Mar 2010 07:10 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
From: KenSheridan via AccessMonster.com on 29 Mar 2010 18:13 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/Forums.aspx/access-gettingstarted/201003/1
From: TheScullster on 30 Mar 2010 04:00 "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
From: Ken Sheridan on 30 Mar 2010 11:55 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 > > > . >
From: TheScullster on 31 Mar 2010 06:22 "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
|
Pages: 1 Prev: Create a Search Box on a form Next: joe jonas.comfanstie@gmail |