Circular reference in calculated query

[aquaticjewel]

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?

[Rawb]

How are you going to be using this data? If it's going into a Report, it would be far easier (and faster) to simply use grouping/sorting to generate the balance forward for you.

If you want to generate the information in the Query itself for some reason, you'll have to use a Subquery.

Also, you shouldn't use slashes or spaces in Column names, you should only use letters, numbers, and the underscore character (_).

[aquaticjewel]

The information will be going into a report. The reason I used the query is so that it would automatically calculate the balances for the report so that if a correction is done and the leave records updated the query will recalculate each time so that the leave balances on the report are always correct. I'm not familiar with using grouping/sorting to generate calculated balances. How would I go about doing that?

[Rawb]

1. Create a basic Query that includes the Records from [Leave Records] that you want to include. Don't worry about grouping or summing anything.
2. Create a new Report in Design View and set your Query up as the Record Source.
3. Group by [Leave Records].[Pay Period]. This will create a new set of header and footer sections called "Pay Period Header" and "Pay Period Footer".
4. Add a Control bound to [Leave Records].[A/L Balance to Date] to one of these new sections (depending on where you want it to show up on the Report).
5. In the Properties for the Control you just created, go to the Data Tab and change the Running Sum Property to "Over All".

[aquaticjewel]

I tried this, but it doesn't solve my original problem, which is that the balances of both [Leave Records].[A/L Bal Fwd] and [Leave Records].[A/L Balance to Date] are dependent on each other. The balance of [Leave Records].[A/L Balance to Date] is equal to (([Leave Records].[A/L Bal Fwd] + [Leave Records].[A/L Accrued]) - ([Leave Records].[A/L Used] + [Leave Records].[A/L Donated (+) or (-)). [Leave Records].[A/L Bal Fwd] needs to be calculated as equal to [Leave Records].[A/L Balance to Date] for the same employee in the previous pay period. That's where I've gotten stuck.