Building An Organization Tree From Records?

  • Thread starter Thread starter Henry Smith
  • Start date Start date
H

Henry Smith

Using MS Access 2000. My objective is to build an organizational tree from
the data contained in 5 fields of each record in a record set. The record
set may be as many as 10000 records. Receive each month from the corporate
financial system a set of records which, when extracted and processed, make
up a monthly profit and loss (P&L) statement for a particular level of the
organization. There are four levels of the organization that have P&L
responsibility (in descending order J2, G1, F1 and D1). Within the D1 are
cost centers such as 3445, 3446, etc. Each record has these 5 fields to
identify the cost center where the work was accomplished, hence labor costs,
that roll up to the D1 level, then the F1, then the G1 and finally the J2
level.
Sample of records:
Field names
BG OpsGrp DivGrp Div CstCntr
BG01J2 BG01G1 BG01F1 BG01D1 3445
BG01J2 BG01G1 BG01F1 BG01D1 3446
BG01J2 BG01G1 BG01F1 BG01D1 3447
BG01J2 BG01G1 BG01F1 BG01D1 3447
BG01J2 BG01G1 BG01F1 BG01D2 3456
BG01J2 BG01G1 BG01F1 BG01D2 3456
BG01J2 BG01G1 BG01F2 BG01D3 3458
BG01J2 BG01G1 BG01F2 BG01D3 3459

My objective is to read the records each month and create an updated
organizational tree from the data presented by each record set.

Any help with this puzzle will be greatly appreciated.
Cheers,
Henry
 
Henry,

Not quite sure what you are trying to achieve. Are you trying to (a) produce
a tree-like report, or (b) convert your input table to a hierarchical
structure of related tables?

For (a), make a query on the table that reads the two characters to the
right of each of the first four fields (e.g. Level1: Right([BG],2), Level2:
Right([OpsGrp],2) etc.) and the fifth field as is.
Then use this to make a report on, and use grouping on the first four
fields. This will give you the tree-like structure.

For (b), you will need a series of make-table queries, selecting two
consecutive fields each time (1st and 2nd, 2nd and 3rd etc.) with Totals >
Group By on both. Then you will have to go to Relationships screen and add
the relationships.

HTH,
Nikos
 
Nikos,
Thank you for the quick reply. Your ideas are very helpful.
My objective is to accomplish both, but primarily (b) convert my source data
into a hierarchical structure of related tables. I would then use these
tables as the source for a "TreeView" control and for reports. My intention
is to create the hierarchical related tables and then continue to use them
until the operator needs to update the tables because of an organizational
change or he/she suspects there is incorrect data in the tables. It would
also be great to create the relationships with code at the time the tables
are created.
The "Treeview Control" with its related data list window will display the
roll ups of the accounts to each organization level. For example, if the
user clicks on BG01F1 the list will display the roll ups of the accounts
from the lower related organization units that belong to this organization
(allows the user to drill down into the organization structure).

Looking forward to more ideas.
Cheers,
Henry
Nikos Yannacopoulos said:
Henry,

Not quite sure what you are trying to achieve. Are you trying to (a) produce
a tree-like report, or (b) convert your input table to a hierarchical
structure of related tables?

For (a), make a query on the table that reads the two characters to the
right of each of the first four fields (e.g. Level1: Right([BG],2), Level2:
Right([OpsGrp],2) etc.) and the fifth field as is.
Then use this to make a report on, and use grouping on the first four
fields. This will give you the tree-like structure.

For (b), you will need a series of make-table queries, selecting two
consecutive fields each time (1st and 2nd, 2nd and 3rd etc.) with Totals >
Group By on both. Then you will have to go to Relationships screen and add
the relationships.

HTH,
Nikos

Henry Smith said:
Using MS Access 2000. My objective is to build an organizational tree from
the data contained in 5 fields of each record in a record set. The record
set may be as many as 10000 records. Receive each month from the corporate
financial system a set of records which, when extracted and processed, make
up a monthly profit and loss (P&L) statement for a particular level of the
organization. There are four levels of the organization that have P&L
responsibility (in descending order J2, G1, F1 and D1). Within the D1 are
cost centers such as 3445, 3446, etc. Each record has these 5 fields to
identify the cost center where the work was accomplished, hence labor costs,
that roll up to the D1 level, then the F1, then the G1 and finally the J2
level.
Sample of records:
Field names
BG OpsGrp DivGrp Div CstCntr
BG01J2 BG01G1 BG01F1 BG01D1 3445
BG01J2 BG01G1 BG01F1 BG01D1 3446
BG01J2 BG01G1 BG01F1 BG01D1 3447
BG01J2 BG01G1 BG01F1 BG01D1 3447
BG01J2 BG01G1 BG01F1 BG01D2 3456
BG01J2 BG01G1 BG01F1 BG01D2 3456
BG01J2 BG01G1 BG01F2 BG01D3 3458
BG01J2 BG01G1 BG01F2 BG01D3 3459

My objective is to read the records each month and create an updated
organizational tree from the data presented by each record set.

Any help with this puzzle will be greatly appreciated.
Cheers,
Henry
Click to expand...
Click to expand...
 
Back
Top