Connecting Parent/Child/sibling items

[caseyn]

I have a very large database of designs that have a number of "similar to" fields:



-design converted from (design # xxx)
-design converted to (design # xxx)

-old copyright
-copyright

-similar to 1 (design # xxx)
-similar to 2 (design # xxx)
-similar to 3 (design # xxx)

-client converted from (client code XX)
-client converted to (client code XX)

I am trying to figure out how to set these up in some kind of parent/child/sibling tree so that when I pull one item up, all the ones that are related to it are easy to find. The database is much larger and messier than the ones I am used to, so I am not sure how to go about managing this.

I'm also redesigning the back end of the database right now, so I can change just about anything that needs to be changed.

thank you all

[ranman256]

youd have the master form (on the master table) for record say: 2018 Honda Accord.
then the subtable would hold :
-similar to 1 (design # xxx)
-similar to 2 (design # xxx)
-similar to 3 (design # xxx)

this would be a subform linked to the parent record on ID.

then you can view all changes made to the parent record in the subform.

[CJ_London]

The basic principle is to add a parentFK field to your table and for the parent populate it with the tablePK. Populate this field in the child records with the parent tablePK.

Then to find all records that are related filter on this field.

However if a parent can also have a parent and you want to map the relationship then you need a slightly different approach. You need to use a recursive query to identify the children, then the grandchildren etc. Access sql does not support a recursive action, but this can be achieved instead in vba.

There are plenty of threads on how to create and maintain a tree. Which one is right for you depends on your requirements. From your post it would seem sufficient to know that 'all these designs are related' in which case the first option is sufficient (perhaps with some process around naming convention), but if you truly need a tree to show which design begat which design, you will definitely need a tree. Google 'binary tree' and similar for ways to do it