Edit Specific Rows in Recordset

[Elysynn]

Hello,

I have a record set that will return anywhere from 1 to 3 records. Of those records, I have 2 fields that I am concerned with "Points" and "EarnBackPoints". The Points field can have a value of .33,.66 or 1. What I need to do is apply a value in EarnBackPoints so that the sum of EarnBackPoints will be no greater than 1.
Here are some scenarios of what the recordset would need to look like after the EarnBackPoints are applied...


Example1:
Rec Points EarnBackPoints
A-------1------------1

Example2:
Rec Points EarnBackPoints
A-------.33----------.33
B-------.33----------.33
C-------.33----------.33

Example3:
Rec Points EarnBackPoints
A-------.66----------.66
B-------.33----------.33
C-------.33----------.00

Example4:
Rec Points EarnBackPoints
A--------1------------1
B-------.33----------.00


I'm at a loss on how to proceed with this. Any help you can offer would be extremely appreciated.

Thanks!
Elysynn

 

[hwkranger]

This seems to me to be a calculated field -- use a query if this is the case.

Otherwise, what is your logic for backpoints? It might be easier to help you if we understood how backpoints were calculated?

Randall Vollen
National City Bank Corp.

 

[Elysynn]

The basic premise is this:

Each month the employees are evaluated for perfect attendance. If they have perfect attendance, they have the opportunity to earn back an accumulation point. The greatest value they can earn back is 1 point, but they can not earn back greater than what they have accumulated. So, if an employee has only one tardy worth .33 points in the month of March, and has perfect attendance in the month of April, he can only earn back .33 points. But if another employee went home early on 3/15(.33 points) and called in sick on 3/16 (1 point) then had perfect attendance in April, she would earn back 1 point, leaving her with .33 accumulated points left.

The EarnBackPoints need to be applied to the oldest occurance(s) in a rolling year, where up to a full point value can be applied.

I don't know if this is quite what you were looking for, but I hope it will help to understand what I am trying to do. Perhaps I need to completely re-think the strategy...

-Elysynn

 

[hwkranger]

I see. This gives your employees the ability to earn back points -- but not more than they have. It's like a demerit system, if you're bad you get a demerit, if you're good you remove the demerit, you can't lose demerits that you don't have.

The real question is if your database is normalized. This truly should be an "End of year" report or a "review" report or a "quarterly".. you get the drift.

You don't really care that every day you know what their score is, but rather you want to know what their current score is since the last time it was scored. You'll probably have to figure in handling of end of year, -- unless you roll over.

My suggestion is to get the last score the employee has, then for each rolling score subtract 1 if it's >= 1, but subtract itself otherwise. You can store these numbers individually, intermittently, or never. (Never being that you just check this quarterly or annually on a report)

This still looks like something you can do with a query, but it might be a little timely.

Randall Vollen
National City Bank Corp.

 

[Elysynn]

We actually do care every day (well, almost every day) what their scores are at, because once they breach a certain threshold they are put on a warning. (Then if they behave themselves they can come off a warning when they earn back a point...)

The database is not fully normalized... (long story) If I didn't have to apply the earn back to the oldest event(s) (per our attendance policy) I could just store a single point value in another table to reference in a query.

The report basically has to look like the examples I showed in my first post... Thus my conundrum... In need to input a variable value into a field in a variable number of records. :-(

 

[hwkranger]

Elysynn,

I still believe this can be done on a daily basis via a query -- unless you have rolling scores. The reason I believe this is because assuming you have less than 1,000 employees, this doesn't seem to be as if it would be a difficult calculation.

I would probably get the score as follows (this may not be 100%, i'm doing this by hand in the browser):

'Returns the recordset of an employee and range to be scored
Public Function GetEmplScore(lng_EmpID as long, dt_begin as date, dt_End as date, optional BaseScore as double = 0, dt_BaseDate as date) as double

'lng_EmpId is your employee
'dt_begin is the begining date
'dt_End is the ending date
'BaseScore is an optional base score, should you want to store your data or continue on a previous score

dim rst as new adodb.recordset
' *** The following is all 1 line ***
rst.open "Select Sum(fldPoints) as MPts, EmplID, format(fldDate, "mm/yyyy") as Month from Tbl where fldDate between " & dt_begin & " and " & dt_End & " and EmplID = " & lng_EmpID & " group by format(fldDate, 'mm/yyyy'), EmplID order by fldDate", currentproject.connection
' ***  end of 1 line ***


'fldPoints if your points field
'EmplID is your Employee ID
if not rst.eof then
     GetEmplScore = ScoreData (rst, basescore, dt_basedate)
else
     GetEmplScore = 0
end if


private function ScoreData(rec as adodb.recordset, BaseScore as double = 0, dt_BaseDate as date) as double
'this function scores the records you send
dim rst as new adodb.recordset
dim dt_tmpDate as date
dim rtnScore as double

rtnScore = BaseScore 

if basescore = 0 then
     dt_tmpDate = rec!Month
else
     dt_tmpDate = dt_baseDate
end if
do
     rtnScore = rtnScore + rec!MPts 
     if datediff(m, dt_tempDate, Rec!Month) > 1 then
          if rtnScore > 1 then
             rtnScore = rtnScore - 1
          else
             rtnscore = rtnscore - rtnscore
          end if
     end if
rec.movenext
loop while not rec.eof

ScoreData = rtnScore

end function

This may not be 100% what you want, but this is the general direction you want to go. These functions allow you to use a precalculated score with a date if you want to store the score..

I hope this is helpful.

Randall Vollen
National City Bank Corp.

 

[hwkranger]

     if datediff(m, dt_tempDate, Rec!Month) > 1 then
          if rtnScore > 1 then
             rtnScore = rtnScore - 1
          else
             rtnscore = rtnscore - rtnscore
          end if
     end if

Needs to read

     if datediff(m, dt_tempDate, Rec!Month) > 1 then
          if rtnScore > 1 then
             rtnScore = rtnScore - 1
          else
             rtnscore = rtnscore - rtnscore
          end if
     end if
     dt_tempDate = rec!Month

Randall Vollen
National City Bank Corp.

 

[Elysynn]

Thank you very much... This is definately worth delving into. I've not worked with ADO yet, so this is going to take me some time to work through. (Everything in the database is DAO...) I really appreciate your help!!

-Elysynn