Microsoft Access考勤查询：如何判断OriginType列的打卡进出状态？

Hey there! Let's dig into this attendance query problem you've been wrestling with for a week—building a fingerprint attendance app in VB6 is already a lot, so figuring out the IN/Out logic shouldn't add more stress.

From what you described, your table tracks employees who punch multiple times a day, and you need to map each record to an IN or OUT status. The most reliable way to do this is by ordering punch records by time for each employee and alternating the status starting with IN for the first punch of the day.

Here are a couple of practical solutions tailored to Microsoft Access:

1. Using ROW_NUMBER() (Access 2010+) #

If you're using a newer version of Access that supports window functions, this is the cleanest approach. We'll assign a row number to each employee's punches sorted by time, then use the row number's parity (odd/even) to mark IN/OUT:

SELECT 
    EmployeeID,
    CheckTime,
    OriginType,
    IIF(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY CheckTime) MOD 2 = 1, 'IN', 'OUT') AS AttendanceStatus
FROM 
    YourAttendanceTable;

Breakdown: #

• PARTITION BY EmployeeID: Groups records by individual employees so we handle each person's punches separately.
• ORDER BY CheckTime: Sorts each employee's punches chronologically—critical for getting the IN/Out sequence right.
• ROW_NUMBER() MOD 2: Checks if the row number is odd (first punch = IN) or even (second punch = OUT), repeating the pattern for subsequent punches.

2. For Older Access Versions (No Window Functions) #

If you're stuck with an older Access version that doesn't support ROW_NUMBER(), use a subquery to calculate the row count for each employee's prior punches:

SELECT 
    t1.EmployeeID,
    t1.CheckTime,
    t1.OriginType,
    IIF((SELECT COUNT(*) FROM YourAttendanceTable t2 
         WHERE t2.EmployeeID = t1.EmployeeID AND t2.CheckTime <= t1.CheckTime) MOD 2 = 1, 
        'IN', 'OUT') AS AttendanceStatus
FROM 
    YourAttendanceTable t1
ORDER BY 
    t1.EmployeeID, t1.CheckTime;

Breakdown: #

• The subquery counts how many punches the same employee has made on or before the current record's time—this acts as our row number.
• Again, we use the odd/even check to assign IN/Out status.

Handling Edge Cases #

What if an employee only punches once in a day? You can adjust the logic to flag this as an anomaly:

SELECT 
    t1.EmployeeID,
    t1.CheckTime,
    t1.OriginType,
    IIF(
        (SELECT COUNT(*) FROM YourAttendanceTable t2 
         WHERE t2.EmployeeID = t1.EmployeeID AND DateValue(t2.CheckTime) = DateValue(t1.CheckTime)) = 1,
        'IN (Missing OUT)',
        IIF((SELECT COUNT(*) FROM YourAttendanceTable t2 
             WHERE t2.EmployeeID = t1.EmployeeID AND t2.CheckTime <= t1.CheckTime) MOD 2 = 1, 
            'IN', 'OUT')
    ) AS AttendanceStatus
FROM 
    YourAttendanceTable t1
ORDER BY 
    t1.EmployeeID, t1.CheckTime;

Just remember to replace YourAttendanceTable with your actual table name, and adjust field names (like EmployeeID or CheckTime) to match your schema.

内容的提问来源于stack exchange，提问作者Tan Nguyen