option in form to open subforms and sub-subforms

[ozaman]

Hello everybody

I am planning to analysis parliamentary questions. For this reason, I designed tblMain, tblOrgQuestion, tblOrgQuesAnswer, tblSupplQuestion, tblSupplAnswer and other related tables and established one to many relationships among them. I also designed forms as frmMain, frmOrgQuestion, frmOrgQuesAnswer, frmSupplQuestion, frmSupplAnswer.

Here frmOrgQuestion and frmOrgQuesAnswer are linked with frmMain. Similarly, frmSupplQuestion and frmSupplAnswer are linked with frmOrgQuestion.

To check the relationships among the tables it is need to understand the method of Parliamentary questions- answers procedures. MPs submit their questions for answer to the respective ministry/division on predefined date of a parliament's sessions. Respective minister has to reply it on the fixed date or transfer it for other days. Questions are four types: Starred, Un-starred, Short Notice, and Prime Minister's Questions. Only Starred and Prime Minister's questions may have supplementary questions and answers. Supplementary questions may ask by other MPs than original questioners.

So I want frmMain has an option to open frmOrgQuestion and frmOrgQuesAnswer sequentially and add data from combo boxes with save data. After that if question types are Starred or Prime Minister's then frmOrgQuestion has an option to open frmSupplQuestion and frmSupplAnswer sequentially for few times and add data to these forms from combo boxes and save it.

When I enter data, forms could not save it. Even relationships among tables looks pretty odds. Every table shows eight sub-table (when clicking '+' sign).

Here I attached my database for check and modify so that using this database I can analysis parliamentary questions and answers in details.

Can anyone help me? I will be grateful.

Thanks

Ozaman

 

[boblarson]

Your problem is your table structure and the relationships that you have defined. I think you need to start again from the beginning and get your table structure correct first and then worry about forms later. Right now they are definitely off.

So, I don't have time right now to do a full analysis for you but essentially you have linked your tables with extraneous fields which don't belong in them.

So I am attaching the START of a modified version of your database. The relationships have been deleted for now (except for two that I put back because I can tell that they belong), and I have deleted some fields from the tables that shouldn't be there.

So, what we need is to get a better understanding of what it is you are wanting to store and how these different things relate. What are all of these tables and what are they supposed to store? How do they relate to the member?

I do believe that we may need a few different tables and may be able to get rid of a few existing ones. But that will have to wait until we get more of a definition from you.

Oh, also - I don't think you understand what Cascade Update really means. It doesn't mean what you think it means. All it does is, if the primary key value is changed, it will propagate the change to any child records. Otherwise it does not do anything at any other time and has nothing to do with when you add a record having it update the other tables with the primary key of that other record.

 

[ozaman]

Thanks boblarson for your generous help and suggestions. I try to open your modified database version but I failed. Security problems didn't allow me to open that file. Please send it again so that I can open.

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, tblOrgQuestion, tblOrgQuesAnswer, tblSupplAnswer, and tblSupplQuestion. 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.

Diagrammatically 5 main tables' relationships are shown in the attachment. Please check it.


I think now you understand my 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

 

[ozaman]

Dear Bob Larson

I am waiting for reply and your modified version.

Thanks

ozaman

 

[boblarson]

Dear Bob Larson

I am waiting for reply and your modified version.

Thanks

ozaman

I'm afraid I'm not going to be able to help much for a while. I am currently swamped at work and have little time at home to do anything right now. I think you should post the question of how to correctly normalize your table structure in the Design and Theory of Database Design category here on the forums and you should get some help redesigning it correctly.