title: Access 2010 - query showing running total for multiple records, dropping old record and adding new record on each line
body: <p>I have an Access database which is tracking hours used by each employee in each pay period.  </p>
  
  <p>I need a query that will allow me to look back in time and see what the total was for <strong>just the last 25 pay periods</strong>, as calculated from <strong>each</strong> of the previous 26 pay periods, <strong>for each employee</strong>.  This a rolling total in which, essentially, records that are 26 pay periods (or more) older must be ignored on a per-line basis.  I have five years' worth of records, and it is unfortunately necessary to retain them all, and it is very important to be able to see what the 26-pay-period running total for each employee was, together with the hours used in a particular pay period.  </p>
  
  <p>Basically, we're looking at <strong>51</strong> records total for each employee - the first pay period the query actually <em>shows</em>, however, is <strong>26</strong> pay periods ago; it has a running total based on what happened in that pay period plus the previous 25.</p>
  
  <p><strong>Tables:</strong><br>
  <em>HoursID</em>, <em>EmployeeID</em>, <em>PayPeriodID</em>, <em>HoursUsed</em><br>
  <em>PayPeriodID</em>, <em>PayPeriodEndDate</em></p>
  
  <p><strong>Query Results (already working):</strong><br>
  <em>EmployeeID</em>, <em>TotalOfHoursUsedLast26PP</em> (this query requests a date to count back from)</p>
  
  <p><strong>Query Results I Need:</strong><br>
  <em>EmployeeID</em>, <em>PayPeriodID</em>, <em>HoursUsed</em>, [<em>Total Hours Used, Based on the 25 PP immediately previous to this PP</em>]  </p>
  
  <p>I've been messing around with DSum, but I can't figure out how to make it <em>only</em> count the last 26 PP, counting the PPs I need, for one employee at a time, for each pay period separately.</p>
  
  <p>To be clear: I need to be able to look back and run this query for 26 pay periods in 2009 or something; in Excel, for one individual employee, we did something that looks like this:</p>
  
  <p>Line32_HoursUsedLast26PP = Line32_HoursUsedThisPP + Line31_HoursUsedLast26PP - Line4_HoursUsedThisPP</p>
  
  <p>The results are something like:</p>
  
  <p>2011-5   -  338.8<br>
  2011-6   -  398.5<br>
  2011-7   -  428.7<br>
  2011-8   -  442.5<br>
  2011-9   -  451<br>
  2011-10  -  451<br>
  2011-11  -  451<br>
  2011-12  -  451<br>
  2011-13  -  451<br>
  2011-14  -  451<br>
  2011-15  -  451<br>
  2011-16  -  452.4<br>
  2011-17  -  453.1<br>
  2011-18  -  454.3<br>
  2011-19  -  454.3<br>
  2011-20  -  455<br>
  2011-21  -  456.1<br>
  2011-22  -  460.2<br>
  2011-23  -  480<br>
  2011-24  -  480<br>
  2011-25  -  480<br>
  2011-26  -  480<br>
  2012-01  -  453.2<br>
  2012-02  -  381.2<br>
  2012-03  -  301.2<br>
  2012-04  -  221.2<br>
  2012-05  -  141.2<br>
  2012-06  -  81.5<br>
  2012-07  -  51.3<br>
  2012-08  -  37.5<br>
  2012-09  -  29<br>
  2012-10  -  29<br>
  2012-11  -  29<br>
  2012-12  -  29<br>
  2012-13  -  29<br>
  2012-14  -  29<br>
  2012-15  -  29<br>
  2012-16  -  27.6<br>
  2012-17  -  26.9<br>
  2012-18  -  25.7<br>
  2012-19  -  25.7<br>
  2012-20  -  25<br>
  2012-21  -  23.9<br>
  2012-22  -  19.8<br>
  2012-23  -  0  </p>
  
  <p>As you can see, the running total goes up <em>and</em> down.</p>
  
  <p>This is the actual code I have for the "find total hours in the last 26 pay periods" query; the stuff about certifications, etc., is because in any given pay period, the employees may have hours recorded under multiple certifications.</p>
  
  <pre><code>SELECT List_Employees.ID, Sum(Items_Hours_Actually_Taken.HoursActual) AS SumOfHoursActual
  FROM (List_Pay_Periods INNER JOIN (List_Employees INNER JOIN (Items_Certifications INNER JOIN Items_Hours_Actually_Taken ON Items_Certifications.[CertificationID] = Items_Hours_Actually_Taken.[HoursActualCertificationID]) ON List_Employees.ID = Items_Certifications.[CertificationEmployeeID]) ON List_Pay_Periods.[PayPeriodID] = Items_Hours_Actually_Taken.[HoursActualPayPeriod]) INNER JOIN Last_26_PP_From_Today ON List_Pay_Periods.PayPeriodID = Last_26_PP_From_Today.PayPeriodID
  GROUP BY List_Employees.ID;
  </code></pre>
  
  <p>In case it's not clear, this query depends on a separate query, called Last_26_PP_From_Today:</p>
  
  <pre><code>SELECT List_Pay_Periods.PayPeriodID, List_Pay_Periods.PPEnd
  FROM List_Pay_Periods
  WHERE (((List_Pay_Periods.PPEnd)&lt;Date() And (List_Pay_Periods.PPEnd)&gt;(Date()-14)));
  </code></pre>
  
  <p>I also have a query that switches to find the last 26 pay periods from any particular date:</p>
  
  <pre><code>PARAMETERS [Date] DateTime;
  SELECT List_Pay_Periods.PayPeriodID, List_Pay_Periods.PPEnd
  FROM List_Pay_Periods
  WHERE (((List_Pay_Periods.PPEnd)&lt;[Date] And (List_Pay_Periods.PPEnd)&gt;([Date]-14)));
  </code></pre>
  
  <p>The brute-force method I thought of in desperation involved a cascading series of Make Table queries, which was when I decided perhaps someone here could offer something cleaner.  </p>
  
  <p><strong>Update</strong></p>
  
  <p>Thanks to Gord, I now have everything working except that I don't see the Pay Period numbers:</p>
  
  <pre><code>SELECT hbep1.Employee, hbep1.PP
  
  (
  
  SELECT Sum(hbep2.Hours) 
          FROM Hours_By_Employee_Per_PP AS hbep2
          WHERE hbep2.Employee=hbep1.Employee 
              AND (hbep2.PP Between hbep1.[PP]-25 And hbep1.[PP])
  
      ) AS SumLast26
  
  FROM Hours_By_Employee_Per_PP AS hbep1;
  </code></pre>
  
  <p>That gets me:</p>
  
  <pre><code>Employee    SumLast26
  2           12
  2           12
  2           12
  2           18
  2           91
  3           4
  3           8
  3           88
  3           168
  3           180
  5           15
  5           15
  5           15
  7           43
  7           61
  7           75
  7           97
  7           106
  7           121
  9           19
  9           0
  </code></pre>