Roll-Up and Aggregate query

[Guest]

I have an MS-Access lookup table that contains various categories and

subcategories. The fields are:

-------------------------------------------------
tblCategory:

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.

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

I need to create a select query that shows:

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?

 

[David S via AccessMonster.com]

Hi Tom,

This is the well known parent child multiple regression problem that I
unfortunately don't know much about. There's lots of doco on it in this forum,
though - try checking out this thread:

http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/20659/Parent-Child-Query