query based on minimum amount across several fields

[gvanhook]

I am an access neophyte and I am having difficulties creating a query to get the data I need. Here is the set-up. I have a table with hours that employees have worked. The rows contain fields for the employees name, their job title, the hourly pay rate, the fiscal year, the pay period, and then four fields that represent the hours worked in the pay period (WK1, Wk2, OT1, OT2). There is a row for each employee, pay period, job title combination (some of my staff work multiple jobs during the week). The data goes back to 2008.

The task I have been given is to come up wih a list of historical pay rates based on the position and the employee. I was able to do this by linking to a pivot table, but now the user has asked me to limit the data so that they only see the name listed if the employee worked more than 10 hours in that position during the fiscal year. This is where I get stuck. How do I tell the query to only give me rows if the total of all 4 hours columns is greater than 10 in a fiscal year?

Thanks in advance for any assistance provided.

[HansV]

Create a query based on the table.
Add the employee and fiscal year fields, and a calculated column TotalHours: [WK1]+[WK2]+[OT1]+[OT2]
Change it to a totals query, and change the Total option for the TotalHours column to Sum.
Enter the condition >10 in the Criteria row of the TotalHours column.
This query will give you the employees who worked more than 10 hours in a fiscal year.
Save this query.

Next, create a new query based on the table and on the query that you just saved. Join them on the employee and fiscal year fields.
You should now be able to proceed as before, except that the join will ensure that you limit the employee/fiscal year combination.