Access 2010-查询显示多个记录的运行总计，删除旧记录并在每行上添加新记录 [英] Access 2010 - query showing running total for multiple records, dropping old record and adding new record on each line
问题描述
我有一个Access数据库，该数据库跟踪每个员工在每个薪酬期内使用的小时数.
I have an Access database which is tracking hours used by each employee in each pay period.
我需要一个查询，以使我能够及时回顾并查看仅最近25个付款期的总金额，该金额是根据以下情况的每个前26个薪酬期，每位员工.这是一个滚动的总计，从本质上讲，每行要忽略26个工资期(或更长时间)的记录.我有五年的记录，很遗憾，必须保留所有记录.了解每个员工的26个薪资运行期总计是多少，以及在哪个工作区中使用的小时数，这一点非常重要.特定的付款期限.
I need a query that will allow me to look back in time and see what the total was for just the last 25 pay periods, as calculated from each of the previous 26 pay periods, for each employee. 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.
基本上，我们查看的是每位雇员的 51 条记录-该查询实际显示的第一个工资期显示，但是，该记录为 26 >付款期之前；它会根据该支付期内的实际情况加上之前的25个记录来累计.
Basically, we're looking at 51 records total for each employee - the first pay period the query actually shows, however, is 26 pay periods ago; it has a running total based on what happened in that pay period plus the previous 25.
表格:
HoursID ， EmployeeID ， PayPeriodID ， HoursUsed
PayPeriodID ， PayPeriodEndDate
Tables:
HoursID, EmployeeID, PayPeriodID, HoursUsed
PayPeriodID, PayPeriodEndDate
查询结果(已经开始工作):
EmployeeID ， TotalOfHoursUsedLast26PP (此查询请求日期作为倒计时)
Query Results (already working):
EmployeeID, TotalOfHoursUsedLast26PP (this query requests a date to count back from)
查询我需要的结果
EmployeeID ， PayPeriodID ， HoursUsed ，[总工作时间，基于紧接该PP之前的25 PP ]
Query Results I Need:
EmployeeID, PayPeriodID, HoursUsed, [Total Hours Used, Based on the 25 PP immediately previous to this PP]
我一直在纠缠于DSum，但我不知道如何使它成为 只计算最后26个PP，一次计算一名员工所需的PP，每个付款期分别.
I've been messing around with DSum, but I can't figure out how to make it only count the last 26 PP, counting the PPs I need, for one employee at a time, for each pay period separately.
要明确:我需要能够回顾并在2009年的26个付款期中运行此查询；在Excel中，对于一位员工，我们做了如下所示的事情:
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:
Line32_HoursUsedLast26PP = Line32_HoursUsedThisPP + Line31_HoursUsedLast26PP-Line4_HoursUsedThisPP
Line32_HoursUsedLast26PP = Line32_HoursUsedThisPP + Line31_HoursUsedLast26PP - Line4_HoursUsedThisPP
结果类似于:
2011-5-338.8
2011-6-398.5
2011-7-428.7
2011-8-442.5
2011-9-451
2011-10-451
2011-11-451
2011-12-451
2011-13-451
2011-14-451
2011-15-451
2011-16-452.4
2011-17-453.1
2011-18-454.3
2011-19-454.3
2011-20-455
2011-21-456.1
2011-22-460.2
2011-23-480
2011-24-480
2011-25-480
2011-26-480
2012-01-453.2
2012-02-381.2
2012-03-301.2
2012-04-221.2
2012-05-141.2
2012-06-81.5
2012-07-51.3
2012-08-37.5
2012-09-29
2012-10-29
2012-11-29
2012-12-29
2012-13-29
2012-14-29
2012-15-29
2012-16-27.6
2012-17-26.9
2012-18-25.7
2012-19-25.7
2012-20-25
2012-21-23.9
2012-22-19.8
2012-23-0
2011-5 - 338.8
2011-6 - 398.5
2011-7 - 428.7
2011-8 - 442.5
2011-9 - 451
2011-10 - 451
2011-11 - 451
2011-12 - 451
2011-13 - 451
2011-14 - 451
2011-15 - 451
2011-16 - 452.4
2011-17 - 453.1
2011-18 - 454.3
2011-19 - 454.3
2011-20 - 455
2011-21 - 456.1
2011-22 - 460.2
2011-23 - 480
2011-24 - 480
2011-25 - 480
2011-26 - 480
2012-01 - 453.2
2012-02 - 381.2
2012-03 - 301.2
2012-04 - 221.2
2012-05 - 141.2
2012-06 - 81.5
2012-07 - 51.3
2012-08 - 37.5
2012-09 - 29
2012-10 - 29
2012-11 - 29
2012-12 - 29
2012-13 - 29
2012-14 - 29
2012-15 - 29
2012-16 - 27.6
2012-17 - 26.9
2012-18 - 25.7
2012-19 - 25.7
2012-20 - 25
2012-21 - 23.9
2012-22 - 19.8
2012-23 - 0
如您所见，运行总数上升，下降.
As you can see, the running total goes up and down.
这是我用于查找最近26个付款期间的总小时数"查询的实际代码；关于认证等问题的原因是，在任何给定的薪资期限内，员工可能会在多个认证下记录小时数.
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.
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;
如果不清楚，则此查询取决于一个称为Last_26_PP_From_Today的单独查询:
In case it's not clear, this query depends on a separate query, called Last_26_PP_From_Today:
SELECT List_Pay_Periods.PayPeriodID, List_Pay_Periods.PPEnd
FROM List_Pay_Periods
WHERE (((List_Pay_Periods.PPEnd)<Date() And (List_Pay_Periods.PPEnd)>(Date()-14)));
我还有一个查询，可以查询从任何特定日期开始的最近26个支付期:
I also have a query that switches to find the last 26 pay periods from any particular date:
PARAMETERS [Date] DateTime;
SELECT List_Pay_Periods.PayPeriodID, List_Pay_Periods.PPEnd
FROM List_Pay_Periods
WHERE (((List_Pay_Periods.PPEnd)<[Date] And (List_Pay_Periods.PPEnd)>([Date]-14)));
我在绝望中想到的蛮力方法涉及一系列级联的Make Table查询，那时我决定也许这里有人可以提供一些更清洁的方法.
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.
更新
多亏了Gord，我现在可以正常工作了，除了我看不到工资期"数字:
Thanks to Gord, I now have everything working except that I don't see the Pay Period numbers:
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;
那让我:
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
推荐答案
我希望这会为您指明正确的方向:
I hope this points you in the right direction:
我从一个名为[Hours_by_Empl_PP]的表开始，该表具有每个Pay_Period中每个员工的总工作时间.
I started with a table I called [Hours_by_Empl_PP] which has the total hours for each Employee in each individual Pay_Period.
EmployeeID PayPeriodID HoursUsed
1 1 3
1 2 7
1 3 4
1 4 4
1 5 5
1 6 8
1 7 5
2 1 1
2 2 2
2 3 1
2 4 0
2 5 0
2 6 1
2 7 2
如果表数据具有给定(雇员+ Pay_Period)的多个记录，则可以创建一个简单的聚合查询以总计值...
If your table data has multiple records for a given (Employee + Pay_Period) you can create a simple aggregation query to total up the values...
SELECT EmployeeID, PayPeriodID, SUM(HoursUsedToday) AS HoursUsed
FROM YourRawData
GROUP BY EmployeeID, PayPeriodID
...将那个另存为[Hours_by_Empl_PP]，并使用它代替表格.
...save that as [Hours_by_Empl_PP], and use that instead of a table.
下一步是创建一个查询，以汇总4周期窗口中的小时数. (为了方便检查工作，我选择了4个而不是26个.)此窗口包括当前的Pay_Period和前三个.
The next step was to create a query to sum up the number of hours over a 4-period window. (I chose 4 instead of 26 just to make it easier to check my work.) This window includes the current Pay_Period and the previous three.
SELECT hbep1.EmployeeID, hbep1.PayPeriodID,
(
SELECT Sum(hbep2.HoursUsed)
FROM Hours_by_Empl_PP hbep2
WHERE hbep2.EmployeeID=hbep1.EmployeeID
AND (hbep2.PayPeriodID Between hbep1.[PayPeriodID]-3 And hbep1.[PayPeriodID])
) AS Sum_of_Hours_last_4_PPs
FROM Hours_by_Empl_PP hbep1;
这个查询给了我
EmployeeID PayPeriodID Sum_of_Hours_last_4_PPs
1 1 3
1 2 10
1 3 14
1 4 18
1 5 20
1 6 21
1 7 22
2 1 1
2 2 3
2 3 4
2 4 4
2 5 3
2 6 2
2 7 3
请注意，该查询假定PayPeriodID值是连续的并按时间顺序排列.您可以使用[PayPeriodEndDate]值获得相同的结果，但这将意味着另一个JOIN，我想使示例尽可能清晰.
Note that the query assumes that PayPeriodID values are sequential and in chronological order. You could achieve the same result using [PayPeriodEndDate] values, but that would mean another JOIN and I wanted to keep the example as clear as possible.
就是这样，真的.您只需使用[Hours_by_Empl_4PP_window]之类的名称保存该查询，并在查询中使用它即可在其他表上联接(用于Employee名称等)，然后从那里获取.
That's it, really. You can just save that query with a name like [Hours_by_Empl_4PP_window] and use it in your queries to JOIN on other tables (for Employee name, etc.) and take it from there.
这篇关于Access 2010-查询显示多个记录的运行总计，删除旧记录并在每行上添加新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
