Summing scores from multiple records

[Guest]

I have two related tables (EVAL and EVALFIND). EVAL contains data on an
evaluation, and EVALFIND, which contains the related findings. They are tied
together with an EVALID field. There is a field on the EVALFIND form that
looks at a related table FINDTYPE, which includes a column for the finding
type and another for a score. I need to sum all of the finding scores for a
specific EVALID and store that in a field on the EVAL table. I've set up the
Finding type selection on the EVALFIND form to two columns (the FINDTYPE
table fields TYPESEL and POINTS).

How can I get it to sum up all of the scores from each of the related
EVALFIND records and save that to the field? I had used a subroutine to sum
all of the scores on a single record, but don't think it'll work for multiple
records.

 

[Guest]

Hi Rebecca,

You generally don't want to store a value that you can calculate. To
calculate the value, you could have an unbound control on your form that has
the following as its control source:

=dsum("POINTS_FIELD", "QUERY_LINKING_EVALFIND_TO_FINDTYPE", "EvalID = " &
me.EVALID_FIELD)

Hope this helps.

Damian.

 

[Guest]

My problem is that I am needing to use the calculated value to then look at a
third table OUTCOME and determine the outcome based on the score. Any
suggestions?

--Rebecca

 

[Guest]

Hi again,

Further info... lovely... ;-)

Write a custom function like this:

function GetScore() as integer
dim lngTotal as long
dim lngOutcomeScore as long

lngTotal = dsum ("POINTS_FIELD", "QUERY_LINKING_EVALFIND_TO_FINDTYPE",
"EvalID = " & me.EVALID_FIELD)

lngOutcomeScore = dlookup("SCORE", "OUTCOME", "CRITERIA TO GET THE SCORE
YOU WANT BASED ON lngTotal")

GetScore = lngOutcomeScore

end function

Then have your control source as =GetScore()

Damian.