I have this table in access

Userinfo

Dept

Checkinout

But i want to create a Inner join that will show this table

This is the SQL that i used but i dont know how to put 0 as Out and 1 as In

SELECT Userinfo.userid as ID,UserInfo.Name as Name,Dept.DeptName as Dept , Checkinout.Checktime 



from ( Userinfo
inner join Checkinout on Userinfo.userid = Checkinout.userid )

inner join  Dept on Userinfo.DeptId =Dept.DeptId ;

CodePudding user response：

Starting from the non-normalized Checkinout you can get everything but Out directly. Then add a calculated field to the query to look up the appropriate Out Checktime for each In Checktime:

Out: (SELECT TOP 1 q.Checktime FROM  [Checkinout] as q WHERE  (q.userid = [Checkinout].[userid]) AND (q.Checktype = 0) AND  (q.Checktime > [Checkinout].[Checktime])  
ORDER BY q.Checktime ASC)

SQL:SELECT Userinfo.userid, Userinfo.Username, Dept.[Dept Name], Checkinout.Checktime AS [In], (SELECT TOP 1 q.Checktime FROM  [Checkinout] as q WHERE  (q.userid = [Checkinout].[userid]) AND (q.Checktype = 0) AND  (q.Checktime > [Checkinout].[Checktime])  
ORDER BY q.Checktime ASC) AS Out
FROM (Dept INNER JOIN Userinfo ON Dept.deptid = Userinfo.[dept id]) INNER JOIN Checkinout ON Userinfo.userid = Checkinout.userid
WHERE (((Checkinout.Checktype)=1))

'I used a nested query for the calculated field and calculated Out with the business rules that each user check-in must have a corresponding and later check-out by the same user without any check-in/check-outs between.