title: Group by contiguous dates in MS Access
body: <p>I have a table in our database keeps track of all our teams, their name, their lead and to which team (and manager) they are hierarchically subordinate. There will be a row for each day/team combination.</p>
  
  <pre><code>DAY         | TEAM    | MANAGER | PARENT_TEAM | PARENT_MANAGER
  01/01/2017  | TeamRed | John    | GroupWendy  | Wendy
  ...
  05/01/2017  | TeamRed | John    | GroupKarl   | Karl
  ...
  10/05/2017  | TeamRed | John    | GroupWendy  | Wendy
  </code></pre>
  
  <p>Now, my task is to group this data to create a "historical" report. Something like this:</p>
  
  <pre><code>TEAM    | MANAGER | PARENT_TEAM | PARENT_MANAGER | VALID_FROM | VALID_TO
  TeamRed | John    | GroupWendy  | Wendy          | 01/01/2017 | 04/01/2017
  TeamRed | John    | GroupKarl   | Karl           | 05/01/2017 | 10/05/2017
  TeamRed | John    | GroupWendy  | Wendy          | 11/05/2017 | 31/12/2099
  </code></pre>
  
  <p>Normally I would proceed by grouping by the string fields and setting the dates to min and max. The catch that I cannot overcome is that a team can "jump" back and forth from a parent team, and I hope the example reflects this.</p>
  
  <p>using</p>
  
  <pre><code>SELECT team, manager, parent_team, parent_manager, MIN(day) AS valid_from, MAX(day) AS valid_to
  FROM tblMock
  GROUP BY team, manager, parent_team, parent_manager;
  </code></pre>
  
  <p>would not produce the result above (desired) but rather</p>
  
  <pre><code>TEAM    | MANAGER | PARENT_TEAM | PARENT_MANAGER | VALID_FROM | VALID_TO
  TeamRed | John    | GroupWendy  | Wendy          | 01/01/2017 | 31/12/2099
  TeamRed | John    | GroupKarl   | Karl           | 05/01/2017 | 10/05/2017
  </code></pre>
  
  <p>Thank you to whomever might help and final disclaimer, hope this was not asked before, but the search I performed did not really help.
  Cheers,
  Igor</p>