Show that a person use 5 days or more

[Garyj]

How to I get a query to show if an employee have used 5 consective day of vacation? I hope this explains what I am trying to do.

Thanks in advance

 

[barbarossaii]

Hi Garyj,

I suppose u got a table like this in which the vacation is stored:

NameOfEmployee.......DateVacation
<Name1>.....................Day1ofVacation
<Name1>......................Day2ofVacation
.....
<Name1>......................lastDayOfVacationOfName1
<Name2>......................Day1ofVacationOfName2



So you could get the employees w/ >5 days of consec. vacation like this (terrible solution to programm I've to admit but it should work...):


1.) create a table w/ all days of the year(s) you want to check (01.01.03, 02.01.03,...; please make sure it is formated the same way your date in your vacation table is); add a 2nd column for a 5day period: day 1-5 would get a 1 in the 5day period column, day 6-10 a 2, 11-15 a 3,...(change this if Sat & Sun should not be considered)

2.) create a table or query w/ all the employees names (in the same 'formate' give in the Vacation tbl)

3.) create a query w/ the tables /query from 1.) & 2.) without a join. Running this query will return a list of all employees combined with every date (a timetable w/ worktime for every employee).

4.) create another query which links query 3.) with your vacation table; join
DateOf1.) => DateVacation

5.) create another query with 4.) as base which groups on the employees and on the 5dayperiods and c o u n t s the DateVacationTbl (leave the actual date colums out in this query, otherwise it wont work)

6.) create another query which returns every employee from 5.) with a 5 in CountOf_DateVacationTbl

-> the returned employees were off in some 5 day period.

7.) as special 5day periods were specified in 1.) you will have to
REPEAT 1.) to 6.) and start 5dayperiod No1 with the 2nd of Jan, then
REPEAT 1.) to 6.) an start 5dayperiod No1 with 3rd of Jan, then
REPEAT 1.) to 6.) and start 5dayperiod No1 with 4th of Jan
(no forther step because the very first 5dayperiod No2 starts with 5th of Jan)

8.) create a query which returns all employees with a "5" in one of the CountOf_DateVacationTbl's (combine the fields with an "or")


Quite complex but it should work....


HTH,
Barbarossa II

 

[GumbyD]

Odd that you should ask this question as I just created a query like that for one of my clients. You must work at a finanicial institution - What software are you using to track absences?

GumbyD

 

[Garyj]

GumbyD,
I am using Access97. I have created a database that tracks employees vacations, sick, personal time etc...

Now barbarossaii,
Thank you for your quick response. It will take awhile for me to examine your theroy. I will post my progress as I go along. My tables are simple. The main table has the following.

ID
Date
EmployeeID
LastName
FirstName
Unit
Amount
EarningCode (This field is used to describe Vacation, sick etc..) example: 921 is Vacation and 918 is personal time and 920 is sick

This system is 99% complete. I just need to figure out how to create a query that will give me a 5 day compliance no matter what code is use as long as it is 5 days in a row.

Thanks barbarossaii I'll take a look at your sugguestion and compair it to my table.

 

[GumbyD]

Garyj -

It looks to me like you are tracking each day seperatly and not blocks of days (I noticed that you are just tracking date and not startdate & enddate). Because this is the case to find out if you have 5 consecutive days you will need to look at the seperate records grouped by empoyee and dates will be critical. Can the 5 days be over a weekend (Thurs - Wednesday of the following week) or does it have to be Monday thru Friday? I think your best bet is a new function that runs a loop for the employees and then creates a recordset of their absences and runs through them to look for consecutive days. You could have the code write the names of the employees without 5 consecutive days off in a temp table. If you want me to I will construct the basic code for you - but I need to know about the weekend stuff so that it works correctly for you.

GumbyD

 

[Garyj]

GumbyD,


That would be great, the code that is.

It looks to me like you are tracking each day seperatly and not blocks of days (I noticed that you are just tracking date and not startdate & enddate). Yes, I am tracking everyday. I have a query that ask the users a date range in all reports.

