What I am currently working on is a database to help me keep track of employee time and attendance. I have a table called [Leave Records], where I enter each employee's leave information for each pay period. I'm working on a query that will automatically calculated the leave balance brought forward from the prior pay period, as well as the total leave balance to date after the leave accrued and used has been inputted.

I have been able to calculate the balance for pay period one with an IIf statement, because I have a separate table where I have entered the leave balance brought forward from the previous year. What I'm having trouble with is getting the query to calculate the leave balance forward for pay period 2 and up. The leave balance forward should equal the total leave balance to date for the same employee in the previous pay period. For example, Employee Jones has 166 hours of leave brought forward from the prior pay period, and a total leave balance of 172 for pay period 2. In pay period 3, that same employee's leave balance forward should equal 172, the same as the total leave balance for pay period 2. The problem I'm having is because these two calculations are dependent on each other, I haven't been able to figure out a way to calculate them both without creating a circular reference. This 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 didn't. How can I get these figures to calculate without creating a circular reference?