0

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:Query Event AssertionYou 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.

0

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.

  • Did you mean to add name to EventTheme_IsActive? I see you used it in Events_Subactive. – David Dec 18 '18 at 5:25
  • Never mind, my bad I didn't see the ..FROM Events AS... I'm trying them in my db right now. – David Dec 18 '18 at 5:28
  • It works, thank you! Also, no I don't need editing here so this works fine. I only have a problem though. In hindsight I can understand what each query is supposed to output, but I am not well acquainted with SQL, so I just don't get how the COUNT(*) works in here. Would you mind explaining that? – David Dec 18 '18 at 5:50
  • COUNT(*) just counts the rows. It seems a bit redundant, but it works for our current purpose: show TRUE if there is one or more rows of the current group where IsActive=TRUE. – mendosi Dec 18 '18 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.