How to write query that can recursively check values in a column

[poobalanm]

This is my table OrgUnit:

UnitID (Autonumber starting from 0)
UnitCode (Text)
UnitName (Text)
UnitParent (Number) -- refers to unitID, except the root item which parent is set -1
StaffTotal (Number)
Vacancies (Number)


Sample Data:
Parent Unit Unit Name Total Subunits Total Staff Total Vacancies
-------------- ------------- ----------------- ------------ -------------------

0 4 State Dept -1 8 1
1 4.1 Admin Section 0 5 2
2 4.1.1 Finance Unit 1 5 1
3 4.1.2 HR Unit 1 2 0
4 4.2 Training Section 0 3 0
5 4.3 R & D Section 0 4 1
6 4.3.1 Planning Unit 5 9 2
7 4.3.1.1 Project Team 6 5 0
8 4.3.1.2 EOPU Team 6 5 1
9 4.3.2 Private Sector Unit 5 3 0
10 4.4 Public Relations Section 0 4 0

Expected Output for a Report:

Parent Unit Unit Name Total Subunits Total Staff Total Vacancies
-------------- ------------- ----------------- ------------ -------------------
4 State Dept 4 53 8
4.1 Admin Section 2 12 3
4.1.1 Finance Unit 0 1 1

etc...

my problem is how to write a recursive type of query that can check and calculate total staff and vacancies for each unit that can include its subunit too. for example, the root (State Dept) need to show total staff for all unit, sub-units under it. Admin Section only need to show values for all those units that is under it (2 units). While the units that have no groups under them only show their own staff total and vacancies....


any idea is appreciated. I'm using Access 2000 as the tool.

 

[simongallop]

Have done it by using code.

First I did this with numbering starting at 1 so do not know if starting at 0 will be a problem. Test it and see!

2/: Create a table called tmpResult. It has 4 fields:

UnitCode (Text)
UnitName (Text)
StaffTotal (Number)
Vacancies (Number)

Place primary key on the first 2 fields

3/ Create a query called PrepData. Copy the code below and paste into the SQL view of the query.

SELECT OrgUnit.UnitID, OrgUnit.UnitCode, OrgUnit.UnitName, OrgUnit.UnitParent, OrgUnit.StaffTotal, OrgUnit.Vacancies, Left([UnitCode],1) AS Lvl1, DLookUp("UnitName","OrgUnit","UnitCode='" & [Lvl1] & "'") AS Lvl1Name, Left([UnitCode],3) AS Lvl2, DLookUp("UnitName","OrgUnit","UnitCode='" & [Lvl2] & "'") AS Lvl2Name, Left([UnitCode],5) AS Lvl3, DLookUp("UnitName","OrgUnit","UnitCode='" & [Lvl3] & "'") AS Lvl3Name, Left([UnitCode],7) AS Lvl4, DLookUp("UnitName","OrgUnit","UnitCode='" & [Lvl4] & "'") AS Lvl4Name
FROM OrgUnit;

4/ Create a module and paste the following code:

Option Compare Database
Option Explicit
Dim qdfTmpQ As QueryDef
Dim strSQL As String
Dim x As Integer

Sub MakeResults()
DoCmd.SetWarnings False

CurrentDb.CreateQueryDef("tmpQuery").SQL = "DELETE * FROM tmpResult"

DoCmd.OpenQuery "tmpQuery"

Set qdfTmpQ = CurrentDb.QueryDefs("tmpQuery")

For x = 1 To 4

strSQL = "INSERT INTO tmpResult ( UnitCode, UnitName, StaffTotal, Vacancies ) " & _
"SELECT PrepData.Lvl" & x & ", PrepData.[Lvl" & x & "Name], " & _
"Sum(PrepData.StaffTotal) AS SumOfStaffTotal, Sum(PrepData.Vacancies) AS " & _
"SumOfVacancies FROM PrepData GROUP BY PrepData.Lvl" & x & ", PrepData.lvl" & x & "Name;"
qdfTmpQ.SQL = strSQL

DoCmd.OpenQuery "tmpQuery"
Next x

qdfTmpQ.Close

DoCmd.DeleteObject acQuery, "tmpQuery"

DoCmd.SetWarnings True

End Sub


Now it ought to create the data that you want

HTH