recalculate a score for old records in a new field

[Andreas]

Hello together,

I have calculated a total score based on the values entered in some
fields (see EXAMPLE1 below). This was for a questionnaire and the
questions 1-26.

Now I have to recalculate the score, but exclude some of the
questions.
How can recalculate for ALL the records in the table in a new field?Is
it possible with vba?

Hope it is clear what I want.

Thanks in advance,

Andreas

EXAMPLE1
'question 2
Select Case Me.com_2
Case 1
Me.txt_score_raw = Me.txt_score_raw + 1
Case 2
Me.txt_score_raw = Me.txt_score_raw + 0
Case 3
Me.txt_score_raw = Me.txt_score_raw + 2
End Select

'question 3
Select Case Me.com_3
Case 1
Me.txt_score_raw = Me.txt_score_raw + 0
Case 2
Me.txt_score_raw = Me.txt_score_raw + 1
End Select

 

[rquintal]

Hello together,

I have calculated a total score based on the values entered in some
fields (see EXAMPLE1 below). This was for a questionnaire and the
questions 1-26.

Now I have to recalculate the score, but exclude some of the
questions.
How can recalculate for ALL the records in the table in a new field?Is
it possible with vba?

Hope it is clear what I want.

Thanks in advance,

Andreas

EXAMPLE1
    'question 2
    Select Case Me.com_2
        Case 1
            Me.txt_score_raw = Me.txt_score_raw + 1
        Case 2
            Me.txt_score_raw = Me.txt_score_raw + 0
        Case 3
            Me.txt_score_raw = Me.txt_score_raw + 2
    End Select

    'question 3
    Select Case Me.com_3
        Case 1
            Me.txt_score_raw = Me.txt_score_raw + 0
        Case 2
            Me.txt_score_raw = Me.txt_score_raw + 1
    End Select

I see two major problems with your data which will make this an
uphill battle.

Firstly, you have violated normalization rules in putting multiple
answers in one row instead of a subtable with 1 question per row.
that allows recalculation by simply running a Totals Query.

Second, it is a very!!!! bad idea to store any calculated value
unless one or more of the parts of the calcuklation is not stored.

It is far better to redo the calcuklation on a query, or on a form
or report every time you need it. This guarantees that the
calculated value will always be in sync with the base data.

Now to offer some help, you have some code that is obviously taken
from a form. To redo your calc without openng each record in a form,
you will need to write code that opens a recordset, uses similar
logic to calculate the new total and write it back to the table.

the template for that would be something like.

Public sub RecalcIt()
dim rs as recordset
dim lCalculatedResult as long

SET rs= currentdb.openrecordset("name_of_table")
do while not rs.eof
' do the calculations
' use rs!fieldname to read each field.
'write the result back.
rs.edit
rs!newfield = !CalculatedResult
rs.update
rs.movenext
loop

rs.close
end sub.

 

[Andreas]

Hello,
great, it works (with some minor changes, see below)!

And, mea culpa, the database is not fully normalized, but its
running...

Thank you very much for your help, it saved a lot of people a lot of
time!

Sincerely,
Andreas

 

[Bob Quintal]

@f40g2000pri.googlegroups.co
m:

Hello together,

I have calculated a total score based on the values entered in
some fields (see EXAMPLE1 below). This was for a questionnaire and
the questions 1-26.

Now I have to recalculate the score, but exclude some of the
questions.
How can recalculate for ALL the records in the table in a new
field?Is it possible with vba?

Hope it is clear what I want.

Thanks in advance,

Andreas

EXAMPLE1
'question 2
Select Case Me.com_2
Case 1
Me.txt_score_raw = Me.txt_score_raw + 1
Case 2
Me.txt_score_raw = Me.txt_score_raw + 0
Case 3
Me.txt_score_raw = Me.txt_score_raw + 2
End Select

'question 3
Select Case Me.com_3
Case 1
Me.txt_score_raw = Me.txt_score_raw + 0
Case 2
Me.txt_score_raw = Me.txt_score_raw + 1
End Select

I see two major problems with your data which will make this an
uphill battle.

Firstly, you have violated normalization rules in putting multiple
answers in one row instead of a subtable with 1 question per row.
that allows recalculation by simply running a Totals Query.

Second, it is a very!!!! bad idea to store any calculated value
unless one or more of the parts of the calcuklation is not stored.

It is far better to redo the calcuklation on a query, or on a form
or report every time you need it. This guarantees that the
calculated value will always be in sync with the base data.

Now to offer some help, you have some code that is obviously taken
from a form. To redo your calc without openng each record in a form,
you will need to write code that opens a recordset, uses similar
logic to calculate the new total and write it back to the table.

the template for that would be something like.

Public sub RecalcIt()
dim rs as recordset
dim lCalculatedResult as long

SET rs= currentdb.openrecordset("name_of_table")
do while not rs.eof
' do the calculations
' use rs!fieldname to read each field.
'write the result back.
rs.edit
rs!newfield = !CalculatedResult
rs.update
rs.movenext
loop

rs.close
end sub.