Microsoft Access中的考勤查询 [英] Time Attendances query in Microsoft Access

问题描述

我在用Microsoft Access编写查询时遇到麻烦. 这是我的表的外观，以及我要从中检索数据的位置:

我想编写一个查询，结果如下:

如您在第一张表中所见，员工每天可以签入和签出两次以上.员工首次签到时，日期/时间应放在第一栏签到"中.当他第二次签到时，日期/时间应放在第二列签出"中.当他第三次签到时，日期/时间应放在签到"列中，依此类推.

我从上一个问题中学到，对于类似的情况，我可以使用子查询和模运算符.但是我不知道如何使查询对上述问题有效.

解决方案

让我们从上一个问题的答案开始，然后从那里开始.

此查询定义它是签入还是签出.我们称之为qryCheckInOut

SELECT EmployeeID, 
    timeInOut, 
    IIF(
        (SELECT COUNT(*) 
        FROM MyTable s 
        WHERE s.EmployeeID = m.EmployeeID 
        AND s.timeInOut <= m.timeInOut
        AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1, "I", "O") As OriginType
FROM MyTable m

然后，我们可以从该查询中获取签入，并使用子查询获取签出.

我们使用条件来确保退房是在同一天，而不是在退房之后进行的，并使用Min汇总以确保是下一次(尽可能短的时间).

SELECT q.EmployeeID, 
    q.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM qryCheckInOut s
    WHERE s.EmployeeID = q.EmployeeId
    AND s.TimeInOut > q.TimeInOut
    AND s.TimeInOut <= Int(q.TimeInOut) + 1) As TimeOut
FROM qryCheckInOut q
WHERE q.OriginType = 'I'

请注意，在第二个查询的子查询中，您无需检查它是否是签入或签出，因为比同一天的签入时间高的最短时间始终是签出. /p>

如果要在单个查询中执行此操作，则可以使用以下查询.但是，调试起来将非常困难

SELECT m.EmployeeID, 
    m.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM MyTable s
    WHERE s.EmployeeID = m.EmployeeId
    AND s.TimeInOut > m.TimeInOut
    AND s.TimeInOut <= Int(m.TimeInOut) + 1) As TimeOut
FROM MyTable m
WHERE 
    (SELECT COUNT(*) 
    FROM MyTable s 
    WHERE s.EmployeeID = m.EmployeeID 
    AND s.timeInOut <= m.timeInOut
    AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1

I'm having troubles with writing a query in Microsoft Access. This is how my table looks like and where i want to retrieve data from:

I want to write a query that has the following result:

As you can see in the first table an employee can check IN and OUT more than 2 times a day. When a employee checks in for the first time the Date/time should be placed in the first colum "CheckIn". When he checks in for the second time the Date/time should be placed in the second column "CheckOut". When he checks in for the 3th time the Date/time should be placed in the column "CheckIn" and so on.

I have learned from my previous question that I can use a subquery and the modulus operator for a similar situation like this. But I can't figure out how i can make the query work for the problem above.

解决方案

Let's start with the answer from the previous question, and work our way from there.

This query defines if it's a check in, or check-out. Let's call it qryCheckInOut

SELECT EmployeeID, 
    timeInOut, 
    IIF(
        (SELECT COUNT(*) 
        FROM MyTable s 
        WHERE s.EmployeeID = m.EmployeeID 
        AND s.timeInOut <= m.timeInOut
        AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1, "I", "O") As OriginType
FROM MyTable m

Then, we can get the check-ins from that query, and use a subquery to get the check-outs.

We use conditions to make sure the check out is on the same day, and later than the check in, and use the Min aggregate to make sure it's the next time (the lowest possible time).

SELECT q.EmployeeID, 
    q.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM qryCheckInOut s
    WHERE s.EmployeeID = q.EmployeeId
    AND s.TimeInOut > q.TimeInOut
    AND s.TimeInOut <= Int(q.TimeInOut) + 1) As TimeOut
FROM qryCheckInOut q
WHERE q.OriginType = 'I'

Note that, in the subquery of the second query, you don't need to check if it's a check in or check out, since the lowest time higher than the check in on the same day always is a check out.

If you want to do it in a single query, you can use the query below. However, it will be substantially harder to debug

SELECT m.EmployeeID, 
    m.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM MyTable s
    WHERE s.EmployeeID = m.EmployeeId
    AND s.TimeInOut > m.TimeInOut
    AND s.TimeInOut <= Int(m.TimeInOut) + 1) As TimeOut
FROM MyTable m
WHERE 
    (SELECT COUNT(*) 
    FROM MyTable s 
    WHERE s.EmployeeID = m.EmployeeID 
    AND s.timeInOut <= m.timeInOut
    AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1

这篇关于Microsoft Access中的考勤查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！