[X]My Assistant

[Makya]

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

[RJD]

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

HTH
Joe

[Makya]

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.

[RJD]

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)

 SummaryReportByEmployee.zip ( 22.06K )Number of downloads: 3

 

[Makya]

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. Thanks to you guys

[RJD]

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

Continued success ...

Regards,
Joe