Newbie Query Question

[AccessDeano]

I have a form that is used to enter data to a table.

The form has toggle buttons for a series of questions, with answers of Completely Satisfied, Somewhat Satisfied, etc. and depending what button is "clicked" a value of either 4,3,2,1 is written to the table.

I now need to display the number of "Completely Satisfieds, Somewhat Satisfieds,etc" for each question. I also need to show the answers between two dates.

I believe I need a query to do this, but not sure which type. I am also not sure how I get all fields with a value of "4" or "3" and between specific dates.

Please could someone point me in the right direction.

I am using Access 97

 

[Jack Cowley]

I did it with two queries.... Change the names of the fields in the table to reflect the names in your table....

SELECT tblData.Satisfaction, tblData.mDate
FROM tblData
WHERE (((tblData.mDate) Between #5/1/2003# And #8/30/2003#));


SELECT qryDates.Satisfaction, Count(qryDates.Satisfaction) AS CountOfSatisfaction
FROM qryDates
GROUP BY qryDates.Satisfaction
HAVING (((qryDates.Satisfaction)=1 Or (qryDates.Satisfaction)=2 Or (qryDates.Satisfaction)=3 Or (qryDates.Satisfaction)=4));

You may want to modify the query above to show the text values rather than the numberical values of the degree of satisfaction.

hth,
Jack

 

[AccessDeano]

Jack,

Thank you for taking the time to help me out, but I am a serious newbie and confused.

My table is called "new" and has field names of q1,q2,q3,.......q10 and a date field.

Each questionaire entered has 10 questions, hence q1,q2,q3,etc, with 4 possible answers.

When a used clicks new record on the form, each field is assigned a value ranging from 1 to 4, representing Completely Satisfied to Completely Dissatisfied.

How can I use the queries in your reply to get what I need. I am still confused.

Please could you show me how I would write the query using my field and table names. I appreciate your time in helping me with this.

 

[AccessDeano]

I have just tried this using

SELECT new.q1, new.q2, new.q3, new.q4, new.q5, new.q6, new.q7, new.q8, new.q9, new.q10, new.date
FROM new
WHERE (((new.date) Between #5/1/2003# And #8/31/2003#));

and

SELECT test1.q1, Count(test1.q1) AS CountOfq1
FROM test1
GROUP BY test1.q1
HAVING (((test1.q1)=1 Or (test1.q1)=2 Or (test1.q1)=3 Or (test1.q1)=4));

which gets me the number of each answer for question 1 only. Is it possible to get all of the answers for all 10 questions in one query or should I just create a seoarate query for each question?

Furthermore I am a little confused as to how I pass the dates to the first query. Do I use a form with two text boxes and a button that calls a second form and then add these two text box names to the SQL statement. I am sorry if I am being very dumb here, I am trying to learn as fast as I can.

 

[Jack Cowley]

Your table is not normalized and that is your problem. Your table(s) should look like this:

tblRespondents
RespondentID (PK and autonumber)
LastName
FirstName
....other fields...

tblQuestions
QuestionID (PK and autonumber)
Question (Text field)

tblResponses
ResponseID (PK and autonumber)
ResondentID (FK)
QuestionID (FK)
Response (Integer Field)

tblQuestion is basically a Lookup table of your questions. tbleRespondents is not necessary if you are not keeping track of individuals and their responses. The value (1, 2, 3 or 4) is saved in the Response field of tblResponses and this is the table where you get your totals.

If you are new to databases this structure is probably a bit confusing, but it is the way to do it. This type of question has been discussed a lot here and I suggest that you search the archives for Survey or Questionnaire to see what others have to say on the subject. You will find an example of what I am talking about here.

Good luck and if you have further questions just let us know...

Jack

 

[AccessDeano]

Thanks Jack.

I will work through the forum, now that I know what to search for, and attempt to work it all out.

Thank you for your help.

 

[Jack Cowley]

You are welcome... You will find a survey db similar to yours here.

Good luck with your project....

Jack

 

[AccessDeano]

Thanks, I have it working nicely with an addition in the Append queries of the date as I need to produce a report that shows the results for each week or month, etc.

This brings me to 2 more questions ( I have searched, but cannot find answers).

Firstly, how do I run the cross tab queries automatically, do I have to put some "onClick" code on my form that runs the queries when data is entered via the form?

Secondly how do I give the user the ability to specify the date range for the data to be returned. I assume I need to create a simple query that gets results from my cross tab query, but I am again confused. How do I pass this data to the query.

I have played with adding "Between [Start Date:] And [End Date:] in my query but do not see how I would then make these input boxes available to the user.

Any help is much appreciated. Once this is done I think I will post this code as an example as there seem to be quite a lot of questions relating to surveys.

 

[Jack Cowley]

I am not quite sure what you mean by running a query automatically. A query can be the Record Source for a form or report or you can open it to have it display data. If a query is the Record Source of a form all you need to do is open the form to see the results of the query. You can open a query directly with code like this:

DoCmd.OpenQuery "MyQuery"

and you will see the results in datasheet view.

As for getting criteria to the query create a small form with two unbound controls and a command button. Name the controls StartDate and EndDate. In our query put code like this in the Criteria line of the date field:

Between [Forms]![NameOfYourForm]![StartDate] And [Forms]![NameOfYourForm]![EndDate]

The user enters the dates into the unbound controls and then clicks the command button to open the query or a form based on the query.

hth,
Jack

 

[AccessDeano]

Thanks again Jack.

The problem I have experienced is from the example you pointed me to at the Microsoft Website, which suggested creating 10 Append Queries, that append data to a second table.

I cannot see how these queries will be run, without me manually running them to append the data. So I guess I need to play around with some code to run them using the DoCmd..... as you suggested.

The other problem is that I created a small form just as you suggested earlier today, when I click the button I get an error that says:

The Microsoft Jet database does not recognise '[Forms]![datetest]![StartDate]' as a valid field name or expression.

Do you have any ideas as to what I am doing wrong? It seems to work fine for a select query but not for a cross tab.

 

[Jack Cowley]

When it comes to Crosstab queries I am a total failure so for that part of your question it might be a good idea to check the MS KB for articles on the subject or post a new question.

Ten append queries? Why so many? What are you trying to append?

Jack

 

[AccessDeano]

I have sorted the error message. To saving anyone the time to look at this.

I had to enter the column names, separted by commas in the "Column Headings" query properties and it works - YES!

So happy now.

Just need to sort out the running of the append queires automatically and I will be virtually done.

 

[AccessDeano]

The MS KB sugests a append query for each question in the questionaire, which then appends the results each time a new record is added.

But they need to be manually run, from what I can work out. before the data is added to the new table.

I will keep plugging away at it.

 

[Jack Cowley]

You do not need to append each question and answer to the table. I don't know how you have your forms set up but if the user moves from one question to the next and the question and response tables are propely related Access will add the QuestionID and the Response directly to the Response table along with the RespondentID. Look at the survey I suggested at Utter Access and you will see how it is done...

If your tables are not set up that way then you should consider it as it makes it easier.

If you have it set up where all the questions and answers are displayed at one time for the user to answer then use a lookup table with all the questions and then just use one Append Query to append all the questionsID's at one time to the table....

I hope that makes sense. There are a couple of approaches to how you display and ask your questions and I don't know which approach you are using...

Jack

 

[AccessDeano]

Thanks Jack.

I will take a look at using a Lookup table.

Unfortuinately the example database you suggeted is in Access 2000 and I am using 97 so I could not open it.

I will see how the Lookup table goes.

Thanks again.

 

[AccessDeano]

I am now totally confused. I am so close yet so far to finishing this project.

Microsoft say the folowing, which I have followed:

<start>
MORE INFORMATION
The following examples outline nonrelational table design commonly used for questionnaires and surveys and suggests an improved, relational table design.
Nonrelational Table Design
When designing tables for questionnaire applications, many users begin with a design that resembles the table below. Each record contains multiple fields, called Question1 through Question<n>, that contain responses to the questions.
Table: Table1 (old)
--------------------------------------
FieldName: Respondent ID [Primary Key]
FieldName: Question1
FieldName: Question2
FieldName: Question3
.
.
.
FieldName: Question<n>


Problems occur when you want to perform crosstab queries to summarize and/or graph the questionnaire results.
Relational Table Design
A relational table design better supports summary queries, reports, and graphs. In the table below, the Question ID field identifies the question and the Response field contains the answer.
Table: Table2 (new)
----------------------------------
Field: Respondent ID [Primary Key]
Field: Question ID [Primary Key]
Field: Response



How to Switch to Relational Database Design
To convert data that has been entered in Table1 format, follow these steps:

Create Table2 according to the above structure, using the following data type criteria:


All Respondent ID fields must be the same data type.
The Question ID field must be a Text data type.
The Response field must be the same data type as the Question<x> data types.
Create a new query based on Table1.
From the Query menu, choose Append Query (Append in versions 7.0 and earlier) Select Table2 as the table to which you want to append the data. Design the query as follows:
Query: Query1
---------------------------
Field: Respondent ID
Append To: Respondent ID
Field: Question1
Append To: Response
Field: "Question1"
Append To: Question ID

Run Query1 to append to Table2 each participant's responses to Question1.
Repeat steps 2-4 above, replacing Question1 with Question2, and "Question1" with "Question2". You must re-create or modify this query for each question in Table1.
After running all the append queries, the result is a table (Table2) that can easily summarize your results in a crosstab query:
Query: CrossTabExample
---------------------------

Field: Question ID
Total: Group By
Crosstab: Row Heading
Field: Response
Total: Group By
Crosstab: Column Heading
Field: Response
Total: Count
Crosstab: Value

<end>

The problem is that the append queries do not update automatically and once they have been updated once you cannot update them again as you get key errors.

I have uploaded my Db to http://www.auto-alert.net/access/csi.zip in case this helps to allow you to see what I am doing wrong. I would be really grateful if you could point out my errors.

 

[Jack Cowley]

I wish I had known you were using Access97 and I would converted the Survey db sooner. It is attached. Take a look and see if it makes sense.

I am looking at your db and I will see if I can sort out what you are up to...

Jack

 

[Jack Cowley]

I have looked at your db and would suggest you set up up like the Survey sample I just sent you. Much easier all the way round in my opinion...

Jack

 

[AccessDeano]

Thanks again Jack.

I will look at the example tomorrow as I have been at this task all day.

Hopefully I will get it sorted.

Thank you for converting the example and taking a look at my "attempt"

Kind regards

 

[Jack Cowley]

Attached is a revised version of your db. You will need to run Query1 when you add a new Survey. And you will want to add Next and Previous buttons to the Question subform. this needs work but the basics are here.

hth,
Jack