Questionnaire Database structure & Data Entry Forms

[larai]

I've been looking through the forums and came across this thread, which I read through thread705-844594. I found mention of possible alternatives if there are different data types required in the answers.

My question relates to how to manage the different datatypes. I have boolean, integers, decimals, memo, varying text, dates and objects. Do I create separate answer tables for each datatype?

Then, from a data entry perspective, how do I create the different data entry forms in Access to display the question and allow for entry of the answer?

TIA for your help

 

[dhookom]

Check out "At Your Survey" available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You could store a value in the question table that identifies the allowable data types.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[larai]

Wow, impressive application. It appears as though rather than splitting your answers into different tables depending on the datatype that you are storing all responses in 255 char text fields.

Is that preferable than using different tables that each contain answers of different datatypes?

TIA,
Lillianne

 

[dhookom]

I think my structure with possible a field that identifies the datatype for each question would work very well.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[larai]

What about objects and memo fields? And what would the purpose of the datatype identifier field be?

Thanks!

 

[dhookom]

I would set up a different field in the answers table or possibly a related table. I don't believe I have ever taken a survey that required storing an object.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[larai]

The application isn't a survey, although it is similar to a question and answer type document. It's a record of each machine built. There are stations the machine passes through. Each station has "questions" to collect data on different aspects of the process. Some portions require storage of screen captures or test outputs.

The other really nasty part is that some sections in each workstation have data collection requirements on a specific subassembly. This subassembly (head), may come in a single, double or triple configuration, which means that it's a child of the parent machine and is usually many (heads) to 1 machine.

I've been researching all different types of Q&A structures to try and find something that will be flexible enough to meet this requirement and honestly, at this point, I'm pretty stumped ;p

Loosely the structure is
Machine (SN, WO#, SO#, Customer Name)
-workstation 1
>question 1
>question 2
>question 3
>object 1
>memo 1
-workstation 2
>question 4
>question 5
-workstation 3
>question 6
>question 7
--head 1 (Serial number, Head Position, Head Type, etc.)
>>question 9
>>>test result1
>>>test result2
>>question 10
>object 2
--head 2 (Serial number, Head Position, Head Type, etc.)
>>question 9
>>>test result1
>>>test result2
>>question 10
>object 2
-workstation 4
>question 11
--head 1
>>question 12
>>question 13
--head 2
>>question 12
>>question 13
--workstation 5
>question 14
>question 15
--workstation 6
>question 16
>comment 2
--head 1
>>question 17
>>question 18
--head 2
>>question 17
>>question 18

There are hundreds of required data collection (questions), one workstation alone has over 160 questions, and they change frequently (every time there's a change to the manufacturing process).

 

[dhookom]

I'm not sure on your best strategy but you could store the path and file name to a screen shot as the "answer". At Your Survey does kinda support a hierarchy of questions. The nice part about At Your Survey is that it is very "data driven". Once you create your tables and forms it can be used over and over.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]