Microsoft Access Query - SQL Help For Cumulative Sum
Posted on 2016-07-15
Apologies as I am an accountant by trade, but am trying to sharpen my access/sql skills to build out better forecasting/planning capabilities for my company.
I currently have a table with the below fields:
MonthEndDate: Monthly end dates for each month 2016 - 2018
Year: The corresponding Year in "yyyy" format for each of the month end dates
Month: The corresponding Month in "m" format for each of the month end dates
Employee ID: unique identifier for ~200 employees
MonthlySalary: The amount of monthly salary paid to each of the employees, for each month
For tax purposes, I'm trying to calculate the cumulative salary for each employee, each year (I need the cumulative amounts per employee to start over each year in January). I've spent a solid 5-6 hours trying to solve this on my own using DSUM as well as trying to write a SQL query in access that inner joins the table called "tblMonthlySalary" to itself and sums the field MonthlySalary.
I'm not even getting close to getting this worked out, so figured I would ask the experts for some guidance.