Hierarchy in Access tables (2 Viewers)

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i have to create hierarchy on my database in attachment.
Now my output data are looking like:

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=74087&stc=1&d=1551092913

but i got requirement that i have to use hierarchy here.

So for specific systemVersion for Hana i will have restricted list of NW systemVersions. And for specific FE version i will have specific NW version.

I added also RestricteTable1 and 2 to show how restrictions should work.

How can i do this in Access?
Please help,
thank you in advance!

Best wishes,
Jacek
 

Attachments

  • Database1611.accdb
    568 KB · Views: 110
Last edited:

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
What i think output query should something similar to:



Because NW versions are dependent from HANA and FE topologies.
If i would have structure like Parent - Child i would merge them somehow...

Key is a combination from parent FE and HANA topologies.
The second column is the list of systemVersions restricted by key.

Please help,
Best,
Jacek
 

Attachments

  • Screenshot_6.png
    Screenshot_6.png
    15.1 KB · Views: 814

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:37
Joined
Feb 28, 2001
Messages
27,001
Since English is not your primary language, this might be difficult, but I think you might get the best advice by describing the problem, not by showing us a database that you think might be part of your solution.

Trying to decipher your requirements from the database is sort of like looking through the wrong end of the telescope in order to see the stars.
 

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
Hi,

ok i can try.
In my business model i have database to input all required data for specific customers.
All set ups are deployed in Customer's environment.

For each set up to be deployed we have:
Topology (set up of tiers and specific servers - it can be called Application deployment).
And SystemVersion specific for topology. So when you are deploying topology HANA you will get specific set up of systemVersions for it to install. And user later can choose one of them (for example user will choose HANA to install and SAP HANA system version on it) to be deployed by my Company specific system.
User is selecting choosen set ups later in Excel (this is my FE - please do not touch the topic why Excel is my Front End, not Access - this is requirement which i can not change).

And furthermore, if you are deploying more then 1 topology, each system version depends on installed topology and can have impact on system version to be installed.

So if you are installing HANA and NW you will get systemVersion narrowed by this specific set up (HANA systemVersions will restrict NW systemVersion to choose by user like in restricted tables which i attached to sample database).
If you add additionally FE your set up will be even smaller.

So i have to create hierarchy of all systemVersions. Because specific systemVersions to choose by user depends on specific topology set ups.

Please help in this task,
Best wishes,
Jacek
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:37
Joined
Jan 23, 2006
Messages
15,364
Jacek,

Do you have a data model for this business set up?
I do not know HANA nor NW as per your posts.
Perhaps you could use simpler terms and no jargon.

Topology--->SupportsSystemVersion<---SystemVersion

Update: I did find this via Google SAP S/4HANA
 
Last edited:

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
Hi Guys,

sorry for my late answer.
I found solution what i wanted.

Awesome solution is based on hierarchy database example, what an effect!

I have questions for you:

1. I added ResultQuery to have all results in one place as select query:


It is possible to redo this query in order to exclude Members like Homer Simpson, Bill Murray (so the last member of family chain which are already exists in chains)?
I would like to keep ParentMother because it is a Parent and she has no family (null in field MemberParentID)

2. We have duplicates also in database like for example for Bill Smith --> Tom Thumb --> Bill Murray we have Tom Thumb --> Bill Murray in separate row.(it is duplicate). It is possible to avoid duplicates like this one ?

Best Wishes for you,
Jacek
 

Attachments

  • Screenshot_8.png
    Screenshot_8.png
    92.2 KB · Views: 774
  • hierarchy.accdb
    556 KB · Views: 127

isladogs

MVP / VIP
Local time
Today, 09:37
Joined
Jan 14, 2017
Messages
18,186
Hi Guys,

sorry for my late answer.
I found solution what i wanted.

Awesome solution is based on hierarchy database example, what an effect!

Can you please provide the link to the thread where you obtained the example you've uploaded

