Query Returning two or more records, want to select just one

[dbaker5204]

I have a query that produces login in times for our agents. On a normal day, an agent would only have 1 log in time, but due to phone system issues they may get logged out and have to log back in. Thus my query is returning 2 log in times causing an issue when I try to use the log in time for a calculation as it is using the second log in time instead of the first one. Any solutions for fixing this? I've been trying to think of an IIF statement but the ones I have tried have not worked. I have tried grouping as well, but since the records do not match exactly, that does not work either.

 

[arnelgp]

Add subquery to the query:

Select agentId, agentname, logindate, (select min(t1.logintime) from yourtable as t1 where t1.agentid=yourtable.agentid and t1.logindate=yourtable.logindate) as logintime from yourtable;

 

[dbaker5204]

I have never used a subquery before. Below is my SQL, what would I need to type:

SELECT tbl_AgentSchedule.[Agent Name], tbl_AgentSchedule.[Start Time], tbl_AgentSchedule.[End Time], tbl_LogInLogOut.[Login Date], tbl_LogInLogOut.[Log In Time], tbl_LogInLogOut.[Reason code], tbl_LogInLogOut.[Log Out Time], [Log In Time]-[Start Time] AS Expr1, [Log In Time]*86400 AS Expr2, [Start Time]*86400 AS Expr3, [Expr3]-[Expr9] AS Expr4, IIf([Expr4]>0,"0",[Expr4]) AS Expr5, [Expr5]/86400 AS [Time from Start of Shift], [Log Out Time]*86400 AS Expr6, [End Time]*86400 AS Expr7, [Expr6]-[Expr7] AS Expr8, IIf([Expr8]>0,"0",[Expr8]) AS [Time to End of Shift in Seconds], [Time to End of Shift in Seconds]/86400 AS [Time to End of Shift], [tbl_Exceptions Query].[Begin Time], [Start Time]*86400 AS [Start Time in Seconds], [Begin Time]*86400 AS [Begin Time in Seconds], IIf([Begin Time in Seconds]=[Start Time in Seconds],[Start Time in Seconds],[Expr2]) AS Expr9, [Expr9]/86400 AS [Start Time in Mins], tbl_LogInLogOut.[LogIn Duration], [LogIn Duration]*86400 AS [Log In Duration in Seconds]
FROM (tbl_AgentSchedule INNER JOIN tbl_LogInLogOut ON tbl_AgentSchedule.[Agent Name] = tbl_LogInLogOut.[Agent Name]) LEFT JOIN [tbl_Exceptions Query] ON tbl_AgentSchedule.[Agent Name] = [tbl_Exceptions Query].[Last Name, First Name]
WHERE (((tbl_LogInLogOut.[Login Date])=[Enter Start Date:]));

 

[arnelgp]

if it is ok with you will just build a function to get the first login.
copy and pasted in module in vba. (alt-f11, on menu insert->module):

public function getFirstTimeIn(agentName as variant, loginDate as variant) as date
	dim db as dao.database
	dim rs as dao.recordset
	set db=currentdb
	set rs=db.openrecordset("select min([log in time]) from tbl_logInLogOut where " & _
	"[agent name]=" & chr(34) & agentName & chr(34) & " and " & _
	"[login date]=#" & format(loginDate,"mm/dd/yyyy") & "#")
	with rs
		if not (.bof and .eof) then 
			.movefirst
			getFirstTimeIn=![Log in time]
		end if
		.close
	end with
	set rs=nothing
	set db=nothing
end function

now, back to your query.
replace:

tbl_LogInLogOut.[Log In Time]

with:

[Log In Time]: getFirstTimeIn([tbl_AgenSchedule].[Agent Name], [tbl_logInLogOut].[Login Date])