If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
date overlap
I want to build a database to track an employee work day hours. I have a
group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID ,Date & working Hours .. when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? Thanks |
#2
|
|||
|
|||
date overlap
This assumes that you have a record of hours already scheduled by employee.
The below query would give you every one that has time available for the assignment. You could make this the source for a list or combo box and select from those eligible. SELECT EmplID, HrsScheduled, WorkDate FROM tblScheduled WHERE (((HrsScheduled)=12-Forms!MyForm!txtAssignmentHrs) AND ((WorkDate)=Forms!MyForm!txtDateOfAssignment)); Regards Kevin "Jon" wrote in message news I want to build a database to track an employee work day hours. I have a group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID ,Date & working Hours . when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? Thanks |
#3
|
|||
|
|||
date overlap
Thank you kc-mass for your reply but I need to prevent Scheduled table from entering data if this employee excess the 12 hours by showing a msg and cancel the event. Please advice?? "kc-mass" wrote: This assumes that you have a record of hours already scheduled by employee. The below query would give you every one that has time available for the assignment. You could make this the source for a list or combo box and select from those eligible. SELECT EmplID, HrsScheduled, WorkDate FROM tblScheduled WHERE (((HrsScheduled)=12-Forms!MyForm!txtAssignmentHrs) AND ((WorkDate)=Forms!MyForm!txtDateOfAssignment)); Regards Kevin "Jon" wrote in message news I want to build a database to track an employee work day hours. I have a group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID ,Date & working Hours . when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? Thanks |
#4
|
|||
|
|||
date overlap
The only employees that you will be able to pick are those with sufficient
time left in their 12 hour schedule. That is if you have an assignment for 3.5 hours then the only employees this query will show you are those that have 8.5 hours or less currently scheduled on that day. Employees with 8.6 or more hours already scheduled on that day won't show and you then cannot pick them and they cannot get the assignment. Regards Kevin "Jon" wrote in message ... Thank you kc-mass for your reply but I need to prevent Scheduled table from entering data if this employee excess the 12 hours by showing a msg and cancel the event. Please advice?? "kc-mass" wrote: This assumes that you have a record of hours already scheduled by employee. The below query would give you every one that has time available for the assignment. You could make this the source for a list or combo box and select from those eligible. SELECT EmplID, HrsScheduled, WorkDate FROM tblScheduled WHERE (((HrsScheduled)=12-Forms!MyForm!txtAssignmentHrs) AND ((WorkDate)=Forms!MyForm!txtDateOfAssignment)); Regards Kevin "Jon" wrote in message news I want to build a database to track an employee work day hours. I have a group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID ,Date & working Hours . when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? Thanks |
#5
|
|||
|
|||
date overlap
Use the BeforeUpdate event procedure of the form (not control.) Use DSum()
to get the number of hours from *other* records, and add this one. (That approach will work for new records and edited records.) The event procedure will be something like the example below. Note that: a) I've assumed the Employee ID field is named EmpID. Add square brackets around the name if it contains spaces, e.g. [Emp ID] b) I've assumed the Date field is named WorkDate. DATE is a reserved name in Access and can cause you grief. c) I've assumed the Hours field is a Number type (not Date/Time.) d) I've assumed the primary key field is named ID. We use that to exclude the current record from the DSum(). e) Don't change the JET Date format constant (regardless of your regional settings.) 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) & "(ID " & Me.ID & ")" dblHours = Nz(DSum("Hours", "Table1", 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 End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" wrote in message news I want to build a database to track an employee work day hours. I have a group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID, Date & working Hours . when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? |
#6
|
|||
|
|||
date overlap
Thank you all
Mr. Allen, I have the following error Error3075 Missing ),], or item in query expression ‘(EmpID=234)AND (WorkDate = #07/07/2009#(ID3)’. And the following line is highlighted dblHours = Nz(DSum("Hours", "WorkSchu", strWhere), 0) + Me.Hours please advice?? "Allen Browne" wrote: Use the BeforeUpdate event procedure of the form (not control.) Use DSum() to get the number of hours from *other* records, and add this one. (That approach will work for new records and edited records.) The event procedure will be something like the example below. Note that: a) I've assumed the Employee ID field is named EmpID. Add square brackets around the name if it contains spaces, e.g. [Emp ID] b) I've assumed the Date field is named WorkDate. DATE is a reserved name in Access and can cause you grief. c) I've assumed the Hours field is a Number type (not Date/Time.) d) I've assumed the primary key field is named ID. We use that to exclude the current record from the DSum(). e) Don't change the JET Date format constant (regardless of your regional settings.) 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) & "(ID " & Me.ID & ")" dblHours = Nz(DSum("Hours", "Table1", 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 End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" wrote in message news I want to build a database to track an employee work day hours. I have a group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID, Date & working Hours . when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? |
#7
|
|||
|
|||
date overlap
Slight typo: there's a missing keyword ) AND in there. Change the code to
strWhere = "(EmpID = " & Me.EmpID & ") " & _ " AND (WorkDate = " & Format(Me.WorkDate, strcJetDate) & ") " & _ " AND (ID " & Me.ID & ")" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Jon" wrote in message ... Thank you all Mr. Allen, I have the following error Error3075 Missing ),], or item in query expression '(EmpID=234)AND (WorkDate = #07/07/2009#(ID3)'. And the following line is highlighted dblHours = Nz(DSum("Hours", "WorkSchu", strWhere), 0) + Me.Hours please advice?? "Allen Browne" wrote: Use the BeforeUpdate event procedure of the form (not control.) Use DSum() to get the number of hours from *other* records, and add this one. (That approach will work for new records and edited records.) The event procedure will be something like the example below. Note that: a) I've assumed the Employee ID field is named EmpID. Add square brackets around the name if it contains spaces, e.g. [Emp ID] b) I've assumed the Date field is named WorkDate. DATE is a reserved name in Access and can cause you grief. c) I've assumed the Hours field is a Number type (not Date/Time.) d) I've assumed the primary key field is named ID. We use that to exclude the current record from the DSum(). e) Don't change the JET Date format constant (regardless of your regional settings.) 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) & "(ID " & Me.ID & ")" dblHours = Nz(DSum("Hours", "Table1", 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 End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" wrote in message news I want to build a database to track an employee work day hours. I have a group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID, Date & working Hours . when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? |
#8
|
|||
|
|||
date overlap
Thank you all,
One more question Is there any way to show a list box with alternative employees + available hours who can be assigned for those hours if the current employee hours is full. I have a table for employee info with more than one employee can be assigned. How to do that please?? "Douglas J. Steele" wrote: Slight typo: there's a missing keyword ) AND in there. Change the code to strWhere = "(EmpID = " & Me.EmpID & ") " & _ " AND (WorkDate = " & Format(Me.WorkDate, strcJetDate) & ") " & _ " AND (ID " & Me.ID & ")" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Jon" wrote in message ... Thank you all Mr. Allen, I have the following error Error3075 Missing ),], or item in query expression '(EmpID=234)AND (WorkDate = #07/07/2009#(ID3)'. And the following line is highlighted dblHours = Nz(DSum("Hours", "WorkSchu", strWhere), 0) + Me.Hours please advice?? "Allen Browne" wrote: Use the BeforeUpdate event procedure of the form (not control.) Use DSum() to get the number of hours from *other* records, and add this one. (That approach will work for new records and edited records.) The event procedure will be something like the example below. Note that: a) I've assumed the Employee ID field is named EmpID. Add square brackets around the name if it contains spaces, e.g. [Emp ID] b) I've assumed the Date field is named WorkDate. DATE is a reserved name in Access and can cause you grief. c) I've assumed the Hours field is a Number type (not Date/Time.) d) I've assumed the primary key field is named ID. We use that to exclude the current record from the DSum(). e) Don't change the JET Date format constant (regardless of your regional settings.) 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) & "(ID " & Me.ID & ")" dblHours = Nz(DSum("Hours", "Table1", 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 End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" wrote in message news I want to build a database to track an employee work day hours. I have a group of employees and each employee should not excess 12 Hours per day. What I want to do is making a table for entering employee ID, Date & working Hours . when I want to input employee record, access should check if this employee is with Hours limit(12 HOURS) if yes, access precedes with record, if not access show me the capability of hours still and give me alternative of other employees with the same group who can work the number of hours I entered. Is that possible in access. Please help??? |
Thread Tools | |
Display Modes | |
|
|