Loop query

From: neen (neen_at_discussions.microsoft.com)
Date: 07/19/04


Date: Mon, 19 Jul 2021 06:58:03 -0700

I have a database that keeps employee attendance. Absences can be 1 day or 1/2 day. Absences drop off after one year and if an employee does not miss any time in 90 days, they can earn one back so 1 absence drops off. But, they cannot bank days ( they only drop an absence if they currently have any accumulated)

I have this calculating in a query and it has been working fine for quite sometime by calculating ... The new absence, less any dropoff amount by the "next date missed" with the total less than zero = zero, else the total.

The problem I have just run into is as follows: If the new absence is 1/2 and there is a drop off of 1, the total is a negative 1/2 (can't bank time so = 0) - But there are other absences, so this is acutually cheating the employee out of 1/2 an drop off.

Do I need to rethink the enitre database, or is there someway to loop through the query to calculate off a total of absences instead of each current line?