Newbie with sql

[Jeanette]

Hi everyone, This is my first attempt at using sql. I have created two queries based on sql that I found in this forum. I am attempting to calculate the difference between loan amounts in subsquent records of a table over a group. Below is a sql view of the two queries:

SELECT Project.PrjtID, Project.ProjectName, CityFunds.TypeFundID, ProjectActualsProjections.FYDate, Project.LoanNo, CityFunds.AmtReg, CityFunds.LoanType
FROM Project INNER JOIN (ProjectActualsProjections INNER JOIN CityFunds ON ProjectActualsProjections.FYID = CityFunds.FYID) ON Project.PrjtID = ProjectActualsProjections.PrjtID
ORDER BY Project.ProjectName, CityFunds.TypeFundID, ProjectActualsProjections.FYDate DESC;

SELECT LA.PrjtID, LA.TypeFundID, LA.ProjectName, LA.LoanNo, LA.LoanType, LA.AmtReg, LA.FYDate, CCur(nz([AmtReg]-(SELECT max(AmtReg) FROM LoanAmounts
WHERE (TypeFundID=LA.TypeFundID and PrjtID=LA.PrjtID and LoanType=LA.LoanType) and FYDate < LA.FYDate),0)) AS RunningBalance
FROM LoanAmounts AS LA;

The results below are correct.

TypeFundID ProjectName RunningBalance AmtReg FYDate
City 850 Longwood $903,817 $2,024,817 6/20/03
City 850 Longwood $16,625 $1,121,000 5/17/91
City 850 Longwood $0 $1,104,375 3/20/91

The results below are incorrect. Specifically 903,817 should be 1,510,817.

TypeFundID ProjectName RunningBalance AmtReg FYDate
PerCity 850 Longwood $903,817 $2,024,817 6/20/03
PerCity 850 Longwood $(607,000) $514,000 9/21/99
PerCity 850 Longwood $16,625 $1,121,000 5/17/91
PerCity 850 Longwood $0 $1,104,375 3/20/91

Any help would be greatly appreciated. Again I do not know sql well. I do not know why the "runningbalance" is correct in some instances and not correct in other instances.