Using MS Access/Microsoft Query
I have been asked to produce a query on ms access, and I'm a bit stuck. the query will be used to feed a section on a form and report.
What I am trying to do is an attendance percentage for the year based on sickness hours lost against an annualised contracted hours.
There are 2 tables in question Employees and sickness.
I can currently have the query show me the Employee details with the count of sickness records and sum of hours lost as a summary, however anytime i add another column to the query it then splits it up to show the individual records.
The current setup;
The query uses the above fields hours lost is set to sum and then there is an expression field for counting the records.
i need to add another field for the attendance percentage to show as 100% but then decrease as the person has more sickness.
I think there was a typo in your post. The second table, where Hours Lost is, would be table 2. Storing hours lost in a table is not the best idea. You should have a record for each event and then calculate the hours lost.
I'm also not clear whether you are trying to calculate the percentage for each employee or for all employees,
But the way I would do this is to create a query that sums the hours lost per employee. Then join that to your Employees table and add the Employee info, the annualized contract hours and the sum of hours lost. From there its easy to add a calculated column to divide hours lost by total hours.
If you need to do this for all employees use DSums() to calculate all hours and Hours lost in a division expression.