Linking & visualising relationships between records within a table (2 Viewers)

[Rich99]

I'm not sure if Access is the right tool for this, it's not a tool I'm particularly familiar with - if any can tell me either how to do this in access, or whether some other program might be better, it would be appreciated!

We have compounds, we then take those compounds and modify them. The modified compound gets a new name. We want to view all the compounds in 1 list along with various other pieces of info (which shelf they are on etc), but importantly we want to keep a record of the relationship between them. Over time, there will be quite extensive relationships between different samples, and we want to be able to click on something & see all the parents and/or all the children.

If I set up 2 columns - column A is compound name, column B is the parent compound. I've set column B up as a lookup to the first column. So far, so good - but then how would I visualise the relationships between the compounds? Am I even setting the table up in the right way?

Thanks!

 

[plog]

I don't know what you mean by visualize. I mean its an abstraction and either you get the abstraction or you don't. There is a Relationship Tool in Access in which you can bring tables and define relationships, maybe that will help.

As for the right structure, the answer is maybe. Can a compound have 2 different compounds as its parents? If so, then you need another table to sort all that out.

 

[Rich99]

Hi, any compound can only have 1 parent, but there may be a chain of relationships. By visualise, I mean that I want to click on compound 1 & see what all it's descendents are (and their descendents, and theirs etc), or I want to click on a compound and see the chain of parents all the way back to the starting compound.

The visualise tool only seems to show me the relationship between tables (column b is linked to column a), not all the relationships between individual records.

 

[jdraw]

Perhaps if you gave us some sample data and described how they relate, your situation would be clearer.
If I abstract what you have said to very general terms, it sounds like you have some sort of raw materials. If you combine or process certain raw materials you get some sort of "assembly" or "semi-finished component". It might even be possible to bring 2 or more components together to make a finished product. I think this is one of those situation where a picture and example are worth more that a 1000 words.

eg: components (resistors, capacitors,chip A, chip B)--->circuitboard
(semi finished component)circuit board 1, circuit board 6 ---> amplifier
(amplifier, tuner,.....)---> digital radio

 

[Rich99]

OK - I've attached an example database of various compounds. I've also attached a diagram of what I'd like to end up with - a diagram showing the relationship between the various molecules. In this diagram I've shown all the descendents of compound A, but in reality I'd like to be able to choose any compound, and show either the parents or the children.

 

[plog]

That image helps. I see your issue now.

How deep can your relationship be? For example, CompoundG is 5 levels deep. Would that be the most? Or do you expect more? If you know that the deepest you would ever go is 7 levels or so, you could actually write a query to always look that deep. Otherwise you might need to roll the relationship into some sort of recursive function.

As for the output you want, that's kind of difficult to achieve as well. I think a better (more suited to Access) report would display more vertically than horizontally. Something like this:

CompoundA
--CompoundC
----CompoundE
------CompoundF
--------CompoundG
------CompoundH
--CompoundB
----CompoundD

With all that said, I've never really done something like this (done something 3 levels deep looking into itself, but that's it). The best advice I can offer, is turn on the Google and start searching for similar software/Access databases. Family Tree software, Multi-Level Marketing Software, Corporate Structure software, etc.

 

[Rich99]

The relationship can be many layers deep (effectively it needs to be capable of hundreds deep). A horizontal report would be fine, as long as it's understandable. Funnily enough, someone else suggested a family tree software! I'll go googling now.....

 

[jdraw]

Family Tree software works with the same general concept --hierarchy.

You may get some ideas from this article.

Get a good description of what info you want to capture, how you want to access and display/report it. Having a clear objective will help. Getting your structure designed and tested to meet your requirements is critical.

I recommend that you research self-joins, and look for some examples to see if there is similarity to your issue.

This article may offer some options and the rationale for each.

Good luck.

 

[Rich99]

I've had a look at family tree software - while it seems attractive, I can't find any that is flexible enough to let me re-purpose it.

I've had a look at the articles that JDraw published, but they look hellish complicated for a noob like me - this system would need to be used by people with no database understanding, so expecting them to fill in a second table defining relationships would be asking a lot!

 

[jdraw]

Re-purposing software is not necessarily a good strategy. It's often better to get some grasp of the concepts of a software/product. Table structures, queries to display stored data, then apply the concepts to your situation.
In previous post I asked/suggested

Get a good description of what info you want to capture, how you want to access and display/report it. Having a clear objective will help. Getting your structure designed and tested to meet your requirements is critical.

Do you have such a description? Sufficient to start a design? With some detail?
Can you post it?

 

[Rich99]

Hi,

I posted an example above, in post 5.

Thanks!

 

[jdraw]

That's it? No details?

Using your table 1

with this query

SELECT IIf(IsNull([parent compound]),space(6) &[compound name],space(6*[parent compound]) & DLookUp("[compound name]","table1"," id =" & [parent compound] ) & "  -  " & [compound name]) AS CompoundHierarchy
FROM Table1;

