Avoid null values in a query

[gsrajan]

Hi, I am creating a report which will be exported as a word document with the values from a query. The user will have a questionnaire form with check marks. There are around 100 questions. The user may check around 25 questions with check mark. I want to have the report generated only with the questions the user checked. Please let me know what is the best way of doing this.

Thank you for your help

 

[theDBguy]

Hi. Can you apply a criteria to the query? What does your table structure look like? A table with 100 checkboxes may not be a good design.

 

[gsrajan]

It is a legal form related to divorce cases. Divorce based on various reasons and various claims like custody of children, alimony etc. The user will have only check marks. Based on their selection, a lengthy legal doc is generated in word as a retainer. It is for an attorney client (plaintiff) face to face interview. Other than the few fields like names, children names, marriage date, all are static statements. Thanks.

 

[theDBguy]

It is a legal form related to divorce cases. Divorce based on various reasons and various claims like custody of children, alimony etc. The user will have only check marks. Based on their selection, a lengthy legal doc is generated in word as a retainer. It is for an attorney client (plaintiff) face to face interview. Other than the few fields like names, children names, marriage date, all are static statements. Thanks.

So, were you able to use a criteria?

 

[gsrajan]

like joint custody or not, any pending cases, visitation rights, whether seeking child support, whether own property jointly, seeking monetary award, whether limited divorce etc., like these there are many questions. All are not applicable to everyone. Based on the answer, I will have hidden text boxes which will populate actual legal statements and they only appear in the report. I do not know better way of doing this than what I am doing now. I need to handle singular and plurals like child is, children are also in my statements. Thanks for your help.

 

[theDBguy]

like joint custody or not, any pending cases, visitation rights, whether seeking child support, whether own property jointly, seeking monetary award, whether limited divorce etc., like these there are many questions. All are not applicable to everyone. Based on the answer, I will have hidden text boxes which will populate actual legal statements and they only appear in the report. I do not know better way of doing this than what I am doing now. I need to handle singular and plurals like child is, children are also in my statements. Thanks for your help.

Okay, not sure how we can help you if we can't see what you got. Can you post a copy of your db?

 

[gsrajan]

So far, I don' have a DB! Just doing a planning. Anyway, thanks for trying to help me. If there is any better way please let me know. Thanks

 

[theDBguy]

So far, I don' have a DB! Just doing a planning. Anyway, thanks for trying to help me.

Okay, if you're still planning, the first thing I would advise to you is study Normalization and make sure you create a sound table structure. As I said earlier, thinking of using 100 checkboxes in a table is usually not a good idea. You need to worry about the table structure first before thinking about reports. Good luck!

 

[gsrajan]

Thank you, I will.

 

[The_Doc_Man]

I will throw in an idea. You have a preset list of yes/no questions. Make this a table of its own. You also have a master table of cases, I'm sure.

When you are reading about normalization, assure that you read about something called a JUNCTION TABLE. Because you can make one that links a single case to your list of questions. You would store a set of junction entries that show that case #1234, the client answered questions 10, 21, 45, 62, 93, and 99 with YES. Anything that was answered NO, you don't store. Then when you generate your report of answers, all you have to include is the list with affirmative answers. That is all that would be in the junction table.

You agreed to look up normalization. Once you do, add JUNCTION TABLES to the list of reading. They are a normalized way to store exactly what you suggested that you wanted to store, and take up minimum room to do so.