Query within a query loop

Results 1 to 5 of 5

Thread: Query within a query loop

  1. #1
    Spooky Guest

    Default Query within a query loop

    Is it possible to nest an rs query while already in a &#039Do until RS.EOF&#039 loop?<BR><BR>ie:<BR><BR>RS.Open "select * from table", objConn<BR>Do until RS.EOF<BR> <BR>ID = RS("ID")<BR><BR> RS2.Open "select * from table where RelatedID=" & ID &"", objConn<BR> Do until RS2.EOF<BR> GetRS2 data<BR> RS2.movenext<BR> LOOP<BR> RS2.Close<BR>RS.movenext<BR>LOOP<BR><BR><BR><BR><B R>

  2. #2
    Spooky Guest

    Default More info?

    Just so you dont focus on the small problem, Ill expand a bit :)<BR><BR>What I am after is actually something similar to a threaded discusion I guess.<BR><BR>I&#039d like to list a unique record(1) from RS1, then check for any records(2) related to record 1 from RS2, check any records related to record 2 FROM RS3 etc then move back to RS1 for the next unique record<BR><BR>Make sense? Is there a better way to do this?<BR><BR>TIA

  3. #3
    Neil Popham Guest

    Default RE: More info?

    yes, there is no reason why you cannot do this, but whether you need to...<BR><BR>my SQL is, frankly, pants - BUT could you not do an SQL statement something like:<BR><BR>"SELECT Table1.*, Table2.*, Table3.*<BR>FROM (Table1 LEFT JOIN Table2 ON Table1.Table1ID = Table2.Table1ID) LEFT JOIN Table3 ON Table2.Table2ID = Table3.Table2ID<BR>ORDER BY Table1.Table1ID, Table2.Table2ID, Table3.Table3ID;"<BR><BR>this will give a nasty looking recordset - but it will at least bring in all records in one set. try pasting this into the SQL view in a new query in access (exc. quotes) and see what you get...<BR><BR>if you really ARE after a threaded discussion type scenario, then i achieved this a while back using the very simple, and perhaps restrictive, method of using ONE table with a text string to record the thread. eg: if the string was "abcad" then i could say that record was the fourth branch of the first branch of the third branch of the second branch of the first post. simple.<BR><BR>your method WILL WORK - but you are obviously restricted to 3 tiers (whereas my method is restricted to 26). i&#039m sure there are other methods which are &#039unrestricted&#039.

  4. #4
    Spooky Guest

    Default RE: More info?

    Thanks Neil, your abc sounds promising. Can you post some code snippets to allow me to get the idea on what you are doing with it?

  5. #5
    Neil Popham Guest

    Default RE: More info?

    k. i can give you the basics (and it is a pretty simple concept)<BR><BR>each record has three distinguishing fields which i have called &#039ID&#039, &#039ReID&#039 and &#039Path&#039<BR><BR>the ReID is set to the ID of the initial post. therefore all posts relating to an original post with the ID 5 will have a ReID of 5<BR><BR>the path, as previous, holds the branch structure.<BR><BR>eg: for a record: ID=101 ReID=87 Path=&#039bdaec&#039 - this means it is the 3rd (c) branch of the 5th (e) branch of the 1st (a) branch of the 4th (d) branch of the 2nd (b) branch of the initial post which has the ID 87<BR><BR>when listing the &#039tree&#039 bring the records in sorted by ReID then Path<BR><BR>when replying to a message you need to know the ReID and the path - the ReID will be the same, and for the path take the last character of the string and increase it by one (using Asc/Chr)<BR><BR>as you can see, the higher the letter the longer the thread. this means you can only have 26 replies to a post, but you could have an unlimited number of posts on a thread. i knda think if there are 26 replies to one post things are getting a bit silly anway - and they should just repost...<BR><BR>thats the basics really

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts