Building a complex cascading tables in access

[alvingenius]

Hello
I have this idea about a new project we are doing at my work
I'll try to describe it and I hope you get the idea

So at first lets say we have a main table called : Topics


Topics table have about 20 records

and i've another table that have a one to many relation with it called : Articles

So, every Topic, have many articles

next, i need to build another table/s that hae questions !, and it's again one to many relation with the articles table

i've write it in excel to show it to you

as you can see, the main table "" Topics "" and every topic have many articles and every article of every topic have a different question

So i don't know how to proceed with the questions table
Every topic can have different articles
so Topic id: 1 can have 20 articles, topic id : 2 can have 60 different articles ( I've no problem doing that till this step )

and every article can have 30-90 different questions than another article

So is there an easy way to build this questions table? what I think is I need to build massive numbers of questions tables based on every article on every topic

what do you think?
i'm attaching the example db i'm building for this project

thanks for helping

 

[strive4peace]

hi @alvingenius,

on tracking topics, I find it good to use a hierarchical structure ... maybe this video can give you some ideas

Hierarchical Relationships in Access (18:52)

 

[plog]

First, your explanation in this post differs from that in your database. In your database articles and topics are in a many to many relationship. It seems tbl_Articles would be better named tbl_Content because content values are unique, not Articles:

TopicID	ArticleID	ArticleContent
1​	1​	Content 1
2​	1​	Content X
3​	1​	Content W
1​	2​	Content 2
2​	2​	Content Y
3​	2​	Content M

ArticleID=1 goes to 3 topics and 3 contents. This is a junction table between Topics and Articles, not an articles table. So, the issue you posted about should is a part of your problem not the whoel thing.

You've got 4 entities: Topics, Articles, Content and Questions. You need to explain how each is related. So put away the database jargon and tell us what you're real world organization does and how this database is to help in that process. Again, no database jargon.

 

[strive4peace]

adding on ... @alvingenius

I also created a very complicated Question/Answer application ... and was trying to sift it down to a light version ... here is a very simple relationships diagram. In this, topics are called categories ... topic is a better name though!

 

[alvingenius]

hi @alvingenius,

on tracking topics, I find it good to use a hierarchical structure ... maybe this video can give you some ideas

Hierarchical Relationships in Access (18:52)

Thanks @strive4peace
I've watched the video, it's talking about relations, not hierarchical tables and it have good ideas but is not related to what i'm asking

adding on ... @alvingenius

I also created a very complicated Question/Answer application ... and was trying to sift it down to a light version ... here is a very simple relationships diagram. In this, topics are called categories ... topic is a better name though!

after seeming
In the screenshot, you have only 1 table named: QUESTIONS, that have only a couple preset questions, and it's not changing
and that's not the same idea

The "questions" concept has many uses and all have subtle differences. For example, a teacher might have a selection of questions for each segment of a course and want to generate different tests for each class. So out of 30 questions for chapter 2, He might want a random 10 and so those would be created as testChapter2_ver1. Then he might regenerate ver2 with a different (hopefully) set of random question.

again, you have a set of fixed questions and u can random it for every user or case, but it's not the same idea

 

[alvingenius]

You've got 4 entities: Topics, Articles, Content and Questions. You need to explain how each is related. So put away the database jargon and tell us what you're real world organization does and how this database is to help in that process. Again, no database jargon.

I'll explain the whole idea

We are an organization the issues a Conventions, a convention is an agreement with our organization and every country that approves it
we have about 20 conventions
every convention have a unique number and name of the convention
eg: "convention1: for Occupational safety and health"
eg: "convention2: for child work"

every convention has its own articles and it's not the same at each convention.

eg: convention1 - article 1 : regarding owning an OSH facility in-country
eg: convention1 - article 2 : regarding OSH training for workers, and article 3 .....etc

eg: convention2 - article 1 : regarding the minimum age of work ,, article 2 : ....etc

so, now, i've a table for countries list and a table for conventions list and articles list

and we have countries that approved a couple of conventions

and i've a table that has all countries that approved the conventions

--

now every year we follow up with the countries that approved the conventions and we send them a question form regarding the conventions they approved
here's the followup table that tells what we sent to which country in what year

as you can see country 1 at 2019 we sent a follow up form regarding conventions no. 3,5,8,9,11,,12,13,16,18,19

till this step, i've the project working at the step of what we sent as follow up with the country at specific year

now i need to add more information regarding the follow-up
so for example we are sending to country 1 at 2019 convention:19
this convention has about 10 articles

So we are following up with the country with this convention, regarding every article on it
every article we put 3-6 questions so we know how much they are using it in the country
and for sure every article on every convention has its own questions

we are doing this step as a Word Question form, and when we got the reply from the country
we sent it to our legal expert to tell us if the country is following all articles on the convention or not based on the country's answer
and then the expert has 4 options on every article to tell
1- the country is fully following this article
2- the country is partially following this article
3- the country is not following this article
4- No answer from the country on this article questions

and at the end, the expert tells, this country with convention 19 has:
articles no. 1,2,4,5,7,8 fully followed by the country
articles no. 3,6 is not followed by the country
articles no. 9,10 have no answer

then we send another mail to the country telling them to follow articles no 3, 6 and answer articles no. 9,10

consider this process is done on a scale of 20 countries and 20 conventions every year

we are losing our focus on which country should we send a follow-up to and which articles !!!!

so i want to insert the form of questions regarding every convention into this project
and it should be a sub table from the follow-up table

and i will fill the country response on the db project and the expert will only check if it fully / not follow / no answer

then from a query, i can tell what country should i send convention articles for another follow up regarding the rest of articles after expert revision notes

i'm attaching sample of ur database regarding countries and conventions and articles and follow-up tables

 

[plog]

That made things so much worse. I mean I almost had my head around your 4 entities and then you went and changed them and added more. Then you drowned me in database jargon and actual data. I'm lost, perhaps the others can assist.

 

[alvingenius]

That made things so much worse. I mean I almost had my head around your 4 entities and then you went and changed them and added more. Then you drowned me in database jargon and actual data. I'm lost, perhaps the others can assist.

I'm so sorry for that @plog
First i've asked of the part the holds me, and when you asked for a full explanation I provided it

So, i'm trying to figure it out till now, and did many trial and errors to get it
and i think i need to build a table for every convention that holds articles questions and expert opinion and notes regarding every article answers

as u can see, every color means an article

i'm still testing this scenario and wanna see if i can get an over all report that can tells
regarding country 1 at 2021 whice articles that : fully followed / not followed / no answers

 

[silentwolf]

Hi,

not sure if I can help but I do believe you need a many to many relationship to join Country and Conventions.


tbl_Convention
tbl_Country
tbl_CountryConventions

So one or more country can have one or more conventions

After this you join tbl_CountryConvention with a table called tbl_ConventionArticles,
So one CountryConvention can have one or many ConventionArticles,

From there on you can then build tbl_ArticleQuestion or tbl_ConventionQuestion or something down those lines.

Therefore you would be independent .

Meaning each Country and each CountryConventionArticle can be answered from each country differently or different Questions can be given to them to check or answer or whatever they need to do with those.

You could then even go further and create a tbl_ConventionAnswer, and populate those with those Answers you are having as a standart.
With one field called "Other" for an unexpacted Answer given to that ConventionArticel given from the Country.

That means you then can populate those answers given from each Country easily back to your database or even automate those actions.

HTH

 

[silentwolf]

Also you should look into how to name tables in Access and also ID's for each table with an AutoIncrement Datatype.
Those AutoTypes not sure how they are called correctly in an english Access Version...Maybe someone can tell me
need to be in relationship to the other Tables .. not the ConventionNumber!!!

 

[mike60smart]

I added Autonumbers to the attached.

But as Pat has said you need to look at the Survey Example uploaded previously