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
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
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
0
Gustav BrockCIOCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Thank you. This brings me to the correct solution.
0
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.