puzzling on how to proceed

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I have a situation which is puzzling to me so I thought I would put it out
here to see if anyone else has had to do what I've been asked to do.

The situation is one in which the user selects a client (a parent) and all
of the client's subsidiaries appear along with any subsidiaries a sub might
have and so on down the line to the end. It would be a hierarchy look. Then
if the user selected any of the entities at any level, it would bring the
information onto the screen.

The first thing I thought of was to go with 10 tables (same info in each)
for going down 10 levels. But before I do something like this, I thought I
would put it out here first to see if any one has had to do something like
this. If anyone has, can you explain what you did? Do you know of any
samples/examples that I could review?

Any feedback on this topic will be appreciated.

Thanks.

.... John
 
Hi John,

In a way, this is similar to website content management where you have product categories to assign to a product. This is a single table structure that maps the relationship between our client or product or whatever and it's parent node.

Although they are a pain in the rear to set up - highly recommend that you bite the bullet and use a treeview control to display the relationship. The user can see the relationships, you can use the double-click event to bring up the appropriate form/data for the specific node selected. In spite of rumors to the contrary, treeview controls are stable/usable once you get your data assigned to the node structure correctly.

Here is a good link to get you started.

http://msdn2.microsoft.com/en-us/library/aa160699.aspx

What you need at a minimum are three columns (plus whatever other data you wish to carry with you, probably a descriptive name at least. )

UniqueID, ItemID, ParentNodeID

The UniqueID is the primary key of our 'category' table. The ItemID is a foreign key representing the client or product, the ParentNodeID is also a foreign key representing the the client or product.

If we have a null ParentNodeID we have a root element, otherwise we belong one level below our respective parent. This structure does not restrict a node to one position. The same node could be a child of Company A and a child of Company B (a joint venture, for instance) and it would carry its children at both locations.

The way you build the structure - find the root node(s) (null parentnodeID), start attaching the children, the grandchildren, the great-grandchildren, etc, until you hit an empty recordset, move to the next root node and repeat. I will show a Root/Child build - the rest of the levels build off of the previous level the same way.

Here is, more or less, how I might tackle getting my node data (air code, may not actually work as advertised) In the production version - I might reuse rsRoot & rsOne, after finishing the loop through root, assign rsRoot = RsOne This way rsRoot.ItemID now points to the first level Items and rsOne would now be picking up the grandchildren, keep doing that until rsOne comes up empty.
Just a first glance concept -

dim rsRoot as DAO.Recordset
dim rsOne as DAO.Recordset

Set rsRoot as CurrentDb.OpenRecordset("SELECT * FROM CategoryTable WHERE ParentNodeID Is Null;")
If rsRoot.RecordCount > 0 then
Do While Not rsRoot.EOF

set rsOne = CurrentDb.OPenRecordSet("SELECT * FROM CategoryTable WHERE ParentNodeID = " & rsRoot!ItemID & ";")
'assign the rugrats to the treeview control
If rsOne.RecordCount > 0 Then
Do While Not rsOne.EOF
'blah blah blah
rsOne.MoveNext
Loop
End If
rsRoot.MoveNext
Loop
Else
msgbox "No ROOT RECORDS FOUND?????", vbCritical, "UH OH - DooDoo Alert!"
Exit Sub
End If

Hope this helps,
Gordon
 
John, it may be possible to place all the entities in the one table, with a
self-join indicate who the parent is.

For example, you have fields like this:
ClientID AutoNumber primary key
Company Text name of this company
ParentID Number ClientID of the parent company.

This cops with any level of nesting. You have a main form bound to this
table, with a combo for selecting the ParentID, and a subform showing the
related companies. Set up the DblClick event of the ParentID combo on the
main form so if the user dbl-clicks it, you move to the parent record.

The subform's LinkMasterFields is ClientID, and LinkChildFields is ParentID
(not ClientID - you will have to set this manually.) Again, in the subform
you can set up the double-click of the ClientID so that it saves any edits
in the subform, and then moves the main form to that record.

In that way, the user can drill down (or drill up) the hierarchy very
simply.

This is such a simple and elegant solution! The problem comes if the user
wants you to create a report showing all the levels of nesting in a
flat-file view. It's doable, but you have some issues to solve (e.g. the
possibility of infinite recursion, where a company is wrongly recorded as
being its own grandparent.)

Still, it's a beautifully flexible structure. There's a similar example
here:
People in households and companies - modeling human relationships
at:
http://allenbrowne.com/AppHuman.html
Includes a sample database to pull apart and see how the structure works.
 
Gordon and Allen, thank you both for the feedback information. I will digest
them both and go from there.
Thank you.
.... John
 
Back
Top