asked on

MS ACCESS: Align Timestamps in two different tables

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

als315

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Simmons

ASKER

Thank you. This brings me to the correct solution.

Gustav Brock

You are welcome!