this is the display

CompoundHierarchy
      Compound A
      Compound A  -  Compound B
      Compound A  -  Compound C
            Compound B  -  Compound D
                  Compound C  -  Compound E
                              Compound E  -  Compound F
                                    Compound F  -  Compound G
                              Compound E  -  Compound H

 

[Rich99]

Apologies, I thought my descriptions and example were fairly clear. I'm not sure what other details you want? It's a simple database, losing multiple compounds. Most compound are derivatives of a previous one, and we want to track those relationships (viewing parent/grandparent etc, or children/grandkids/great grandkids etc).

 

[jdraw]

Did you see my post (I revised it to show sample output)?

 

[gemma-the-husky]

out of interest, how can a compound have only one parent? Can you give an example of such a compound?

if that assertion is true, then all you need is to store the parent of any given compound. The parent then either has a parent of it's own, or no parent - in which case it is top of the tree. So you can derive the whole tree of parents (any number) with simple recursion. Presentation of a tree is tricky, though, as it grows exponentially, or at least binomially.

I would have thought that normally a compound would have multiple parents - like a "people tree", where any person has both a mother and a father. Similar principle, but it's a matter of taste in that case whether you have 2 parents in a record, or a spearte table for the parents.

I suppose with a person table, you always have a mother and father parent. Maybe with a compound you can have a positive and negative ion parent. (although you say there is only a single parent ...)

 

[jdraw]

Dave,

I agree re single parent compounds. I anticipate the user OP has created an example using compounds which may or may not be real.

I was looking at a method to display the hierarchy (any number of levels). I added records to his table and the query will produce as follows:

CompoundHierarchy
      Compound A
      Compound A  -  Compound B
      Compound A  -  Compound C
            Compound B  -  Compound D
                  Compound C  -  Compound E
                              Compound E  -  Compound F
                                    Compound F  -  Compound G
                              Compound E  -  Compound H
                              Compound E  -  Compound J
                              Compound E  -  Compound K
            Compound B  -  Compound X
                                          Compound G  -  Compound 5D
                                          Compound G  -  Compound 5e
                                                Compound H  -  Compound M
                                                      Compound J  -  Compound MA
                                                            Compound K  -  Compound Mb
                                                            Compound K  -  Compound MB
                                                                  Compound X  -  Compound MC
                                                                        Compound 5D  -  Compound MD

There aren't many details so it was more of a can it be done exercise.

 

[Rich99]

Dave,

I agree re single parent compounds. I anticipate the user OP has created an example using compounds which may or may not be real.

As a general rule, each compound will only have 1 parent. Technically, some could have 2, but practically most will only have 1. E.g. we take a compound, put it through a chemical modification, and produce a new product with just 1 parent.

Your query is close, although the formatting is a little weird. Most of that relates to it being text based - for example if I have multiple children of compound A, compound A gets an entry in the output for each child, as opposed to 1 entry for compound A, with each child represented as arising from that single original.

 

[jdraw]

You mean like this

CompoundHierarchy
         Compound A
         Compound A  -  Compound B
         Compound A  -  Compound C
                  Compound B  -  Compound X
                  Compound B  -  Compound TA
                  Compound B  -  Compound D
                           Compound C  -  Compound E
                                    Compound D  -  Compound TB
                                             Compound E  -  Compound F
                                             Compound E  -  Compound H
                                             Compound E  -  Compound J
                                             Compound E  -  Compound K
                                                      Compound F  -  Compound G
                                                               Compound G  -  Compound 5e
                                                               Compound G  -  Compound 5D
                                                                        Compound H  -  Compound M
                                                                                 Compound J  -  Compound MA
                                                                                          Compound K  -  Compound Mb
                                                                                          Compound K  -  Compound MB
                                                                                                   Compound X  -  Compound MC
                                                                                                   Compound X  -  Compound T
                                                                                                            Compound 5D  -  Compound MD
                                                                                                                     Compound 5e  -  Compound Q

That is exactly why I was seeking more details. You know more detail than you think. Readers have no knowledge of you nor your environment and that is why we ask for more info.

Anyway, good luck with your project.

 

[Rich99]

More like that, yes. I still think that a purely text-based solution is always going to struggle when the relationships become complex, but this looks like it might be close. The other requirement would be the ability to only include parents/children of a given compound. I.e. we currently have ~3,000 compounds. Clearly we can't refer to a single diagram of all 3,000 in order to understand relationships. What we actually want to do is say 'what's the parent chain of compound x' or 'what are all the children (including grandchildren etc) of compound Y.

 

[jdraw]

I used your database and created the query. I did add some records to ensure it was not limited to 3 or 4 levels.
Here is my version of your database with the extra records and the latest query. You can adjust as necessary.

Hierarchy.