Product tree

[Roberto Nicolas via AccessMonster.com]

Good evening!
I have built a database that stores data about a given product in form of a
tree. I didnt have any idea of how to make it, so I created a single
'LEVEL' column to deal with the tree structure , and it works like this:
Every two digits correspond to one level of the tree so that I may have up
to 100 ( 0-99) items in each level
Examples:
01 First item of first level
0403 Third item of second level, sublevel of item 04
07 Seventh item of first level
0701 First item of secondlevel, sublevel of item 07

Luckily I only have up to 5 levels in my product , and never more than 99
items per level in each subitem.
I have been working this way, because its extremely easy to use a tree like
this in forms or reports, as I only need to sort data in ascending order
for this 'LEVEL' column.
I imagine there are other ways where I dont have to 'build' the tree every
time I need to print a report or view the tree in a from. I hope any one
knows it , but if there is no other way with simple programming, could
somebody tell me a reference where to learn about data trees in access?

Thanks

 

[Adrian Jansen]

Good evening!
I have built a database that stores data about a given product in form of a
tree. I didnt have any idea of how to make it, so I created a single
'LEVEL' column to deal with the tree structure , and it works like this:
Every two digits correspond to one level of the tree so that I may have up
to 100 ( 0-99) items in each level
Examples:
01 First item of first level
0403 Third item of second level, sublevel of item 04
07 Seventh item of first level
0701 First item of secondlevel, sublevel of item 07

Luckily I only have up to 5 levels in my product , and never more than 99
items per level in each subitem.
I have been working this way, because its extremely easy to use a tree like
this in forms or reports, as I only need to sort data in ascending order
for this 'LEVEL' column.
I imagine there are other ways where I dont have to 'build' the tree every
time I need to print a report or view the tree in a from. I hope any one
knows it , but if there is no other way with simple programming, could
somebody tell me a reference where to learn about data trees in access?

Thanks

Joe Celko's "SQL for Smarties" has a good discussion on trees and
database structures to handle them.


--
Regards,

Adrian Jansen adrianjansen at internode dot on dot net
Design Engineer J & K Micro Systems
Microcomputer solutions for industrial control
Note reply address is invalid, convert address above to machine form.

 

[Guest]

Am not sure that tree structure is particularly relevant to Access as a
design point - in terms of speed, indexing or anything like that...... I
think that the benefit of your design is one of management and this is more a
part numbering issue. Which is very important. Your organization can
visually see linkages between products by their part number sequence. This
can help alot to avoid human error - let's say in fulfilling a stocking order
- - and is vastly superior to randomly generated part numbers......