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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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
 
 
Start new topic
Replies
RJD
post Aug 5 2019, 03:38 AM
Post#2


UtterAccess VIP
Posts: 10,087
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,087
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,087
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 11:50 AM