How to subtract current value by a previous in Ms Access query

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Hi
(1)      I have a simple select query below, now what I want is to have the cumulative balances by employee under cumtotal
(2)      A moving value difference which is achieved by subtracting the cumulative value in the month of Feb by Jan, example see below:
                 
     
Emp      Mth       Sal          Cum      Diff
James      Jan      5,000      5,000      0
              Feb  5,000      10,000      5,000
Beth      Jan      4,500      4,500      0
              Feb  4,500      9,000      4,500
Annie      Jan      3,500      3,500      0
              Feb  3,500      7,000      3,500
Chris      Jan      3,650      3,650      0
              Feb      3,650      7,300      7,300

I do not want to use the domain function, I have enough of poor performance, I understand the answer to this are sub queries, this is where I’m trapped, I real need your help. Our pay as you earn works on cumulative basis, that is the reasons why I need this.

I will appreciate if can be done, unfortunately access does not work like excel, otherwise this is very thing in excel.

See actual query:

SELECT tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, Sum(([tblSalaries].[Gross])) AS CumTotal, 0 AS Diff
FROM (tblstaff INNER JOIN tblpayslips ON tblstaff.Id = tblpayslips.EmployeeName) INNER JOIN tblSalaries ON tblstaff.Id = tblSalaries.Id
GROUP BY tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, 0
ORDER BY tblstaff.Id, tblpayslips.SalaryDate;

Open in new window



payrollcum.png
Regards

Chris
FinTest.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Take a look at my attachment (query  :q2)
I don't think you can get around without Domain Aggregation
FinTest.accdb
There is still an error undefined DSUM.

I know someone here talked about sub queries as a replacement for the domain functions.
John TsioumprisSoftware & Systems Engineer

Commented:
Correction...missed the diff
FinTest.accdb
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

John TsioumprisSoftware & Systems Engineer

Commented:
Strange about DSUM
The output of Q2
Clipboard01.jpg
See attached screen shoot



payrollcum.png
John TsioumprisSoftware & Systems Engineer

Commented:
If Dsum is erroring out then you have some issue with your Access....Dsum is a builtin function.. switch to VBA and check about Dsum if it works...
I'm using a brand new access 2016 , I have never had any problems with Dsum or Dlookup in some forms I'm using it , no issue at all.
John TsioumprisSoftware & Systems Engineer

Commented:
Take the SQL of the query and rewrite it...maybe something in the regional settings...
John TsioumprisSoftware & Systems Engineer

Commented:
SELECT QrySalaries.Id, QrySalaries.FirstName, QrySalaries.LastName, QrySalaries.JobTitle, QrySalaries.SalaryDate, QrySalaries.Gross, DSum("Gross","qrysalaries","ID = " & [qrysalaries]![ID] & " AND SalaryDate  <=#" & [qrysalaries]![SalaryDate] & "#") AS CumTotal, [Gross]-DSum("Gross","qrysalaries","ID = " & [qrysalaries]![ID] & " AND SalaryDate  <=#" & [qrysalaries]![SalaryDate] & "#") AS Diff
FROM (SELECT tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, DSum("Gross","tblSalaries","ID = " & [tblstaff]![ID] & " AND SalaryDate  <=#" & [tblpayslips]![SalaryDate] & "#") AS CumTotal
FROM (tblstaff INNER JOIN tblpayslips ON tblstaff.Id=tblpayslips.EmployeeName) INNER JOIN tblSalaries ON tblstaff.Id=tblSalaries.Id
GROUP BY tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross
ORDER BY tblstaff.Id, tblpayslips.SalaryDate) QrySalaries
GROUP BY QrySalaries.Id, QrySalaries.FirstName, QrySalaries.LastName, QrySalaries.JobTitle, QrySalaries.SalaryDate, QrySalaries.Gross, [Gross]-DSum("Gross","qrysalaries","ID = " & [qrysalaries]![ID] & " AND SalaryDate  <=#" & [qrysalaries]![SalaryDate] & "#");

Open in new window

Distinguished Expert 2017

