How Do I Query this Data in Access Without Creating a Circular Reference
It would probably be easiest to do what you are trying to do by using views, instead of trying to add calculated columns to the table.

EventTheme_IsActive

SELECT ThemeID, 
       EventID, 
       StartDate, 
       FinishDate, 
       IIf(Date()&gt;=StartDate And Date()&lt;=FinishDate,True,False) AS IsActive
FROM EventTheme;


Events_Subactive

SELECT e.EventID, e.Name, et.Subactive
FROM Events AS e
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) &gt; 0, True, False) AS Subactive 
    FROM EventTheme_IsActive 
    WHERE IsActive = TRUE 
    GROUP BY EventID)  AS et ON e.EventID = et.EventID;


Events_RetroActive

SELECT et.ThemeID, et.EventID, et.StartDate, et.FinishDate, et.IsActive, a.RetroActive
FROM EventTheme_IsActive AS et 
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) &gt; 0, TRUE, FALSE) AS RetroActive 
    FROM EventTheme_IsActive 
    WHERE IsActive = TRUE 
   GROUP BY EventID)  AS a ON et.EventID = a.EventID;


These views may not be updateable but if you just need read access then this should suit your needs.