Scattered fields make a messy table (1 Viewer)

hilian · Jul 9, 2015

I am designing a data-entry system for an ongoing clinical case-reading study. There are two kinds of questions for case readers to answer and input data: about 60 that require a single answer and another approximately 25 that can have more than one answer. An example of the latter type is, which necessary actions did a supervisor not take. A supervisor could have neglected one, two or many actions.

I put the fields for the questions requiring single answers in a main table and created a related table for the questions with multiple answers (one-to-many relationship). I called this second table tblDropdowns because the answer choices are in the form of dropdown lists. The questions allowing multiple answers are scattered through the hard-copy case-reading guide, whose order the data-entry system follows. Because these questions aren't grouped together, my solution was to create a separate subform attached to the tblDropdowns table for each of the 25 questions. Each subform, in datasheet view, contains a combo box for its own question.

If I had thought for a moment, I would have realized that each time data was added, Access would create a new row in tblDropdowns, meaning that the same case could have dozens of rows, each with all but one field empty, because Access had created a new row each time data was added from each subform--if the questions had been grouped together and could be displayed on the same subform, this wouldn't happen. My mistake was to assume that because the tables were related, the first response for each of the questions in the subforms would be added to the first row for that case in the tbleDropdowns table, the second on the second row, and so forth.

Anyway, the result is a table that is large and unwieldy. Is there a better solution?

Many thanks,

Henry

MarkK · Jul 9, 2015

I would be tempted to have a Question table and a related Answer table. Then if a question only has one answer, there would be only one related row in Answer. If a question has 6 answers, then there would be 6 rows in Answer. In this way you actually model the exact dynamics of the problem.

Then you would probably need another two tables if a user actually filled out the questionaire, 1) a header table with their Name and the date, and 2) a details table with their specific responses to questions.

gemma-the-husky · Jul 9, 2015

alternatively, if questions can have multiple answers - is it possible to rephrase the questions in a way that gives more questions, and only one answer per question.

eg.
in relation to step 6, was each of these actions carried out
action 1
action 2
action 3

rather than
step 6, click all that apply ....

hilian · Jul 9, 2015

Mark, I’m not sure I follow your suggestion. The Question table would have fields for both types of questions—single and multiple response. If the form was attached to the Question table, how would the answers populate in the Answers table? I’m also not sure about the other two tables. The user is a case reader who, after reviewing the case, answers questions on the data-entry screens. There isn’t an option for a questionnaire not being filled out. Are you suggesting splitting the table with basic identifying data, with a related table containing all of the other questions, both single and multiple response? Then there would be only one form (or subform) for the questions table Would Access return to the first row for each case with each new question? Is the problem having separate subforms or is it having many questions?

Gemma, Unfortunately, it isn’t possible to rephrase the questions, although that would solve the problem. Some of the questions have as many as fourteen answer choices. It would also create headaches in terms of analyzing the data. What I need to do is calculate comparative frequencies for the different responses, and then see if those frequencies differ by a number of other variables. I am planning to use SPSS to do this, and SPSS needs all of the answer choices to be in the same field (variable). In my experience, the intermediate steps to get the answers from many fields into the same field is major headache.

Is it possible to use a (hidden) subform attached to my tblDropdowns with all of the multiple response questions and then, because the multiple response questions are scattered, have separate subsubforms attached to this subform. That way, wouldn’t each new field on the subform start on the same row?

gemma-the-husky · Jul 9, 2015

I would probably use a table allowing for the maximum answers, then. (eg 20 yes nos). Have a field identifying the number of active yes/no choices

so fields
question number
text response
number of active yesno boxes
yesnobox1
yesnobox2
yesnobox3
yesnobox4
etc

Q1, text response, 4, 20 yes nos
Q2, text response, 1, 20 yes nos
Q3, text response, 1, 20 yes nos
Q4, text response, 14, 20 yes nos

use a continuous form. In the current event for each Question disable the columns for responses above the legitimate number for the current question.

