利用Access製作成績報表(三)(函數的應用)

2011-12-29

接續上篇文章(利用Access製作成績報表(二)(群組功能應用))，在前文中提到利用加入等級群組，將各等級分組統計，但是這樣的方式無法進行全班以座號排序列表，這樣與一般成績表習慣的呈現方式相異，一般成績表呈現應該如下圖(一)。簡單的說；我們希望以班級為群組，每班以座號排序，然後統計各等級的人數，應該要怎麼做？

圖(一)

首先我們先利用報表精靈測試看看是否可以達到我們的目的，經過測試要達到這樣的需求，沒有辦法直接利用報表精靈來完成，必須使用者在報表設計中加入額外的函數計算才能達成。

Access提供的函數很多，如何找到我們需要的函數，報表精靈完成的報表可以提供一個索引，利用以下的步驟影片完成一個"測試的成績"報表。

依照上述的步驟，可以完成以下的報表，在報表摘要的部分，我們可以看到一列資料 摘要 "班級" = 1 (29 詳細記錄) ，這列摘要資料代表班號為1的資料有29筆，因為我們以班級為群組，所以摘要資料中的筆數很明顯是班級群組的資料，所以這個資料來源必為計數的運算結果，我們可以透過報表的設計模式看到它是如何得到的。

(圖二)

在報表的視窗，點選"測試成積表"報表，然後按滑鼠右鍵，在浮動功能表中點選"設計檢視"。

(圖三)

在設計模式下我們可以看到報表的一些秘密，例如摘要 "班級" = 1 (29 詳細記錄) 這列資料是怎麼來的？

(圖四)

用滑鼠點選一下="摘要 " & "'班級' = " & " " & [班級] & " (" & Count(*) & " " & IIf(Count(*)=1,"詳細記錄","詳細記錄") & ")"這個資料區塊，可以發現報表是用一個"文字方塊控制項"(Text10)來呈現這個資料，在設計模式中可以看到報表其實也如同表單一樣，它是由一些"標簽控制項"與"文字方塊控制項"組合而成，然後散佈在報表的不同區塊中，"標簽控制項"主要呈現靜態的文字內容；例如欄位標題內容。而"文字方塊控制項"主要是隨著資料來源異動內容，簡單的說就是資料表的細部內容，所以文字方塊控制制項中會有一個"控制項資料來源"的屬性。

(圖五)

舉例來說仔細觀察右側的屬性表，可以發現這個Text10文字方塊控制項的資料來源是透過以下這列公式而來的：

="摘要 " & "'班級' = " & " " & [班級] & " (" & Count(*) & " " & IIf(Count(*)=1,"詳細記錄","詳細記錄") & ")"

最左邊=代表是這個控制項資料來源的屬性值等於 "摘要 " & "'班級' = " & " " & [班級] & " (" & Count(*) & " " & IIf(Count(*)=1,"詳細記錄","詳細記錄") & ")" 這個運算式運算的結果。

簡單說明這個運算式各個運算符號與函數的意義：

& 運算符號是字串相加，運算式字串必須用""號包括其中，舉例 "班級=" & "3" 輸出的結果班級=3 。

[班級] 這個是代表報表表單中名為"班級"的"文字方塊控制項"欄位的資料，因為我們利用資料表中"班級"欄位分群組，所以在設計模式中報表"班級群組首"區塊中有一個名為"班級"的"文字方塊控制項"，在運算式中我們用[班級]來代表，其呈現的是班級群組所代表的值。

Count(expr)這個函數是SQL聚合函數，expr是代表函數參數，expr可以輸入指定的欄位名稱，例如Count('座號')則為計算座號欄的記錄數，但是若座號欄為Null 則Count不計算記錄數，如果要計算多個欄位用&來連接，舉例Count('座號 & 姓名')，計算查詢傳回的座號和姓名兩欄的記錄數目，但是如果兩欄記錄都為Null則則Count不計算記錄數。函數中參數若為*，則Count(*) 會將所有記錄數目傳回，包括Null的欄位記錄，所以為了確定可計算出記錄筆數，報表精靈在運算式中使用Count(*) 。

另外利用報表精靈產生報表，報表中不同區塊其代表的是不同查詢條件產生的查詢結果，所以當以班級為群組層次，報表群組區塊(包括群組首與群組尾)其查詢相當於執行以下的SQL語法

