date overlap

[Jon]

Greeting

I have a form that has 4 fields as follows:
empID
Work Date
No of hours
Group

Each employee must not excess 12 hours per day and I would like to create a
list box to shows the number of hours available for this employee and others
within his group to be alternative options for the supervisor to assign the
work to another employee how do not have extra hours. Please help me??

 

[Wayne-I-M]

Hi Jon

You will need to add a Start Time/Date and (maybe) either a End Time/Date or
Now().

The simplest thing I can think of would that you have a popup message
informing you/user when a specific person was approching the allowed time
(say 2 hours before they should finish).

Can you give more details of the fields you have in the table for recording
hours (start end etc) and also what type of warning/message/etc you are
looking for.
Also you need to allow for people going over theri allowed hours (over time
etc)

 

[Jon]

Hi,
Thank you for your response. In fact, I am not looking for a code to make a
message, I already had the following code to control the data entry and not
existing 12 hours , I am looking for alternative employees who can be
assigned on the job at specific date . My table fields are as above.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblHours As Long
Dim strWhere As String
Dim strMsg As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const dblcMaxHours As Double = 12

If IsNull(Me.EmpID) Then
Cancel = True
strMsg = strMsg & "Employee required." & vbCrLf
End If
If IsNull(Me.WorkDate) Then
Cancel = True
strMsg = strMsg & "Date required." & vbCrLf
End If
If IsNull(Me.Hours) Then
Cancel = True
strMsg = strMsg & "Hours required." & vbCrLf
End If
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the data, or press Esc to undo. "

MsgBox strMsg, vbExclamation, "Incomplete"
Else
strWhere = "(EmpID = " & Me.EmpID & ") " & _
" AND (WorkDate = " & Format(Me.WorkDate, strcJetDate) & ") " & _
" AND (ID <> " & Me.ID & ")"
dblHours = Nz(DSum("Hours", "WorkSchu", strWhere), 0) + Me.Hours
If dblHours > dblcMaxHours Then
strMsg = "This employee now has " & dblHours & _
" hour(s) on this date." & vbCrLf & "Continue anyway?"

If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Overload") <> vbYes Then
Cancel = True
'Me.Undo

End If
Else

End If
End If

 

[Wayne-I-M]

So are you looking for a filtered list of employees who have more than a
specified amount of alloted time remaining within a day.

Say the time is now 11am and you allow each person to work for 5 hours

EmployeeA - Start 5-45 - Now() +00-15 (no) remaining time Nil
EmployeeB - Start10-45 - Now() -0-15 (yes) remaining time 4-45
EmployeeC - Start10-00 - Now() -1-00 (yes) remaining time 5-00
EmployeeD - Start04-30 - Now() +6-40 remaining time Nil
EmployeeE - Start06-20 - Now() -4-40 remaining time 0-40

Do you just want a filtered list showing employees B,C and E

Is this along the lines you're looking for ?

 

[Jon]

Thank you very match,
The start time is not important , I just want to make sure that the employee
is not working more than 12 hours which I have solved this problem, now I
want to see alternative employees if the current employee has more than 12
hours. For example:

Current employee
employeeName work date Hours Group
EmployeeA 07/18/2009 4 A

Once I input above data and employee has more than 12 hours the following
msg appears:

This employee has 16 hours and you can not assign more hours to him, please
see select another employee from the list below


***Here is my question****

List box

EmployeeName Work Date Available hours Group

EmployeeB 07/18/2009 12 A
EmployeeC 07/18/2009 8 A
EmployeeD 07/18/2009 10 A
EmployeeE 07/18/2009 0 A

How to do the list box in order to select another employee ??

 

[Wayne-I-M]

I don't understand your application as I can't see it
But
If it were me I would just create a query and filter it for the employees
with enough time left and then use this as the base of the list - that users
can select emplyees from

 

[John Spencer]

Use a query like the following

SELECT EmpID, [Work Date]
, 12-[No of Hours] as RemainingTime
FROM [TheTable]
WHERE [Work Date] = Forms![YourFormName]![WorkDateControl]
AND [Group] = Forms![YourFormName]![GroupControl]

You will need a way to refresh the list as you change records. Probably
just a requery of the list box in the current event of your form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

I just realized that this will not show anyone that has NO time on the
date involved. Hopefully, you have a table of employees and the group
the employee belongs to.

If so, you can build a query to based on that table and a subquery to
get the results you need. Since your field names (and table names?)
contain spaces, building the query is not as simple as it could be since
due to the requirement to use square brackets around your field names.

Post back if you have an Employees table. Give us the actual field
names and table names and perhaps we can come up with an idea that will
work for you.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Use a query like the following

SELECT EmpID, [Work Date]
, 12-[No of Hours] as RemainingTime
FROM [TheTable]
WHERE [Work Date] = Forms![YourFormName]![WorkDateControl]
AND [Group] = Forms![YourFormName]![GroupControl]