Also in case you've not seen them before have a look at these interesting examples involving hierarchical structures / treeview etc. Both are very lengthy threads:
a) Bird Inbreeding - https://www.access-programmers.co.uk/forums/showthread.php?t=302630&highlight=inbreeding
b) Family tree - http://www.accessforums.net/showthread.php?t=75300&highlight=family+tree
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:37
Joined
May 21, 2018
Messages
8,463
It is possible to redo this query in order to exclude Members like Homer Simpson, Bill Murray (so the last member of family chain which are already exists in chains)?
I would like to keep ParentMother because it is a Parent and she has no family (null in field MemberParentID)

2. We have duplicates also in database like for example for Bill Smith --> Tom Thumb --> Bill Murray we have Tom Thumb --> Bill Murray in separate row.(it is duplicate). It is possible to avoid duplicates like this one ?

Need to union 6 queries
Return only ParentMother
union
Return only recornds with Parent no other
Union
Return only records with Parent and GrandParent
Untion
Return only records with parent, grand parent, great grand parent ...


 

Attachments

  • Hierarchy.jpg
    Hierarchy.jpg
    37.9 KB · Views: 605
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:37
Joined
May 21, 2018
Messages
8,463
The issue with reflexive data is that you do not know how many levels deep to make the query. In access sql there is no capability for recursive queries. Your query has 5 levels, but if the data has more you would need to redo the query. If you look at the Bird Inbreeding example I do a recursive loop on each record, and store the pedigree. From there you know the max levels. You can then build in code your results query at the max possible level.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:37
Joined
May 21, 2018
Messages
8,463
One more thing. The standard MSCOMCTL Treeview is not supported in 64 bit applications, so the life of your treeview is short. A lot of users will not be able to use it. I have been using the MSFORMS version by JKP.
https://www.jkp-ads.com/Articles/treeview.asp
This works very well and very portable. This is some of the best VBA code I have ever seen with some very unique ideas of exploiting a user form instead of an access form.
 

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
Can you please provide the link to the thread where you obtained the example you've uploaded

Also in case you've not seen them before have a look at these interesting examples involving hierarchical structures / treeview etc. Both are very lengthy threads:
a) Bird Inbreeding - https://www.access-programmers.co.uk/forums/showthread.php?t=302630&highlight=inbreeding
b) Family tree - http://www.accessforums.net/showthread.php?t=75300&highlight=family+tree

Hi thank you,
unfortunately i was looking for this site but i didnt find it second time, sorry.
Thank you for links!

MajP,
awesome!

I was reading these topic but sinuses and cosinuses are to hard for me :)
And i do not understand this birds database...