SELECT 成績表.[班級] FROM 成績表 GROUP BY 成績表.[班級];

此查詢執行結果如下:

(圖六)

把成績表中班級的資料相同者視為同一群組，等於列出所有班級資料料。而在報表群組區塊中執行SQL的聚合函數Count(*)，相當於執行以下SQL語法

SELECT 成績表.[班級] , Count(*) AS 班級人數 FROM 成績表 GROUP BY 成績表.[班級];
查詢產生的結果如下:

(圖七)

而詳細資料區塊，就是利用群組資料當作查詢條件所得的查詢結果，例如班級=1的詳細資料相當於執行以下的SQL語法:

SELECT 成績表.班級, 成績表.座號, 成績表.學號, 成績表.姓名, 成績表.總分, 成績表.等級 FROM 成績表 WHERE (((成績表.班級)=1));
查詢產生的結果如下:

(圖八)

知道報表精靈資料產生的概念，簡單的說透過群組層次區塊，可以將群組查詢的結果傳入詳細資料區塊中，當成查詢的條件。如果要計算出同一個班級中某一等級的人數，例如1班精熟級的人數，執行的SQL語法如下

SELECT Count(*) AS 精熟級 FROM 成績表 WHERE (((成績表.班級)=1) AND ((成績表.等級)='精熟'));

執行結果如下

(圖九)

要在報表區塊中產生這樣的運算，很明顯必須傳入兩個查詢條件，一個是"班級"，另一個是"等級"。要利用報表精靈達成這個目的，按照前一篇文章利用Access製作成績報表(二)(群組功能應用)，只要再新增一個等級群組層次即可計算出，但是這樣我們無法產生一個依照班級座號排序的成績表，只能產生如以下的成績表。

(圖十)

如果我們希望同一班能按座號排序，同一班為同一群組，不再區分其它群組，則不能再新增群組層次。如何才能不破壞資料排序，計算出各等級的人數，顯然不能利用Count()函數，因為Count函數的參數只能是欄位名稱或*，基本上無法加上類似WHERE的條件。

還好Access還提供一類型的"範圍聚合函數"，此類函數等於是SQL的聚合函數，再加上範圍條件(WHERE)，我們可以用DCount(Expr, Domain, Criteria)這個範圍聚合常數來達成目的。以下參考微軟的手冊分別說明此函數三個參數的意函：

參數Expr:此參數是字串型態，代表要計數的資料表或查詢的欄位名稱，以上述報表為例，我們要針對報表中"詳細資料"區塊中查詢的結果"等級"欄位進行計數，我們可以輸入"[成績表]![等級]"，一定要有引號因為是字串型態參數。

參數Domain:此參數是字串型態，代表要計數的資料表名稱、查詢名稱或資料集名稱，以上述報表為例，我們的目標資料表是"成績表"。

參數Criteria:此參數是字串型態，用來限制要執行 DCount 函數的資料範圍。例如，criteria 通常相當於 SQL 運算式中的 WHERE 子句，但不用加上 WHERE 這個字。如果省略 criteria，則 DCount 函數會計算整個範圍的 expr。criteria 中包含的所有欄位也都必須是 domain 中的欄位，否則 DCount 函數會傳回 Null。以上述報表為例，我們要計算1班"精熟"等級的人數，我們的WHERE條件可以寫成(((成績表.班級)=1) AND ((成績表.等級)='精熟'))

，但是這樣的寫法，只能算1班的資料，當班級群組層次資料改變為2的時候，這個條件((成績表.班級)=1)必須變成((成績表.班級)=2)才能統計2班的資料，這樣是無法在輸出報表時隨著班級變動而進行正確的計數，所以如果要讓班級等於變動的群組值，必須把條件改成"([成績表]![班級] = " & [Text20] & " )"，其中[Text20]是我們報表中一個"控制項資料來源"來自[班級]資料的文字方塊的控制項名稱，我們要把此控制項置於"詳細資料"區塊中，那麼它呈現的資料就是班級群組的資料，所以當班級群組資料變動[Text20]的值也跟著變動，這樣就可以計算不同班級的資料。

知道算式的寫法，接下來我們進入報表的設計模式，在班級群組尾區塊，除了原有的班級人數統計的文字方塊，另外再放置四個文字方塊控制項

(圖十一)