how to query a simple table.... or so I thought!

[emma clare]

I have a very simple database for recording data collected from a questionnaire. The database comprises of

table1: QUESTIONNAIRE
fields: QUESTIONNAIRE ID
QUEST_1 ........
up to QUEST_75

table2: VALUE (1 to 6)
ANSWER (strongly agree, agree etc)

All of the 75 question fields in table1 record the same information, and all look up to a table2 which stores the 6 values: agree (1), strongly agree (2), neither (3), disagree (4), strongly disagree (5) and spoilt (6). As the data is being entered into a from using option groups, the database stores the value rather than the text in each QUEST field.

Pretty straightforward I thought, but when I come to query the database I am getting into such a muddle I feel like my head is going to explode!!

The results I need to produce should look like this:

ANSWER QUEST_1 QUEST_2 ....all the way to QUEST_75
agree 34 3 25
strongly agree 45 5 34
neither 3 2 4
disagree 3 4 32
strongly disagree 3 3 5
spoilt 0 1 0

Basically all I want is a count of the number of answers for each question! Given that the answers for all the questions are the same I thought this would be a piece of cake, but alas no.

I thought I would simply be able to crosstab my results from the questionaire into a new table, which would have the six rows (agree, strongly agree etc) and the questions as fields. The values should be the count of "agree" etc. I can quite happly have one field and the counts of each answer but when I try an add any other fields it just duplicates the counts!! I have sat staring at my query and am at a complete blank as to how I resolve this seemly simplistic problem .

Please if anyone has any ideas why I am in such a muddle over something which seems so simple I would really appreciate their help!

Many thanks

[reclusivemonkey]

According to your example, you have 88 answers for question 1. Not a good sign to begin with. You should be able to do what you describe, but I am not sure where its going wrong. If you post your DB before I leave work, I will take a look. I remember at somepoint having trouble with crosstabs, they aren't quite as intuitive as in Excel.

[emma clare]

I have posted a copy of my database for you to look at, I hope it makes more sense than my original message. The query results I want to get are totals for each option of each question. I hope seeing the database makes it all a bit clearer.

I would just add, this questionnaire is one I have been asked to create a database for, not one I designed. Unfortunately it has been designed by committee, hence the rather large number of questions!!

Any comments how I can successfully query this would be greatly recieved. At the moment, I am inclined to querying each individual question but the thought of writing 75 questions makes me feel rather numb.....

many thanks again for your help!

[reclusivemonkey]

Hi Emma,

You're either working far too late or are in the US...

I'm at home now and I don't have Windows/Access and I am afraid I am not in work tomorrow so its going to be Monday before I am able to take a look. You may get lucky and find someone else picks up the thread. If not I am sure we can find a solution after the weekend.

[emma clare]

Hi reclusive monkey

No, neither US or working late - I am doing someone a favour with this database and they need the questionnair data entry forms on Tuesday - the querying of the database will not be for a while yet. However I wanted to check I could get some sensible results out before handing the forms over.

Unfortunately I have not done any work in Access for 5 years and I would appear to have forgotten how to query databases! Therefore any comments are gratefully received!! You never know another evening of hitting my head against my husbands laptop may yield results!

[reclusivemonkey]

Originally Posted by emma clare

No, neither US or working late - I am doing someone a favour with this database and they need the questionnair data entry forms on Tuesday - the querying of the database will not be for a while yet. However I wanted to check I could get some sensible results out before handing the forms over.

You sound like a glutton for punishment... ;-)

Originally Posted by emma clare

Unfortunately I have not done any work in Access for 5 years and I would appear to have forgotten how to query databases! Therefore any comments are gratefully received!! You never know another evening of hitting my head against my husbands laptop may yield results!

I generally find that your "mood enhancer" of choice brings the best inspiration. But if masochism is your thing, who I am to argue!

I'll get back to you on Monday. Its usually a slow day for me.

[Pat Hartman]

You aren't going to want to hear this but the problem is with the table design. You have created a spreadsheet rather than a table. Therefore, you have to work with it as a spreadsheet which is not a pretty sight as you have discovered.

Instead of each question being a column in a row, each question should be a row in a table. So for each questionnaire, you will have 75 rows in a table that looks like:

tblAnswers:
SurveyTakerID (foreign key to the table that holds the people)
SurveyID (foreign key to the survey - this allows the tables to hold more than one survey)
QuestionID (foreign key to the question)
AnswerID (foreign key to the answer table)

Your answer table will be nothing but foreign keys to other tables. With this table stucture, you will be able to create simple crosstab queries to do all your analysis. With your present sturcture, you are looking at 75 queries for each type of analysis you want to do rather than just one!!!!

Your form will be much simpler since it will be a main form (for the taker) with a subform for the list of questions. You won't have to try to squish 75 questions on a single form.

[emma clare]

Thanks for your reply Pat. I understand what you’re saying and was trying not to come to the same conclusion that you have given me!! Partly because I am looking for the simplest structure I can come up in a short amount of time.

