Join Table on Itself By Next Previous Date Field

[kd2017]

I have a table called PayApplications

PayApplications
---------------
ID
JobID (fk long)
PeriodTo (date, always last day of a month for which this app applies)
ApplicationDate (date record was created)

What I would like to be able to do is select all of the PayApplications for a specific job (JobID) AND along with each PayApplication return the ID field of the PayApplication with the closest previous PeriodTo date (from the same job).

SELECT
  ID,
  PeriodTo,
  ApplicationDate,
  PreviousPayApplicationID      <-----------THIS IS THE PICKLE, HOW DO I GET THIS???
FROM PayApplications AS pa
WHERE JobID=[some job id]

Now I know I can do a subquery like so:

(SELECT TOP 1 PayApplicationID
FROM PayApplications As prev_pa
WHERE prev_pa.JobID=pa.JobID
  AND prev_pa.PeriodTo<pa.PeriodTo
ORDER BY prev_pa.PeriodTo DESC)

HOWEVER it's buggy, this subquery causes funny issues down road in stacked queries that force me to use outer joins when I don't want them otherwise it'll crash access.


In testing if I add a PreviousPayApplicationID field directly to the table, and manually enter the appropriate foreign key then all my stacked queries and joins work like they are supposed to, so I know they aren't the problem. But this is kind of a calculated value that I'd rather calculate at run time than store in the PayApplication table.
I've already devised one solution that is a vba function that given a JobID it will loop through the Job's PayApplications and set each PrevPayApplicationID field correctly, I can call this function from a WHERE clause in my query, but this feels like a dirty hack...

So is there another way to query what I'm after besides the SELECT TOP 1 subquery?

[kd2017]

*Bump*
--------------------------
In the mean time this is the solution I've come up with: Every time I run my query (stack) the very first query that gets executed will call the following vba function in the WHERE clause to guarantee (i think?) that all is as it should be before the rest of the queries are processed. Given a JobID the function will retrieve all the PayApp records for that job in the correct order by date, then loop through them to double check that all of the PrevPayAppID foreign key's are correct. And I suppose I can always call this function when changes are made on the PayApplications table too.

Public Function SetPrevPayAppIDs(JobID As Long) As Long
On Error GoTo Error_Proc
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tempID As Long
    
    Debug.Print ">>>SetPrevPayAppIDs as been called on job id #" & JobID

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT PayApplicationID, PrevPayApplicationID FROM PayApplications WHERE JobID=" & JobID & " ORDER BY PeriodTo;")

    With rs
        If Not .BOF And Not .EOF Then
            'I'm told not necessary but good practice (forces vba to wait to continue until all of query's rows are loaded):
            '.MoveLast
            '.MoveFirst
            
            'The first Pay Application of a job can't have a previous pay app, make sure it's null:
            If Not IsNull(!PrevPayApplicationID) Then
                !PrevPayApplicationID = Null
            End If
            
            'remember the current pay app id and move on to the next row
            tempID = !PayApplicationID
            .MoveNext

            Do While Not .EOF
                'don't bother making changes if it's already what it's supposed to be:
                If IsNull(!PrevPayApplicationID) Or !PrevPayApplicationID <> tempID Then
                    Debug.Print ">>>   Updating PayApplicationID #" & !PayApplicationID
                    .Edit
                    !PrevPayApplicationID = tempID
                    .Update
                End If
                
                'get ready to do it again
                tempID = !PayApplicationID
                .MoveNext
            Loop
        End If
    End With
    
    SetPrevPayAppIDs = True
    
    rs.Close
    db.Close

Exit_Proc:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Proc:
    Debug.Print ">>>   There was an error in SetPrevPayAppIDs"
    SetPrevPayAppIDs = False
    Resume Exit_Proc
End Function

If anyone thinks this strategy is a mistake please let me know, as I said before it just kind of feels like a hack... but then again my query stack is now more 'stable' and everything works.