Solved

MS Access Question - Logout time calculations

Posted on 2015-01-04
18
268 Views
Last Modified: 2015-01-05
We have MS Access 2010

From the attached text file export and others like it, I need to be able to batch import and re-import these files to produce the required data.

I need to show each day's logged-out times along with totals for each agent on each day.  

Each agent works a different shift, so the report should be based on the shift entered and stored for each agent.

If no login existed for an expected shift, then that time should be considered as well.

There will be multiple txt export files in each batch (one per agent, but replaced regularly for each agent).

I do not need to calculate logout times lasting 6 minutes or less.  Assistance is greatly appreciated.


EXAMPLE
11/17/2014 Agent Logged Out From 11:42am -12:29pm; 2:15pm-2:30pm Total 62min (1.2hrs)
150104-Any-File-Name-Export.txt
0
Comment
Question by:frugalmule
  • 10
  • 7
18 Comments
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530320
Is the first column the Agent's name? Second column some sort of Shift ID?
0
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530322
To add to my above question, take this example:

Thompson Tevin	1311367		9:28AM		10:18AM	12/9/2014
Thompson Tevin	1311528		10:33AM		10:55AM	12/9/2014
Thompson Tevin	1311492		11:00AM		4:08PM	12/9/2014

Open in new window


Is this one agent working three different assignments?
0
 

Author Comment

by:frugalmule
ID: 40530367
Yes.  I'd rather scrub that if possible when posting about it, but yes it is.  The second column is a phone extension number.  What we are calculating is time that is on the person's schedule, but where they are logged-out of the phone when they should be logged in.
0
 

Author Comment

by:frugalmule
ID: 40530432
Thank you very much for the help.  It is one campaign with login on the left and logout on the right.
0
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530621
This is getting into the realm where I would probably do the entire import in VBA code.   Are the export files always in ascending date/time order?

Without doing any coding, I have this:

SELECT A.EmpName, A.Extension, A.Login, A.Logout, A.WorkDate, DMin("[Login]","Export","[EmpName] = '" & [EmpName] & "' AND [WorkDate] = #" & [WorkDate] & "# AND [Login] > #" & [Logout] & "#") AS Expr1, DateDiff("n",[Logout],[Expr1]) AS Expr2
FROM Export AS A
WHERE (((DMin("[Login]","Export","[EmpName] = '" & [EmpName] & "' AND [WorkDate] = #" & [WorkDate] & "# AND [Login] > #" & [Logout] & "#")) Is Not Null));

Open in new window


HOWEVER, this still runs into a snag on times that are past midnight.  This can probably be fixed by running an update query for records where the logout time is less than the login time, you could add a day to the logout time.   (This is an example of the multitude of steps that need to be taken to massage the data first.)

Also, regarding manual steps..  Access' standard import from text had issue with importing the times to DateTime type.  However, after importing them to "Text" fields, and then going into table design and changing those fields to DateTime, it does the conversion just fine... (go figure..).  

Doing all this in VBA code would be less complicated (in my mind), and I'll take a look again this evening to see if someone else hasn't come along with a more elegant solution.  :D
0
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530627
Correcting for the dates that are beyong midnight, you could run the following update query:

UPDATE Export SET Export.Logout = DateAdd("d",1,[Logout])
WHERE (((Export.Logout)<[Login]));

Open in new window

0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40530638
Extending the above a little further, and having run the update query above to take care of "Past-midnight" dates, the following query then gives you all break times along with the length of the break in minutes:

SELECT A.EmpName, A.Extension, A.Login, A.Logout, A.WorkDate, DMin("[Login]","Export","[EmpName] = '" & [EmpName] & "' AND [WorkDate] = #" & [WorkDate] & "# AND [Login] > #" & [Logout] & "#") AS NextLoginToday, DateDiff("n",[Logout],[NextLoginToday]) AS Minutes
FROM Export AS A
WHERE (((DMin("[Login]","Export","[EmpName] = '" & [EmpName] & "' AND [WorkDate] = #" & [WorkDate] & "# AND [Login] > #" & [Logout] & "#")) Is Not Null));

Open in new window

0
 

Author Comment

by:frugalmule
ID: 40530642
Perfect.  Let me see if I can figure out how to implement.

1. Open MS Access
2. Import ...    ?
0
 

Author Comment

by:frugalmule
ID: 40530652
I will be comparing the report to another database which I do not have control of and cannot get control of.  What I will want to do is verify the numbers produced by this database, confirm by looking at another system that no ATO (approved time off) was entered for the discrepancy, and then enter those numbers into the database pictured below that I have no control over.capture
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530653
And, lastly, to get you closer to the output you were looking for (albeit missing the detail of each break..):

