UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Rolling Up By Hiearchy, Access 2013    
 
   
dflak
post Dec 20 2017, 01:30 PM
Post#1


Utter Access VIP
Posts: 6,124
Joined: 22-June 04
From: North Carolina


I’m working on a training database for a military unit.

Military units have a hierarchical structure: Platoons belong to Companies, Companies belong to Battalions, Battalions belong to Brigades or maybe directly to a Division and Brigades belong to a Division. You can even have one or two special-type platoons or companies working directly for a division. In this database, I seeded a "grand ancestor" unit called Home. Then you can create a unit and in the parent field enter HOME. The new unit now becomes eligible to become a parent unit to other units and so on.

In one table (tbl_lk_trn) I define the training course names, a type (not important at this moment), a term due (year, month, or day), and a number.

So Year & 1 means the training is due annually. Month & 6 would be every 6 months and Day and 30 would be every 30 days.

I have a personnel table and the key filed is the service number. Part of the personnel record is the unit to which the person belongs. There is another table called tbl_units that holds the relationships between the units. Some units belong to a parent unit and the parent unit may belong to a grandparent unit, and so on. In practicality, 3 or 4 levels.

A series of training records are linked to the personnel table via the service number.

I have no problem extracting the training records for a specified service number. I don’t even have a problem with a summary report for units that are at the bottom of the task organization. That is, the unit does not have any subordinate (child) units.

Task organization-wise, The 16 MI and 16 MR and 21 Bty RA all belong to HQ 16X. HQ 16X, in turn, belongs to the unit called Home (the seeded “grand ancestor” of all units).

So now we look at the Personnel Roster and we see that Beetle Bailey, Donald Duck and Michael Mouse all belong to subordinate units of HQ 16X.

So when I run a report for HQ 16X, I want to see the training records for these three people as well as Private Snuffy Smith who is assigned directly to the HQ. If needs be, I can assign Snuffy to a "pseudo" unit such as HQ 16 Staff.

Conversely, if I run a report for HOME, I need to see everybody’s record.

I would like some pointers on how to do this roll-up. What I would need, given a parent unit name, is Training Name, subordinate unit name (if any) and date last accomplished.

The commander is going to want to see a summary by immediate subordinate unit.

So HOME will see HQ16X, 12 Regt RA and all units immediately reporting to HOME. HOME will *not* see 16 MI, 16 MR, 21 Bty RA and 126 Sig since they have parent units between them and HOME.


Attached File(s)
Attached File  Unit_Training.zip ( 327.44K )Number of downloads: 6
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
ranman256
post Dec 20 2017, 02:35 PM
Post#2



Posts: 856
Joined: 25-April 14



just like IRL, I have a field called PARENTID

so if Platoon, 123 has members each person will have a GrpAssigned field
each tUnit has a ParentID so you can roll all the way to the top until the top has no parentID.

tUNIT table
GrpID, GrpType, GrpName, ParentID
123, Platoon, Gray Platoon, 555
555, Company, Easy Company, 101
101, Battalion, Red Battalion, 95
etc

tMEMBER table
MemID, Last,First, GrpAssignedID (aka Parent)
543, Smith,Bob, 123
Go to the top of the page
 
dflak
post Dec 20 2017, 03:01 PM
Post#3


Utter Access VIP
Posts: 6,124
Joined: 22-June 04
From: North Carolina


That is essentially what I have. The person belongs to a unit and that unit belongs to another unit all the way to the top seeded unit.

The question I am asking is how do I write a query so that when I specify a unit, it drills down to the bottom-most unit. I don't think it will be plain SQL. My SQL skills are in the lower end of the intermediate category so I don't know for sure. I suspect it would take a recursive subroutine - one that calls itself continuously until it runs out of data.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jleach
post Dec 20 2017, 03:52 PM
Post#4


UtterAccess Editor
Posts: 9,896
Joined: 7-December 09
From: Staten Island, NY, USA


Hi Dan - I don't really think you can do this with SQL alone. Try a recursive VBA proc that tracks the "indent" level (or whatever you want to call it) and plops all the results into a temp table. Then once you have a flat set of results with info for determining the indent level, you can usually query them up for display as required.

Not sure if you get into recursion much. Here's some aircode: one "entry" proc to fire the process off, one more that gets called for each set of "parents":

CODE
Public Function GetHierarchy(RootID)

Dim rs As DAO.Recordset
  'open writable rs
  Set rs = CurrentDb.OpenRecordset("MyTempTable", dbOpenDynaset)

   Indent = 0;

  RecruseChildren(RootID, rs, Indent)

End Function

Function RecurseChildren(CurrentID, rs, ByVal Indent)

  ' open another readonly RS to grab this "level's" records
  Dim readRS As DAO.Recordset
  Set readRS = CurrentDb.OpenRecordset("SELECT blah FROM MainTable WHERE ID = " & CurrentID, dbOpenSnapshot)
  While not readrs.eof
     with rs
       .addnew
         .fields("somefield") = readrs("thisfield")
         .fields("indent") = indent
         ..etc
       .update
     endwith

     'now re-run this particular record...
     If Not ISNULL(readRs("ChildID")) Then RecurseChildren(readRs("ChildID"), rs, Indent + 1)

    readrs.MoveNext
  Wend

  rs.close
  set rs = nothing

end function


If that's not enough you may have to get a little fancy with recursive calling orders to get the results structured in the correct order, but usually just having an indent level and the "full tree" stuffed into a flat table is enough to get the job done.

hth

