Calculating Overtime and Saturday Rota

[foxykitty]

Hi,

How can I group in my qryMonthlyHours in SQL view by [EmployeeName], [WkComDate] and then sum [TotalHrs] done in that month?

I have another query - qrySaturdayRota

[ProjectCode],[WorkCode],[Sat],[PartNumber],[EmployeeName],[WkComDate][SumOfTotalHrs] ALL THIS IS GROUPED EXCEPT FOR [SumOfTotalHrs]

[PartNumber] has a criteria = Like "Saturday Rota" Or Like "Sat Rota" Or Like "Rota"

Another query - qrysummedmonthlyhours - so far I have

[EmployeeName],[SumOfTotalHrs],

I need this query to work out any overtime (overtime is only recognised if it is over 50 hrs per week) from qryMonthlyHours.

I then need it to Work off the qrySaturdayRota (if someone has worked a Saturday on the Rota it needs to add 4 hours in a new column, but the plot thickens:

if that employee has any overtime, ie if they have 55 hours overtime then their overtime needs to minus 4 hours and leave 1 hour in the [SumOfTotalHrs] and record 4 Hours in a SaturdayRota column BUT

if the employee has no overtime 4 hours still needs to be recorded.

This sounds complicated even to me I hope you understand what I am trying to do and can help me in anyway possible.

Thanks

[Rabbit]

SELECT [EmployeeName], [WkComDate], Sum([TotalHrs]) As SumOfTotalHrs
FROM [Table Name]
GROUP BY [EmployeeName], [WkComDate];

As for the second part, could you provide a couple of examples?

[NeoPa]

I would suggest asking these questions one at a time. It's very hard to focus on many items at once. When one aspect has been satisfactorily answered, then move on to the next. It's also easier for you to explain one thing at a time (As you noticed when you read it back to yourself ;)).
They can probably proceed each other in this single thread without problem.

[foxykitty]

SELECT [EmployeeName], [WkComDate], Sum([TotalHrs]) As SumOfTotalHrs
FROM [Table Name]
GROUP BY [EmployeeName], [WkComDate];

As for the second part, could you provide a couple of examples?

Thank you for responding and sorry for the confusion of the 2nd part of the question.

Ie.
(Another Field Added)
[EmployeeName] [SumOfTotalHrs] [SaturdayRota]
(Overtime 50+ hrs)

Foxykitty 1 4

What I need is to some way workoutand display in the report and query the following:

Foxykitty has done 55 hrs total in a week and in that week she worked a SaturdayRota. So she has in effect only worked 1 hr overtime and 4 hours on the SaturdayRota (the 2 are paid out in different rates). So maybe I need another field which works out the total hours and then does the calculations - this is where I am lost.

But

If Foxykitty has done 45 hrs overtime and done 4 hours of the Saturday Rota then in effect she won't get any Overtime but will get 4 hours for working SaturdayRota.

I hope that this is more clearer or understandable?

Thanks