so on question 1, disable yesno boxes 5 thru 20
so on question 14, disable yesno boxes 15 thru 20
if the text response is not valid, you could disable that as well.

it looks slightly weird, as you will grey the other rows as well as the active row, but it will work, and it just takes a bit of getting used to.

MarkK · Jul 9, 2015

hilian said:
The Question table would have fields for both types of questions—single and multiple response.

It seems to me that a question is a question, and I don't see how it is a part of the structure of a question that the questions knows how many answers there will be. Answers are defined in the answer table. If there is one answer in Answers table, there is one answer. If there are 6 answers ...

hilian said:
If the form was attached to the Question table, how would the answers populate in the Answers table?

A row in the answer table has a field called QuestionID, which contains the unique ID of the Question the Answer belongs to.

hilian said:
I’m also not sure about the other two tables.

You are defining a questionaire, correct? And many people with different names may fill out this questionaire on different days, correct? Is that part of your solution? If so, where do you store the name of the person filling out the questionaire, and the date they did so? Not in the Question or the Answers table.

There is a difference between the data that IS a questionaire (Questions and Answers) on the one hand, and the data the someone generates when they complete the questionaire.

Say I make a test and hand it out to students, who complete it. When they complete the test, it doesn't change the test itself. The test itself is a separate thing from how a test, or questionaire, might be completed or filled out. IMO

hilian · Jul 9, 2015

gemma-the-husky said:
I would probably use a table allowing for the maximum answers, then. (eg 20 yes nos). Have a field identifying the number of active yes/no choices

Gemma, If I understand you correctly, and I may not, I would have a calculated field that counts the number of responses to a particular question, e.g. "which actions did the supervisor fail to take?" I would know how many responses there were for that question for that case, but when I went to analyze the data, I would not have the answers in a single field. That is, I think what you're telling me to create is a flat table with as many questions (fields) as there are valid answer choices for each multiple response question. I don't see a way to have easily analyzable data without creating a new row for each answer selected, so that some cases would have three or four rows and others would have one or two--all cases would have at least one row because of the the single response questions.

Mark, I'm getting stuck on a Questions table. A table has fields that correspond to the questions on a questionnaire. But a table also contains the data that people enter as answers to the questions. Whether I call it a Questions table or an Answers table, it still has fields which correspond to questions and the fields contain data that are the answers to those questions.

When you point out that "A row in the answer table has a field called QuestionID, which contains the unique ID of the Question the Answer belongs to." you are describing related tables, which I have. I'd be fine if I were willing to have a table with many hundreds or or even thousands of rows, each with data in only one field, for only a few hundred cases. I'm trying to avoid that.

I do appreciate the effort that each of you is making to help me, so if I don't readily adopt your suggestions, it's not that I don't value the thought that went into them.

MarkK · Jul 9, 2015

Each question should have its own row.

I'd be fine if I were willing to have a table with many hundreds or or even thousands of rows

Yes, that's how tables work. You add a new row for each unique thing.

hilian · Jul 9, 2015

Yes, but very soon the table will have so many rows that it will slow everything down. That's what I'm trying to avoid. If I use a separate subform for each of the multiple response questions, Access will create a new row for each answer for each question. Say there are four multiple response questions, one with two responses, one with none, one with three, and one with four. Access will add two rows for the first question; It won't add a row for the second; it will add three additional rows for the third; and it will add four more rows for the fourth. What I would like to have is three added rows, the maximum number of responses for any question, for the case, with all of the responses for the questions contained on those rows. What I will get is nine rows. If I have 25 of this type of question, then the number of rows will multiply very quickly.

MarkK · Jul 9, 2015

This number of rows you are talking about is very small. Biggest tables I have worked with in Access are around 400,000 rows, and I still don't see a performance problem with those, so thousands and tens of thousands is really a trivial matter.

But still, we don't design tables in a relational database on these criteria. We model the reality that we are working with, and if one question has many answers then it does, and the table design reflects those facts.

hilian · Jul 9, 2015

So you're saying that it isn't really poor database design to wind up with many rows with empty fields and that I shouldn't worry about it?