Because this is the case to find out if you have 5 consecutive days you will need to look at the seperate records grouped by empoyee and dates will be critical. I have queries that qroup the employees and I believe dates through a date range.

Can the 5 days be over a weekend (Thurs - Wednesday of the following week) or does it have to be Monday thru Friday? No weekend is required only weekdays. I need something that will track the weekday and omit the weekends.


I think your best bet is a new function that runs a loop for the employees and then creates a recordset of their absences and runs through them to look for consecutive days. You could have the code write the names of the employees without 5 consecutive days off in a temp table.
That sound good I think that might work. Yes, I would appreciate your assitance with this. Tracking only employees who have taken 5 consective days off is just what I need.

Thanks again for your help

 

[GumbyD]

Try this code. You may have to update the SQL a bit to get the table references correct.

Public Function Get5LessEmps()

Dim rstEmps As Recordset 'Recordset for All Emps
Dim rstAbs As Recordset 'Recordset for absences
Dim rstLess As Recordset 'Recordset for the table where we will put the emps with less then 5 days
Dim strSQL As String 'String to hold SQL statements
Dim intConCt As Long 'Integer to hold number of consectutive days
Dim EID As String 'Variable to hold the Employee ID
Dim Tdate As Date 'Temp storage for date value
Dim TDay As Integer 'Temp storage for day value
Dim TPDate As Date 'Temp storage for previous records date
Dim TPDay As Integer 'Temp storage for previosu records day
Dim CDays As Integer 'Temp storage counter for consecutive days
Dim CMaxDays As Integer 'Varible to hold longest consecutive days off

'Clear the holding table of data
CurrentDb.Execute "delete * FROM MaxDays"

Set rstLess = CurrentDb.OpenRecordset("MaxDays", dbOpenDynaset)

'Get a recordset of all the employeeID's - I will use the Table I created called EmpAbsence - if you have just an
'Employee table you may want to use that instead so that you don't miss emps with no absences
strSQL = "SELECT EmpAbsence.EmployeeID " & _
"FROM EmpAbsence " & _
"GROUP BY EmpAbsence.EmployeeID;"
'Set the SQL statement above to the rstEmps Recordset
Set rstEmps = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
'check to make sure we have data in the recordset
If rstEmps.EOF Then
MsgBox "There are no employees coming up. Please check the query.", vbOKOnly
Else
'loop through the emps
rstEmps.MoveFirst
Do Until rstEmps.EOF
EID = rstEmps!EmployeeID
'Using the EmployeeID as criteria loop through the absences counting consecutive days
'Create a recordset for the first emp with absences in asending order and showing day of the week
strSQL = "SELECT EmpAbsence.EmployeeID, EmpAbsence.Date, Weekday([date]) AS DayOfAbsence " & _
"FROM EmpAbsence " & _
"WHERE (((EmpAbsence.EmployeeID) = '" & EID & "')) " & _
"ORDER BY EmpAbsence.Date;"
Set rstAbs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rstAbs.EOF Then
'This employee has no absence records add them to the table
GoSub addrecord
Else
rstAbs.MoveFirst
'initialize the counter
CDays = 0
Do Until rstAbs.EOF
'Set the values from the record
Tdate = rstAbs!Date
TDay = rstAbs!DayOfAbsence
If CDays <> 0 Then
'More then one day so far
If Tdate = TPDate + 1 Then
CDays = CDays + 1
TPDate = Tdate
TPDay = TDay
ElseIf Tdate = TPDate + 3 And TDay = 2 Then
CDays = CDays + 1
TPDate = Tdate
TPDay = TDay
Else
'Days not consecutive reset cdays
CMaxDays = CDays
CDays = 0
End If
If CDays = 5 Then
GoTo NextEmp
End If
Else
'first day in sequence
TPDate = Tdate
TPDay = TDay
CDays = 1
End If
rstAbs.MoveNext
Loop
End If
'move to next emp
If CMaxDays < 5 Then GoSub addrecord
NextEmp:
rstAbs.Close
rstEmps.MoveNext
Loop
End If
'Close rstemps recordset
rstEmps.Close
rstLess.Close
'message box to let user know the process is completed
MsgBox "Process Complete", vbOKOnly
Exit Function
addrecord:
rstLess.AddNew
rstLess!EmployeeID = rstEmps!EmployeeID
rstLess!MaxDays = CMaxDays
rstLess.Update
Return
End Function


