MS ACCESS: Align Timestamps in two different tables

Ryan Simmons
Ryan Simmons used Ask the Experts™
on
Hello Experts,

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)

Open in new window


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.
Example1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your sample has some errors:
userid and resourceloginid are different, numeric field is compared as text, etc.
Please, prepare samples as small Access DB.
Try this query:
SELECT TOP 1 Case.userid, Case.[punch type], Case.[punch timestamp], Case.shiftseqid, Class.start, Class.end, Class.state
FROM (SELECT * FROM RawPunchRpt_Tbl WHERE shiftseqid =1)  AS [Case] LEFT JOIN (SELECT * FROM AgentStateDetailRpt WHERE State like "LOG IN")  AS Class ON Case.userid = Class.resourceloginid
ORDER BY Class.start;

Open in new window

DB29119665.accdb
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can create a query to find the closest login:

SELECT 
    RawPunchRpt_Tbl.userid, DateValue([start]) AS [Date], Min(DateDiff("s",[Punch Timestamp],[start])) AS TimeDiff
FROM 
    AgentStateDetailRpt, RawPunchRpt_Tbl
WHERE 
    (((RawPunchRpt_Tbl.userid)=[AgentStateDetailRpt]![resourceloginid]) 
    AND ((RawPunchRpt_Tbl.ShiftSeqID)=1) 
    AND ((AgentStateDetailRpt.state)="LOG IN") 
    AND ((DateDiff("d",[Punch Timestamp],[start]))=0))
GROUP BY 
    RawPunchRpt_Tbl.userid, DateValue([start]);

Open in new window

Save this as FirstLogin, and then create your query using this:

SELECT 
    RawPunchRpt_Tbl.userid, RawPunchRpt_Tbl.[punch type], RawPunchRpt_Tbl.[punch timestamp], RawPunchRpt_Tbl.[shiftseqid], AgentStateDetailRpt.[start], AgentStateDetailRpt.[end], AgentStateDetailRpt.[state]
FROM 
    (AgentStateDetailRpt 
INNER JOIN 
    RawPunchRpt_Tbl ON AgentStateDetailRpt.resourceloginid = RawPunchRpt_Tbl.userid) 
INNER JOIN 
    FirstLogin ON RawPunchRpt_Tbl.userid = FirstLogin.userid
WHERE 
    (((AgentStateDetailRpt.resourceloginid)=RawPunchRpt_Tbl!userid) 
    And ((RawPunchRpt_Tbl.ShiftSeqID)=1) 
    And ((DateDiff("s",[Punch Timestamp],[start]))=[TimeDiff]) 
    And ((AgentStateDetailRpt.state)="LOG IN") 
    And ((DateDiff("d",[Punch Timestamp],[start]))=0));

Open in new window

It is called DailyUserLogin in the attached sample.
DB29119665.accdb
Ryan SimmonsBusiness Analyst III

Author

Commented:
Thank you. This brings me to the correct solution.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial