ms-access合并两个Id相似的记录

Question

我试图合并两个记录与类似的Id和类似的日期在考勤应用程序我有这个表code=1表示开始， code=2表示结束

Id        Date           Time                 Code
----------------------------------------------------------------------------
1         2016.05.01     12:15                 1
1         2016.05.01     18:15                 2
2         2016.05.02     14:35                 1
2         2016.05.02     20:35                 2
x         xxxxxxxxxx     xxxxx                 x

，我想用这种方式显示我的表

Id        Date           Time_Start          Time_Finish
------------------------------------------------------------------------
1         2016.05.01     12:15               18:15
2         2016.05.02     14:35               20:35
x         xxxxxxxxxx     xxxxx               xxxxx

i found this query

SELECT Id,Date,
 MAX(CASE  WHEN code =1 THEN Time END) AS  Time_Start,
 MAX(CASE WHEN code =2 THEN Time END) AS  Time_Finish
FROM tbl_attendance
GROUP BY Id,  Date

但实际上ms-access不支持CASE WHEN我也不能在正确的结构中使用IIF来代替

Answer 1

可以(采用逻辑方法忽略代码):

SELECT 
    Id,
    [Date],
    Min(CDate([Time])) AS Time_Start,
    Max(CDate([Time])) AS Time_Finish
FROM 
    tbl_attendance
GROUP BY 
    Id,  
    [Date]

保持输出为文本:

SELECT 
    Id,
    [Date],
    Format(Min(CDate([Time])), "hh:nn") AS Time_Start,
    Format(Max(CDate([Time])), "hh:nn") AS Time_Finish
FROM 
    tbl_attendance
GROUP BY 
    Id,  
    [Date]

或者:

SELECT 
    Id,
    [Date],
    Sum(CDate(IIf([Code] = 1, [Time], "0"))) AS Time_Start,
    Sum(CDate(IIf([Code] = 2, [Time], "0"))) AS Time_Finish
FROM 
    tbl_attendance
GROUP BY 
    Id,  
    [Date]