SELECT B.WorkDate, B.EmpName, Sum(B.Minutes) AS SumOfMinutes
FROM (
      SELECT * FROM 
      (
          SELECT A.EmpName, A.Extension, A.Login, A.Logout, A.WorkDate, DMin("[Login]","Export","[EmpName] = '" & [EmpName] & "' AND [WorkDate] = #" & [WorkDate] & "# AND [Login] > #" & [Logout] & "#") AS NextLoginToday, DateDiff("n",[Logout],[NextLoginToday]) AS Minutes
          FROM Export AS A
          WHERE (((DMin("[Login]","Export","[EmpName] = '" & [EmpName] & "' AND [WorkDate] = #" & [WorkDate] & "# AND [Login] > #" & [Logout] & "#")) Is Not Null))
     )

      WHERE Minutes > 6
)  AS B
GROUP BY B.WorkDate, B.EmpName;

Open in new window


Note, this also excludes breaks that are less than 6 minutes in duration.
0
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530659
OK, so as far as those steps go...

From "Import"...  import the file from a text file.  On the step where you have to set the field types for each field, you'll want to make the Login/Logoff times type "Text" (for some reason the import doesn't convert them to DateTime correctly).

AFTER you import to a table, you can go into "Table Design" mode and change the field types of those Login/Logoff fields to DateTime.  They will convert currectly this way.  Save the table.

At that point run the UPDATE query I specified above  (You'll need to adjust table and field names accordingly, to match what you name the fields when you do the import...)

From there, the last SELECT query I posted should work... of course, you'll also need to adjust accordingly to match tabel and field names that you specify.  

Not the cleanest of solutions, though I'm not sure there is a cleaner way outside of doing the import in code, reading in a file one line at a time, comparing it to the next line, etc...
0
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530667
If this helps at all, here is the database file I was working with while trying to work this out...  I named the big query that does everything "TheWholeEnchilada".  :D
Database15.accdb
0
 

Author Comment

by:frugalmule
ID: 40530681
Thank you for the help very much.  Unfortunately, if it is that messy, it will defeat the purpose of coding it.  As for break and lunch, they use what we call aux codes for that, so they should still be logged in during those times.  I'm looking at the process but am afraid that I need a cleaner solution.  I just posted a similar item in the excel area here http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28590451.html.  It's not the same project.  I was hoping to expand it much larger but on seeing how messy this solution is, the spreadsheet solution might prove more elegant/usefull.
0
 

Author Comment

by:frugalmule
ID: 40530685
It even looks as though I don't actually get a download when clicking on the Database15.accdb file.  I get some text gibberish instead.  It could be the old IE that we are forced to use, or it could be network restriction.
0
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530711
Try right clicking the link, then "Save as".

But, yeah, the solution I provided is complicated.   In all likeliness, there may be a better solution overall.  However, without hands-on knowledge of all of the parts you have to deal with, it's hard to say.  

The fact that you have multiple files, as well, makes for additional work.  And, if you're using this input to calculate PTO/ETO, you would also need to know all of the dates that an employee was scheduled to work.  The login/logout data would not alert you to days where an employee was scheduled to work but did not work that day.

As I indicated, a small VBA script would allow you to 1) browse for a file to open, then 2) output the break times by day for that file... however, how many individual files are you talking about having to process?

I could cobble something together if that sounds like it would be helpful...
0
 

Author Comment

by:frugalmule
ID: 40530715
Thanks very much.  I really do appreciate it.

I really think what is pictured in this question is going to be the easiest solution at the moment http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28590451.html

I'm hoping we can go from there.
0
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 450 total points
ID: 40530764
When there is a logout time after midnight, is the date on that line the date of login or logout?  I ask because, from the sample file:

Thompson Tevin	1311504		9:29AM		6:00PM	10/30/2014
Thompson Tevin	1311504		6:00PM		12:45AM	10/31/2014
Thompson Tevin	1311504		9:30AM		5:00PM	10/31/2014

Open in new window


If this person logged off at 12:45am on 10/31/2014, then they logged ON at 6:00pm 10/30 -- so they were on for a little over 15 hours.  However, if the log IN was 6:00pm on 10/31 (meaning they logged OUT on 11/1/2014) then they still have the same 15+ hour day, except with a 1-hour break in between 5 and 6pm.  Just wondering which it is.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 50 total points
ID: 40531118
You can sum the times like this:

SELECT
    TimeLog.Name,
    TimeLog.Date,
    Sum(TimeValue(CDate(1+CDate([TimeStop])-CDate([TimeStart])))*24) AS Hours
FROM
    TimeLog
GROUP BY
    TimeLog.Name,
    TimeLog.Date;

This will give you the attended shifts:

Name      Date      Hours
Thompson Tevin      16-10-2014      8,3
Thompson Tevin      17-10-2014      8,31666666666667
Thompson Tevin      18-10-2014      8,46666666666667
Thompson Tevin      20-10-2014      7,58333333333333
Thompson Tevin      21-10-2014      8,41666666666667
Thompson Tevin      22-10-2014      2,38333333333333
Thompson Tevin      23-10-2014      8,4
Thompson Tevin      24-10-2014      8,5
<snip>

Have a table with all shifts and create an outer join to the above to create a list of all shifts.

/gustav
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now