Calculations based on previous record

[MorningSun]

Is there a way to base a calculation in a report on the result of the previous record?

My report contains the following headers:

Element(text)
Result (Y/N)
Impact (Double)
Score (percent)

I need for my report to show something like this:
Element YES 1 100%
Element No .5 50%
Element Yes .33 50%
Element No .33 34%

So the person got the first element right and their score is at 100%. They got the next element wrong and so their score drops to 50%. The 3rd element is right so they stay at 50% but the last element is wrong so they loose another 33% making their score 34%.

Problem is that my report design only displays the one row and pulls the factors from the query. How do I reference the Score in the previous row on the report???

Thank You!



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[Remou]

It may be possible to do something with running sum. You would probably need two hidden controls set "Over Group" for the running sum property.

 

[GingerR]

How do you determine which score is the "previous" one? Are these "elements" numbered in sequential order some how?

What if the first one is NO? then what is the Score?
Please put down more examples.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[MorningSun]

Problem with the running sum is that I'm multiplying rather than summing. I might need to explore that further though.

If the first one is NO, the score is 0%. The person get's no credit. But it doesn't matter what order they fall in. If I did it the opposite way I'd get the same outcome:
Element YES 1 100%
Element No .33 67%
Element Yes .33 67%
Element No .5 34%

So order does not matter.

THANKS GUYS!




PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[Remou]

Where does the .5 come from?

Here is the result of a mock-up:

[tt]Element Result Impact If Result=No Result=No RS Score
Element Yes 1 0 0 100
Element No 0.33 0.33 -33 67
Element Yes 0.33 0 -33 67
Element No 0.33 0.33 -66 34[/tt]

 

[GingerR]

You have to have them ordered some how, in order to get the "previous" one and apply a forumula to it. There's no way to tell the computer "Go up one quarter inch on the page and get that percentage for me". You have to tell it to go back one sequential ID number, for example. Get it?

Again, please put an example if the first entry is NO. If the first line is 0% and the second is YES and it's .5, .5 x 0 = 0 so line 2 = 0% as well. Listen, I'm asking for this information cause I'm trying to help. In order to created a formula i have to understand what you are doing.

Is there a Person ID for these? So your example above is for a single Person?

So, not knowing your table structure or anything, I'd say generally what you'd have to do is create a field in your table called ReportOrder, and write code to fill it in with sequential numbers, beginning with a 1, grouped by person. Or fill them in by hand, if this is a one-time thing or very little data. Anyhow, then you'd use that ReportOrder number to write a formula on the report (which I can't figure out without more examples, sorry).

=iif(ReportOrder = 1, 0-(Result*Impact))

that will get the first row. After that, use Dlookup() to get the 'previous' value and multiply it some how.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[MorningSun]

No, there is really no specified order because it's a detail in a report and not all of the Audits will have the same details and the order that they display on the report is irrelevant to what I'm doing.

But I'm trying a different approach and hopefully there is an easy solution....

I still have my Impact Factors that I need to multiply but is there a way to multiply numbers in the footer like you can add them... I need this in my footer sum(Impact) but instead of sum, I need it to multiply all of the numbers... is there anyway to do this? Order won't matter here either as shown by changing the order .5x.5x.67 = .1675 and .5x.67x.5 = .1675 and .67x.5x.5= the same....

Is there some sort of Operand that will do this??

THANKS A MILLION!



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[GingerR]

You'll have to write your own custom function

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[MorningSun]

Do you have any idea what that custom function would be? That's what I'm trying to figure out but have no clue.



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[GingerR]

something like this (not tested, and without any clue from you as to your table structure, object names, or if this is one "person's" score, or what....)

Function GetScore(PersonID)

Dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from TableName where PersonID = " & intPersonID)
dim dblScore as double
dim dblRunningScore as double
dblRunningScore = 0

rs.movefirst
while not rs.eof
       'put something here to make your calculation, i.e.
       dblScore = rs!Field1 * rs!Field2
       dblRunningScore = dblScore + dblRunningScore
rs.movenext

GetScore = dblRunningScore
set rs = nothing
end function

then in a query or report you'd put a calculated field

FinalScore: GetScore(PersonID)

and it would return the score. you should be able to find plenty of examples to figure out what you need.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html