PDA

View Full Version : Is looping the answer ?



goshdarnit
02-16-2005, 01:08 PM
Hi - I'm reposting this ( thanks Ken ) .. I can't reproduce my original post exactly but the first coupla lines were about me being a newbie to vba and pathetically grateful for any help :-)

That said : here's my problem. Readers are given tests at periodical intervals with each test consisting of 5 questions - each question having 5 possible answers ranging from 0 to 1. The problem I'm facing is with scoring this. It's not a straight scoring where I just add the values from q1 to q5.

In this case, to calculate the total score, I have to use the formula ( total score * no of questions) / num of questions answered. It's also important to keep track of which questions were answered because each question is scored differently. For e.g if a reader enters a value of 3 for question 1, then the actual score is 4-3 = 1 and so on...

Of course, the above scenario is an oversimplified version of my original db : in actuality, there's about 40 questions in each test..:bug:

I'm attaching a sample db - the test table has the date as pk [ this won't work in a real world scenario but for now it should do ]

Once again, thanks for your time

JK :banghead:

Anne Troy
02-16-2005, 01:45 PM
So...you want to see this info on a form or report, gosh?

Can you, please:
Give more data for us to work with? (You should be able to edit your post and replace the existing DB.)
Create at least some semblance of a form or report with which we can work?

Thanks!!

Anne Troy
02-16-2005, 01:46 PM
PS: You can use the wizards to get the "semblance". :)

goshdarnit
02-16-2005, 02:11 PM
Dreamboat : I'll go ahead and put some more data in and re-attach the db. I know it's not much ( since this is only a skeleton db anyway ) but I've already put in a form to enter the responses to the questions for every reader that takes the test.

The "score" field won't really be part of the data entry form [ I was just fooling around trying different stuff and forgot to take that out ]

Where do I want to see the score : the way I feel now, I'll take it anyway I can get it ;-)) But I do want to generate a report or a query that'll give me the total score. That's where this goshdarned db is tripping me up. I can't figure out how to add up the scores from q1,q2,q3,q4 and q5 while keeping track of which of these were answered.

I know in English what I need to do : putting that in VBA "codish" is an altogther different story.

Thanx.

