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

[poobalanm]

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:
UnitID UnitCode UnitName UnitParent StaffTotal 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 (53) for all unit, sub-units under it. Admin Section only need to show values for all those units that is under it (2 units) plus itself (5+5+2 = 12). 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.

 

[KKilfoil]

I would make a vba function...
This not going to be correct syntax...

Create a public function (let call it GetStaff)



The code will resemble

public function GetStaff(ThisUnit as Long) as Long

{dim statements...}

{open a recordset of all records that have ThisUnit as parent}

{for each member of recordset, call GetStaff() using the child's UnitID as argument, summing the return values. be sure to allow for an empty recordset (i.e. base unit) } THIS IS THE RECURSIVE PART!

{add the sum of the return values to the TotalStaff value of ThisUnit}

Set GetStaff equal to the value of the previous step

{recordset cleanup}


End function

Don't forget to add appropriate error-handling...




Now you can use this function anywhere you need it. Repeat the exercise for vacancies.


HTH

 

[Nouba]

Here is a sample for what KKilfoil was saying.

 

[KKilfoil]

Nouba:

Your method of creating a generic function for any fieldname is quite slick.


Minor quibble:

In your GetRecursive() function, you added the following note:
' This is what you need if you use -1 for the root instead of Null
'strSQL = strSQL & "WHERE UnitParent = -1"

However, I think you meant to say
'strSQL = strSQL & "WHERE UnitParent <> -1"

 

[Nouba]

Thanks KKilfoil for your statement. My comment was wrong.