You will need a way to refresh the list as you change records. Probably
just a requery of the list box in the current event of your form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Greeting

I have a form that has 4 fields as follows:
empID
Work Date
No of hours
Group

Each employee must not excess 12 hours per day and I would like to
create a list box to shows the number of hours available for this
employee and others within his group to be alternative options for the
supervisor to assign the work to another employee how do not have
extra hours. Please help me??

 

[Jon]

Hi John
You are right, there is a table called “EmployeeInfo†which has the
following fields:
EmpID
EmpName
Group
HrsScheduled >>>>Default value =12

And it has EmpID as primary key

I just realized that this will not show anyone that has NO time on the
date involved. Hopefully, you have a table of employees and the group
the employee belongs to.

If so, you can build a query to based on that table and a subquery to
get the results you need. Since your field names (and table names?)
contain spaces, building the query is not as simple as it could be since
due to the requirement to use square brackets around your field names.

Post back if you have an Employees table. Give us the actual field
names and table names and perhaps we can come up with an idea that will
work for you.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Use a query like the following

SELECT EmpID, [Work Date]
, 12-[No of Hours] as RemainingTime
FROM [TheTable]
WHERE [Work Date] = Forms![YourFormName]![WorkDateControl]
AND [Group] = Forms![YourFormName]![GroupControl]

You will need a way to refresh the list as you change records. Probably
just a requery of the list box in the current event of your form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Greeting

I have a form that has 4 fields as follows:
empID
Work Date
No of hours
Group

Each employee must not excess 12 hours per day and I would like to
create a list box to shows the number of hours available for this
employee and others within his group to be alternative options for the
supervisor to assign the work to another employee how do not have
extra hours. Please help me??

 

[John Spencer]

You might be able to build the SQL statement and assign it as the row source.
The problem is that you have field names with spaces in them and they
require square brackets and you have a field name (Group) that is probably a
reserved word. If you can change the field name in EmpWorkingHours to
WorkDate then this would take care of the problem.

Private Sub sShowAlternateNames()
Dim strSQL as String

If Len(me.txtWorkDate & vbnullstring)>0 and Len(me.txtGroup & "") > 0 THEN

