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;

Table 1
Employee Number
First Name
Last Name
Annualised House

Table 1
Hours Lost

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.

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.