AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
I am facing some problem related to scheduling the people in shift. We have 3 shifts coming in our office. They all login to avaya and i get login logut report for them in the below format:
Agent Name Login Time Logout Time
DB Nasser M 05/02/2005 16:04 05/02/2005 17:10
DB Nasser M 05/02/2005 17:11 05/02/2005 17:13
DB Nasser M 05/02/2005 17:22 05/02/2005 17:22
DB Nasser M 05/02/2005 17:23 05/02/2005 21:28
DB Nasser M 05/02/2005 21:28 05/02/2005 23:00
DB Maryam M 05/02/2005 8:24 05/02/2005 9:54
DB Maryam M 05/02/2005 10:04 05/02/2005 13:49
DB Maryam M 05/02/2005 14:03 05/02/2005 15:08
DB Ahmed Rafaat 05/02/2005 22:57 06/02/2005 1:45
===========================================
All of them are assigned in some or other shifts and with above data we need find out(there can be multiple entries in single day of login and logout. People are even doing night shift and there time comes in next day):
1) What shift they were assigned to and if they logged in late or logged off early from there shift.
2) We need to figure out on 15 min interval basis for whole day how many people were logged in. So there would be one table of requirement of 15 min interval for each day and then match with above login logout how many were logged in. In case people were less then required then auto mail or alarm.
Example: In case a agent like nasser is coming in shift 7:30 in morning so we need to see if he logged in this time or not he was late by how many minutes or was he absent he was supposed to logout did he logout early.
Second with all these people in shift what was the actual no of agents in a shift in 15 min interval. like 7:30 4 agents 7:45 3 agents.
I am not a programmer and trying to fgiure out the best way to design tables in such way i can calculate time and tweak the raw data to get the results. Please help me out
Thanks and have a nice day
Regards
Dhruv
Answer I assume you can get the data into a table with the structure: LoginID, Timein, TimeOut. You then need another table, that shows the shifts they were scheduled for on any day. From there its a matter of constructing queries to return the data you you want.
Using your examples: to find out if Nasser clocked in early or late on 5/2 could use an expression like this:
SchedTime-DMIN("[Timein]","TimeLog","[LoginID] = 'Nasser M' AND [Timein] = #5/2/05#")
Then check to see if the result of the expression is positive or negative to determine if he was late or not.
To find out how many people were clocked in for a period you would run a query using a Criteria of TimeIn <= start time and Time Out >= End time.
HTH
Scott<>
P.S. the expressions are off the top of my head so you may need to double check syntax.
