hi guys, need help with a holiday tracking Q

[reddwarf]

I am desgning a holiday tracker.

basically, each team member has a numbers of hours worked per day
ech team meber has different days they work - so it can look like this >:

Darren - Monday 5 hours, Tursday 5 hours, Wednesday 0 hours, thursday 5 hours , friday 5 hours.

the working patter is kept on "HANDLERTABLE".

i have already worked out how to see how many hours between two dates and add this in the query as "hoursHoliday".

however, we have an 18% holiday rate here menaing no more then 17* of staff off at any one time. so what i wanted was a table or list which could show the % of hours off against available, the avaiilable can be worked out by adding up all the hours for a date from the HandlerTable - however, i am struglling on how to add up hours holiday on individual dates as I am only adding a start and end date ?

can this be done with a query or am I going to have to hit the VBA running ?

its a long shot, but anyone got any ideas ? let me know if you need more clarification and i hope i can return the favour one day.

 

[MarkK]

I don't understand from your post how your data is structured. It seems odd, for instance, you'd have a record of zero hours for Darren on Wednesday. If it's zero, I'd expect there not to be a record.
And what kind of data in a table called Handler? Is this where you store hours worked?
But to get specifics on how to calculate a result, please describe in much greater detail how your data is structured and what operation needs to be performed on it.
Cheers,

 

[reddwarf]

Hi - it is confusing hope this helps

Basically

We have 100 Employees who all take holiday – they also all work different hours days. For instance

Darren works 20 pers week

5 hours Mon
5 hours Tues
Doesn’t work Wednesday
5 hours Thursday
5 hours fri

Sharon works

7 hours Mon
8 hours Tuesday
7 hours wed
Doesn’t work Thursday
9 hours Fri

And so on – 100 employess

Bear I mind this is part of a larger system for Employees which records audit result and Sickness and performance reports.

We have an existing Table Called HandlerTbl – They are all claims handlers you see.

This info holds all emp data including what hours they work on what day. We have 5 fields to record this H_Monday to H_Friday. So if they don’t work on a particular day it is 0 hours instead of null as nulls would affect the sum for working out hours worked in week. Eg H_Monday + H_Tuesday If one was null then we get an error.

So – we have another table set up called Holiday table. In this table we record the Date of the start of the Holiday and the end date of the holiday. I then have some code set up in VBA to work out how many hours holiday they will be taking by using the For..Next commend and checking days for the week.

Eg

Private Sub Command13_Click()
Dim TotalHols As Integer

TotalHols = 0

If IsNull([Holiday_StartDate]) Then
MsgBox "No Start Date added yet?", , "STOP"
Else
If IsNull([Holiday_EndDate]) Then
MsgBox "No End Date added yet?", , "STOP"
Else
If IsNull([H_UserId ]) Then
MsgBox "No User Id Added?", , "STOP"
Else

For DateCount = Me.Holiday_StartDate To Me.Holiday_EndDate

If Weekday(DateCount) = vbMonday Then
TotalHols = TotalHols + Me.H_MONDAY
Else
If Weekday(DateCount) = vbTuesday Then
TotalHols = TotalHols + Me.H_TUESDAY
Else
If Weekday(DateCount) = vbWednesday Then
TotalHols = TotalHols + Me.H_WEDNESDAY
Else
If Weekday(DateCount) = vbThursday Then
TotalHols = TotalHols + Me.H_THURSDAY
Else
If Weekday(DateCount) = vbFriday Then
TotalHols = TotalHols + Me.H_FRIDAY

End If
End If
End If
End If
End If

Next DateCount

MsgBox ("The total Hols is : " & TotalHols)
Me.Holiday_Hours = TotalHols

End If
End If
End If

End Sub

Now what we need to know if that on a particular day – say 15/10/10 – how many hours are taken as holiday. We also need to calculate how many hours are available that day by looking at the HandlerTbl – then give a percentage of Holiday for that date – all this needs to either in a button press( eg – check availability ) or in the background with a query ?

Does this make any more sense ?