By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Calculating Overtime and Saturday Rota

P: 3
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
Mar 2 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Expand|Select|Wrap|Line Numbers
  1. SELECT [EmployeeName], [WkComDate], Sum([TotalHrs]) As SumOfTotalHrs
  2. FROM [Table Name]
  3. GROUP BY [EmployeeName], [WkComDate];
As for the second part, could you provide a couple of examples?
Mar 2 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 3 '07 #3

P: 3
Expand|Select|Wrap|Line Numbers
  1. SELECT [EmployeeName], [WkComDate], Sum([TotalHrs]) As SumOfTotalHrs
  2. FROM [Table Name]
  3. 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
Mar 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.