I have two tables called
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
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
EventSeasons if today's date is between range, then I assign true to
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.