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.

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

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:

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.

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!

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...

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.

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.

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...

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.

Featured Post

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)

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 …