Creating salary slips – how to list all employees plus reasons for absence. (1 Viewer)

[Ajabu]

I would very much appreciate some help. I want to create payslip reports for each employee. In addition to all the expected information I also want to include information showing the reason for any absences. I’m having problems listing the absence comments.

I’ve created a query (qMonAttendanceSum) which shows absences and includes those who worked but were not absent:

Previously I was struggling to include all employees in the results: those without absences and those with. I've sorted that.

But now I want to show the reasons for each absence as I want to include this information in the salary report slips.

In other words, I need to develop a query which gives me something like this:

Any advice would be appreciated. I’ve attached a copy of the database which may help.

 

[Gasman]

You could use Allen Brownes Concat function.

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

The DBguy also has a similar function.

Code Snippets

 

[CJ_London]

surely you need 1 row for each employee/month? So not sure how you are getting your result
So month 2, employee 2 should be showing 3 days leave and 4 days sick?


-

 

[Ajabu]

Yes indeed, the second table is not an actual result from Access; I created that in Excel. But that’s the result I need. In order to be able to give the REASON for the absence in the report, each absence needs to be listed separately if I understand things correctly. That’s also the reason why I don’t think concatenating is the way to do this. But I appreciate the links from Gasman. Thank you.

For a report, I believe I need to have this information about the employee: have they worked this month? If they have, have they been absent? If so, itemise the days and give the reason.

The query result has to include the employees who have worked but not been absent. And if someone has worked and had some absences, then there is no reason to have a duplicate entry for them: (1) they worked this month and (2) they were absent this day. I can easily extract the data for who was working on a given month or who was absent, but I’m struggling combining those two criteria.

I’m finding it difficult to explain the problem so maybe it might help if I show what I’d like the end report to look (the numbers are not included in the attached database but are simply given as examples). How do I amend my query to be able to get a report like this:

I can sort the query to get the one on the left, but how can I include and and separate the absences like on the right?

 

[Gasman]

How does 3 days sick equate to 80 and 1 day 40 ?

As you want them on seperate lines, then include sick leave with your deductions data.

 

[Ajabu]

The numbers are just dummy figures. You picked up a mistake which I didn't notice. Sorry about the confusion.

I believe I have included sick leave with deductions so not sure what you mean.

 

[Gasman]

The numbers are just dummy figures. You picked up a mistake which I didn't notice. Sorry about the confusion.

I believe I have included sick leave with deductions so not sure what you mean.

I mean as you bring in whatever deductions to the report at source, you also bring in the sick entries?

 

[Ajabu]

Thanks for your input Gasman.

I have not included any salary numbers in my sample database as any deductions are simply a calculation matter. I shouldn't have any problems doing that.

My problem is including the reasons for absence for each period of absence. When I include an absence column in my query, for example, I don't get the result like the image I posted above in my first post. So instead of the ten rows of summarised data showing those who worked and days/reasons for absences I get 29 rows of duplicated data which is useless:

My desired end point is a report like I pictured above. And if I can figure out how to create a query similar to the Excel table I attached above, I should be able to generate such a report.

 

[Ajabu]

This is another possible way around the problem. I can get this table with a query:

But how do I get rid of the double entries per month? Filtering out the “Worked this month” doesn’t work as it would also remove those employees who didn’t have any absences (Ben Gerard and Dave Black in January).