Solved

Microsoft Access Query - SQL Help For Cumulative Sum

Posted on 2016-07-15
17
76 Views
Last Modified: 2016-07-15
Hi,
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.

Thanks!

Brett
0
Comment
Question by:Bcn78
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41712727
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
0
 

Author Comment

by:Bcn78
ID: 41712839
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
0
 
LVL 39

Expert Comment

by:als315
ID: 41712901
Do you like to see all months with cumulative salary or only one, which can be selected on some form?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 35

Expert Comment

by:PatHartman
ID: 41712905
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
1
 

Author Comment

by:Bcn78
ID: 41712907
@als315 - all months
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41712925
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;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41712941
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;
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 250 total points
ID: 41712942
Look at sample. I've changed names in table form Year to Tyear and Month to Tmonth, because it not good idea to use reserved words in column names.
Query:
SELECT tblMonthlySalary.[Employee ID], tblMonthlySalary.MonthEndDate, tblMonthlySalary.TYear AS Y, tblMonthlySalary.TMonth AS M, tblMonthlySalary.MonthlySalary, DSum("[MonthlySalary]","tblMonthlySalary","[Employee ID] = " & [Employee ID] & " and TYear = " & [TYear] & " and TMonth <= " & [TMonth]) AS CumSalary
FROM tblMonthlySalary;

Open in new window

DBSalary.accdb
1
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41712950
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;
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41712984
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

Open in new window

0
 

Author Comment

by:Bcn78
ID: 41713097
@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
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41713113
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...
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 41713125
@brett

fields Month, Year and EmployeeNumber should be "NUMBER" data type.

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
ORDER BY tblMonthlySalary.Year;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41713143
@Ferruccio Accalai,
your query will work if the salary will remain fix through out the year.
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41713155
@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
0
 

Author Comment

by:Bcn78
ID: 41713163
@ 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.
0
 

Author Closing Comment

by:Bcn78
ID: 41713165
Thank you!!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can you open the FORM2 2 32
Modal form 11 30
Setting Macro in Access to Automate Running an Append at a Certain Time 2 22
Advice on Listbox or Listview usage 3 13
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question