Results 1 to 4 of 4
  1. #1
    nigelbloomy is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    51

    Is a recursive query possible?

    I have a table with WBS information and I need to retrieve the full WBS. The problem is that the information is broken down to the lowest level of the WBS. So if my WBS is 1.2.3.4 then 1 has it's own line item and WBS_ID with no Parent_WBS. 2 has it's own line item with its own WBS_ID and a Parent_WBS that is the same as the WBS_ID for 1. That goes on down to 4 which has its WBS_ID and a Parent_WBS pointing back to 3. The difference here is that when you search for the WBS_ID for 4 in the Parent_WBS column, there are no results.



    How can I possibly combine all of these separate line items back together so I can show the user what the full WBS is in the query results?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    myself, i would do it like:
    have a 'report' table to catch the results.
    a 'level' counter starts at 1
    query the top , the parents, they would have blank in the parent field. write to the result ([level] 1)
    count = 2
    query all recs that have parents in tResults [level] = 1
    if rst.recordcount = 0 then
    end
    else
    post to 'results'
    endif

    increment counter and continue until recordcount = 0

    view tResults.

  3. #3
    nigelbloomy is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    51
    I ended up creating a recursive VBA function kind of like what Ranman suggested. In my case I know the WBS_ID for the lowest level. That's what I plug into the formula. The top level would eventually stop with a blank Parent_WBS, but I decided to stop it sooner. The WBS_ID in the code below is the highest level anyone would want to use. I used this as a formula in my query and it seems to work well. The only thing I don't like is when the top level is reached, each level back down the chain has to assign the string "wbs" to the function over and over again until it gets back to the original call. I tried to figure out a way for the function to return a string that could be added to the previous function call, but I couldn't make it work. I don't think it is a big difference on the system resources. It would just be a little cleaner in my mind.

    Anyway, here is the code that worked for me.

    Code:
    Function FullWBS(ID As Long, Optional wbs As String) As String
    Dim rs As DAO.Recordset
    Dim stQuery As String
    If ID = Empty Then
        Exit Function
    Else: End If
    stQuery = "SELECT WBS.WBS_ID, WBS.WBS_SHORT_NAME, WBS.PARENT_WBS_ID " & _
            "FROM WBS " & _
            "WHERE (((WBS.WBS_ID)= " & ID & "));"
    Set rs = CurrentDb.OpenRecordset(stQuery)
    If rs!PARENT_WBS_ID = 839358 Then
        Exit Function
    Else
        ID = rs!PARENT_WBS_ID
    End If
    If wbs = Empty Then 'The first time through this should be empty
        wbs = rs!WBS_SHORT_NAME
    Else
        wbs = rs!WBS_SHORT_NAME & "." & wbs
    End If
    
    Call FullWBS(ID, wbs)
    FullWBS = wbs
    End Function

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't forget to close the recordset, then destroy it:
    Code:
       rs.close
       set rs = nothing
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help with recursive design
    By frankos72 in forum Database Design
    Replies: 12
    Last Post: 10-09-2013, 08:04 AM
  2. Recursive data
    By jvrsoto in forum Access
    Replies: 4
    Last Post: 12-20-2011, 10:56 AM
  3. Recursive Report!
    By Rawb in forum Reports
    Replies: 7
    Last Post: 01-26-2011, 04:00 PM
  4. Recursive Tables!
    By Rawb in forum Database Design
    Replies: 1
    Last Post: 01-26-2011, 02:46 PM
  5. Recursive Lookup and display?
    By madyson in forum Access
    Replies: 3
    Last Post: 12-15-2009, 09:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums