Need help to create a tree strhcture

[to truth]

Hi! I'm new to this forum and happy to joining

I'm building an access form that store a field's value as hierarchical data.
assume that a form that saves favorites of persons. like this:
name: Bob
favorites: Access
in the case this value comes under a more general subject like: IT.

and we need to specify all favorites that "IT" includes them, for various queries or representation our data as tree view for users.
I know that the all favorites should be saved in another table. But i need to your help for create that in a Correct and optimal construction.
considering that I'm beginner please explain that in a simple words and without coding!
Thanks

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[theDBguy]

Hi. Welcome to AWF!

I moved your thread out of the introduction forum.

Feel free to post an intro in that forum if you like.

 

[MajP]

Here is a ton of information on tree views and a class to make loading a tree simple.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

However simple is relative. There is not native bound treeview so they require code and an understanding of good data structure.

You provided insufficient detail for anyone to help you. You will need to discuss in far greater detail. There is something about people and favorites. No idea of the real use, the tables, the relationships, etc. Start describing what you are doing without talking about forms and tables.. My first guess is that this is not hierarchical data or the little you provided does not suggest it.

 

[The_Doc_Man]

In a sense, ALL relational databases that actually have "attributes" tables (such as your "likes" table) ARE hierarchical.

Since you are beginner, I will suggest that you read on the subject of "Database Normalization." In this forum you would find articles on the topic of "Normalization" because this IS a database forum. If you go on the web, however, remember to run your search with "Database Normalization" because other disciplines also use the term "Normalization." It might get confusing if you aren't careful.

For us, the Search button is to the top-right, at the far end of the menu bar.

Having hierarchical data and displaying hierarchical data are two different beasts, so the actual display format you want might be an issue. You have to clarify to us (and probably, also to yourself) regarding your actual goals. Without some idea of where you are going with this, it will be hard as heck to advise you.

 

[arnelgp]

simple words and without coding!

you can't have it without coding.
please read something about ms access first (ebooks or any books).
test the water first before diving.
you can't operate something without reading the manual first.

 

[to truth]

I am grateful for all the friendly explanations and guidance from everyone, and I apologize for my incomplete explanations. In fact, I had summarized my goal to make it less complicated.

My main project is as follows:
Creating a database of various historical reports, classified based on specific topics and accessible. Each historical report can be related to several general topics. For example: "literary", "theological", and "geographical", where each of these topics branches out into subtopics and further subtopics. For example, "theological" includes: 1. Islamic 2. Christianity 3. Judaism 4.etc...

This is the main categorization. Additionally, other fields are also intended in this database; such as the time of occurrence (which includes different historical periods); source of citation, etc.
The ultimate goal is for the general users to be able to view, filter, and search the content of these reports based on desired topics through an active user interface.
For example, a user want to filter and display all historical reports related to "theology" that occurred during the Renaissance period in which cited in source A.
In order for users to easily access the content they need, it is necessary to display all the parent and child topics in a tree structure.

 

[to truth]

please read something about ms access first (ebooks or any books).

Thanks. I know the basics of Access , but this is a specialized issue.
Also, I had already searched on the web about my issue; But I wanted to get more detailed information.
Finally, I think it's unfair to spend months learning VB & SQL codes just to solve a problem! That is the philosophy of exist forums like this

 

[ebs17]

The ultimate goal is for the general users to be able to view, filter, and search the content of these reports based on desired topics through an active user interface.

The ultimate start of the approach is not to think about an interface, but rather to develop a database schema, where the necessary data is first stored in a relational database for processing.

You can only evaluate and display data with saved and previously recorded data.

A. Stern: Keine Angst vor Microsoft Access! - ADaMo

The link points to a tool that is intended to help newcomers with data modeling.

 

[to truth]

The ultimate start of the approach is not to think about an interface

Thanks; you are right. But i explained my goal, According to this helpful message:

Having hierarchical data and displaying hierarchical data are two different beasts

 

[arnelgp]

creating the Treeview is a simple task (maybe?).
it is a matter of connecting your tables/reports
with the tree. the Nodes of the tree acts as
menu.

here is a demo based on what you have said.

 

[to truth]

here is a demo based on what you have said.

It's great! I will try to apply that to my project. Thank you very much.

 

[The_Doc_Man]

There are many ways to "skin the cat" here. If you want to (and CAN) "level out the pyramid" then you might find what we call "cascading combo boxes" (HINT: That's a good "search" topic here) to be a useful way to select reports. You would have a table where the first column is the highest level of hierarchy discriminator. (Literary, theological, geographical, etc.), then the next column would included (for theological stuff) Christianity, Islam, Judaism, Zoroastrian, Buddhism, etc.) Then if it came to that, you would have a third column for, say, Christianity that has Roman Catholic, Eastern Orthodox, Methodist, Presbyterian, Baptist, etc. - and the LAST column would always be the name of the report to be opened.

So rows in this table, if it goes only three topics deep, might look like

Literary, USA, Modern, USAModLit (=the name of a report, so you would run a DoCmd.OpenReport on that name rather than a constant name in quotes)
Literary, USA, 19th Century, USA19CentLit
...
Literary, UK, Modern, UKModLit
...
Theological,Christianity,Methodist,TheoChrMeth
Theological,Christianity,Eastern Orthodox, TheoChrEO
...
Theological,Islam, Sunni, TheoIslSun
Theological,Islam, Shiite, TheoIslShi

Wrinkles in this would be "place-holders" for cases where the hierarchy doesn't go that deep.

REMEMBER: There ARE other ways to do selection. This is just food for thought. YOU get to choose what you want. But you WERE looking for ideas.

 

[to truth]

Thanks for your helpful explanation, It's a good idea. But It seams i was mistake in using the word "report", because that confused with the term "report" in Access. But I meant accounts or historical stories.

 

[MajP]

It is not really a tree as explained. You have certain categories of Tags and under that selected values.
You could show this in a tree, just like pretty much any relationship, but this is not Hierrarcial. Or at least you have not demonstrated an example. Can you give an example.

For Example are you going to tag something?
Theology
--Christianity
and sub tag
-------Baptist
and sub tag
--------------Southern Baptist

From what I see it is simply A tag group and a tag value
tblTags
--TagID
--TagGroup
--TagValue

All I am seeing are groups that do not fall under each other. (However, they could. I could add Countries and they could fall under continent)

TagID	TagGroup	TagValue
1	Theology	Islamic
2	Theology	Christianity
3	Theology	Judaism
4	Time Period	Classical
5	Time Period	Renaissance
6	Time Period	Modern
7	Continent	Europe
8	Continet	Asia

If there are no "sub tags"

then

tblDocuments_Tags
--DocumentTagID - PK
--DocumentID_FK
--TagID_FK

Assume document 1 is Islamic and Related to Asia, and Europe, and Moden time period

DocumentTagID	DocumentID_FK	TagID_FK
1	1	1
2	1	6
3	1	7
4	1	8

You could display this in a simple subform showing the tag group and tag
Theology Islamic
Time Period Modern
Continent Asia
Contiente Europe


, but if you wanted you could display in a tree.

Document1
--Theology
---- Islamic
--TimePeriod
-----Modern
--Continent
---- Europe
---- Asia

However if you wanted to really nest categories (again you have not provided an example)

tblDocuments_Tags
--DocumentTagID - PK
--DocumentID_FK
--TagID_FK
--ParentDocumentTagID_FK (this is not a fk to the tag table but to tblDocuments_tag)

Then in theory you could show
Document1
--Theology
---- Islamic
--TimePeriod
-----Modern
--Continent
---- Europe
-----------Country
-----------------Germany
------------------ Italy
---- Asia

Displaying this in a tree will be easy. Building an interface to let you assign tags and especially subtags will be extremely challenging and cannot be done in my opinion without significant code.
Can you better explain your categories? Can you build like I did some notional trees you would expect to see.

 

[MajP]

In other words I can categorize my car as Color Blue and Black, Year 2019, Model Sedan,

My Car
--Colors
---- Blue
---- Black
--Year
---- 2019
-- Model
----- Sedan

I am not doing (and I do not know if you are
--Colors
------ Blue
------------ Light Blue
-------Black
-------------- Charcoal

I am not seeing and hierarchy as you described, and that makes the table and user interface more complicated.

 

[The_Doc_Man]

Thanks for your helpful explanation, It's a good idea. But It seams i was mistake in using the word "report", because that confused with the term "report" in Access. But I meant accounts or historical stories.

Stories? OK. But in what format? Because instead of having a formal report name, you could put a file name there to be opened in Word. Or a hyperlink to a web page. The selection/drill-down process is one thing. The content is another. You could even have a Long Text field if you didn't need really fancy formatting.

 

[to truth]

It is not really a tree as explained

Thanks for your attention. Let me show the tree structure:

Matters
+Theology

-+Islamic​

---Shiite​

---Sunni​

--Christianity​

--Judaism​

+literary

--Grammatical​

--linguistical​

etc,

 

[to truth]

Assume document 1 is Islamic and Related to Asia, and Europe, and Moden time period

I want to categorize all documents in a table with required relationships; Not a table for each document.
also remember that each historical account (in my case) can relate to more than one category.
Then this form is not standard for my project:

You could display this in a simple subform showing the tag group and tag

In fact there is A few problems in my project, How categorize? and How add various values in one field? and how edit them in forms?
Maybe the Access program is not suitable for this kind of project...!?

 

[MajP]

Not a table for each document.

No one would do that

How add various values in one field?

You do not EVER do that

Maybe the Access program is not suitable for this kind of project

I am sure I can do this in Access if I understand the requirement. If you look at the tread I posted the demos, do much more than that.

I think the user interface would be something similar to this example. See the video where you build a tree from available items

Looking for some direction.

I am realizing that the Tree View works well for navigation, but would not be our preferred method for input/queries. Too many mouse clicks. That depends on your data. I can demo some structures that adding a node and moving a node would be way faster then any other method. Treeviews work well...

www.access-programmers.co.uk

I already gave you the proper table structure.

There are two challenges.

1. One is simply creating the table of Possible Tags. That would be organized like the following

tblTags
--TagID
--Level
--TagValue
--ParentID
--ParentValue (This is not needed, but will keep you from losing your mind looking at the data)

TagID	Level	TagValue	ParentID	ParentValue
1	1	Theology		None
2	1	Literary		None
3	2	Islamic	1	Theology
4	2	Christianity	1	Theology
5	2	Judaism	1	Theology
6	3	Shiite	3	Islamic
7	3	Sunni	3	Islamic
8	2	Grammatical	2	Literary
9	2	Linguistical	2	Literary

This then gives you a hierrachial Table to assign tags to your Document_Tag table. But you kind of have to repeat by pulling from here.

If you limit this To no more than 3 or 4 levels this is Orders of Magnitude easier than having unlimited amount of levels.
So I ask you how many Max levels do you need? If you want unlimited then it will make the solution far more complicated. If you limit to 3-4 levels then you can load the above with standard comboboxes. If not you will need a second tree view to just load the potential tags and another tree view to assign tags to documents.

Again that table only gives you a way to choose the tags.
If you can create table tags with all your tags, you will then need a way to add to that table.

2. Problem 2. Then you have to assign tags to a document. The assignment table would be

tblDocuments_Tags
--DocumentTagID - PK
--DocumentID_FK
--TagID_FK
--ParentDocumentTagID_FK (this is not a fk to the tag table but to tblDocuments_tag)


This would be similar to the video.
1. Click on a document.
2. Click on a level 1 tag that you want and add it to the tree. (it is added to the tblDocuments_Tags)
3. If that level 1 tag has a level 2 tag then you can click on it and add it to the tree (it is added to the table with it parent in the table)
4. If that level 2 tag then you can add a level three tag.

If you want a demo, the more data the better.
Provide a table of documents
Provide a table of tags like I shown, with lots of tags.

If you can provide your table of documents and create a table of tags like I did. I probably can demo this.
However I am going to be honest with you. I can do all this with no problem. You will not be able to do it and likely unable to add, edit, modify or even understand it. This will require lots of VBA to make a useable GUI and probably lots of SQL to manipulate. I can demo it like the BOM link which is extremely complicated, but does something similar, but then will you be able to take the demo and tailor to your needs.