Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Q & A - Forums _ Access Summary Report By Employee

Posted by: Makya Aug 5 2019, 03:33 AM

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?????

Posted by: RJD Aug 5 2019, 03:38 AM

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


Posted by: Makya Aug 5 2019, 06:09 AM

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.

Posted by: RJD Aug 5 2019, 08:47 AM

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]

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.

Joe ( 22.06K ): 2

Posted by: Makya Aug 5 2019, 02:45 PM

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

Posted by: RJD Aug 5 2019, 02:59 PM

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

Continued success ...