Building a complex cascading tables in access (1 Viewer)

alvingenius

IT Specialist
Local time
Today, 04:21
Joined
Jul 10, 2016
Messages
168
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
1641244535557.png


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

So, every Topic, have many articles
1641244586212.png

1641244602350.png


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
1641244771020.png


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
 

Attachments

  • 1641244753570.png
    1641244753570.png
    21.8 KB · Views: 249
  • example.accdb
    444 KB · Views: 230

strive4peace

AWF VIP
Local time
Yesterday, 21:21
Joined
Apr 3, 2020
Messages
918
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

Banishment Pending
Local time
Yesterday, 21:21
Joined
May 11, 2011
Messages
11,000
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:


TopicIDArticleIDArticleContent
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

AWF VIP
Local time
Yesterday, 21:21
Joined
Apr 3, 2020
Messages
918
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!

iRel_QAlite_201030.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:21
Joined
Feb 19, 2002
Messages
36,849
I took a much more complicated database I built for a client and reduced it to the minimum requirements. I called it a survey because that is what the original intent was but basically, it is just a group of questions associated with a grouping and related to a person to store his responses. Looks like you want an additional break down to have a grouping of questions within a survey. If that is as far as it would ever go, I would create an additional category table and tie it to the question table to group the questions within a survey. If you want more than two/three levels in your hierarchy, you should think about Crystal's suggestion. SQL is not recursive so each query would need to limit itself to a maximum number of levels. Crystal chose 5. I once did one that was 18 (The database was IMS rather than a relational database so it was a little easier) The application was Boeing's 747 "as designed", "as planned", and "as built" configuration management. In reality, the BOM for the 747, as I recall was actually less than 10 levels deep. We just did 18 as a safety play:)

The original database of the one I attached, had multiple options for answer type. It could be text, numbers, an option group, or a combo selection. It makes for more complex data entry but I can explain how that works since I actually did it in a different application years later. I took it out because it is really too complicated a concept for a sample.
I've attached two pictures from the original db. The first one shows the question definition form. It is clicked in the third row so those are the values showing in the subform to the right. The second picture is the report that is rendered from the survey definition. Match up the items to see how the different types display. This was built in around 2000 and so the data was intended to be printed and filled in by the client. The company contracted with large, international businesses to help relocate people and make sure they settled in to the foreign destination as smoothly as possible so this survey was "orientation".

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.

CheckListDefineJPG.JPG

CheckListPrint.JPG
 

Attachments

  • surveyExampleSimple.zip
    818.3 KB · Views: 241

alvingenius

IT Specialist
Local time
Today, 04:21
Joined
Jul 10, 2016
Messages
168
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

IT Specialist
Local time
Today, 04:21
Joined
Jul 10, 2016
Messages
168
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
1641300007810.png


1641299888739.png


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
1641299909679.png


--

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
1641300030946.png

1641299973426.png


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
 

Attachments

  • 1641300003275.png
    1641300003275.png
    7.9 KB · Views: 234
  • Convention.Example.accdb
    980 KB · Views: 248
Last edited:

plog

Banishment Pending
Local time
Yesterday, 21:21
Joined
May 11, 2011
Messages
11,000
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

IT Specialist
Local time
Today, 04:21
Joined
Jul 10, 2016
Messages
168
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
1641316119633.png

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

Member
Local time
Yesterday, 19:21
Joined
Jun 12, 2009
Messages
362
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
 
Last edited:

silentwolf

Member
Local time
Yesterday, 19:21
Joined
Jun 12, 2009
Messages
362
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!!!
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:21
Joined
Feb 19, 2002
Messages
36,849
but it's not the same idea
My sample is closer to what you need than you think. You just read my suggested uses and I didn't hit on yours specifically.
The database is questionnaires which is essentially what you are doing. The original application was used to help executives and their families settle into communities abroad. So there were dozens and some were destination country specific. Some were very general, others got more specific so if the executive had children, there would be questionnaires regarding child needs, Others might be about recreational activities or language needs, etc. The sample does need one more level to better suit your situation. Try adding a little data to it and see how it works. It should give you ideas on how to proceed. I also posted pictures of some more refinements from the original version of the app that I stripped from the sample because most people would be confused by them but you seem to need that functionality.

You seem to be overwhelmed by the data. There is no getting around it. Someone has to do the data entry and it almost certainly won't be you except for some early test data and there is no shortcut. The only good thing is that you will be able to use the same set of questions and assign them to multiple articles if that is relevant and each year, you can send out the same questions you sent last year.
 

mike60smart

Registered User.
Local time
Today, 03:21
Joined
Aug 6, 2017
Messages
1,235
I added Autonumbers to the attached.

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

Attachments

  • Convention.Example.accdb.zip
    59.1 KB · Views: 250

Users who are viewing this thread

Top Bottom