萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Access 教學 >> Access 基礎教學

Access 交叉查詢實戰:打卡記錄由直式轉橫式顯示

人資系統常需將打卡明細 (每筆一列) 轉為「每員工每天一列、多次打卡橫向展開」的報表格式。本文示範用兩層查詢 (選擇查詢 + 交叉查詢) 完成轉換。

原始資料範例

卡號日期時間
1234562006-07-1708:00
1234562006-07-1712:01
1234562006-07-1713:31
1234562006-07-1808:05
1234562006-07-1812:00
1234562006-07-1912:00

目標輸出

卡號日期時間1時間2時間3
1234562006-07-1708:0012:0113:31
1234562006-07-1808:0512:00
1234562006-07-1912:00

步驟 1:建立選擇查詢 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... 序號。

步驟 2:建立交叉查詢 qry_Punch_Crosstab

TRANSFORM 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 處理後再回寫。
copyright © 萬盛學電腦網 all rights reserved