I have created another test database but though I am confident I can get the results out I now face the problem of getting the data in! One of the requirements of this data base is that the form reflects directly the form used by the people filling in the database. This was one of the reasons I went for my original table comprising 75 fields – unwieldy but at lease I could get them all on one form in the same layout as the form being used in the field, which I have attached the first page of. I am constrained by what the group who want this questionnaire want, the people who will be completing the questionnaire and also those who will have the unenviable task of entering 2000 questionnaires. We all, I would add, are doing this voluntarily. The data entry people are probably going to be people who are not familiar with computers so I need something that is foolproof (hence my flat file noddy approach). Those who will query the data are going to be the same, though I fully expect to help.

My problem is how would I create a form where by when a record for a completed questionnaire is made, this then generates the 75 questions in the TBL_ANSWERS, and gives me an five option boxes according to where the person agrees, strongly agrees, neither, disagrees, strongly disagrees with the statement(or question) asked.. Every record in the questionnaire table (surveytaker ID) should always have 75 questions completed – but how would I enforce this? I can feel myself turning back to my big flat file and 75 separate queries which I orginally opted for...

I am worried that a task I undertook as a favour to a friend in now turning into a major project!!

Many thanks for your help!

[reclusivemonkey]

Originally Posted by emma clare

My problem is how would I create a form where by when a record for a completed questionnaire is made, this then generates the 75 questions in the TBL_ANSWERS, and gives me an five option boxes according to where the person agrees, strongly agrees, neither, disagrees, strongly disagrees with the statement(or question) asked.. Every record in the questionnaire table (surveytaker ID) should always have 75 questions completed – but how would I enforce this? I can feel myself turning back to my big flat file and 75 separate queries which I orginally opted for...

Hello Emma,

I see Pat helped out by explaining how you would set your database up. That is of course the correct answer. The way I would create your answer form is to use an Append query to create a list for each questionnaire. Then you can populate the table with all the question IDs and simply choose the response for each. I have knocked up a *very quick and basic* example, which you should be able to follow. I filled the table with a basic set of questions for one response.

Originally Posted by emma clare

I am worried that a task I undertook as a favour to a friend in now turning into a major project!!

Its not that major if you know Access; you might get a little stuck in creating the form but I am sure I can help you with that.

[emma clare]

Hello Reclusive Monkey

Thank you so much for your help - I am so grateful to you and Pat for your comments and time in helping me.

I am about to start designing my form tonight so I have something to show the committee who are undertaking the survey tomorrow. I am finding that over the last three or four days little bits of Access database design and theory are surfacing in my brain, which has been dormant for the last five years!!! However, without both your help I know I would not have sorted this database out.

I shall, I hope, have no further questions or posts on this topic other than to hopefully post my completed database for you to see!!

Many thanks again!

Emma

[Pat Hartman]

Use a continuous subform for the questions.
Use an option group with checkboxes for the responses. By using an option group, the user can never check more than one response and you end up with one bound field rather than 5.

[emma clare]

Hi Reclusive Monkey and Pat

I am so close and yet so far!! I have sorted out my structure for my questionnaire database and am happy that I will be able to query it as required. What I am now having problems with is my form for the data entry, which is based on the append query, as predicted by Reclusive Monkey! I thought the form would be fairly straightforward, main form, subform, button to enter all the questions complete with options for answers. Although I am aware that this is a form issue, the problem is based on the append query (ok, and my ineptitude….) which is why I have continued with my thread.

I have created a form, based on the tbl_questionnaire with subform based on the tbl_answers. The dataentry person basically will be working off one questionnaire at a time, so needs to append the answers once the questionnaire record has been created. So, on this form I have a button which runs the append query via an event code, though I have altered it by removing the parameter query for the questionnaire id, thinking that the records appended would only be for the questionnaire response of the record open in the form.

Instead of only appending the records for the current record open in the frm_questionnaire, I am appending as many records as I have in the tbl_questionnaire. Although on the form I am getting nearly the correct records (though oddly I always get one blank record with no question, but the question_id??) I tried to put in a “where” statement (where tbl_questionnaire.questionnaire_id = tbl_answers.questionnaire_id) at the end of the append query, but this failed?

I am also unable to enforce any constraints within my tbl_answers, the question_id and answer_id together should always be unique (compound primary key). Whenever I apply these rules to my table, the append query fails? This means that every time a new record is entered into the tbl_questionnaire form, and the button clicked I am able to enter duliplicate answers for each record, which I should never be able to do?

I have attached my database; I think it’s probably easier to see the form “working” than my bungling explanations of why it’s failing.

Many thanks for helping, I really was hoping I could sort this part out and am disappointed not to be posting a finished database .

Emma

[Pat Hartman]

I changed your tables and forms so they work. You will need to build a new report. Put the fields that will only appear on the first page into the report header. Put anything you want at the top or bottom of each page such as page numbers in the page header or footer. Add a section break on questionnaire id and put the header stuff in the header section. Put a SINGLE row of fields in the report's detail section. The detail row will be repeated as many times as necessary to display all detail rows from the query.

BTW, I normalized your tables so you'll need to use a select query as the RecordSource for the report.

[emma clare]

Many thanks Pat, I have had a look at your version of my database, and all works perfectly. I started finishing off the forms but progress has be interrupted by illness, hence my delay in replying to your message.

Thank you once again for your time and for helping me solve these problems!

Emma