人資系統常需將打卡明細 (每筆一列) 轉為「每員工每天一列、多次打卡橫向展開」的報表格式。本文示範用兩層查詢 (選擇查詢 + 交叉查詢) 完成轉換。
| 卡號 | 日期 | 時間 |
|---|---|---|
| 123456 | 2006-07-17 | 08:00 |
| 123456 | 2006-07-17 | 12:01 |
| 123456 | 2006-07-17 | 13:31 |
| 123456 | 2006-07-18 | 08:05 |
| 123456 | 2006-07-18 | 12:00 |
| 123456 | 2006-07-19 | 12:00 |
| 卡號 | 日期 | 時間1 | 時間2 | 時間3 |
|---|---|---|---|---|
| 123456 | 2006-07-17 | 08:00 | 12:01 | 13:31 |
| 123456 | 2006-07-18 | 08:05 | 12:00 | |
| 123456 | 2006-07-19 | 12:00 |
qry_Punch_Seq 加入序號SELECT 卡號, 日期, 時間,
"時間" & DCount("[時間]","datarecord",
"時間 <= #" & Format([時間],"Short Time") & "# AND 日期 = #" & [日期] & "# AND 卡號='" & [卡號] & "'") AS 時段
FROM datarecord
GROUP BY 卡號, 日期, 時間,
"時間" & DCount("[時間]","datarecord",
"時間 <= #" & Format([時間],"Short Time") & "# AND 日期 = #" & [日期] & "# AND 卡號='" & [卡號] & "'")
ORDER BY 卡號, 日期, 時間;
DCount 計算同卡號、同日期中時間小於等於當前列的筆數,產生 時間1、時間2... 序號。
qry_Punch_CrosstabTRANSFORM First(qry_Punch_Seq.時間) AS 時間值
SELECT qry_Punch_Seq.卡號, qry_Punch_Seq.日期
FROM qry_Punch_Seq
GROUP BY qry_Punch_Seq.卡號, qry_Punch_Seq.日期
PIVOT qry_Punch_Seq.時段;
TRANSFORM ... PIVOT 將 時段 欄位值轉為欄位標題,First 取每群組第一筆時間值。
DCount 效能較差,可改用視窗函數 (Access 不支援) 或匯出至 SQL Server 處理後再回寫。