My goal is to append an auto calculated column to my 'Payment' table called 'AllPdUpTo' which would represent a Date data type. So far, I've been trying to perform a query that compares the amount paid and the amount due for each month (retrieved from the LeaseID), and if the full amount has been paid, then the 'AllPdUpTo' value would look at the previous record's 'AllPdUpTo" value, add 1 month to the value and apply that to the current record being looked at.

The issue is that my current query is giving me the error: "Circular Reference caused by: 'Payment Query'"

I understand why its considered circular, but is there any alternative in accomplishing this?

Before you ask, I didn't want to calculate the "AllPdUpTo" value every time a payment record is entered, because I can see issues arising where the most recent payment entered has a "Date Paid" value which is prior to an already entered payment record.

Here is my payment tbl:

AND HERE"S THE QUERY I'M ATTEMPTING (BAD CODE is the line that ends in "As AllPdUpTo):

SELECT Payment.PaymentID, Payment.LeaseID, Payment.DatePaid, Payment.Amount, Payment.BdCk,    
DCount("LeaseID","Payment","DatePaid<=#" & [DatePaid] & "# And LeaseID=" & [LeaseID] & " And PaymentID<" & [PaymentID]) AS PaymentNumber, 
((SELECT TypicalMonthlyAmntDue FROM Lease WHERE Lease.LeaseID = [Payment.LeaseID])
           + (IIF( [BdCk] = TRUE, 
             (SELECT [Bad Check Fee] FROM Lease WHERE Lease.LeaseID = [Payment.LeaseID]),
              0)
)) AS AmntDueForOnlyThisMonth, 

IIf([AmntDueForOnlyThisMonth]-[Amount]<=0, 
      DateAdd("m",1,(IIf([PaymentNumber]=0,(SELECT StartDate FROM Lease WHERE [Payment.LeaseID] = 
                      Lease.LeaseID),
                     (SELECT AllPdUpTo FROM (SELECT max([PaymentNumber]) FROM [Payment Query] 
                      WHERE LeaseID = Payment.LeaseID))))),
      (SELECT AllPdUpTo FROM (SELECT max([PaymentNumber]) FROM [Payment Query] WHERE LeaseID = 
       Payment.LeaseID))
) AS AllPdUpTo

FROM Payment
ORDER BY Payment.DatePaid;