--------------------
Go to the top of the page
 
dflak
post Dec 20 2017, 05:13 PM
Post#5


Utter Access VIP
Posts: 6,124
Joined: 22-June 04
From: North Carolina


Yes, that is along the lines of what I am thinking. I have written a recursion program once in C and again a VB version of it to convert one number from one base to another. That was a long, long time ago in a galaxy far, far away.

The trick is to keep calling itself until some condition, like running out of data, is reached.

I was concerned that a temp table would have to be created, but I am glad you have an example of the code. I'm close to guru status with Excel VBA, I'm still cutting my teeth with Access VBA. At least the language is the same even if addressing things is different.

Thanks for the leg up. I think I can pick your code apart and figure out the logic. I'll keep you posted.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jleach
post Dec 20 2017, 07:38 PM
Post#6


UtterAccess Editor
Posts: 9,896
Joined: 7-December 09
From: Staten Island, NY, USA


>> until some condition, like running out of data, is reached <<

Yea, I forgot in my aircode: because it's impossible to put the constraints directly on the table level to prevent circular dependencies in this type of setup, I'll pick a ridiculously high number to max out and and keep a count of each recursion nest (like indent...). If you think you'll never exceed 20 levels, set your max count to 2000 and run a check and an throw an error if the level reaches that high. That gives you something to back out of if there is the off case (with the alternative being a stack overflow and complete crash of your app).

Cheers,

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2017, 07:00 AM
Post#7


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


You could consider incorporating a tally table to provide the self-limiting functionality Jack describes. If you use a tally table to determine the depth of recursion, you can either let it go through the end of the records in the table, or apply a criteria that stops it after the recursions reach that point.


--------------------
Go to the top of the page
 
dflak
post Dec 21 2017, 08:55 AM
Post#8


Utter Access VIP
Posts: 6,124
Joined: 22-June 04
From: North Carolina


Hmm, looks like I got a lot of reading to do. I'm glad I asked the question. I don't have to rebuild this wheel from scratch.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jleach
post Dec 21 2017, 09:02 AM
Post#9


UtterAccess Editor
Posts: 9,896
Joined: 7-December 09
From: Staten Island, NY, USA


Seems like a lot of work for an otherwise very simple stop-check (I'd probably just go with a MAX_COUNT constant in VBA myself - not sure that using a tally table would offer any particular advantage, but OTOH I've never considered it so I'll look into it a bit further myself).

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2017, 09:17 AM
Post#10


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Ben and I used it with a Data Macro in the last book. As I recall it was an way to get around the issue of limited options in DMs.

I throw it out as something to consider.

--------------------
Go to the top of the page
 
dflak
post Dec 22 2017, 01:03 PM
Post#11


Utter Access VIP
Posts: 6,124
Joined: 22-June 04
From: North Carolina


Eureka! (It's too cold to go running down the street naked, so I'll just sit at my desk and type).

I have the backbone of what I need to do my rollups. I modified jleach's code a bit. I took the rs and made it a global so I don't have to pass it as a parameter. Also I did not see the need for the index. Running into a null record was sufficient to stop the recursion. However, it was good to take a leap of a giant's shoulders, I would not have figured out the Accessisms without help. Thanks all for the boost in the right direction.

The only thing I need to do is empty out the temp table before starting the process. I'm sure I can find that code on line smile.gif. Once I have good units with these fields populated in the temp table, then everything else can use "regular" SQL - by using "who is my parent" (parent ID), I can summarize at the immediate subordinate unit level.

CODE
Dim FindChild As Boolean
Dim rs_temp As DAO.Recordset

Public Sub DoRollup(RootID As Long)

' Open a writable recordset
Set rs_temp = CurrentDb.OpenRecordset("tbl_TmpOrg", dbOpenDynaset)

' Starting at the top
GetChild (RootID)

End Sub

Public Sub GetChild(RootID As Long)

Dim rs_unit As DAO.Recordset

Set rs_unit = CurrentDb.OpenRecordset("Select * from tbl_units where unit_parent_id = " & RootID)

While Not rs_unit.EOF
    With rs_temp
        .AddNew
            .Fields("tmp_unit_id") = rs_unit("unit_id")
            .Fields("tmp_unit_name") = rs_unit("unit_name")
        .Update
    End With
    
    If Not IsNull(rs_unit("unit_id")) Then
        GetChild (rs_unit("unit_id"))
    End If
    
    rs_unit.MoveNext
Wend

rs_unit.Close
Set rs_unit = Nothing

End Sub

Sub Rollup_Test()
DoRollup 1
End Sub

Attached File(s)
Attached File  Unit_Training.zip ( 335.74K )Number of downloads: 3
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jleach
post Dec 22 2017, 01:31 PM
Post#12


UtterAccess Editor
Posts: 9,896
Joined: 7-December 09
From: Staten Island, NY, USA


Sweet. thumbup.gif

--------------------
Go to the top of the page
 
pdanes
post Dec 25 2017, 02:40 PM
Post#13



Posts: 81
Joined: 19-June 10



Clearing out your temp table is super simple.

CODE
CurrentDB.Execute "Delete * From [tmpTableName]"


If your temp table has no spaces or other weird characters in the name, you can even omit the square brackets.
Go to the top of the page
 
dflak
post Dec 26 2017, 08:49 AM
Post#14


Utter Access VIP
Posts: 6,124
Joined: 22-June 04
From: North Carolina


Thanks. I hadn't looked it up yet. But it makes sense.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 12:37 PM