title: Call parameter query from another query - Access SQL
body: <p>I have a table in MS Access which holds staff details (tblStaff):</p>
  
  <pre><code>| Employee ID    |  Employee Name |  Manager ID  |   Manager Name  |
  --------------------------------------------------------------------
  |     205147     |  Bobby Fischer |     201154   |   Alan Turing   |
  |     205442     |  Carl Sagan    |     204468   |   Nikola Tesla  |
  </code></pre>
  
  <p>I refresh this table with new data weekly and if any employees have changed manager, then they are archived in another table (tblArchiveStaff) along with the dates that that record was valid from and to.</p>
  
  <pre><code>| Employee ID | Employee Name | Manager ID| Manager Name | DateFrom| DateTo|
  ----------------------------------------------------------------------------
  |     205442  |  Carl Sagan   | 235663    |  Marie Curie |03/01/16|01/06/17|
  </code></pre>
  
  <p>I have written a parameter query (qryDatedStaffList) that will select records from either of these tables based on which ones were valid for a given date (<code>SelectedDate</code>)</p>
  
  <pre><code>SELECT 
      tblUnion.[Employee ID], 
      tblUnion.[Manager Name], 
      tblUnion.DateFrom, 
      tblUnion.DateTo
  FROM 
      (SELECT 
          ts.[Employee ID], 
          ts.[Manager Name], 
          DateAdd("d", 1, Nz(ta2.MaxDateTo, #01/02/16#)) AS DateFrom, 
          Date() AS DateTo
      FROM 
          tblStaff ts 
      LEFT JOIN 
         (SELECT 
          [Employee ID], 
          max(DateTo) AS MaxDateTo
      FROM 
          tblArchiveStaff
      GROUP BY 
         [Employee Number]) ta2 
          ON ts.[Employee Number] = ta2.[Employee Number]
  
      UNION ALL
  
      SELECT 
          ta.[Employee ID], 
          ta.[Manager Name], 
          ta.DateFrom, 
          ta.DateTo
      FROM 
          tblArchiveStaff ta) AS tblUnion
  WHERE
      SelectedDate Between [DateFrom] And [DateTo];
  </code></pre>
  
  <p>The above query works fine. </p>
  
  <p>I am now trying to write another query that will return any employees whose manager has remained the same but their second line manager (manager's manager) has changed. At this point I'm getting stuck. </p>
  
  <p>I'm guessing that I would need to enter the DateTo value of each record in tblChanges as the <code>SelectedDate</code> parameter in the qryDatedStaffList above but have no idea if this is possible? </p>
  
  <p>Worst case scenario, I'm sure I could achieve this with VBA but am trying to stick to a pure SQL solution for performance reasons. Could someone point me in the right direction? </p>
  
  <p>(p.s I am not necessarily asking anyone to write the query for me, I just want to know if there is a way to pass a value from a column in one query as the parameter in another query)</p>