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

问题描述

我在 Microsoft Access 中编写查询时遇到问题.这是我的表的外观以及我想从中检索数据的位置:

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:

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

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.

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

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.

我们使用条件来确保签出在同一天，并且晚于签入，并使用 Min 聚合来确保它是下一次(可能的最低时间).

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屋！