Query to calculate work hours

[Rhayzor]

Good Day people,

I am new to Access. I only learned by watching another person create a database in Access 2010. We are now trying to add improvements to that database. Please bear with me if I ask simple questions.

We are trying to find a way to have a query figure out when the hours of an employee reach 40 hours (Overtime) and figure the hours correctly. The Data Entry table contains the basic fields:

[Workdate].....The date the work was done
[WEDate].......The Week Ending Date. Always on Sunday, and calculated like this "[WorkDate]-Weekday([WorkDate]-1)+7"
[Employee].....Employee Name
[Qty].............# of hours worked that day
[RegHours].....Blank, to be updated by query
[OTHours]......Blank, to be updated by query

The problem we are running into is getting it to update the daily running totals (using the [WEDate] & [employee] as the "Group Bys"). We'd like use an update query to fill in the to blank fields with the correct calculations.

Example - Working 6 day week, at 9 Hours per day in the [Qty] field

Day.............[RegHours]......[OTHours]
Monday..............9..................0
Tuesday............18..................0
Wednesday........27..................0
Thursday...........36..................0
Friday...............40..................5
Saturday...........40.................14
Sunday.............40.................23

We have tried several things to get it to compute this field and haven't had much luck. Any help you can give would be appreciated.

:banghead:

 

[plog]

You shouldn't store calculated values. That means, WEDate shouldn't be a field in your table, nor should RegHours and OTHours. These values will be calculated when you need them in a query.

To get to your final result, you will need 2 subqueries (you could do it in 1 subquery, but it would be pretty complex). You didn't include your table's name, so replace every instance of 'YourTableNameHere' in all of the below code with the name of your actual table. This is the SQL of the first subquery:

SELECT YourTableNameHere.Workdate, [WorkDate]-Weekday([WorkDate]-1)+7 AS PayPeriodEnd, YourTableNameHere.Employee, YourTableNameHere.Qty
FROM YourTableNameHere;

Name that 'sub_PayPeriodHours_1'. It just calculates the Week ending date for every record which will allow us to create a running total query. This is the SQL for the second subquery:

SELECT sub_PayPeriodHours_1.Employee, sub_PayPeriodHours_1.Workdate, sub_PayPeriodHours_1.PayPeriodEnd, sub_PayPeriodHours_1.Qty, DSum("[Qty]","sub_PayPeriodHours_1","[PayPeriodEnd]=#" & [PayPeriodEnd] & "# AND [Workdate]<=#" & [Workdate] & "# AND [Employee]='" & [Employee] & "'") AS TotalHours
FROM sub_PayPeriodHours_1;

Name that query 'sub_PayPeriodHours_2'. It creaties runnint total of a total hours worked in a pay period. It doesn't break it out into Regular and OT, that's what this query is for:

SELECT sub_PayPeriodHours_2.Employee, sub_PayPeriodHours_2.Workdate, sub_PayPeriodHours_2.PayPeriodEnd, sub_PayPeriodHours_2.Qty, IIf([TotalHours]>40,40,[TotalHours]) AS RegHours, IIf([TotalHours]>40,[TotalHours]-40,0) AS OTHours
FROM sub_PayPeriodHours_2;

That is the query that will give you what you want.