GumbyD

 

[GumbyD]

Sorry - I lose all my indents when I post code. I hope it is still clear. I forgot to mention a couple of things. First, you need create a table for the data to get dumped into. It needs to be called "MaxDays" and have the fields "EmployeeID" (Text)and "MaxDays" (Long). Second, you will need to run this from the debug window. Press CtrlG and then type in Get5LessEmps and press return.

Good Luck

GumbyD

 

[Garyj]

GumbyD,

WOW, Thanks allot. I appreciate all your efforts.

Question:

I added a checkbox into my table and within the form which is in continues view the users would click the checkbox indicidating when the 5 day compliance ended. I then created a query where I ask for "Yes"only then created a report. This report gave me every employee that has completed their 5 day compliance. However, my query also show No as well.

Example: My query shows Yes and No under the 5days column and it also shows duplicates. Which is OK. My problem is how would filter out the No's without including the dups. So if I have Yes in the 5days column I do not want to see that name under again with No.

If tried writing an If statement and also IsNull but I haven't had any success.

Think this would be an easy and simple solution. I hope I explain this correctly and any help would be appreciated.

Thanks again

 

[GumbyD]

I think what I would do is create a seperate table call Emp5DayCompliance. In the table I would put EmployeeID and Completed5Day (as a boolean - yes/no). link the new table to the existing empAbsence table that you have and add the check box field to the query and form. That way if you check the completed5day check on any absence record it will show completed for the employee. Then at the beginning of the next year just clear all the checkboxes and you are ready to go again.

GumbyD

 

[Garyj]

Show That a person use 5 days compliance

GumbyD,

I actually have created a query using one of my existing tables. I was able to show from the query who has taken 5 day compliance. However, I need a second report that shows the one's that still needs to take their 5 day compliance. I feel that I'm close but at the moment I'm stuck. In the table I added a new field and called it 5daycompliance, using the yes/no. In the lookup area instead of the default "checkbox" I changed it to "text". Now when I run the query it says yes or no which is what I want.

The query also shows the "No" and "Yes" for any employee that I check off. This is fine for finding who has taken a 5 day compliance but since it shows that same employee a second time with a "No" I have been tring to write something that will filter out all the employees that shows "Yes" and "No" and show those with just 1 "No". Or something like that.

Query Example:

EmployeeID: 552546
LastName: Due
FirstName: John
5DayCompliance: Yes

Now since this is in a form and viewed in a continues view and each employee has its own section via a drop down box. So if you select a person name you'll only see that person information.
Now saying that, in the query it shows John Due a second time with a "No". Hence my problem.....

I hope I explain this correctly.

Thanks again.

 

[Garyj]

I think I found a different direction. I have a table that holds all of my information about every employee. In this table I add a checkbox feature.

How do you automatically uncheck each box without deleting any rows? If I can get this it would solve everything.

Thanks

 

[Rich]

Use an update query

 

[GumbyD]

I hope you followed Rich's lead! If you create an update query that updates the value in all of the checkboxes to 0 that will set all of the checkboxes back to an unchecked state.

GumbyD

 

[Garyj]

Thank you,

I was able to create the query to run my report which give me everyone who has completed their 5 day compliance and those who haven't.

Would an update query also apply if I was using a drop down box where the user select "Yes" or "No"? In this case I am.

Thanks again

 

[Garyj]

Thanks GumbyD for all your help everything is working great.

Thanks Rich your sugguestion to use an Update query worked perfectly.

This just a nice to have question. In the my table how would I get a message box to pop up when all my entires in the 5daycompliant combo box says "YES".

Thanks again