strSQL = "SELECT EmployeeInfo.EmpID, Employee.EmpName" & _
", HrsScheduled-Nz([No of Hours],0) as TimeRemaining" & _
" FROM EmployeeInfo LEFT JOIN " & _
" (SELECT * FROM EmpWorkingHours" & _
" WHERE [Work Date] =" & _
Format(Me.txtWorkDate,"\#yyyy-mm-dd\#") & ") as EWH" & _
" ON EmployeeInfo.EmpID = EWH.EmpID" & _
" WHERE EmployeeInfo.Group = """ & Me.txtGroup & """"

Me.SomeListbox.RowSource = StrSQL
Else
Me.someListbox.RowSource = vbnullstring
End if

I can't tell you exactly when you need to call the code, but I would guess
that you would need to call it after you change the employeeID and the work date.

Good luck

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jon]

Thank you John for your follow up. but still there is a problem which is When
I call the code, it works but any employee who has a record fall within the
input date, his records appear duplicated. For example, employee No 1 has 3
records in 07/22/2009 with remaining Hours 9, and I input employee No 2 dated
07/22/2009 , the list box shows employee ID#1 3 times and any employee else
who has a record within the same date, Please advice?

You might be able to build the SQL statement and assign it as the row source.
The problem is that you have field names with spaces in them and they
require square brackets and you have a field name (Group) that is probably a
reserved word. If you can change the field name in EmpWorkingHours to
WorkDate then this would take care of the problem.

Private Sub sShowAlternateNames()
Dim strSQL as String

If Len(me.txtWorkDate & vbnullstring)>0 and Len(me.txtGroup & "") > 0 THEN

strSQL = "SELECT EmployeeInfo.EmpID, Employee.EmpName" & _
", HrsScheduled-Nz([No of Hours],0) as TimeRemaining" & _
" FROM EmployeeInfo LEFT JOIN " & _
" (SELECT * FROM EmpWorkingHours" & _
" WHERE [Work Date] =" & _
Format(Me.txtWorkDate,"\#yyyy-mm-dd\#") & ") as EWH" & _
" ON EmployeeInfo.EmpID = EWH.EmpID" & _
" WHERE EmployeeInfo.Group = """ & Me.txtGroup & """"

Me.SomeListbox.RowSource = StrSQL
Else
Me.someListbox.RowSource = vbnullstring
End if

I can't tell you exactly when you need to call the code, but I would guess
that you would need to call it after you change the employeeID and the work date.

Good luck

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John
You are right, there is a table called “EmployeeInfo†which has the
following fields:
EmpID
EmpName
Group
HrsScheduled >>>>Default value =12

And it has EmpID as primary key

 

[John Spencer]

THen you are going to need to use an aggregate query. And as I said, I'm not
sure that this query won't fail because of your field names.

The replacement SQL would look something like the following UNTESTED statement.

strSQL = "SELECT EmployeeInfo.EmpID, Employee.EmpName" & _
", HrsScheduled-Nz(AssignedHours,0) as TimeRemaining" & _
" FROM EmployeeInfo LEFT JOIN " & _
" (SELECT EmpID, Sum([No Of Hours]) as AssignedHours FROM EmpWorkingHours" & _
" WHERE [Work Date] =" & _
Format(Me.txtWorkDate,"\#yyyy-mm-dd\#") & _
" GROUP BY EmpID " & _
" HAVING SUM([No of Hours])<12) as EWH" & _
" ON EmployeeInfo.EmpID = EWH.EmpID" & _
" WHERE EmployeeInfo.Group = """ & Me.txtGroup & """"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

You might be able to build the SQL statement and assign it as the row
source. The problem is that you have field names with spaces in them
and they require square brackets and you have a field name (Group) that
is probably a reserved word. If you can change the field name in
EmpWorkingHours to WorkDate then this would take care of the problem.

Private Sub sShowAlternateNames()
Dim strSQL as String

If Len(me.txtWorkDate & vbnullstring)>0 and Len(me.txtGroup & "") > 0 THEN

strSQL = "SELECT EmployeeInfo.EmpID, Employee.EmpName" & _
", HrsScheduled-Nz([No of Hours],0) as TimeRemaining" & _
" FROM EmployeeInfo LEFT JOIN " & _
" (SELECT * FROM EmpWorkingHours" & _
" WHERE [Work Date] =" & _
Format(Me.txtWorkDate,"\#yyyy-mm-dd\#") & ") as EWH" & _
" ON EmployeeInfo.EmpID = EWH.EmpID" & _
" WHERE EmployeeInfo.Group = """ & Me.txtGroup & """"

Me.SomeListbox.RowSource = StrSQL
Else
Me.someListbox.RowSource = vbnullstring
End if

I can't tell you exactly when you need to call the code, but I would
guess that you would need to call it after you change the employeeID and
the work date.

Good luck

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John
You are right, there is a table called “EmployeeInfo†which has the
following fields:
EmpID
EmpName
Group
HrsScheduled >>>>Default value =12

And it has EmpID as primary key

 

[Jon]

Thank you John, you are genius, it works just what the doctor ordered now

THen you are going to need to use an aggregate query. And as I said, I'm not
sure that this query won't fail because of your field names.

The replacement SQL would look something like the following UNTESTED statement.

strSQL = "SELECT EmployeeInfo.EmpID, Employee.EmpName" & _
", HrsScheduled-Nz(AssignedHours,0) as TimeRemaining" & _
" FROM EmployeeInfo LEFT JOIN " & _
" (SELECT EmpID, Sum([No Of Hours]) as AssignedHours FROM EmpWorkingHours" & _
" WHERE [Work Date] =" & _
Format(Me.txtWorkDate,"\#yyyy-mm-dd\#") & _
" GROUP BY EmpID " & _
" HAVING SUM([No of Hours])<12) as EWH" & _
" ON EmployeeInfo.EmpID = EWH.EmpID" & _
" WHERE EmployeeInfo.Group = """ & Me.txtGroup & """"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

You might be able to build the SQL statement and assign it as the row
source. The problem is that you have field names with spaces in them
and they require square brackets and you have a field name (Group) that
is probably a reserved word. If you can change the field name in
EmpWorkingHours to WorkDate then this would take care of the problem.

Private Sub sShowAlternateNames()
Dim strSQL as String

If Len(me.txtWorkDate & vbnullstring)>0 and Len(me.txtGroup & "") > 0 THEN

strSQL = "SELECT EmployeeInfo.EmpID, Employee.EmpName" & _
", HrsScheduled-Nz([No of Hours],0) as TimeRemaining" & _
" FROM EmployeeInfo LEFT JOIN " & _
" (SELECT * FROM EmpWorkingHours" & _
" WHERE [Work Date] =" & _
Format(Me.txtWorkDate,"\#yyyy-mm-dd\#") & ") as EWH" & _
" ON EmployeeInfo.EmpID = EWH.EmpID" & _
" WHERE EmployeeInfo.Group = """ & Me.txtGroup & """"

Me.SomeListbox.RowSource = StrSQL
Else
Me.someListbox.RowSource = vbnullstring
End if

I can't tell you exactly when you need to call the code, but I would
guess that you would need to call it after you change the employeeID and
the work date.

Good luck

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John
You are right, there is a table called “EmployeeInfo†which has the
following fields:
EmpID
EmpName
Group
HrsScheduled >>>>Default value =12

And it has EmpID as primary key