If I wanted to make a more compact data table later on to facilitate analysis, say to spot potential relationships by eyeballing the data, could I use a query?

gemma-the-husky · Jul 9, 2015

hilian said:
gemma-the-husky said:

I would probably use a table allowing for the maximum answers, then. (eg 20 yes nos). Have a field identifying the number of active yes/no choices

Gemma, If I understand you correctly, and I may not, I would have a calculated field that counts the number of responses to a particular question, e.g. "which actions did the supervisor fail to take?" I would know how many responses there were for that question for that case, but when I went to analyze the data, I would not have the answers in a single field. That is, I think what you're telling me to create is a flat table with as many questions (fields) as there are valid answer choices for each multiple response question. I don't see a way to have easily analyzable data without creating a new row for each answer selected, so that some cases would have three or four rows and others would have one or two--all cases would have at least one row because of the the single response questions.

Mark, I'm getting stuck on a Questions table. A table has fields that correspond to the questions on a questionnaire. But a table also contains the data that people enter as answers to the questions. Whether I call it a Questions table or an Answers table, it still has fields which correspond to questions and the fields contain data that are the answers to those questions.

When you point out that "A row in the answer table has a field called QuestionID, which contains the unique ID of the Question the Answer belongs to." you are describing related tables, which I have. I'd be fine if I were willing to have a table with many hundreds or or even thousands of rows, each with data in only one field, for only a few hundred cases. I'm trying to avoid that.

I do appreciate the effort that each of you is making to help me, so if I don't readily adopt your suggestions, it's not that I don't value the thought that went into them.

Click to expand...

Is that a question, or a suggestion?

my original idea was to split the responses to the question to give each response its own row, which seems to me to more normalised, and which I think would be relatively easy to analyse. (I think this is a similar idea to that suggested by other posters, too)

I thought you said that this wouldn't work in your particular case, and for ease of use, you wanted not to do that, but to have each row carry all its own responses, which I think is not properly normalised, but may still work practically.

My alternative suggestion was therefore geared to assist you implement your idea - and help manage the case where every question may have a varying number of responses for you to manage.

hilian · Jul 10, 2015

"my original idea was to split the responses to the question to give each response its own row, which seems to me to more normalised, and which I think would be relatively easy to analyse. (I think this is a similar idea to that suggested by other posters, too)"

Gemma, Splitting the responses to give each response its own row is exactly what I am trying to achieve. What I said wouldn't work was creating a separate question (field) for each possible response for each question. My original strategy was to create separate subforms for each multiple response question. Then each response would have it's own row. This works, but it creates a separate set of rows for each question. For one case with 25 of this type of question, that's a lot of rows, perhaps 60 or 70. I was trying to avoid that. MarkK is telling me that it's not a problem, but I'd still like to avoid it, if possible. Unfortunately, I'm having difficulty understanding your suggestion.

For example, when you suggested using a table allowing for the maximum answers, then. (eg 20 yes nos), and having a field identifying the number of active yes/no choices, I took that to mean that you were were suggesting that, instead of allowing multiple rows, I create multiple fields and use a calculated field to count responses on one row. That's the opposite of giving each response its own row, so I'm confused.

Unfortunately, I also wasn't able to follow your illustration:

so fields
question number
text response
number of active yesno boxes
yesnobox1
yesnobox2
yesnobox3
yesnobox4
etc

Q1, text response, 4, 20 yes nos
Q2, text response, 1, 20 yes nos
Q3, text response, 1, 20 yes nos
Q4, text response, 14, 20 yes nos

I understood the first part, headed fields, as listing field names including several whose answer choice was yes/no. In the second part of the illustration, you show text response numbers for each field, e.g. 4 for Q1 (the fourth response in the combo box list?). This is the way I'd like to set it up, although I don't understand what 20 yes nos is.

I think you have a way of doing what I want to do, but I'm missing something, or several things. Can you help me some more to understand what you're telling me?

Thanks

Uncle Gizmo · Jul 10, 2015

You may find this set of videos useful:-

