Complex query in MS Access for a history/tracking table?

I'm building a database for my unit at work to better track information related to employees. It's government so people are just used to using Excel spreadsheets for everything, and I'm a pretty low level employee so Access is the only viable tool I have to build a better system. Our organizational structure comprises dept branches, then divisions, units, and "seats" for individual employees. One employee per seat (along with open seats for vacant positions). What's odd is that sometimes employees have multiple seats in different units or even different divisions, and over time, people get rotated around different seats (I don't know why), sometimes moving back and forth to their original one. But this movement is important to track because my unit has some large-scale monthly reports that tie seat information to tasks assigned/completed.

So I've already created all the tables and relationships between them, along with some important queries. I have a SeatsReference table with just 2 columns: Seat (primary key) and UnitID (foreign key); and a SeatsHistory table with 5 columns: Seat, RecordDate (dual PK/composite key), Employee (FK), JobID, and JobStep (I, II or III for certain positions only). I have a few saved queries with correlated subqueries that can be used to find the employee seat allocations on any given date, which I've used to make a datasheet form (to give the Excel users something that feels familiar), which works all fine and dandy.

Now I'm building a search form that can be used to view the history of any seat (the easy part) as well as the history/movement of employees (the part I need help with). The way I set up the SeatsHistory table is that we enter a new record for any change related to a seat, which can be as small as a promotion from a step I to II, or obviously one employee replacing another in a given seat. If a seat gets vacated, I enter the seat and record date with the remaining fields blank. There are otherwise no records entered to specifically signify an employee being removed from a seat. I would like to keep it this way to not need to enter extraneous records for one employee replacing another in a seat (i.e. one record for an employee leaving and another record for new employee being assigned when they happen simultaneously). The challenge becomes querying the date that an employee left a seat.

For example, let's say Bob's seat history looks like this:

It looks like Bob moved from seat A to seat B, then back to A. But it turns out the full history for these two seats looks like this:

So Bob was moved to seat B from seat A, which was vacant for 2 weeks before being filled by Tom, but then Tom was moved to seat C 3 weeks later (not pictured) and now Bob reclaims seat A and while still holding seat B. I want the search form to return something like this for Bob:

The null End Dates show that those allocations are current. How do I construct a query to return this result? I don't necessarily need a query to return this directly. Since I'm building a search form (with a continuous subform), I can have the End Date be an unbound textbox with a DLookup formula or something. This wouldn't be too complicated if people didn't move back to a seat they held previously, since I could search for the first record of that seat that no longer had that employee. Do I have to redesign my SeatsHistory table? I feel like there should be a solution that I just don't know of yet.

I'd also be interested to know how the potential queries differ in different RDBMSs for future reference, if anyone can provide solutions.

After working on this problem for a whole day, I was able to find a solution using a disconnected ADO recordset in VBA (requires adding reference Microsoft ActiveX Data Objects 6.1 Library). Here's the code in case it might help anyone with a similar problem:

(SearchEmp is the name of the search button; EmployeeCombo is the combo box for typing/selecting an employee)

Private Sub SearchEmp_Click()

    If IsNull(EmployeeCombo) Then
        MsgBox "Enter an employee's name to search seat history", vbInformation, "No Employee to Search"
        EmployeeCombo.SetFocus
        Exit Sub
    End If
    
    Dim rst As Recordset, rst2 As ADODB.Recordset, CurrSeat As String, CurrEmp As Boolean
    
    ' return all relevant seat history info for seats that selected employee was assigned to at any point in time
    Set rst = CurrentDb.OpenRecordset("SELECT Seat, RecordDate, Employee FROM SeatsHistoryT WHERE Seat IN " & _
                            "(SELECT Seat FROM SeatsHistoryT WHERE Employee='" & EmployeeCombo & "') ORDER BY Seat, RecordDate")
    
    Set rst2 = New ADODB.Recordset
    
With rst2
    .Fields.Append "Seat", adVarChar, 4
    .Fields.Append "StartDate", adDate
    .Fields.Append "EndDate", adDate, , adFldIsNullable     ' prevent default date/time in the last record
    .Fields.Append "EndNull", adBoolean         ' field used to sort null end dates first at the end of sub
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic     ' cannot be adLockReadOnly or adLockBatchOptimistic; adLockOptimistic works too
    .Open
End With
    
    CurrEmp = True    ' flag variable used to track if loop iteration is looking for selected employee or not

With rst
    Do
        ' if current seat doesn't match the seat of the current row of the recordset, add it as a new record in 2nd recordset
        If !Seat <> CurrSeat And !Employee = EmployeeCombo And CurrEmp Then
            rst2.AddNew
            rst2!Seat = !Seat
            rst2!StartDate = !RecordDate
            rst2!EndNull = True
            CurrEmp = False
            CurrSeat = !Seat    ' update current seat being searched to prevent duplicate records in 2nd recordset
        ElseIf !Seat <> CurrSeat And !Employee <> EmployeeCombo And Not CurrEmp Then
        ' reached end of one seat group without finding new employee in seat; means current employee is active in this seat (null EndDate)
            rst2!EndNull = True
            rst2.Update
            CurrEmp = True      ' back to looking for current employee in next seat to get start date of next record
        ElseIf !Seat <> CurrSeat And !Employee = EmployeeCombo And Not CurrEmp Then
        ' same as above condition but we've run into a new seat for same employee, so need to start a new seat record
            rst2!EndNull = True
            rst2.Update
            rst2.AddNew
            rst2!Seat = !Seat 
            rst2!StartDate = !RecordDate
            CurrSeat = !Seat 
        ElseIf !Seat = CurrSeat And (!Employee <> EmployeeCombo Or IsNull(!Employee)) Then
        ' employee will be null for vacated/closed seats
            rst2!EndDate = !RecordDate
            rst2!EndNull = False
            rst2.Update
            CurrSeat = ""
            CurrEmp = True
        End If
    .MoveNext
    Loop Until .EOF
    
    .Close
End With

    rst2.MoveFirst
    rst2.Sort = "EndNull DESC, EndDate DESC, StartDate DESC, Seat ASC"
    SubformLabel.Caption = "Seat history of " & EmployeeCombo
' SearchResults is the subform control object; subform is a continuous form with the 3 fields (EndNull field not displayed)
    SearchResults.SourceObject = "EmployeeHistorySearchSubF"
    Set SearchResults.Form.Recordset = rst2
    rst2.Close
End Sub