List text data in memo fields?

[Kila]

I am designing a database that compiles survey results. In addition to the list of A, B, and C answers for each question (which the report counts and calculates as a %age of the total # of surveys), each question ALSO has a comment blank I have programmed as a memo field. I would like the report to list the comments at the end of each question. The entire body of the report is in a header, since I am summarizing the numbers. In the past, I have done the comment field as a subreport, but there are MANY questions in the survey.

Is there a better...more automated way to do this other than creating 50+ subreports?

Thanks for your help!

 

[Rich]

Why would you need 50 sub reports, how many fields do you have in your table?

 

[Kila]

I have 5 surveys which each have 21, 28, 10,10, and 20 questions. Each question has a lookup field to pick an answer (the possible answers are different for every question) and a comment field in case the patient wants to elaborate on his answer. Each survey has it's own summary report, but thus far the only way I know to list all the comments next to each question in the report is to use a subreport. Now that I look at the real numbers (I was only guestimating before), there are actually 89 separate subreports, unless you can recommend an easier way. PLEASE HELP!!! Thanks!!

 

[Kila]

By the way...each survey has its own table, so the largest table would have about 60 fields...28 questions, 28 comments, & a few demographic fields.

 

[Rich]

You have a design problem with your db, it's not necessary to have 28 fields for 28 questions just one field with 28 records, slightly more advanced than that, but you should get the picture.
I'm sure that Pat Hartman posted an example survey db recently, I don't have the link so you'll have to search for it I'm afraid

 

[directormac]

I don't have the link so you'll have to search for it I'm afraid

God forbid...

--Catty Mac

 

[Kila]

Thanks. I would love to see the sample. I am curious as to how you can have an answer AND a comment in the same field. But even so, does the sample have a report that lists all the comments for each question without using subreports? Thanks.

 

[Rich]

Click the search button at the top of the page and enter Survey, when you have the structure sorted you can come back and discuss further enhancements

 

[Kila]

OK...where's the link?

 

[Kila]

Never mind...found it. I'll let you know when I work it out, probably next week. Thank you!

 

[Kila]

Thanks for your responses, but I think I have not explained my surveys very well. I looked at the one you suggested, but our survey is more complex. For starters, it is a paper survey for patients, with the intention that the nurses will compile the data later (we don't have computers for the patients to use). However, this matters little, I know. I can see where making the questions into a separate table can make it easier, especially when the powers that be change the questions after the fact (not like my bosses have done THAT!!). The second difference is that each question has different answers, which would not be so problematic if it were only A-D for each question, but some of our questions have 2 answers (yes or no) and others have up to 7, and each question's answers are different. Also, each question has a text comment, which is where my problem lies...Each question has an answer to be selected AND a comment field, which is what I am trying to list with the data. THanks for your help.

 

[Rich]

No, your problem lies in your approach to the structure of your db.
You need to do some reading on normalising your data. Lets say
one table will hold the Survey types, and Tbl Questions has the primary key from the survey types as it's foreign key with a one to many relationship between the two, Access now knows to which survey the question relates, you can now have as many questions for as many surveys as you like with just a few fields. Hopefully that should point you in the direction to take with the rest of the design. You can then list as many answers for each question as you like. You just have to get the table structure and the links between them correct to start with.

 

[Kila]

OK. I am working on redesigning the database, but how do we make the survey form display different answer options for each question?

 

[directormac]

Probably by creating a separate table for questions and another for answers, with a junction table in between?

--Conjunction Mac

 

[Kila]

Thank you. I am working on the answer as we speak. My question is how can I get the form to show the 2+ answers per question with radio buttons to select one.

Each question has an ID (Q1, Q1, Q3...) and each answer has an ID (Q1-A, Q1-B, Q2-A, Q2-B). The answer table is also linked to the question table by the questionID.

The junction (when we do it) will save the Answer ID in the Results field. This table will also be linked to the others by the question ID.

 

[Rich]

I'd say a combo box with the available answers listed is a better option

 

[Kila]

Yes, that is what we had with the first survey, but I was trying to set up the new survey more like the sample you suggested. We put all the questions in one table and all the answers in the other identified and linked with ID numbers as discussed above. I liked your suggestion because it makes it easier for the changes I KNOW my bosses will make after the fact. But, is it possible for a form to pull a question and it's related answers and allow a user to choose one?

 

[Kila]

I'll attach a copy of the revised database...

 

[Kila]

Attached...

 

[directormac]

Check out this thread.

Just starting to take a look...

Your questions are coming up in the wrong order because of the way Access sorts text (as opposed to numeric) values. The simplest way to correct may be to add a new column to your query with the following:

MySort: Val(Mid([Question],1,2))

and set to "sort ascending"

--Sorting Mac