How can I subtract a figure from one query and add it to another?

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

I have two queries, almost identical; except one is for scoring questions
answered correctly, and the other is for incorrect answers. The questions are
often repeated, which means the user can sometime get them right/wrong. I
want to have a real time scoring system where as the user begins to get
previously incorrect questions right, I want the 'running score' to reflect
his progress by subtracting from the 'wrong' query and adding to the
'correct' query. but the answers though in word form are the same, underlying
each answer is a unique ID. I just want the subtraction to occur one singular
instance each time.

my SQL query looks like this:

ELECT QX_Score_Points_split_Correct.*, QX_Score_Points_split_Wrong.*
FROM QX_Score_Points_split_Correct INNER JOIN QX_Score_Points_split_Wrong ON
QX_Score_Points_split_Correct.Run_point_Venue =
QX_Score_Points_split_Wrong.Run_point_Venue;
 
Using queries to do what you are doing will be a performance issue. It has
to run the queries for each question. I would suggest an alternative
approach.
Use a DSum function for right and wrong answers. In the Load event of your
form, populate two text boxes (they can be hidden) with the results of each.

Then, in the After Update event of the form, when a question is correct, add
the points to the correct text box and subract them from the wrong text box.
Then you can have a Score text box with a control source that calculates the
score:
= txtCorrect - txtWrong
 
Dave,

This is what I currently have for my 'Answer Combobox':

With CodeContextObject
If (Combo_Answer_A = Run_point_Address_A) Then
answer_box = -1
End If

If (Combo_Answer_A <> Run_point_Address_A) Then
answer_box = 0
'checks if answer is correct; if not the cursor remains on the question.
End
End If

If (answer_box = -1) Then
Tempscore = Tempscore + 1
End If



What happens is the user is presented with 18 questions on a cont.form and
has 18 possible answers on the combo. the above code does a straightforward
comparison check of the question against the answer and scores appropriately.
This is the table that it updates (via a do score/update button):

Tbl_Scores_Running_Totals

Run_No Point_Quiz_ID Run_point_Venue Run_point_Address answer_box Tempscore TestDate
Run_No Point_Quiz_ID Run_point_Venue Run_point_Address answer_box Tempscore Correct_out_Of_10 TestDate
1 893319 Almeida Theatre 1 Almeida Street, N1 1TA Yes 2 21/12/2007
1 893337 Almeida Theatre 1 Almeida Street, N1 1TA Yes 1 21/12/2007
1 893355 Almeida Theatre 1 Almeida Street, N1 1TA No 0 21/12/2007
1 893355 Almeida Theatre 1 Almeida Street, N1 1TA No 0 21/12/2007


This is the code on the update score button:

Private Sub Command23_Click()
DoCmd.SetWarnings False 'disable warnings

DoCmd.OpenQuery "QX_Score_Append_Scores"

'This Runs an append query to the running scores table

End Sub



Which in turn feeds the 'wrong' scores subform which is next to the quiz
subform
I don't have a 'Correct' scores subform because I want the user to just see
which ones he is consitently getting wrong

Maybe it's fatigue, it has taken me an unspeakable amount of time to get
this whole rubber band thing working, or my basic coding abilities, but I
can't even work out where and what to apply the Dsum to?
 
Back
Top