How Do I Query this Data in Access Without Creating a Circular Reference

Question

I have two tables called Events and EventSeasons. Events define the main type of events that there can be, and EventSeasons stores the number of times that the events have occurred yearly. I'm trying to query them so that I can figure out which ones are ongoing (between a range of dates), but my problem is that I also need to retroactively mark past seasons of the same event type as active. The next diagram shows what the output should be:You can see in EventSeason that IsActive column for item 2003 is true. Therefore in Events the Subactive column for the item 1001 (Summer) is True. Finally back in EventSeasons the Retroactive column for all 1001 items is set to true.

I have actually achieved this in Excel, but now that I'm trying to do it in access I feel like I'm going to create a circular reference. What I did in excel was to assign true to IsActive in EventSeasons if today's date is between range, then I assign true to Subactive in Events if there are instances of the current EventID marked as active in EventSeason. I then finally assign RetroActive to true if Subactive is true Or IsActive is true.

Answer 1

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()>=StartDate And Date()<=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(*) > 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(*) > 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.