For 6 queries, how did you build sql to get table in attachement? i can not manage this :(

The issue with reflexive data is that you do not know how many levels deep to make the query. In access sql there is no capability for recursive queries. Your query has 5 levels, but if the data has more you would need to redo the query.

in which posts? Sorry it is hard to understand for me...

Best,
Jacek
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:37
Joined
May 21, 2018
Messages
8,463
Sorry disregard the query I discussed. That was not correct. The union is correct, but at each level you only return those records without a parent record. You only want to return the longest chain. Here is the correct query for the child level
Code:
SELECT t1.memberdesc AS Member, 
       t2.memberdesc AS Child, 
       t3.memberdesc AS [Grand Child], 
       t4.memberdesc AS [Great Grand Child], 
       t5.memberdesc AS [Great Great Grand Child], 
       t1.memberparentid 
FROM   (((tblmembers AS t1 
          LEFT JOIN tblmembers AS t2 
                 ON t1.memberid = t2.memberparentid) 
         LEFT JOIN tblmembers AS t3 
                ON t2.memberid = t3.memberparentid) 
        LEFT JOIN tblmembers AS t4 
               ON t3.memberid = t4.memberparentid) 
       LEFT JOIN tblmembers AS t5 
              ON t4.memberid = t5.memberparentid 
WHERE  ( ( NOT ( t2.memberdesc ) IS NULL ) 
         AND ( ( t3.memberdesc ) IS NULL ) 
         AND ( ( t4.memberdesc ) IS NULL ) 
         AND ( ( t5.memberdesc ) IS NULL ) 
         AND ( ( t1.memberparentid ) IS NULL ) );

Then for the Grand Child Level
Code:
SELECT t1.memberdesc AS Member, 
       t2.memberdesc AS Child, 
       t3.memberdesc AS [Grand Child], 
       t4.memberdesc AS [Great Grand Child], 
       t5.memberdesc AS [Great Great Grand Child], 
       t1.memberparentid 
FROM   (((tblmembers AS t1 
          LEFT JOIN tblmembers AS t2 
                 ON t1.memberid = t2.memberparentid) 
         LEFT JOIN tblmembers AS t3 
                ON t2.memberid = t3.memberparentid) 
        LEFT JOIN tblmembers AS t4 
               ON t3.memberid = t4.memberparentid) 
       LEFT JOIN tblmembers AS t5 
              ON t4.memberid = t5.memberparentid 
WHERE  ( ( NOT ( t2.memberdesc ) IS NULL ) 
         AND ( NOT ( t3.memberdesc ) IS NULL ) 
         AND ( ( t4.memberdesc ) IS NULL ) 
         AND ( ( t5.memberdesc ) IS NULL ) 
         AND ( ( t1.memberparentid ) IS NULL ) );
Do that for each level then union. Notice the last AND clause


Then I get this
Code:
Member	Child	Grand Child	Great Grand Child	Great Great Grand Child
ParentMother				
Bill Smith	Jane Doe			
Bill Smith	Tom Thumb	Bill Murray		
Bill Smith	Tom Thumb	Homer Simpson		
Bill Smith	Tom Thumb	Jack Jill		
Bill Smith	Daisy Duke	Jack Spratt	My Test	
Bill Smith	Daisy Duke	Jack Jill	Sea Shore Sally
 

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
Hi MajP, awesome thank you vry much and sorry for ma late (i became a father! :)

Best,
Jacek
 

isladogs

MVP / VIP
Local time
Today, 09:37
Joined
Jan 14, 2017
Messages
18,186
Congratulations. And you've helped to create your very own hierarchy :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:37
Joined
May 21, 2018
Messages
8,463
in which posts? Sorry it is hard to understand for me..
I was just pointing out with hierarchical data building a query is easy if you know the maximum amount of levels. In you case you only have 4 levels. However, most of the time you do not know how many levels, so you would need to first calculate that and then build a query. These queries could get real difficult to build and inefficient if you have a lot of levels. You have to join a table for each level.
 

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
Thank you Colin :)

I was just pointing out with hierarchical data building a query is easy if you know the maximum amount of levels. In you case you only have 4 levels. However, most of the time you do not know how many levels, so you would need to first calculate that and then build a query. These queries could get real difficult to build and inefficient if you have a lot of levels. You have to join a table for each level.

So in this case you have to find out repating SQL pattern and build VBA code to create, it is possible i think.

Best,
Jacek
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:37
Joined
May 21, 2018
Messages
8,463
So in this case you have to find out repating SQL pattern and build VBA code to create, it is possible i think.
Not sure if that is a question or a statement. But depends. You could pretty easy determine the number of levels and dynamically build a query for the toy database you show. However, I built one for someone to do the components of a factory. Going from the Assembly Line at the top level through sub parts, sub-sub parts.... down to individual nuts and bolts. One tree was 50K items and 100+ level deeps. That query would be well beyond access limits. Not sure how complex your will be.

Since you know how to do the tree view, you already know how to do a recursive call on the data. Since you understand that you should be able to do whatever you need since that recursive call will be the basis for most display and analysis of the data.
 

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
thank you my friend!

One more question.
Recursive data should have also relationships added?

Best,
Jacek
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:37
Joined
May 21, 2018
Messages
8,463
There is a very long discussion on this topic in this thread:

https://www.access-programmers.co.uk/forums/showthread.php?t=303433&highlight=Horse

But yes you can create a relationship. I discuss how to do it in that thread. Add the table twice to the relationship. In your case a top level record has no parent ID so you want to allow nulls but only allow you to enter a parent that is in the database. This may be important because if a parent item is deleted you either need to be alerted to delete the child or relate the child to a new parent.
 

Users who are viewing this thread

Top Bottom