Microsoft Access Query - SQL Help For Cumulative Sum

try this query

select  [Employee ID], sum([MonthlySalary]), [Year]
from tblMonthlySalary
group by [Employee ID],[Year]

post back if this is good enough for you

Thanks Rey,

This works for an annual total, but I need the cumulative totals by month. For example, if an employee makes $120K per year. I need the cumulative total in June to be $60K. When I add "monthenddate" to the query above the sum([monthlysalary]) AS CumulativeTotal just returns the monthly amount for each month (it does not cumulatively sum the prior months). For example, monthenddate 6/30/2017 returns $10K instead of $60K.

Any thoughts on how to make this happen?

Thanks.

Brett

Do you like to see all months with cumulative salary or only one, which can be selected on some form?

This is simple and straight forward to do in a report because a report is a linear process and so it this.  Queries are set operations and so you have to work to make this happen.

In a report, add a control in the appropriate section bound to the salary field and set it's RunningSum Property (on the Data tab) to Over Group.  If the detail section contains one row for each month, the first salary column will show the salary for that month, the second one - with the RunningSum property will show the running sum.

If you do this in a query, you need to use a query that joins the salary table to itself using a non-equi join.

Select a.Employee, a.PayDate, a.Salary, Sum(b.Salary) as RunningSum
From tblEmployee  Left Join tblEmployee as b On a.Employee = b.Employee AND a.PayDate >= b.PayDate
Where a.PayYear = Forms!yourform!SelectYear and b.PayYear = Forms!yourform!SelectYear
Group by a.Employee, a.PayDate, a.Salary

@als315 - all months

try this

SELECT tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary, DSum("MonthlySalary","tblMonthlySalary","DatePart('m',[MonthEndDate])<=" & [Month] & " And  DatePart('yyyy', [MonthEndDate])<=" & [Year] & " And [Employee ID]=" & [Employee ID]) AS CumSalary
FROM tblMonthlySalary
GROUP BY tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary;

include a sorting by year

SELECT tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary, DSum("MonthlySalary","tblMonthlySalary","DatePart('m',[MonthEndDate])<=" & [Month] & " And  DatePart('yyyy', [MonthEndDate])<=" & [Year] & " And [Employee ID]=" & [Employee ID]) AS CumSalary
FROM tblMonthlySalary
GROUP BY tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary
ORDER BY tblMonthlySalary.Year;

to filter by year

SELECT tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary, DSum("MonthlySalary","tblMonthlySalary","DatePart('m',[MonthEndDate])<=" & [Month] & " And  DatePart('yyyy', [MonthEndDate])<=" & [Year] & " And [Employee ID]=" & [Employee ID]) AS CumSalary
FROM tblMonthlySalary
GROUP BY tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary
HAVING (((tblMonthlySalary.Year)=[Enter Year]))
ORDER BY tblMonthlySalary.Year;

typo error correction


SELECT tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary, DSum("MonthlySalary","tblMonthlySalary","DatePart('m',[MonthEndDate])<=" & [Month] & " And  DatePart('yyyy', [MonthEndDate]) =" & [Year] & " And [Employee ID]=" & [Employee ID]) AS CumSalary
FROM tblMonthlySalary
GROUP BY tblMonthlySalary.[Employee ID], tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary
HAVING (((tblMonthlySalary.Year)=[Enter Year]))
ORDER BY tblMonthlySalary.Year;

I guess your query should be like this

SELECT [Employee ID], (MonthlySalary*[Month]) AS ProgressiveSalary, MonthEndDate
FROM tblMonthlySalary
Group BY [Employee ID],MonthEndDate,[Month],[Year],(MonthlySalary*[Month])
Order By [Employee ID],MonthEndDate

Select allOpen in new window

@Rey and @ALS. The cumulative calculation is showing #Error because of what appears to be a data type mismatch. I tried to trouble shoot this by running an update query to update [Month] to be month([MonthEndDate]) and year to be year([MonthEndDate]) in both short text and number formats. Neither of these seemed to resolve the issue however.

EmployeeNumber (not Employee ID; my apologies) is in short text format because our open requisitions contain alpha numeric codes (eg: R1).

MonthlySalary is in number format

I've tried the below queries for each of your solutions but seem to be running into the same issue (#Error):

Rey:
SELECT tblMonthlySalary.EmployeeNumber, tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary, DSum("MonthlySalary","tblMonthlySalary","DatePart('m',[MonthEndDate])<=" & [Month] & " And  DatePart('yyyy', [MonthEndDate])<=" & [Year] & " And EmployeeNumber=" & EmployeeNumber) AS CumSalary
FROM tblMonthlySalary
GROUP BY tblMonthlySalary.EmployeeNumber, tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary;

ALS:
SELECT tblMonthlySalary.EmployeeNumber, tblMonthlySalary.MonthEndDate, tblMonthlySalary.Year, tblMonthlySalary.Month, tblMonthlySalary.MonthlySalary, DSum("MonthlySalary","tblMonthlySalary","EmployeeNumber = " & EmployeeNumber & " and Year = " & Year & " and Month <= " & Month) AS CumSalary
FROM tblMonthlySalary;

Any thoughts would be much appreciated. Thanks for all your help. Apologies again.

brett

As your table already contains fields with the year and month values why are you using the datepart function?
If you look at the query posted above by me there you have simply to add a WHERE criteria.

You have a monthly salary value and the month and year value, so you have simply to muultiply your monthly salary for the month value to know the progressive salary at that month, all grouped year by year...

@Ferruccio Accalai,
your query will work if the salary will remain fix through out the year.

@Rey
Well, looking at the inputs I didn't see any request about salary changes during the same year but thinking about it yes, your right

@ Rey. Thank you!. This worked. I obviously never could have solved this on my own. I'm trying to understand why EmployeeNumber needs to be Number format and cannot be text?

Thanks again.

Thank you!!