Newbie needs help!

[paulreed]

I have a query which includes amongst others the following fields;
[ID] - the payroll number of the staff member
[worksick] - a checkbox (yes/no) indicating if the staff member is either at work or on sick leave on this day.
I need to create a report which lists [ID] numbers down the left column, the second column should include the number of days worked (yes), the third column is the number of days sick (no), and the forth is the percentage worked/sick.
Not all the employees work the same number of days per week.
I am not proficient in Access and would appreciate your help.

Thanks in anticipation

 

[WayneRyan]

Paul,

Your query needs to have a Group By clause for the employee
id field. You can then use the Sum function to sum up the
worked/sick days.

Get your query in design view, select the Sigma sign from the
tool bar. Then set employee id to group by and the others
to sum.

When you're happy with your query, base your report on it.

Wayne

 

[paulreed]

Wayne
I have tried your suggestion, but it just shows the difference between days worked/sick days, ie -2, -8, 0, etc.
Perhaps I am trying to acheive something beyond my capabilities and need to learn the basics, but thanks for your advice.

Paul

 

[WayneRyan]

Paul,

It sounds like you summed up the columns in each row. Can you
post a sample of your db?

Select EmployeeID, Sum(SickDays), Sum(WorkDays)
From   YourTable
Group By EmployeeID

Wayne

 

[paulreed]

Wayne

I have attached a simplified, depersonalised file, which I hope represents the problem.

Thanks for your support

Paul

 

[WayneRyan]

Paul,

You only have their sick days. It's going to be awful tough to
figure out how many hours they worked!

Wayne

 

[paulreed]

Wayne, I have not mentioned hours, as this database just calculates the number of days worked & the number of days sick.
So the resultant report would look like;

ID Days Worked Days Sick Percentage
11 100 50 33%
44 12 12 50%
66 93 0 100%

The idea is to identify those members of staff with the highest absence record, i.e. in the above case, ID 11, is at the top of the list with only 33% attendance - out of 150 days - took 50 sick days.