Anne Troy
02-16-2005, 02:17 PM
Who said you need VBA?
I think you just need some formulas on a report. Create a report that gives us *something* to work with. The idea here (don't take this badly, k?) is that people who answer may not want to spend a bunch of time figuring out what you might want on a form or report, so if you create it, they can just change it...and hence spend less time, which allows them more time to spend answering other questions. :)

Anyhoo (I HATE that word!), you'll need to view the results on a form or report--that's entirely up to you. If you'll be printing it, do a report. If you'll be just viewing it, do it on a form. When you build that form or report, you just change, say...the control info in q1...from "q1" to something like "( total score * no of questions) / num of questions answered" where "total score" is a field or query expression and no of questions is a field or query expression...

Anne Troy
02-16-2005, 02:20 PM
Okay. Suppose it was an invoicing program. You have a table full of invoices. You could calculate a discount on a report. You create the report with the wizard (least I do). Then you insert a control in design view of the report to say something like:

=sum(invoiceamount)

Perhaps you can take this information and work something up.

downwitch
02-17-2005, 04:17 AM
[font=Arial]In this case, to calculate the total score, I have to use the formula ( total score * no of questions) / num of questions answered. It's also important to keep track of which questions were answered because each question is scored differently. For e.g if a reader enters a value of 3 for question 1, then the actual score is 4-3 = 1 and so on...


This is the place to start solving your problem. No point in trying to figure out a total score, if you haven't first set up rules to determine what each question is worth.

As with most test and questionnaire db set-ups, you will need one table for created tests, one table for created questions on each test (this is where the scoring rules would be set up), one table for respondents/test-takers, and one table where their answers go.

You would then create a first-level query in which each question's scoring rule is applied to each respondent's answer. This gives you a per-question score. And finally a second-level query--what Dreamboat's trying to help you with--where you would tabulate the results.

Sound complicated? It is. But not because of Access--until you can clearly set out and articulate your q-by-q scoring rules in plain language for us, there's no help to give you in Access.

goshdarnit
02-17-2005, 07:22 AM
Thanks for the suggestions, dreamboat and downwitch. I've gotten a coupla more ideas which I'm gonna try out. The problem is that the db is so big and complicated, that it's quite a daunting task to try and explain all the aspects. And of course, as always, with oversimplification, the total picture doesn't come through.

I have a feeling you'll see me here again [ I can just about hear you groaning !! ]

Anne Troy
02-17-2005, 07:26 AM
I can just about hear you groaning !!

Not at all! Databases are just not as simple to support as other programs, and making a sample can just take too much time. :(

Questionnaires...I did have someone start making a sample questionnaire database in hopes of selling the thing at some point--you know, just a $3 item or something, but figuring so many people use them and have difficulty setting them up...having an example might help tremendously. It's probably on my hard drive and any other time, I'd be looking for it, but no time this week. If you don't get it sorted, don't hesitate to come back and ask for that sample late next week.

goshdarnit
02-23-2005, 06:14 AM
Hi - I'm back with more questions ( told ya ;-D ). This time my question is about how to make my code 'slicker'.
I've gotten my db to do what I wanted it to do : the scoring and all the conditions are factored in : the darn thing works. Is there a way to write this piece of code in a more effective way ? This is just a small piece of it : I have to do this for 40 questions. Lemme know if you need me to re-attach the db. Thanks in advance.


Private Sub cmdScore_Click()
Dim x As Integer
Dim Y As Integer
Dim Temp As Double
For x = 1 To 3
If x = 1 Then
If sq1.Value < 5 Then
Temp = Temp + sq1
Else: Y = Y + 1
End If
End If
If x = 2 Then
If sq2.Value < 5 Then
Temp = Temp + sq2
Else: Y = Y + 1
End If
End If
If x = 3 Then
If sq3.Value < 5 Then
Temp = Temp + sq3
Else: Y = Y + 1
End If
End If
Next
Score.Value = (Temp * 5) / (5 - Y)
End Sub

mdmackillop
02-23-2005, 11:00 AM
Select case is a bit neater


Private Sub cmdScore_Click()
Dim x As Integer
Dim Y As Integer
Dim Temp As Double
Select Case x
Case Is = 1
If sq1.Value < 5 Then Temp = Temp + sq1 Else: Y = Y + 1
Case Is = 2
If sq2.Value < 5 Then Temp = Temp + sq2 Else: Y = Y + 1
Case Is = 3
If sq3.Value < 5 Then Temp = Temp + sq3 Else: Y = Y + 1
End Select
Score.Value = (Temp * 5) / (5 - Y)
End Sub

downwitch
02-23-2005, 04:53 PM
Well, for starters you don't need "x" at all. You're looping three times, but each time you're just performing a single operation. This is all you really need from what you have:Private Sub cmdScore_Click()

Dim y As Integer
Dim Temp As Double

If sq1.value < 5 Then
Temp = Temp + sq1
Else
y = y + 1
End If
If sq2.value < 5 Then
Temp = Temp + sq2
Else
y = y + 1
End If
If sq3.value < 5 Then
Temp = Temp + sq3
Else
y = y + 1
End If

Score.value = (Temp * 5) / (5 - y)

End SubHere's how you write it as a proper loop, though:Private Sub cmdScore_Click()

Dim x As Integer
Dim y As Integer
Dim sqValue As Double
Dim Temp As Double

For x = 1 To 3
sqValue = Me.Controls("sq" & x).value

If sqValue < 5 Then
Temp = Temp + sqValue
Else
y = y + 1
End If
Next x

Score.value = (Temp * 5) / (5 - y)

End SubHope that helps.

edit: couple things I didn't mention. If your sq controls always return an integer--or better yet, a whole number between 0 and 255--you can use Integer or Byte data types, respectively, instead of Double. Good to learn your data types and how to use them early in VBA, as it will always speed up code and lower your memory use.
And in md's example, x would always be 0, so nothing would happen. Not sure if that was clear to you or not.