Commented:
Why do you need to do this in a query?  This is straightforward to do in a report and efficient since in a report, the recordset is processed in a linear fashion.  You would use the Format event of the detail section to do the arithmetic and keep the running sum.  You would also need code in the group header event to clear the running sum.  I haven't done this in a while so I'm a little rusty.  I think you also need code in the Retreat event of the detail section to undo the last calculation so it won't be duplicated.  Apparently Access formats a print detail line before it knows whether or not it will fit on the current page.  I the line won't fit, the retreat event runs, the page break logic runs to print the page footer and the page header.  Any repeating group headers are printed and then the detail line that wouldn't fit on the previous page goes through the format event again.
The secrete here when using the Domain functions is ensure that the following are followed:

(1) Never use the domain function inside a query , it will make the query run very slowly like a snail
(2) Use the domain functions to grab the required figure from the report
(3) Ensure that all the tables are properly indexed
(4) Avoid using TEXT as criteria

Looks at the query it takes only about 2 seconds to populate the pay slip report even if the actual has heavy calculation, see below:

SELECT tblEmployees.EmpID, tblEmployees.Fname, tblEmployees.Lname, tblEmployees.StaffNumber, tblEmployees.JobTitle, tblEmployees.SocialSecurity, tblEmployees.NRC, tblEmployees.PayMethod, tblEmployees.BankName, tblEmployees.AccountCode, tblEmployees.PaymentType, tblSalaries.BasicSalary, tblSalaries.HousingAllowance, tblSalaries.TransportAllowance, tblSalaries.LunchAllowance, tblOtherSalary.TTDate, tblOtherSalary.XMasBonus, tblOtherSalary.OtherBonuses, tblOtherSalary.Gratuity, tblOtherSalary.LeavePay, tblOtherSalary.OtherArears, tblOtherSalary.Overtime, tblOtherSalary.DoubleTime, tblOtherSalary.OtherIncentive, tblOtherSalary.StaffLoans, tblOtherSalary.OtherDeductions, tblOtherSalary.Advances, tblNAPSA.Rate, tblNAPSA.CelingRate, tblNAPSA.NAPSAFree, tblTaxation.FreeTax, tblTaxation.BandOne, tblTaxation.BandTwo, tblTaxation.TaxRateone, tblTaxation.TaxRateTwo, tblTaxation.TaxRateThree, tblTaxation.EducationLevy, (Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0)) AS Gross, IIf(((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))>[NAPSAFree],[NAPSAFree],((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))) AS Pension, (Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))-(IIf(((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))>[NAPSAFree],[NAPSAFree],((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate])))) AS Taxable, IIf(((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))>[CelingRate],[CelingRate],((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))) AS HHPension, tblPayslip.Closures
FROM ((((tblEmployees INNER JOIN tblSalaries ON tblEmployees.EmpID = tblSalaries.EmpID) INNER JOIN tblPayslip ON tblSalaries.SalarID = tblPayslip.SalarID) INNER JOIN tblOtherSalary ON tblPayslip.IDPAY = tblOtherSalary.IDPAY) INNER JOIN tblNAPSA ON tblPayslip.NAPSAID = tblNAPSA.NAPSAID) INNER JOIN tblTaxation ON tblPayslip.TaxID = tblTaxation.TaxID
WHERE (((tblEmployees.EmpID)=[Forms]![frmpayslipsprint]![CboFirstName]) AND ((tblOtherSalary.TTDate) Between Format(([Forms]![frmpayslipsprint]![txtSalaryStartDate]),"yyyy\/mm\/dd") And Format(([Forms]![frmpayslipsprint]![txtSalaryEnddate]),"yyyy\/mm\/dd")) AND ((tblPayslip.Closures) Is Null)) OR (((tblPayslip.Closures)<>"2"));

Open in new window



Regards

Chris
John TsioumprisSoftware & Systems Engineer

Commented:
I don't see any substraction here...is this from another question...???
The code i gave you works fine...if you have issues with Dsum probably is due to a bad reference or something else..
Okay

These are done within the report.

Regards

Chris
John TsioumprisSoftware & Systems Engineer

Commented:
The question was about "query" and i think i answered it..
PatHartman

Why do you need to do this in a query?  This is straightforward to do in a report and efficient since in a report, the recordset is processed in a linear fashion.

Our PAYE is calculated on a cumulative basis not direct proportional , that is why you no choice but to do it this way.

Regards

Chris
Distinguished Expert 2017

Commented:
Our PAYE is calculated on a cumulative basis not direct proportional , that is why you no choice but to do it this way.
What are you doing with the query?  Is it the recordsource for a report?  a form?  are you using VBA to do something with the query?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial