UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Finding No Activity Gaps Between Times, Access 2016    
 
   
VBStudent
post Mar 6 2019, 07:47 PM
Post#1



Posts: 72
Joined: 17-April 16



Hello All,

Our employees work on a software that timestamps their activity. We then receive weekly report in excel with everyone's activities for that week. We're tasked to review the report and find no-activity gaps per day. I'm currently doing this manually, but I was wondering if I could do this in Access.

For example, we all work an 8 hour shift. Employees start working at 8 and leave at 4:30. Let's say that an employee works the first account at 8:10 then there is no activity for 2 hours and then he/she works another account at 10:30 and etc.. Having this data imported in Access, how can I capture the no activity gaps through out the day? Any thoughts? Thank you!
Go to the top of the page
 
MadPiet
post Mar 6 2019, 08:05 PM
Post#2



Posts: 3,364
Joined: 27-February 09



Without some data or a table structure, this is an impossible question to answer. So could you post at least a spreadsheet? And no, please no real people in there. Just EmployeeID is plenty.

I have a sneaking suspicion this might resemble the Gaps & Islands question though.
Go to the top of the page
 
VBStudent
post Mar 6 2019, 08:30 PM
Post#3



Posts: 72
Joined: 17-April 16



Here is one example to give you an idea. Assuming the shift starts at 8Am and ends at 4:30Pm, how can I capture the no-activity gaps that are greater than 30 minutes through out the day? For example, notice there is a big idle time between 8:15 and 10:25.


EmployeeID Acount# Last worked
1 2564 3/1/19 8:15 AM
1 5588 3/1/19 10:25 AM
1 2235 3/1/19 11:35 AM
1 1547 3/1/19 1:10 PM
1 2254 3/1/19 2:05 PM
1 1454 3/1/19 2:40 PM
1 59874 3/1/19 2:50 PM
1 236544 3/1/19 3:25 PM
1 21547 3/1/19 3:45 PM
1 25487 3/1/19 4:10 PM
1 2214574 3/1/19 4:25 PM
This post has been edited by VBStudent: Mar 6 2019, 08:32 PM
Attached File(s)
Attached File  Idle_time_example.jpg ( 35.24K )Number of downloads: 1
 
Go to the top of the page
 
MadPiet
post Mar 6 2019, 08:39 PM
Post#4



Posts: 3,364
Joined: 27-February 09



How would you identify a "no activity" gap manually? (What's the logic?)

The time segments aren't standard are they?
Go to the top of the page
 
VBStudent
post Mar 6 2019, 08:53 PM
Post#5



Posts: 72
Joined: 17-April 16



Well, the way we're doing it now is we know that a specific employee was in the office working all day. They only work on a single software which timestamps every account they work. We must work one account between 5 to 10 minutes. If an employee was present for 8 hours, but they only worked 10 accounts, management wants to know why. So they pull these timestamped reports from the system and want us to review it. In the example I posted, this employee worked an account at 8:15 and didn't work another one until 10:25. Management wants to know why this employee was sitting for over 2 hours doing nothing. Currently, we just look at the report and find large gaps, like the one in this example. Hope I make sense. Thank you!
This post has been edited by VBStudent: Mar 6 2019, 08:54 PM
Go to the top of the page
 
MadPiet
post Mar 6 2019, 09:17 PM
Post#6



Posts: 3,364
Joined: 27-February 09



Okay. Makes sense. Had to do this once in SQL Server (infinitely easier!) and then cheated and read Allen Browne's example http://www.allenbrowne.com/subquery-01.html#AnotherRecord

CODE
SELECT WorkTimes.EmployeeID,
    WorkTimes.TimeStart,  
        (SELECT TOP 1 Dupe.TimeStart
        FROM WorkTImes AS Dupe
        WHERE Dupe.EmployeeID = WorkTimes.EmployeeID
        AND Dupe.TimeStart < WorkTimes.TimeStart ORDER BY Dupe.TimeStart DESC, Dupe.EmployeeID) AS PriorValue
FROM WorkTimes;


Then you can use DATEDIFF(n, TimeStart, PriorValue) to get the elapsed time.
Go to the top of the page
 
VBStudent
post Mar 6 2019, 09:20 PM
Post#7



Posts: 72
Joined: 17-April 16



Thank you! I will give this a try tomorrow. Thank you so much!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 06:16 AM