I have a current salary table that each quarter is archived and the most recent salary for the previous quarter is the starting salary for the current quarter. However, there could be up to three salary increases in a quarter, so I need the process to check each employees record and if there is a value in Sal3, that becomes Sal1. If sal3 is 0, then it needs to check Sal2 and if that has a value, Sal2 becomes Sal1 for the new quarter. If Sal2 is 0, then Sal1 for the previous quarter is still Sal1 for the new quarter. I have the attached query, but I realized I need to loop thorugh each record to evalue the salary fields, update Sal1 to the correct amount and then set Sal2 and Sal3 to 0. Dates values work, it is only the salary values that do not update correctly.
'Sets the workdays in the TblEmployeeHRData table to the full number of workdays calculated by
'the workdays function, anyone whose start date was greater than the beginning of the previous
'incentive cycle quarter by default worked the full number of days in the quarter.
strUpdate = "UPDATE TblEmployeeHRData SET TblEmployeeHRData.HRSal1Date = #" & gdteThisQrtDate & "#, " & _
"TblEmployeeHRData.HRSalary1 = IIf([HRSalary3]=0,IIf([HRSalary2]=0,[HRSalary1],[HRSalary2]),[HRSalary3]), " & _
"HRSalary2 = 0, HRSalary3 = 0, HRSal2Date = Null, HRSal3Date = Null, " & _
"HRDaysPaid1 = " & intDays & ", HRDaysPaid2 = 0, HRDaysPaid3 = 0 " & _
"WHERE TblEmployeeHRData.HRStartDate <#" & gdteLastQtrDate & "# "