Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recursive or Hierarchical query (A2003)

    I am designing the structure for some test plans. Each test has an initial condition to describe the state at the start of a test. I am looking at making the initial conditions hierarchical and so need to do some clever queries.
    In the design as it stands, there exists a system default initial condition giving a definition for every "input" (ie, lots of details). Each initial condition used for a test has a at least one parent, starting from the system default (or indeed any other root parent we might create). Each initial condition is a limited set of changes from it's parent. An initial condition may have any number of parents.

    The tables look like this:
    tblTestSequences (OtherStuff, InitialConditionID)
    tblInitialConditions (Name, ID, ParentID)
    tblInitialConditionDetails (ID, ItemName, ItemValue)

    Say TestSequence 12 has Initial Condition 7 which has parents (recursively) of 5,4, 2 and 0. I want two different query results:
    1. All details with ID=7, plus recursively (details from the next parent which aren't already present)
    2. As above but without the root parent, being the parent which has no parent itself.

    Since the result is a progressive sum of changes, I want to only the "latest" record for each ItemName. That is, if ItemName has already been returned, exclude any instance in the parent's details.
    I appreciate this is at least a two part problem, first to get the hierarchical list of condition names (IDs), then get the details, including the exclusion of unwanted details.

    I have found Charlotte's examples for using SHAPE, and just a couple of other posts. but I'm not sure if this the right way to go. I have also tried to look through Access help without much luck, and have an SQL guide which refers to functions Access seems not to implement, such as level. I'd like to do this within a query if possible (or a couple of them), but VBA is no worries either.

    Will it be best to break the solution into parts, should I remove the root parents to another details table with identical structure?
    If anybody could let me know some pointers on where to look and what to search for, or suggested approaches, I'd appreciate that. The alternative is to eliminate the hierachical nature of the design, which may be the most feasible solution but I'd like to have a go at something more elegant.

    Marty

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recursive or Hierarchical query (A2003)

    I don't know of a way to do this using queries/SQL, but perhaps someone else will have a suggestion.

    I have attached a small demo that uses two VBA functions. The code is:

    Public Function GetParent(ByVal InitialConditionID As Long) As Variant
    GetParent = DLookup("ParentID", "tblInitialConditions", _
    "InitialConditionID=" & InitialConditionID)
    End Function

    Public Function GetItemValue( _
    ByVal InitialConditionID As Long, _
    ByVal ItemID As Long, _
    Optional ByVal NoRoot As Boolean) As Variant
    Dim retVal As Variant
    Dim ParentID As Variant
    Dim GrandParentID As Variant
    retVal = DLookup("ItemValue", "tblInitialConditionDetails", _
    "InitialConditionID=" & InitialConditionID & " AND ItemID=" & ItemID)
    If IsNull(retVal) Then
    ParentID = GetParent(InitialConditionID)
    If IsNull(ParentID) Then
    GetItemValue = Null
    Else
    If NoRoot Then
    GrandParentID = GetParent(ParentID)
    If IsNull(GrandParentID) Then
    GetItemValue = Null
    Exit Function
    End If
    End If
    ' Recursive call
    GetItemValue = GetItemValue(ParentID, ItemID)
    End If
    Else
    GetItemValue = retVal
    End If
    End Function

    The GetParent function returns the parent of an InitialConditionID (or null, if it is a root element). The GetItemValue function drills down to the "latest" ItemValue for the specified InitialConditionID and ItemID (I used ItemIDs instead of ItemNames); this function has an optional third argument NoRoot; if you supply True, the root is not included in the drill-down.

    The two queries in the database demonstrate the use of the GetItemValue function. You'll be prompted to enter an InitialConditionID value; this can of course be replaced with a reference to a control on a form.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recursive or Hierarchical query (A2003)

    That does the job very nicely!!! Thankyou for the effort you've gone to.

    Just a tiny thing not related to recursiveness, is there a way to exclude those ItemNames whose ItemValue is null? An easy way is to use a wrapper query:
    SELECT * FROM qryGetItemValues WHERE Not IsNull(ItemValue);
    I don't quite understand why a similar construction does not work in the query itself - I am interested to undertand this aspect a little better.

    Thanks again for your help,
    Marty

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recursive or Hierarchical query (A2003)

    Just an observation..
    I found an interesting difference in execution time. Development to date has been on a network drive, with no observable performance impact on report generation.
    With about 90 rows in tblItems (we'd expect 3 -4 times this many), only 3 InitialConditions (with incomplete details), the report took 21 seconds to generate over the network. When I moved the database to the local drive the report was fast enough not to be concerned. For reference, the PC is P4 3GHz 1GB XPsp1.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recursive or Hierarchical query (A2003)

    Simply enter

    Is Not Null

    in the Criteria row under ItemValue.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recursive or Hierarchical query (A2003)

    The function performs a lot of lookups in the table, and this will increase rapidly with the depth of the structure (the number of ancestors of a record). Perhaps it could be programmed a bit more efficiently, but I do not see a ready alternative for iteratively looking up ancestors until the needed information has been found. Because of all searching in the table, it is to be expected that performance over a network will be slower.

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recursive or Hierarchical query (A2003)

    I was being too clever(?) and used the alias rather than the function call. I tried
    WHERE Not IsNull(ItemValue);
    Following your post and converting to SQL view gives
    WHERE (((GetItemValue([Enter InitialConditionID],[ItemID])) Is Not Null));

    And there I was wondering about relationships and such...

    Thanks,
    Marty

Posting Permissions

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