Tree Structure (1 Viewer)

[BazLondon]

Greetings

I'm sure this subject must have been addressed in the past but I would be grateful for some thoughts on it.

On the attached .pdf file diagram I have shown :-

1. a typical "top-down" tree structure (similar to a Bill of Materials but without the frills of including the number of a particular part that's required in the makeup of the item in the next level up)

2. How the structure is logically modelled

3. How the data would by physically held in a normalised table.


The problem I've encountered in ACCESS is how to set up the one-to-many relationship from the Parent to the Child. This can be done but if a try to force referential integrity (so that the "1" and the "∞" symbols are displayed at the ends of the relationship line) I am informed that this is not valid because "No unique index found for the referenced field in the primary table"

I am sure I've managed to do this in the distant past (not in ACCESS though) and I can't understand why, what is perfectly logical, cannot seem to be achieved or accepted in ACCESS.

Any thoughts would be gratefully received.

 

[dcb]

Change the column you are referencing to "indexed no duplicates" in your primary table

 

[Thinh]

http://www.access-programmers.co.uk/forums/showthread.php?t=170577

Check out my tree view sample database. It is currently setup as menu system but i believe it will satified your need.

 

[BazLondon]

Thanks dcb and Thinh for your responses

dcb. Doesn't work. By definition, the same Parent can occur many times so it can't be unique (ie can't be set to "no duplicates"). It's the Child that's unique.

Thinh. Your structure is very similar to mine but your database hasn't been set up with any relationships - in particular the one I need but which ACCESS will not allow ie a one to many from Parent to Child.

I've trawled various sites and the conclusion I've come to is that it is not possible in ACCESS. For referential integrity to be set, the "one" end of a one-to-many relationship must be unique which in the case of my tree structure it is not

 

[datAdrenaline]

>> I've trawled various sites and the conclusion I've come to is that it is not possible in ACCESS <<

I whole heartedly disagree ... but in a agreeable manner of course

>> For referential integrity to be set, the "one" end of a one-to-many relationship must be unique <<

That is the case in ANY rdbms.

Check out this sample:
http://www.access-programmers.co.uk/forums/showthread.php?p=724780#post724780

I have used the code in it in several applications, and have built many TreeViews, even ones that allow the child node to appear under more than one parent node. If you can not utilize the sample I provided, please post a sample database with the table structures of the data you wish to utilize a tree view for and I will be glad to help you get to what you want to do.

 

[datAdrenaline]

Oh ... one more comment

>> For referential integrity to be set, the "one" end of a one-to-many relationship must be unique which in the case of my tree structure it is not <<

But on your PDF, you indicate the Child_ID is the Primary Key ... so, I am not sure I understand what you mean.

The data you have shown in your PDF is completely capabale of participating in a RI enforced relationship, even unto itself with Child_ID being the primary key of the relationship and Parent_ID being the foreign key (yep ... you can create relationship with RI using the SAME table as the "one" side and the "many" side)

 

[gemma-the-husky]

why is this not possible? (or maybe what exactly is not possible?)

since any node has one parent (other than the top/root) - then surely all you need is to store the parent record id, in any record. all relational integrity does is makes sure that any child MUST have an exisitng parent which it does other than the root (so if necessary make the root point to itself to achieve this)

although you may not be able to write a simple query to retrieve records, you could certainly write a recursive tree walker, to walk the tree, and extract the ids of the nodes you want - into a temporary table - then use this to retrieve the data.
what information are you trying to extract that is proving difficult

in concept, similar to storing the whole bill in an xml file

 

[dcb]

Please find, attached, an exact replica of your document:
Note:
(No Duplicate, Indexing)
Null on Parent_ID in Child_ID 1
RI

 

[Thinh]

Let me see if this is what you are asking for.

from the main table you create a query that only hold parent item(make item) only.

by join the table and query you can establish what you are looking for