MS Access crosstab queries when raw data holds date ranges

Question

I have the following table of recent US presidencies:

ID President StartDate  EndDate
1  Bush Sr.  20-Jan-89  20-Jan-93
2  Clinton   20-Jan-93  20-Jan-01
3  Bush Jr.  20-Jan-01  20-Jan-09
4  Obama     20-Jan-09  20-Jan-17
5  Trump     20-Jan-17  20-Jan-21

The key thing to notice is that presidencies are given by time intervals with a start date and an end date.

I would now like crosstab where columns are some time interval (years, quarters or months), rows are the different presidents, and values are 1 or 0 depending on whether the president was in office within the given time interval.

In more general terms, I want rows in my data table to match several possible columns in the crosstab query, and not just one. So far, I have only seen crosstab examples of the opposite, where multiple data values map into the same crosstab column.

The table I hope to yield is the following (only showing years 1993-2001 because of space limitations):

          1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001…
Bush Sr.    1    1    1    1    1    0    0    0    0    0    0    0    0
Clinton     0    0    0    0    1    1    1    1    1    1    1    1    1
Bush Jr.    0    0    0    0    0    0    0    0    0    0    0    0    1
…

Please let me know if this makes sense or if I should elaborate further.

Answer 1

The field you are looking for would look something like this:

IIf(MyYear BETWEEN Year(StartDate) AND Year(EndDate),1,0)

Answer 2

Consider populating a separate DateRange table that holds all range values including ordered Years, Quarters, and Months. You can build such a table with dynamic SQL queries in VBA:

Public Sub PopulateTime()
    Dim i As Integer, j As Integer, k As Integer

    CurrentDb.Execute "CREATE TABLE DateRange (" _
                                    & " [Year] Integer," _
                                    & " [Quarter] Integer," _
                                    & " [Month] Integer)", dbFailOnError

    For i = 1989 To 2021
        For j = 1 To 4
            For k = 1 To 12
                CurrentDb.Execute "INSERT INTO DateRange ([Year], [Quarter], [Month])" _
                                    & " VALUES (" & i & ", " & j & ", " & k & ");"
            Next k
        Next j
    Next i

End Sub

Then run a cross join on both tables Presidents and DateRange with date filtering. Use such a query as source for crosstab:

Year

TRANSFORM Count(q.Year) AS CountOfYear
SELECT q.President
FROM 
  (SELECT p.ID, p.President, t.Year
   FROM Presidents AS p, 
       (SELECT sub.[Year]
        FROM DateRange sub
   GROUP BY sub.[Year])  AS t
   WHERE t.Year BETWEEN Year(p.StartDate) AND Year(p.EndDate))  AS q
GROUP BY q.ID, q.President
ORDER BY q.ID
PIVOT q.Year;

Quarter

TRANSFORM Count(q.Year) AS CountOfYear
SELECT q.President
FROM 
   (SELECT p.ID, p.President, t.Year, t.Quarter
    FROM Presidents AS p,
        (SELECT Year, Quarter 
         FROM DateRange sub
         GROUP BY Year, Quarter)  AS t
    WHERE t.Year BETWEEN Year(p.StartDate) AND Year(p.EndDate)
    AND DateSerial(t.Year, t.Quarter *3 - 2, 1) <= p.EndDate)  AS q
GROUP BY q.ID, q.President
ORDER BY q.ID
PIVOT (q.Year & ' Q' & q.Quarter);

Month

Month is a bit extensive here because Access tables/queries have a maximum of 255. Additionally, month orderings are not in numeric order when concatenating as numbers strings since two-digits affect number order: 1 10 11 12 2 3 4.

To resolve first issue, run two crosstabs with Year WHERE filters. Below runs until 2009 where Trump does not output and then run same query from 2010 to 2021. And to resolve second issue, use the crosstab's IN() clause to pre-define the many, many columns for all 12 months from 1989-2009:

TRANSFORM Count(q.Year) AS CountOfYear
SELECT q.President
FROM 
     (SELECT p.ID, p.President, t.Year, t.Month
      FROM Presidents AS p,
           (SELECT Year, Month 
            FROM DateRange sub
            GROUP BY Year, Month)  AS t
      WHERE t.Year BETWEEN Year(p.StartDate) AND Year(p.EndDate)
      AND DateSerial(t.Year, t.Month, 1) <= p.EndDate)  AS q
WHERE q.Year <= 2009
GROUP BY q.ID, q.President
ORDER BY q.ID
PIVOT (q.Year & ' ' & q.Month) IN ("1989 1", "1989 2", "1989 3", "1989 4", "1989 5", 
                                   "1989 6", "1989 7", "1989 8", "1989 9", "1989 10",
                                   "1989 11", "1989 12", "1990 1", "1990 2",  "1990 3",
                                   "1990 4", "1990 5", "1990 6", "1990 7", "1990 8", 
                                   "1990 9", "1990 10", "1990 11", "1990 12",  
                                   ...
                                   "2008 1", "2008 2", "2008 3", "2008 4", "2008 5", 
                                   "2008 6", "2008 7", "2008 8", "2008 9", "2008 10", 
                                   "2008 11", "2008 12", "2009 1", "2009 2", "2009 3", 
                                   "2009 4", "2009 5", "2009 6", "2009 7", "2009 8", 
                                   "2009 9", "2009 10", "2009 11", "2009 12");