## Need help with two calculated fields in the same query that depend on each other.

What I'm currently working on is a database to help me keep track of employee time and attendance. I have a table called [Leave Records] that I enter the leave information for each employee each pay period. I'm working on a query that will automatically calculate the leave balance forward from the previous pay period for each employee, as well as the total balance after the leave used and earned have been calculated. I can get the balance forward for the first pay period because it comes from a separate table where I have entered the yearly leave balance forward for each employee.

What I'm having trouble with is getting the balance forward for every pay period greater than one, because it should equal the total balance that was calculated for the same employee in the previous pay period. For example, Employee Jones has an A/L balance forward of 160, and an A/L balance to date of 166 for pay period 2. Pay period 3's A/L balance forward for that same employee should equal 166, which is the A/L balance to date for pay period 2. I haven't been able to figure out how to do this without creating a circular reference. Below is the SQL I'm working with so far.

SELECT [Leave Records].Employee, [Leave Records].[Pay Period], IIf(IsNull([Leave Records].[Pay Period]) Or [Leave Records].[Pay Period]=1,[Employees].[Yearly A/L Bal Fwd],(SELECT [Dupe].[A/L Balance to Date]
FROM [Test Query] AS Dupe
WHERE Dupe.Employee = [Leave Records].Employee
AND Dupe.[Pay Period] = [Leave Records].[Pay Period]-1)) AS [A/L Bal Fwd], [Leave Records].[A/L Accrued], [Leave Records].[A/L Used], [Leave Records].[A/L Donated (+) or (-)], (([A/L Bal Fwd]+[Leave Records]![A/L Accrued])-([Leave Records]![A/L Used]+[Leave Records]![A/L Donated (+) or (-)])) AS [A/L Balance to Date]
FROM Employees INNER JOIN ([Pay Period] INNER JOIN [Leave Records] ON [Pay Period].ID = [Leave Records].[Pay Period]) ON Employees.ID = [Leave Records].Employee
ORDER BY [Leave Records].Employee, [Leave Records].[Pay Period];

I was hoping the alias would solve the circular reference, but it doesn't. How can I make these calculations without creating a circular reference?