Running Sum Query Across Groups (cumulative)

[melinda_b]

I need help creating a query with a running sum (cumulative total) across two categories. I need to accumulate Wages by employee, by calendar year for every day/every job worked. I have a table containing over 33,000 records, the years span from 2009 to 2014 with multiple employees.

The Dsum option I have found on the internet is too time-consuming and locks up my query for the over 33,000 records I need to generate results for. It needs to be a SQL statement. Although all the SQL statement I have tried simply total all Wages for every entry ever made in the column, instead of per calendar year, per employee.

Here are my fields:
Calyear = Ascending
Employee Name = Ascending
WorkDate = Ascending
ID
ContractNo
Wages
Cumulative Wages

I need data to present as follows:

CalYear|Employee Name|WorkDate |ID |ContractNo|Wages|Cumulative
2014 |Employee 1 |1/2/2014 |5243|0123.01 |240 |240
2014 |Employee 1 |1/3/2014 |5244|0371.05 |150 |390
2014 |Employee 1 |1/3/2014 |5245|0275.05 |110 |500
2014 |Employee 1 |1/6/2014 |5246|0222.01 |210 |710

2014 |Employee 2 |1/2/2014 |5247|9999.99 |300 |300
2014 |Employee 2 |1/3/2014 |5248|0283.01 |350 |650
2014 |Employee 2 |1/6/2014 |5249|0123.01 |320 |970

There may be more than one entry per day per employee in a given year. This is so I can calculate certain payroll taxes which are based on cumulative wages amounts.

I have worked on this for three days. I would truly appreciate a useful answer.

Thank you.

 

[JHB]

...
The Dsum option I have found on the internet is too time-consuming and locks up my query for the over 33,000 records I need to generate results for.
...

And how much in time (minutes) is that? How much would be acceptable?

...
I need data to present as follows:

CalYear|Employee Name|WorkDate |ID |ContractNo|Wages|Cumulative
2014 |Employee 1 |1/2/2014 |5243|0123.01 |240 |240
2014 |Employee 1 |1/3/2014 |5244|0371.05 |150 |390
2014 |Employee 1 |1/3/2014 |5245|0275.05 |110 |500
2014 |Employee 1 |1/6/2014 |5246|0222.01 |210 |710

2014 |Employee 2 |1/2/2014 |5247|9999.99 |300 |300
2014 |Employee 2 |1/3/2014 |5248|0283.01 |350 |650
2014 |Employee 2 |1/6/2014 |5249|0123.01 |320 |970
...

Do you have some sample data from where the above comes?

 

[melinda_b]

As far as the minutes the Dsum option takes, I needed to walk away from my PC and come back to see results. To me, this would not be optimal for an on-the-fly report I need to run by either employee or job number. Others will be using it, as well. Other times, the Dsum query locked up completely.

The source data I use is another query based off of two tables, which performs a calculation of hours worked x pay rate. One table contains all hours worked by each employee by day, by job. The other table contains pay rates for each employee with start and end dates of each pay rate, taking into account pay raises or other modifications to pay rate as needed.

What would be the best way to show you my sample data? Attach the database itself?

 

[JHB]

What would be the best way to show you my sample data? Attach the database itself?

Yes I think so, but you must zip it because you haven't post 10 post yet.
And info in which query you have the "problem".

 

[melinda_b]

Actually, I have found my answer in the meantime. Thank you for being willing to help.

This is what I used:

SELECT OD.CalYear, OD.[Employee Name], OD.ID, OD.WorkDate, OD.ContractNo, OD.TotalTaxableWages, OD.TotalTaxableWages
(SELECT Sum([TotalTaxableWages]) FROM qryWagesOnly WHERE [Employee Name] = OD.[Employee Name] And [CalYear] = OD.[CalYear] And ID<=OD.ID) AS RunningSum
FROM qryWagesOnly AS OD
ORDER BY OD.CalYear, OD.[Employee Name], OD.ID;