Time comparison based on date - Late Report

[gryphin81]

Hello Everyone,

This is my first post excuse my noobness
My question is I have a table that gets information imported from Avaya CMS Supervisor. It provides me with the information below: Agent Name - Login Time - Logout Time - Login Date. I need to be able to perform the items below

1. Look up an agent name (Below example "Agent 1")
2. Look at the earliest login based on the date (Below example = 7:30AM) Note* Our agent sometimes have to login multiple times a day for various activities.
3. Compare earliest login time to start schedule (Agent 1's start schedule is at 7:00AM so formula would be something like Login Time 7:30AM <= 7:00AM if false mark late)

My questions are what are some good ideas to get this accomplished. Is Access the right tool. I've tried Excel but date/time calculation is a pain. Would I need a another table that had the agents and their start times listed ?

[June7]

If agents have different start times then yes a table with this datum would be good otherwise requires hard coding the variable start time. Table much better.

[gryphin81]

for the response. I created an Agent Table called tblAgent that contains the Start times of all agents. I'm running into an issue that I can't solve. In Access I have the following sql statement in a query called qryLate.

SELECT Min(LoginTime), LoginDate
  FROM tblLogin 
  Where AgentName ='Agent 1'

What i'm trying to do is query a table called tblLogin for the lowest/earliest login time. I also need to see what date the login took place so I've included the LoginDate column from the tbleLogin table to obtain that information. When I run the code I get an error message stating. "Your query does not include the specified expression 'LoginDate' as part of an aggregate function." If I remove ", LoginDate from the SELECT statement it works, but I need the date information to be displayed. I have xampp with mysql and tested it on a table with the same properties and information and that code works. Not sure what I'm doing wrong

[June7]

I don't know xampp and MySQL but apparently what you want is a bit more complicated in Access.

Aggregate functions must be used in Totals (GROUP BY) query.

Why are date and time parts in separate fields? The time is saved as text, not date/time data type. This won't work. 10:00PM will sort before 7:30AM

[gryphin81]

I don't know xampp and MySQL but apparently what you want is a bit more complicated in Access.

Aggregate functions must be used in Totals (GROUP BY) query.

That expression will return the minimum time in the database, regardless of date. Is that what you really want? Why are date and time parts in separate fields?

Can try the TOP N qualifier.

I'll try the TOP N qualifier, not too familiar with it need to research. To answer your question I'm performing a late report on agents. The information contained within the table - tblLogin is imported from Avaya CMS Supervisor (Phone application agents login too). I need to validate if an agent logged in on time. Agents have multiple logins during the same day, so I need to use the earliest login time and compare that against their start time. Hope that explains it a little better.

[June7]

I edited my previous post but apparently you read it first.

[gryphin81]

I'm a little confused.

My LoginTime field is setup as a Date/Time field



I added a login time of 10 pm to the table.


When I run the below query it worked and returned 7:30:00 AM as the Min value.

SELECT Min(LoginTime)
  FROM tblLogin 
  Where AgentName ='Agent 1'

I just can't seem to display the date field or any other field after I perform Min(LoginTime), I keep getting the error I initially posted.

[June7]

Your first post showed the data structured and aligned as text. Did you try TOP N?

Alternatives are nested subquery or domain aggregate.

Review http://allenbrowne.com/subquery-01.html#TopN

Example with DMin():

SELECT * FROM tablename WHERE LoginTime = DMin("LoginTime", "tablename", "AgentName='" & [AgentName] & "' AND LoginDate=#" & [LoginDate] & "#");

[gryphin81]

Just wanted to thank you June7. I couldn't have made it this far without you. Your guidance has proven to be invaluable. Below is the code I used to resolve my dilemma.

SELECT Min(l.LoginTime), l.LOGINID, l.LOGOUTDATE
  FROM tblLogin as l
   INNER JOIN tblAgent as a
   ON a.AgentName = l.LOGINID
  Where a.AgentSchedule > l.LoginTime
  GROUP BY LOGOUT_DATE, LOGID