creating a table from hierarchical structure

[dj982020]

Hi!

I have created a hierarchical structure from a flat table.
Ex.
TABLE

RegionID RegionName Population ParentRegionID
1 USA 276059000
2 Texas 20044141 1
3 Illinois 12128370 1
4 Florida 15111244 1
5 Austin 1146050 2
6 Dallas 3280310 2
7 Miami 2175634 4
8 Chicago 8008507 3
9 Rockford 358640 3

HIERARCHICAL STRUCTURE
(I coded this in VBA by using a recursive function. It's not stored as a structure, it's just printing this.)
USA - 276059000
Florida - 15111244
Miami - 2175634
Illinois - 12128370
Chicago - 8008507
Rockford - 358640
Texas - 20044141
Austin - 1146050
Dallas - 3280310

I would like to create a table in Access (programmatically) that stores this structure in a way that adds each element to a table along with the sum of its children's populations. (i.e. Florida would contain Miami's population and Illinois would contain Chicago's population + Rockford's population)
Ex.

Region Population
USA 47283755
Florida 2175634
Miami 2175634
Illinois 8367147
Chicago 8008507
Rockford 358640
Texas 4426360
Austin 1146050
Dallas 3280310

Any hints??
TIA,
DJ

 

[Zion7]

Loop thru the State Table,

Do Until rec.EOF
rec!txtPopulation = DSum("txtPopulation", "tblCities", "ChildRegionID = " & rec!ParentRegionID)
rec.Update
rec.MoveNext
Loop

code is paraphrased.

...assuming your familiar with some of these methods?

Good Luck!

 

[DougStevens]

Can you modify your VBA code so that passing any RegionID and it will recursively calculate its total population for all of its children and grand children? Sounds like you've got the recursive side done.

Assuming you have some code that looks like this psuedocode:

Function GetTotalPopulation(GivenRegionID as Long) As Double
Dim TotalPopulation AS Double
Dim MyRec AS RecordSet
Dim MyDB AS Database


TotalPopulation = 0

IF DCount("RegionName", "TableName", "ParentRegionID =" & GivenRegionID) = 0 THEN
TotalPopulation = DLookup("Population", "TableName", "RegionID =" & GivenRegionID)

ELSE

MyDB = CurrentDB()
MyRec = MyDB.OpenRecordset ("SELECT T1.RegionID FROM [TableName] AS T1 WHERE T1.ParentRegionID = " & GivenRegionID)

MyRec.MoveLast
MyRec.MoveFirst

While Not MyRec.EOF
TotalPopulation = TotalPopulation + GetTotalPopulation(MyRec!RecID)
Wend
END IF

GetTotalPopulation = TotalPopulation

End Function


That code can be used as a function in a query.
Column1: RegionName
Column2: GetTotalPopulation ([RegionID])

Granted it will run slow.