Link same table to two table.Dataentry in main form & sub-subform from same table

[ozaman]

Hello everybody,



I am planning to analysis parliamentary questions. For this reason I designed a database. My wanting from this database to know the frequencies of questions asks by a MP with question's subject, nature and focus according to questions types as well as effectiveness and relevancy of answers provided by ministry and or Division with the questions. If questions & answers are shifted/transferred, I want to know the reasons. Certainly, MPs asks questions and relevant minister answer on it. Therefore above information needs to be displayed with the questioner name(MPs name and Constituency name) and relevant ministry and or division name along with question no, Parliament's session no, and question's date.

When question types are 'Starred' or 'Prime Minister's' it may have supplementary questions and answers. Supplementary may ask MPs in addition to the original questioner. Conventionally, first supplementary question asked by original questioner. Subsequently, minister gives reply. Then other MPs may ask additional supplementary until Speaker moves on next question. Therefore, in supplementary questions, I want to know the supplementary questioner's name & his/her constituency name, supplementary no, question's focus, subject, nature, and relevancy with the original question. Similarly, every supplementary question must answered by minister. So effectiveness and relevancy of supplementary answers also need to know. Information of supplementary questions and answers should be displayed on under the respective original question number.

My main table will be tblMain, tblOrgQuesAns, and tblSupplQuesAns. All other table will be linked these tables via combo boxes on respective fields. By the way, MPs details (education, age, term of office, etc) also need to know for analysis of questions asking criteria.

In the case of form, frmMain is main form, frmOrgQuesAns is subform and frmSupplQuesAns is sub-sub form. So I want a option in frmMain to open frmOrgQuesAns. After data entry in frmOrgQuesAns, I need a option in this subform when question type in frmMain are selected as Starred or Prime Minister's then sub-subform frmSupplQuesAns shall open and I can add data to this form.

Please check my database and modified it so that I can get expected output from this database. Therefore I requested you please help me to design my database properly. Need any other clarification please ask.

Thanks in advance.

ozaman

[rpeare]

Your tables look ok (normalized) but you have no question data that I can see, it's all setup tables and I'm not entirely sure I follow the question.

Can you give an example of your initial data and what you want your query/form/report output to look like.

[ozaman]

Thanks repare for quick reply.

If you go through my previous attached database, you will find my designed forms. Where I filled in frmMain and frmOrgQuesAns. I also try to fill in frmSupplQuesAns but it couldn't allow me to save. all data are filled in these forms from linked tables with options of combo boxes. You will find initial data from linked tables of thes 3 forms.

My query will be find out number of questions asked by all MPs individually according to their questions types(i.e., Starred, Un-starred, Short Notice, Prime Minister's) along with questions and answers details(question's subject, nature, focus, transfer reason, date, questioner name, ministry/division name, answer's effectivness, relevancy and shifting reason). If original questions allow supplementary questions and answers, it needs to show details according to the columns in tblSupplQuesAns.

In reports, I want to know the total number of questions asked by a MP on each question type. I want to explore which question's subjects, nature and focus asked more who are the top questioner as well as answers effectivness of each question also displayed in reports according to the linked tblAnswerEffectiveness. It may be a combinations of individul criteria to get desired result.

I think now you understand my expectations and have some idea of initial data and you will modify my database to get expected results.

If you need more clarifications, please inform me.

Thanks in advance
ozaman