Data Manipulation (Trees?)

[Pete]

I have a set of records (over 5,000). Each record as a key tag and
has multipel pointer to "Children". The Number of "Children" can be
over 100.

Example:
rcd #1 ---> Rcd#5, Rcd#100, Rcd #2

 

[Pete]

I have a set of records (over 5,000). Each record as a key tag and
has multipel pointer to "Children". The Number of "Children" can be
over 100.

Example:
rcd #1 ---> Rcd#5, Rcd#100, Rcd #2

oooh for fast/fat finges: Lets continue:
Rcd#2 -----> Rcd#50,Rcd#30
Rcd#3 ------> Rcd#1, Rcd#32

What is the best way to record and provide a report (Graphical
Preferred) that show the structure of the data and identifies the
overall realtionship of the data.

I have thought about Tree Structures where one file contains a series
of record contain backward and forward pointers. However, I am not
sure if this the best way or should I be using somthing other than
Access. If have created a table that can show that contains data
nested to 10 levels. Howeve, It is possible for for this to grow
expedentially, especially if the record pointers get into a loopin
condition.

Any suggestions would be extremly helpfull, and if ther are pointers
to somthing chat could be used, plese let me know.

Thanks

 

[John W. Vinson/MVP]

It sounds like a case for a "Many to Many Self Join" table structure. This
uses two tables: let's call them People and Relations (you can of course
come up with more appropriate names!)

People would have a unique identifier - what you're calling "rcd", though
you certainly should not be thinking of it as a "record number", together
with any other fields of information about the entity. For consistancy I'll
use rcd as the fieldname (note: DON'T use # in fieldnames, it's a date
delimiter).

The Relations table would have two fields of the same datatype as rcd (Long
Integer if rcd is an Autonumber): ParentRcd and ChildRcd. You would add
Relations to the Relationship window once, and add People to it *twice* -
once joined to ParentRcd and the second to ChildRcd. Relations would have a
two-field Primary Key consisting of these two fields; you will probably also
want other fields containing information about the relationship itself
(though if all you care about is the existance of the relationship, perhaps
not!)

This structure will allow each Rcd to be related to any desired number of
other rcd's. You may well need validation rules to (say) prevent a rcd from
being related directly or indirectly to itself (the "I'm My Own Grandpa"
problem); this may require some VBA code in the data entry process, as it's
difficult to create an index or validation rule to search through an
unlimited depth of links.

You may want to consider using a Treeview control to display the data in
these tables.

 

[Pete]

It sounds like a case for a "Many to Many Self Join" table structure. This
uses two tables: let's call them People and Relations (you can of course
come up with more appropriate names!)

People would have a unique identifier - what you're calling "rcd", though
you certainly should not be thinking of it as a "record number", together
with any other fields of information about the entity. For consistancy I'll
use rcd as the fieldname (note: DON'T use # in fieldnames, it's a date
delimiter).

The Relations table would have two fields of the same datatype as rcd (Long
Integer if rcd is an Autonumber): ParentRcd and ChildRcd. You would add
Relations to the Relationship window once, and add People to it *twice* -
once joined to ParentRcd and the second to ChildRcd. Relations would have a
two-field Primary Key consisting of these two fields; you will probably also
want other fields containing information about the relationship itself
(though if all you care about is the existance of the relationship, perhaps
not!)

This structure will allow each Rcd to be related to any desired number of
other rcd's. You may well need validation rules to (say) prevent a rcd from
being related directly or indirectly to itself (the "I'm My Own Grandpa"
problem); this may require some VBA code in the data entry process, as it's
difficult to create an index or validation rule to search through an
unlimited depth of links.

You may want to consider using a Treeview control to display the data in
these tables.

Thanks for the suggestion. I hooked in the Treeview and it WILL work
once I trap some key errors. Two Quick questions:
1. How can I trap a mouse click on a branch of the tree (should be
simple)?
2. Do you know of an example on the use of ListView with TreeView?

 

[John W. Vinson]

Thanks for the suggestion. I hooked in the Treeview and it WILL work
once I trap some key errors. Two Quick questions:
1. How can I trap a mouse click on a branch of the tree (should be
simple)?
2. Do you know of an example on the use of ListView with TreeView?

Better post a new thread - I'm afraid (and a mite embarrassed to admit) I've
never used a treeview or a listview control!

John W. Vinson [MVP]