Need a query to remove min values from empid

[Guest]

I need to create a pension report from a file. unfortunately the maximum
number of hrs an employee can have is 160. The problem is the employyes may
work in several different departments. I need to remove the depts with the
lowest hrs and then have the remianing dept hrs totals cap at 160. Any
suggestions on how to proceed will be welcome.

 

[Jeff Boyce]

I'm confused. If an employee had 5 departments, with 40 hours in 4 and 39
in the fifth, wouldn't that employee's total hours ("maximum number of hrs
an employee can have") exceed 160?

One way, if you need to find the smallest number of hours, would be to
create a "totals" query and use the "Minimum" to identify it.

To handle a "cap" of 160, you could use an IIF() statement in a query based
on a totals query that SUM'd the hours (this could be a "chain" of queries).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John Spencer]

I'm not sure how to do exactly what you requested. I think we would need
some more detail.

HOWEVER, if all you need to to do is report a maximum of 160, why not use
something like:

IIF(SUM([YourField])>160,160,SUM([YourField]))

Of course, that fails if you need to report the departments or to
permanently remove the hours from the database.