Querying Times Using Greater Than/Less Than Criteria

I am trying to build a query that tells me if a user logged into the system late.

Table "Temp_Agent_Login" has their logins and logouts and their logid
Table "Reference_Agents" has their logid and emp_id
Table "Temp_Reference_Segments" that has their scheduled login and logout time and emp_id.

I built it in Access 97. Here is the SQL view:

SELECT Temp_Agent_Log.row_date, Reference_Agents.CMSName, Temp_Agent_Log.logid, Min(Temp_Agent_Log.login) AS MinOflogin, Temp_Reference_Segments.START_MOMENT
FROM Temp_Agent_Log INNER JOIN (Reference_Agents INNER JOIN Temp_Reference_Segments ON Reference_Agents.AWID = Temp_Reference_Segments.EMP_ID) ON Temp_Agent_Log.logid = Reference_Agents.CMS_ID
GROUP BY Temp_Agent_Log.row_date, Reference_Agents.CMSName, Temp_Agent_Log.logid, Temp_Reference_Segments.START_MOMENT, Temp_Reference_Segments.SEG_CODE
HAVING (((Temp_Reference_Segments.SEG_CODE)="shift"))
ORDER BY Reference_Agents.CMSName;

I want the query only to output the records where Min(Temp_Agent_Log.login) is greater than Temp_Reference_Segments.START_MOMENT by 5 or more minutes.

Min(Temp_Agent_Log.login) and Temp_Reference_Segments.START_MOMENT are both in Long Time format (e.g. 6:00:00 AM).

I tried to put this in the criteria for Min(Temp_Agent_Log.login) but got no results:
> [START_MOMENT]

I also tried this with no results:
> [Temp_Reference_Segments]![START_MOMENT]

I'm stuck. I have no clue how to proceed.
makilaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jerryb30Connect With a Mentor Commented:
SELECT Temp_Agent_Log.row_date, Reference_Agents.cmsName, Temp_Agent_Log.logID, Min(Temp_Agent_Log.login) AS MinOflogin, Temp_Reference_Segments.start_moment
FROM Temp_Agent_Log INNER JOIN (Reference_Agents INNER JOIN Temp_Reference_Segments ON Reference_Agents.awid = Temp_Reference_Segments.emp_id) ON Temp_Agent_Log.logID = Reference_Agents.cms_ID
GROUP BY Temp_Agent_Log.row_date, Reference_Agents.cmsName, Temp_Agent_Log.logID, Temp_Reference_Segments.start_moment, Temp_Reference_Segments.seg_code
HAVING (((Temp_Reference_Segments.seg_code)="shift") AND ((DateDiff("n",[start_moment],Min([login])))>4))
ORDER BY Reference_Agents.cmsName;
Instead of using WHERE aggregation, I merely used EXPRESSION as aggregation, and unchecked the VISIBLE box.
0
 
jerryb30Commented:
Try this (in design view)
Add another column, using WHERE as the aggregation
Datediff("n", temp_reference_segments.start_moment,min(temp_agent_log.login)) >4
BTW-What results are you getting?
0
 
p912sCommented:
Use the DateDiff function to compare your dates.

  x=DateDiff("n",date1,date2)

This would return the difference in minutes between two dates then you can test like you wanted.

  If x>5 then...

Here's a reference online. Also look in your help file for more on the function.
http://www.techonthenet.com/access/functions/date/datediff.php
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
makilaAuthor Commented:
jerryb30-

I did what you said in design view and get this error message:
Can't have aggregate function in WHERE clauses

I created a new column and in the Field just let it autopop with Expr1: [Expr1], I left Table blank, selected Where for the Total, unchecked Show and put your formula in the Criteria.
0
 
makilaAuthor Commented:
Perfect! That worked. Thanks!
0
 
jerryb30Commented:
Thanks. Glad to help.  
0
All Courses

From novice to tech pro — start learning today.