query to total option groups?

[Mrs.Meeker]

I have a continuous form that has an option group. There on 5 questions on the form, each with a scoring range of 10, 8, 5, 2 and an option value of 1, 2, 3, 4.

Can I write a query that will total the scores, and display the total in one field?

How?

Thanks,
Rhonda

 

[dcx693]

You mean that each individual form that is displayed on the continuous forms has five questions within it? Or do you just have 5 total questions with each question appearing in it's own form?

 

[Mrs.Meeker]

Well, Hi there!

Your question confused me. I don't think I have either, so I'm sending an example of the design view and the form view. The form view only shows one question, but on the 'real' form you can scroll down and answer each of 5 questions.

Make sense?

 

[dcx693]

Hey, come here often?

Anyway, is your form just one long screen? Do you have to hit the "next record" button to get to the next record? Or do you just scroll down on the screen?

What I'm getting at is that each record will have 5 questions? If you do not need to display results from different records on the screen at once, do not use a continuous form. Not sure if we're using the same terminology here. In the form design, under the "Default View" property, what does it say? Single form? Or continuous forms?

I think it's easiest to display just a single form at once, with all five questions on the screen. (Some people like to use a tabbed form with a custom Next> button that takes the user to successive questions.) Anyway, if you display just one form record at a time, you can use the form footer (or header) to display summary info (such as the total score) on the record.

 

[Mrs.Meeker]

It's a continuous form that you scroll down.

I don't want the total to appear on that form. I want the total score for each record (which contains 5 questions) to go to a query or table so the the total can be displayed on another form.

Is this possible?

PS It's only one record on the form. This form doesn't appear until the specific project has been chosen from a different form.

 

[Mrs.Meeker]

Ah heck! I sent you the wrong design view!!! Doubt that it matters! But it sure doesn't look like that! That's the questionaire. LOL I'm sure glad it's friday!

 

[dcx693]

Aw, it's easier if you don't need to display it on the form. You can create a query that calculates the totals. For that you will need to create a calculation in your query. (Hope you remembered the last lesson! haha.)

If your table holding the responses has fields like resp1, resp2, resp3, resp4, for example, you can just add them up in a blank column like this:
Total:[resp1]+[resp2]...etc....

The total is now part of the query, and you can use that query as the recordsource for the form you use to display the results.

 

[Mrs.Meeker]

Okay, so here's my table. I have two columns QuestionID and Response. Do I need to worry about the Question ID or can I just say Total:[Response1]+[Response2]+[Response3]+[Response4]+[Response5]?

Well, I am going to try it, but I'm wondering about the way this table is set up. Do you forsee me having any trouble? I will of course add Project Number to the query.

 

[Mrs.Meeker]

Uh Oh, It's asking for me to enter the parameter value for Response2 through Response5.

I was afraid I'd get something like that.

Any ideas?

 

[Mrs.Meeker]

I don't want to forget that I want to turn the values of 1,2,3,4 inton 2,5,8,10 as I mentioned in the first question.

IIf statement!? You know I did PRINT the last lesson!

Gotta go, this is partly a reminder for myself, got a big weekend! Talk to you later!

 

[dcx693]

You cannot just use [Response1]+[Response2]+[Response3]+[Respons
e4]+[Response5] because those are not fields in your table. Based on your table structure each project probably gets surveyed a few times?

How did you want to view the summarized results? By project number, then by survey number? You could design an IIf function that works within a query that calculates the total scores. It would just be a bit unwieldy. My prefernce would be to either create a custom function or to create another table that you could join to your existing table. That table would function only to hold the score weightings.

This is IF the values of 1,2,3,4 will always translate into 2,5,8,10.

On the other hand, you could just skip all of that (which would skip all of the learning that would be involved) by using a mathematical trick. Being a math geek, I happened to notice that this function:
Round([QuestionId]* 2.5,0)
would do the trick for you. It will transform 1,2,3,4 into 2,5,8, and 10. If you'd like to use this shortcut solution, just create a calculated column with that function.

Now, you must be using Access 2000 or higher to use the Round function. But if you're using Access 97, we can devise a workaround.

 

[Mrs.Meeker]

Each project will only be scored once. The results will be viewed by project number.

The 1,2,3,4 will always equal 2,5,8,10.

I'm using Access 2000.

I'll try the calculated column. But then how do I get them to total?

 

[Mrs.Meeker]

IGNORE THIS POST sometimes I'm an idiot

Wait a minute, I got myself confused. 1,2,3,4 equals 10,8,5 2.

I said that in my first question on Friday but somehow got it turned around.

Too bad, that's a really cool trick!!!

I tried it out and it worked! I screwed up Friday evening and told you the wrong thing. sorry!

 

[Mrs.Meeker]

Which day do you suppose if worse for me? Monday morning or Friday afternoon???

I went to the form and changed the values on the option buttons to the opposite order and the equation now works!!!

Now 1,2,3,4 does indeed equal 2,5,8,10.

 

[dcx693]

Wait - so what is and is not working right now?

 

[Mrs.Meeker]

I'm just here to torment you!!

I have the equation in the query working. I went to the form and changed the values on the option buttons because I got the values backwards.

Sorry I'm such a screw-ball. It's part of my charm

I don't know how to total them though.

Thanks for putting up with me!!

 

[dcx693]

Well, at least you're entertaining.

If you want to view the total results by project number, you should create a totals query. Do you know what a "totals" query is? If not, go to the answer wizard in Access 2000 and search for "Ways to perform calculations in a query".

In short, you can design a totals query to total up certain fields in a query for you. In this case, you've got 5 questions, all relating to particular projects. If you want to summzarize the results so that you have the totals by project, you make a totals query where you "Group By' the project number field and Sum the other fields, like the field holding your new equation in the query.

 

[Mrs.Meeker]

I thought I had responded to this. I'm sorry! It worked and thank you very much for making things easier for me to understand!!!

 

[dcx693]

Glad it worked out.