Aggregate and Roll-Up Query 1

[tomatdeepwater]

I have an MS-Access lookup table that contains various categories and subcategories. The fields are:

-------------------------------------------------
TABLE: tblCategory

FIELDS:
CategoryID (Prime Key) -- Autonumber, indexed
Category_Description -- text
fkParentID (Foreign Key pointing to
tblCategory.CategoryID) -- Long integer

I have another table that assigns costs to some subset of the "child" categories.

-------------------------------------------------
TABLE: tblCost

FIELDS:
CostID (Prime Key) -- Autonumber, indexed
fkCategoryID -- (Foreign Key pointing to
tblCategory.CategoryID)
Cost -- currency
-------------------------------------------------

I need to create a select query that shows:

FIELDS:
Category_Description
Cost

Here is the "catch". The query needs to show all the children and their parents simultaneously with child costs "rolled up" and assigned to their parents, recursively all the way to the "root node" with the parents and children sorted in hierarchical order.

When I was working with SQL-Server, I seem to remember a way of doing this without coding.

QUESTION: Is there a preferred way to do this in MS-Access?

 

[gol4]

best way would be use a function to navigate the recordset. You could attempt a self join if the levels dont run too deep.

 

[tomatdeepwater]

I will be using a self join to determine each record's parent from [red]tblCategory[/red] (a lookup table).

I have been "toying" with an algorithm to do the job in code. I haven't fully thought it out. I suspect that I can use some Access "stored" queries to speed up parts, otherwise, the process would go record by record (time consuming).

The algorithm for a function would go something like this, I think:

PROCESS

[blue]1) Since all cost records would be child records (my import process excludes any parent costs to save space), I would find the children at the lowest level.

Note: one field in [red]tblCategory[/red] that I forgot to mention was a field called "Level". This field is an integer that tells how "deep" the child record is down the chain. If a record is 8 levels down, it's level would be "7" (Root record's level = 0).

2) With the records from the lowest level (8 levels possible), I would select all those records and determine who their parent records are.

3) At the level, I would write to a temp table the PK of the child, its cost, it's "Level", its parent's PK, and a boolean value to indicate if further processing was required. For all children at the lowest level, the value would indicate no further processing required.

4) Then add the costs from the child records and create a record including the children's Parent PK, aggregated children costs, the Parent's Parent PK, The Parent's Level, and indicate that further processing IS required.

5) Then I would go to all children at the next higher "Level" and repeat the process until I got to the highest level.

6) Then I would recursively go through the temp table and process in a like manner any records whose field indicated "processing required", adding new Parent records until I get to only 1 Parent record (the highest level).[/blue]

It is a time consuming process using only VBA. Would anyone have any other suggestions OR be able to correct the logic of my 6 steps?

Thanks!

 

[ByteMyzer]

The following recursive VBA function should do what you require, with the above table structures as defined:

Public Function CostRoll()

Dim LLC As Integer
Dim qdfSrc As DAO.QueryDef
Dim rstSrc As DAO.Recordset
Dim qdfDst As DAO.QueryDef
Dim rstDst As DAO.Recordset

Echo True, "Resetting Level..."
DBEngine(0)(0).Execute "UPDATE tblCategory T1 SET T1.Level=0;"

Do
    Echo True, "Setting Level " & LLC & " ..."
    DBEngine(0)(0).Execute "UPDATE tblCategory T1 " _
        & "SET T1.Level=" & LLC + 1 & " " _
        & "WHERE T1.fkParentID IN " _
        & "(SELECT T2.CategoryID FROM tblCategory T2 " _
        & "WHERE T2.Level=" & LLC & ";);"
    LLC = LLC + 1
Loop Until DBEngine(0)(0).RecordsAffected = 0

Set qdfSrc = DBEngine(0)(0).CreateQueryDef("", _
    "SELECT SUM(T2.Cost) AS TOTAL " _
    & "FROM (tblCategory T1 INNER JOIN tblCost T2 " _
    & "ON T1.CategoryID=T2.fkCategoryID) " _
    & "INNER JOIN tblCategory T3 " _
    & "ON T1.fkParentID=T3.CategoryID " _
    & "WHERE T3.CategoryID=ID;")
Set qdfDst = DBEngine(0)(0).CreateQueryDef("", _
    "SELECT T1.CategoryID, T2.Cost " _
    & "FROM tblCategory T1 INNER JOIN tblCost T2 " _
    & "ON T1.CategoryID=T2.fkCategoryID " _
    & "WHERE T1.Level=LLC AND T1.CategoryID IN " _
    & "(SELECT T2.fkParentID FROM tblCategory T2;);")

Do While LLC > 0
    LLC = LLC - 1
    Echo True, "Rolling Level " & LLC & " ..."
    qdfDst.Parameters("LLC") = LLC
    Set rstDst = qdfDst.OpenRecordset
    Do While Not rstDst.EOF
        qdfSrc.Parameters("ID") = rstDst!CategoryID
        Set rstSrc = qdfSrc.OpenRecordset
        With rstDst
            .Edit
            !Cost = rstSrc!TOTAL
            .Update
            .MoveNext
        End With
        rstSrc.Close
        Set rstSrc = Nothing
    Loop
    rstDst.Close
    Set rstDst = Nothing
Loop

qdfDst.Close
Set qdfDst = Nothing
qdfSrc.Close
Set qdfSrc = Nothing

End Function

This function is designed to accomodate a flexible number of "levels". See if it works for you.

 

[ByteMyzer]

Incidentally, in case you're wondering about the time factor involved, I ran a test of this function on sample data consisting of 1,000 records and 13 levels, and the function enumerated and updated the data in 1.5 seconds.

 

[tomatdeepwater]

Looks good! Many thanks!