UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Summary Report By Employee    
 
   
Makya
post Aug 5 2019, 03:33 AM
Post#1



Posts: 18
Joined: 11-July 19



MS Access 2016 -

I have an Employee Payslip Query. Fields I needs Info from: EmpID, PayDate, ProvFund1, UIF, Provfund2, Union. Wages paid Fortnightly.

I need a summary report (monthly)with headings as follows: EmpID, ProvFund1, UIF, Provfund2, Union. Query Parameter: Paydate:Start Date to End Date.

All is fine but in the report I get a Line for all Paydates for every employee eg.

001, Elliot 100.00, 5.00, 250.00, 11.00
001, Elliot 100.00, 5.00, 250.00, 11.00

instead of:

Elliot 200.00, 10.00, 500.00, 22.00 which is the sum of above. I am not sure exactly how to get a summary by Employee only. Query with one or other if formula for EmpID or report and hide fields?????

Go to the top of the page
 
RJD
post Aug 5 2019, 03:38 AM
Post#2


UtterAccess VIP
Posts: 10,027
Joined: 25-October 10
From: Gulf South USA


Hi: Use a Totals query, Group By on the first field, Sum on the others.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Makya
post Aug 5 2019, 06:09 AM
Post#3



Posts: 18
Joined: 11-July 19



Thanx that is what I initially did, but It did not work. I took the date field out now and then it is fine. Just need to do something now to be able to create a report based on a start to end date.
Go to the top of the page
 
RJD
post Aug 5 2019, 08:47 AM
Post#4


UtterAccess VIP
Posts: 10,027
Joined: 25-October 10
From: Gulf South USA


Hi: Unfortunately, "It did not work" really doesn't tell us anything so that we can help. So I made a demo, using your data and some assumed pay dates. Compare that to what you did.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT EmpID, Sum(tblEmployeePay.ProvFund1) AS SumOfProvFund1, Sum(tblEmployeePay.UIF) AS SumOfUIF, Sum(tblEmployeePay.ProvFund2) AS SumOfProvFund2, Sum(tblEmployeePay.Union) AS SumOfUnion
FROM tblEmployeePay
WHERE PayDate Between [Start Date] And [End Date]
GROUP BY EmpID;

And see the report and how the date range is shown even though the dates are not SELECT fields in the query. Use 7/1 and 7/31 as the prompted start and end dates.

HTH
Joe
Attached File(s)
Attached File  SummaryReportByEmployee.zip ( 22.06K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Makya
post Aug 5 2019, 02:45 PM
Post#5



Posts: 18
Joined: 11-July 19



I removed the Paydate Field from the query and entered a StartDate and EndDate and then followed your instructions and it worked perfectly. It seemes that the Paydate field(Group By) prevented the Summary.

I am learning a lot. smile.gif Thanks to you guys


Go to the top of the page
 
RJD
post Aug 5 2019, 02:59 PM
Post#6


UtterAccess VIP
Posts: 10,027
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are all always happy to help.

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 07:39 PM