Please review the example data attached.
The first table is called RawPunchRpt_Tbl and it contains a timecard for an employee. The second table is called AgentStateDetailRpt and it contains all the activities that the employee participated in tracked by a separate system. I want to find the closest start timestamp and state to the timestamp in the RawPunchRptTbl.
So here is the SQL code I tried in MS Access which I limited to just the shift start and the log in's:
SELECT userid, [punch type], [punch timestamp], [shiftseqid], [start], [end], [state], [reason]
FROM (SELECT * FROM RawPunchRpt_Tbl WHERE shiftseqid = "1") AS A LEFT JOIN (SELECT * FROM AgentStateDetailRpt WHERE State = "LOG IN") AS B ON (A.userid = B.resourceloginid) AND (A.[punch timestamp] <= B.start AND A.[punch timestamp] < B.end)
It sort of works but since there are two LOG IN states in the AgentStateDetailRpt table the code returns both of the timestamps instead of the closest timestamp.
Is there a way I can get the code to just return the start timestamp closest to the raw punch timestamp?
Thank you in advance for any assistance you can provide.