訪問：製作具有多個與列相同的值的數據透視表
第一次發帖，謝謝邀請！
目前，我們有一個 Access 數據庫連接到兩個訪問控制設備，跟踪不同狀態的時間：簽入、簽出、休息、休息、加班、加班。
然而，我們的設備如何管理這一點，是它們獨立地記錄每個時間，所以在 Access 數據庫中，我們有不同的行告訴使用者名、日期和時間、狀態、設備和其他有趣的東西，遺憾的是這些東西對解決問題沒有幫助。不過，我想關注的是時間，我想使用 Access 的 CrossTab 模式、TRANSFORM 和 PIVOT 以及其他內容將其放入單獨的列中。
主要問題是員工白天有很多休息時間，有時還有很多加班時間，我無法判斷第一次或第二次休息/加班時間是什麼時候。
這是我與戴夫的一個例子：
Name | Date | Auth | Time | ------------------------------------------------------------------ Dave | 21.01.2019 | CheckOut | 18:00 | Dave | 21.01.2019 | BreakIn | 16:45 | Dave | 21.01.2019 | BreakOut | 16:30 | Dave | 21.01.2019 | BreakIn | 14:00 | Dave | 21.01.2019 | BreakOut | 13:00 | Dave | 21.01.2019 | BreakIn | 10:00 | Dave | 21.01.2019 | BreakOut | 09:45 | Dave | 21.01.2019 | CheckIn | 07:30 |這就是我想要實現的目標：
Name | Date | CheckIn | BreakOut1 | BreakIn1 | BreakOut2 | BreakIn2 | BreakOut3 | BreakIn3 | CheckOut | ---------------------------------------------------------------------------------------------------------------- Dave | 21.01.2019 | 07:30 | 09:45 | 10:00 | 13:00 | 14:00 | 16:30 | 16:45 | 18:00 |現在我設法獲得了一些我計劃擁有的東西，但我只能為每個 Auth 輸出一列。如果我設法以這種方式獲取數據，我或許能夠準確計算出員工工作了多少小時。
您認為使用 CrossTab 這樣做是個好主意嗎？如果是這樣，你能幫我實現目標嗎？並不是真正尋找即時解決方案的片段，而是思考如何實現它的多種方法。一般來說，我對 Access 和數據庫有標準的專業知識，所以一個人永遠不會停止學習！
在此先感謝，祝您有美好的一天。
獅子座
很明顯，您打算創建一個交叉表，但我不禁注意到實際的最終目標：“我可能能夠準確計算出多少小時”。實際上，如果您有顯示的最終數據，則可以獲得總時間，但還有其他標準化形式允許求和，而無需特殊程式碼來處理多個中斷和/或不同數量的中斷。以下是一系列查詢，可為您提供總時間。如果您仍然喜歡您的交叉表格式，前幾個查詢提供了一個表單，允許您根據需要在不同的休息時間上進行旋轉。
這組查詢可能看起來有點矯枉過正，但每個都有其目的，並且這種查詢編碼風格與將數據視為一個集合是兼容的。這只是對集合的一系列操作。在一個查詢中完成所有事情的願望是一種人為的要求。其中一些可以組合為嵌套查詢，但這會消除 Access 查詢設計器中的一些便利，並且 Access 往往會與我發現的 TRANSFORMS 的嵌套查詢相匹配。
命名查詢
$$ BreakSeq $$. 這個相同的查詢可以產生像“BreakOut1”這樣的組合標籤，只需稍作修改：
SELECT Data.Naem, Data.Daet, Data.Time, IIf([Auth]='BreakIn','In','Out') AS InOut, 1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND [Daet]=#" & [Daet] & "# AND [Auth]='" & [Auth] & "' AND [Time] < #" & [Time] & "#") AS [Number] FROM Data WHERE (Data.Auth Like "Break*") ORDER BY Data.Naem, Data.Daet, Data.Time;命名查詢
$$ BreakNorm $$. Transform 需要一個聚合函式，但假設與分組一起保證唯一值，因此
FIRST()將返回這樣的唯一值。此外，如果您的表已使用此表單進行規範化，後續查詢將更容易生成：TRANSFORM First(BS.Time) AS FirstOfTime SELECT BS.Naem, BS.Daet, BS.Number, "Break" AS Type FROM BreakSeq AS BS GROUP BY BS.Naem, BS.Daet, BS.Number ORDER BY BS.Naem, BS.Daet, BS.Number PIVOT BS.InOut;命名查詢
$$ BreakDetail $$. 這會產生負分鐘數，可用於添加到整體 CheckIn 和 Out 跨度：
SELECT BN.*, DateDiff("n",[In],[Out]) AS Minutes FROM BreakNorm AS BN;命名查詢
$$ BreakSum $$:
SELECT BD.Naem, BD.Daet, BD.Type, Sum(BD.Minutes) AS BreakMinutes FROM BreakDetail AS BD GROUP BY BD.Naem, BD.Daet, BD.Type;現在為 CheckIn 和 CheckOut… 命名查詢
$$ CheckSeq $$:
SELECT Data.Naem, Data.Daet, IIf([Auth]='CheckIn','In','Out') AS InOut, Data.Time FROM Data WHERE (Data.Auth Like "Check*");命名查詢
$$ CheckNorm $$:
TRANSFORM First(CS.Time) AS FirstOfTime SELECT CS.Naem, CS.Daet, "Check" AS Type FROM CheckSeq AS CS GROUP BY CS.Naem, CS.Daet ORDER BY CS.Naem, CS.Daet PIVOT CS.InOut;命名查詢
$$ CheckDetail $$:
SELECT CN.*, DateDiff("n",[In],[Out]) AS Minutes FROM CheckNorm AS CN;現在將結果與每個姓名和日期的總工作分鐘數相結合：
SELECT BS.Naem, BS.Daet, [CD].[Minutes]+[BS].[BreakMinutes] AS WorkingMinutes FROM BreakSum AS BS INNER JOIN CheckDetail AS CD ON (BS.Daet = CD.Daet) AND (BS.Naem = CD.Naem) ORDER BY BS.Naem, BS.Daet;該解決方案假設如下。如果無法保證這些假設中的任何一個，則應辨識並解決異常，否則您需要編寫特殊邏輯來避免錯誤和虛假數字：
- 每個姓名和日期恰好有一個 CheckIn 和一個 CheckOut 條目。
- 對於每個 BreakOut 條目，都有一個對應的 BreakIn 條目，因此沒有不匹配的對。
- 進出時間是正確的，因此 CheckOut 時間在 CheckIn 時間之後，BreakOut 時間在相應的 BreakIn 時間之前，並且沒有重疊的休息時間。
- CheckIn、CheckOut、BreakIn、BreakOut 是唯一的身份驗證類型。
- 文本欄
$$ Naem $$和$$ Auth $$不包含撇號 (’)。否則，必須特別注意在某些查詢中正確界定這些值。
如果您堅持顯示的最終形式，這裡有一個查詢較少的解決方案。對不起，如果我做得太即時了。
命名查詢
$$ AllSeq $$:
SELECT Data.Naem, Data.Daet, Data.Time, Data.Auth, IIf([Auth] Like "Break*","Break","Check") AS Type, IIf([Auth] Like '*In','In','Out') AS InOut, 1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND Daet=#" & [Daet] & "# AND Auth='" & [Auth] & "' AND Time < #" & [Time] & "#") AS [Number], IIf([Auth] Like "Break*",[Auth] & [Number],[Auth]) AS AuthNum FROM Data ORDER BY Data.Naem, Data.Daet, Data.Time;命名查詢
$$ AllCross $$:
TRANSFORM First(AZ.Time) AS FirstOfTime SELECT AZ.Naem, AZ.Daet FROM AllSeq AS AZ GROUP BY AZ.Naem, AZ.Daet PIVOT AZ.AuthNum;命名查詢
$$ AllCrossSorted $$:
SELECT AC.Naem, AC.Daet, AC.CheckIn, AC.BreakOut1, AC.BreakIn1, AC.BreakOut2, AC.BreakIn2, AC.BreakOut3, AC.BreakIn3, AC.CheckOut FROM AllCross AS AC;