https://sites.google.com/site/msacc...aires/add-a-questionnaire-to-your-db---videos

hilian · Jul 10, 2015

Thanks, Uncle Gizmo. I actually did look at the questionnaire video a few days ago, but, unfortunately it doesn't apply, and the next video also doesn't tell me how to do what I need to do.

Thinking about the problem some more, I think what I need is some code for each of the subforms that, when the user clicks on the field, returns to the first record for the case. That way I won't be proliferating extra records, which is what I'm trying to avoid. Can you help me with the code?

Again, the problem is: I have two tables, a main table and a related table with about 25 fields that can have several records for each case (the main table has only one record per case). I can't put these 25 fields on a single subform because they are scattered through a hard-copy questionnaire that the user follows in entering data. My solution was to create a separate subform (containing a combo box) for each of the 25 fields and place these subforms on the main form to follow the order in the questionnaire. The problem is that with each question, when the user enters data Access adds a new row rather than returning to the first row for the case. With 25 fields, the number of rows for a case can add up quickly.

gemma-the-husky · Jul 10, 2015

My original strategy was to create separate subforms for each multiple response question.

first, I think the right idea is not to think in terms of presentation, but to think in terms of data management. you can present the information anyway you want. it's the way it is stored that matters

I understood the first part, headed fields, as listing field names including several whose answer choice was yes/no. In the second part of the illustration, you show text response numbers for each field, e.g. 4 for Q1 (the fourth response in the combo box list?). This is the way I'd like to set it up, although I don't understand what 20 yes nos is.

what I meant was have a table like this

Fields:
QNumber, integer - the question number
TextResponse, text - to allow for a text response
YesNoOptions, integer - the number of yesno options for this particular question

-finally, the yesno boxes themselves
yesNo1, Boolean
yesNo2, Boolean
yesNo3, Boolean
yesNo4, Boolean
yesNo5, Boolean
yesNo6, Boolean
yesNo7, Boolean
etc

so for a given question, you can accept a text response.
you can also accept entries to up to <YesNoOptions> check boxes. which are then stored on the given question as yeso1, yesno2, yesno3, etc.

if a question has 4 optional responses, then you active checkbox1, checkbox2, checkbox3 and checkbox4

so for a given question, you can count the number of checked check boxes, although you need an expression like this to do it.

-(sum(yesno1,yesno2,yesno3, ..., yesno20))

(as a true/checked value is actually -1)

--------

Again, as I say, I would have tried to find a way to store the responses 'vertically', rather than 'horizontally'

Q1, option1 true
Q1, option2 false
Q1, option3 false
Q1, option4 false

Q2, option1 true
Q2, option2 true
Q2, option3 false

etc, as then you can find the number of "trues" by question with a simple totals query.

hilian · Jul 10, 2015

Now I see what you are suggesting. My initial reaction is that it will take up a lot of real estate on the data-entry form. I'd prefer to solve the problem another way. but if I can't find that solution, this is what I'll use. So many thanks. (BTW, I haven't been on this forum for a long while, and I forgot how to officially thank you.)

I had the idea that I could solve the problem with code. I've been researching it, and my idea is that I would use the OnClick event for each combo box. I would declare a variable and store the case id in it, then use FindFirst to locate the first instance of the stored id and use MoveFirst to navigate to it. That way, the first response for a question would always be stored in the first row for the case, no matter how many additional rows there were. That's the idea, but I don't know the syntax to write the code.

Scattered fields make a messy table (1 Viewer)

hilian

Episodic User

MarkK

bit cruncher

gemma-the-husky

Super Moderator

hilian

Episodic User

gemma-the-husky

Super Moderator

MarkK

bit cruncher

hilian

Episodic User

MarkK

bit cruncher

hilian

Episodic User

MarkK

bit cruncher

hilian

Episodic User

gemma-the-husky

Super Moderator

hilian

Episodic User

Uncle Gizmo

Nifty Access Guy

hilian

Episodic User

gemma-the-husky

Super Moderator

hilian

Episodic User

Similar threads

Users who are viewing this thread