About Rob Henderson Expertise I can answer most MS Access design questions. I also welcome questions on database design and implementation and VBA programming questions.
I also have expierence in application design for all the Office components (Excel, Outlook, etc).
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 Hi
I don't offer to design databases on this forum for free. If this is something you would like me to look at professionaly please let me know.
Having said that the basic structure should be something like this...
tblEmployee - employee info
tblTime - time info
Link them in a one to many (tblemployee has many times)
