第 14 章 進階的查詢應用 著作權所有 旗標出版股份有限公司
本章提要 14-1 什麼是 查詢? 14-2 以手動方式建立查詢 14-3 使用 簡單查詢精靈 14-4 在查詢中建立 計算欄位 14-5 替查詢的欄位取別名 14-6 運算式的使用技巧 2
本章提要 14-7 以 簡單查詢精靈 做統計分析工作 14-8 建立參數式的查詢 14-9 建立 交叉資料表查詢 14-10 交叉資料表查詢 的進階應用 3
14-1 什麼是 查詢? 查詢 (query) 是萃取資料的方法, 它可從一或多個資料表中選出我們想要的資料, 必要時並做一些排序 計算或統計, 然後將結果放入虛擬的資料表中供我們使用 查詢所產生的資料, 實際上仍存放在資料表中 4
單一資料表的查詢 查詢可只從一個資料表 ( 或多個資料表 ) 中萃取資料, 就像我們以前使用的排序及篩選功能, 都是利用查詢來完成的 以下是由書籍資料表中萃取資料的查詢 : 5
單一資料表的查詢 6
多個資料表的查詢 經由資料表間的關聯, 我們也可利用查詢從多個資料表中萃取出相關的資料, 例如 : 7
多個資料表的查詢 透過查詢 8
14-2 以手動方式建立查詢 雖然 Access 提供好幾種查詢精靈來幫我們建立各式各樣的查詢, 但為了讓各位能實際體會, 接下來我們要利用查詢的設計檢視模式, 來分別建立 單一資料表 與 多資料表 的查詢 在建立查詢之前, 我們要先準備好相關的資料表才行, 請開啟書籍訂單資料庫 ( 或將書附光碟中 Ch14 範例資料.accdb 中的 4 個資料表匯入 ): 9
建立單一資料表的查詢 首先我們看看如何建立查詢來找出單價小於 450 元的書籍, 並將結果依單價做遞增排序 : 10
建立單一資料表的查詢 11
建立單一資料表的查詢 12
建立單一資料表的查詢 13
建立單一資料表的查詢 14
建立單一資料表的查詢 15
什麼是 SQL 檢視? 當您拉下檢視鈕時, 可以看到第 4 個項目為 SQL 檢視 : 16
什麼是 SQL 檢視? 以上視窗中顯示的就是 SQL 指令 SQL (Structured Query Language) 是一種應用在關聯式資料庫中的語言, 語法非常簡單且容易使用, 而 Access 的查詢設定便是使用這種語言來描述的 17
建立單一資料表的查詢 接著 14-6 頁, 請按視窗右上方的關閉鈕, 則會出現如下視窗 : 18
建立單一資料表的查詢 19
建立單一資料表的查詢 篩選 和 排序 其實都可算是查詢的一種, 不過篩選和排序的設定是儲存在資料表中 ; 而查詢則是存成獨立的物件, 因此不會影響到其所使用到的資料表 由於查詢也可當成一般資料表來操作, 所以在查詢的資料工作表檢視視窗中一樣可設定各種排序及篩選條件 操作方法和資料表完全相同, 且這些設定也可儲存在查詢的版面設定中, 方便下次開啟時能產生同樣的效果 20
建立多資料表的查詢 我們的訂單資料和客戶資料分別放在兩個不同的資料表中, 因此在查看訂單資料表時, 無法看到相關的客戶名稱及聯絡人姓名 以下我們就建立一個可同時檢視訂單及相關客戶資料的查詢 : 21
建立多資料表的查詢 22
建立多資料表的查詢 若在建立多個資料表查詢前並未建立永久性關聯, 則當我們在查詢視窗中加入資料表時, Access 會自動幫我們尋找各資料表間是否有相同名稱及資料類型的欄位, 若找到且其中一個欄位是主鍵, 便會以此欄位來設定關聯 接著, 請分別在兩個資料表中選取要顯示的欄位 : 23
建立多資料表的查詢 24
建立多資料表的查詢 2 在檢視區的檢視鈕上按一下, 切到資料工作表檢視模式 25
建立多資料表的查詢 最後請以 " 訂單 / 客戶查詢 " 為名儲存起來 看到這裡, 您應該可體會到查詢最大的好處, 就是可整合分散於各資料表中的資料, 成為一份有意義的資訊! 26
14-3 使用 簡單查詢精靈 簡單查詢精靈的目的在簡化建立查詢的步驟 以下我們就建立一個書籍訂單查詢, 將各種分散的資料整合起來 請按照下列步驟執行 : 27
使用 簡單查詢精靈 按確定鈕繼續 28
使用 簡單查詢精靈 29
使用 簡單查詢精靈 按下一步鈕 30
使用 簡單查詢精靈 31
使用 簡單查詢精靈 按下一步鈕 32
使用 簡單查詢精靈 最後請將此查詢儲存起來 33
14-4 在查詢中建立 計算欄位 查詢的資料除了可從其他資料表或查詢取得外, 也可從 運算式 取得 例如在前面的書籍訂單查詢中, 我們想多增加一個金額小計欄, 來統計每筆訂單細目的銷售總額 : 34
在查詢中建立 計算欄位 這時就必須使用計算欄位了 以下我們就來看看如何修改書籍訂單查詢吧! 請先開啟書籍訂單查詢的設計視窗, 然後依下面的方式操作 : 35
在查詢中建立 計算欄位 將非必要的是否付款及備註欄取消顯示, 並設定以訂單序號做遞增排序, 然後切換到工作表檢視視窗 : 36
在查詢中建立 計算欄位 最後, 請按 Office 按鈕鈕, 執行 另存新檔 / 另存物件為 命令, 將此查詢以 " 訂單金額查詢 " 為名存檔 : 37
在查詢中建立 計算欄位 38
14-5 替查詢的欄位取別名 Access 允許我們在查詢的欄位名稱前加上別名, 賦予欄位較有意義 適當的名稱, 例如 : 39
替查詢的欄位取別名 使用查詢的計算欄位時, 若不指定別名, Access 會自動幫我們取一個別名 :Expr?, 40
替查詢的欄位取別名 其中 Expr 就是 Expression ( 運算式 ) 的意思, "?" 代表一個數字, 例如 : 41
替查詢的欄位取別名 切換到資料工作表檢視視窗 42
14-6 運算式的使用技巧 運算式的構造 什麼地方可以用運算式 運算式建立幫手 43
運算式的構造 在說明使用運算式之前, 先介紹運算式的二大組成單元 : 運算子及運算元 運算子 運算元 運算式 44
運算子 運算子是一種符號, 用來作為資料項目之間的計算方式 在 Access 中常用的大致可分為幾類 : 算術運算子 (+ - * /) 連結運算子 (+ &) 邏輯運算子 (and or) 及比較運算子 (> = < between...and...) 等等 45
運算元 運算元是指運算子處理的資料項目, 一般來說, 只要能提供值, 就可以當作運算元 包括數字 字串... 等 例如 : 46
Access 的函數 在 Access 中內建了許多函數, 可用來幫我們做複雜的運算, 減少撰寫程式碼, 例如 : Int () 可求取數值的整數部份 Date () 傳回今天的日期 Year () 可求得日期資料的年份 Month () 可求得日期資料的月份 UCase () 可將字串中小寫的英文字轉成大寫... 47
運算式 運算式則是由運算元及運算子所組成, 它能進行各種資料的運算, 在 Access 中應用的非常廣泛, 包括一些篩選準則的設定 條件判斷 欄位計算... 等, 都是運用運算式來設定 例如 : 48
運算式 請注意! 上面運算式的識別名稱是指資料庫物件或欄位的名稱 以資料表及查詢的欄位名稱為例, 這些名稱要以方括弧括起來, 例如 :[ 單價 ] [ 數量 ] 若資料庫中有好幾個資料表或查詢物件, 則還要指明所屬的資料表或查詢, 例如 :[ 書籍 ].[ 單價 ] [ 訂單細目 ].[ 數量 ], 中間以句點或驚嘆號作為連接 49
什麼地方可以用運算式 我們在上一節所提到的金額小計欄位及篩選的條件 ( 如 :<450) 就算是運算式 所有需要經過比較或計算後才能得到結果的欄位都可使用運算式 以查詢為例 : 50
什麼地方可以用運算式 51
運算式建立幫手 運算式建立幫手可方便我們建立運算式 您只要將輸入焦點移到要輸入運算式的地方, 然後按查詢設定區的建立幫手鈕 : 52
運算式建立幫手 53
運算式建立幫手 54
運算式建立幫手 那要怎麼操作呢? 您可在上圖的編輯框中直接輸入或編輯運算式的內容, 或由編輯框下方的一排按鈕及在最下面的列示窗中雙按項目來加入成員 舉個例子來說, 假設我們又要在訂單金額查詢中多加一個欄位, 來計算每筆交易的利潤, 可以輸入下面這個運算式 : 請依照下面的步驟操作 : 55
運算式建立幫手 56
運算式建立幫手 57
運算式建立幫手 注意! 輸入焦點是資料插入的地方, 所以插入資料前請先確認輸入焦點的位置是否正確 接著, 請利用同樣的技巧, 將完整的運算式輸入如下 : 58
運算式建立幫手 完成之後, 我們切換到工作表檢視視窗來看結果 : 59
運算式建立幫手 最後, 請將這個查詢存檔並關閉 60
設定查詢欄位的 顯示格式 我們也可設定查詢欄位的 顯示格式, 方法如下 : 61
設定查詢欄位的 顯示格式 切換到工作表檢視 62
設定查詢欄位的 顯示格式 63
14-7 以 簡單查詢精靈 做統計分析工作 簡單查詢精靈 的功能其實並不簡單, 它還可幫我們做資料的統計分析工作! 例如要查看每本書的銷售量, 這項功能就可派上用場了 請在建立頁次的其他區中按查詢精靈鈕 : 64
以 簡單查詢精靈 做統計分析工作 65
以 簡單查詢精靈 做統計分析工作 按下一步鈕 66
以 簡單查詢精靈 做統計分析工作 67
以 簡單查詢精靈 做統計分析工作 按下一步鈕 68
以 簡單查詢精靈 做統計分析工作 69
以 簡單查詢精靈 做統計分析工作 70
統計分析查詢 和一般查詢的差異 接續前面的書籍銷售排行查詢, 請切換到查詢的設計檢視視窗, 我們要看看 統計分析查詢 和一般查詢有何不同之處 : 71
統計分析查詢 和一般查詢的差異 72
統計分析查詢 和一般查詢的差異 當查詢中多了一個合計列時, 這個查詢便具有分組統計分析的功能, 您可按設計頁次的合計鈕來開啟或關閉合計列 在合計列中, 我們可以為每一欄位設定要做為分組條件或做為合計運算 : 73
統計分析查詢 和一般查詢的差異 74
統計分析查詢 和一般查詢的差異 經過以上的解說, 書籍銷售排行查詢的設定就很容易瞭解了 : 75
群組函數 群組函數 又稱為 SQL 聚合函數 或 總和函數, 它是專門用來與 群組式查詢 搭配使用 例如我們前面計算書籍的銷售量時 : 76
揭開分組查詢的真面目 像以上這樣將粹取出的資料做分組計算的查詢, 我們稱為 群組式查詢 以下是群組式查詢的整個流程 : 77
揭開分組查詢的真面目 粹取資料 78
揭開分組查詢的真面目 79
群組函數 在群組式查詢中, 所有的計算欄位都必須以 群組函數 來運算 群組函數的使用方式有二種, 底下我們以 " 筆數 " 功能為例來說明 : 80
群組函數 如果您不遵循以上的規則, 那麼在執行查詢時, 就會顯示錯誤訊息 : 81
群組函數 82
其他常用的群組函數 (SQL 聚合函數 ) 底下是一些常用的群組函數 : 83
在群組式查詢中設定排序 篩選條件 在群組式查詢的設計視窗中, 我們仍可任意在各欄位中設定排序和篩選, 例如我們只想列出總銷售量大於 200 本的書籍銷售排行, 則可依照下圖操作 : 84
在群組式查詢中設定排序 篩選條件 如此, 只有總銷售量大於 200 本的書籍會被取出來做排序 但是, 如果還要在上述條件中篩選書籍單價大於 550 元的交易呢? 由於我們的分組式查詢中並沒有此欄位, 因此必須再加上一個單價欄 : 85
在群組式查詢中設定排序 篩選條件 86
其他常用的運算子 除了上述介紹的運算子外, 我們也常會用到 Like And Or... 等運算子 : Like: 可找出包含指定字串的資料, 例如我們要找出所有 Window 系列書籍的銷售狀況, 便可如下操作 : 87
其他常用的運算子 And: 利用 And 運算子, 我們可為一個欄位設定一個以上的篩選條件, 例如設定如下 : 則系統會幫我們找出單價大於 300 元, 但是小於 500 元的書籍 88
其他常用的運算子 Or: 利用 Or 運算子來做篩選, 只要內容符合其中一個篩選條件即會被選取出來, 設定如下 : 則系統會幫我們找出單價大於 500 元的書籍與小於 200 元的書籍 89
14-8 建立參數式的查詢 依前一節的範例, 我們可查出書籍的總銷售排行, 若只想看某一期間內的銷售排行 ( 例如 2 月 1 日到 3 月 10 日的銷售排行 ) 呢? 由於每次要看的時間範圍可能都不一樣, 所以最好的方法就是在執行查詢的時候, 用輸入參數的方式來指定時間範圍 : 90
建立參數式的查詢 91
建立參數式查詢的方法 要建立參數式查詢, 我們可先設計一個普通的查詢, 再加上要使用的參數設定 底下請先將書籍銷售排行查詢複製一份, 並以期間銷售排行為名, 然後在此查詢的設計檢視視窗中設定 : 92
建立參數式查詢的方法 93
建立參數式查詢的方法 接著, 請按設計頁次隱藏 / 顯示功能區的參數鈕, 即開啟查詢參數視窗, 然後依下圖輸入 : 94
建立參數式查詢的方法 輸入完成請按確定鈕 在上圖中我們定義了二個日期 / 時間類型的參數, 這樣我們便可以將之做為日期欄的篩選條件了 : 95
建立參數式查詢的方法 注意, 當我們將參數加在運算式中時, 要記得用方括弧把參數名稱給括起來 ( 因為它們也是識別名稱的一種 ), 這樣 Access 才知道它們是參數 ( 否則會被視為字串值 ) 好了, 我們就來看看成果吧! 請按檢視鈕 : 96
建立參數式查詢的方法 97
建立參數式查詢的方法 最後請將這個查詢存檔並關閉 以後您只要開啟這個查詢, 就會出現我們設定好的參數輸入視窗, 您可視需要輸入起始及終止日期, 那麼 Access 就會依照輸入的期間來做查詢了 98
參數式查詢的應用 參數式查詢也可配合函式來製作出實用的查詢, 以期間銷售排行的查詢為例, 我們可以利用 Month() 函數將其改建立成單月銷售排行的參數式查詢 首先, 開啟上一節建立期間銷售排行的設計檢視視窗 : 99
參數式查詢的應用 100
參數式查詢的應用 101
參數式查詢的應用 102
參數式查詢的應用 接著, 按設計頁次顯示 / 隱藏功能區的參數鈕, 並如下修改 : 103
參數式查詢的應用 按檢視區的檢視鈕來看成果 : 104
參數式查詢的應用 105
14-9 建立 交叉資料表查詢 交叉資料表查詢 可用來幫我們做交叉分析, 然後產生一個具有欄標題及列標題的 交叉資料表 例如我們想查詢每一個客戶購買每一種書的數量, 就很適合使用交叉資料表查詢 : 106
建立 交叉資料表查詢 107
建立 交叉資料表查詢 這樣一來, 我們所要的資訊就可以一目了然了 那麼, 要如何建立 交叉資料表查詢 呢? Access 提供了 交叉資料表查詢精靈 來幫我們的忙 首先, 在建立頁次的其他區中按查詢精靈鈕, 然後依下圖步驟操作 : 108
建立 交叉資料表查詢 109
建立 交叉資料表查詢 110
建立 交叉資料表查詢 按下一步鈕 111
建立 交叉資料表查詢 按下一步鈕 112
建立 交叉資料表查詢 按下一步鈕 113
建立 交叉資料表查詢 按下一步鈕 114
建立 交叉資料表查詢 按完成鈕 115
建立 交叉資料表查詢 您可按常用頁次的檢視鈕切換到設計檢視視窗, 看看這個交叉查詢的詳細內容 : 116
14-10 交叉資料表查詢 的進階應用 其實, 交叉資料表的列標題可以包含好幾個 ( 最多 3 個 ), 例如我們想在前面的分析表中再加一個 " 月份 " 的分組條件, 以產生如下的分析表 : 117
交叉資料表查詢 的進階應用 這樣我們就可以用月份為單位, 來檢視每個客戶的書籍訂購情形了 要製作這樣的一個分析表, 其步驟和前一節相同, 只除了在選取列標題的地方 (14-45 頁步驟 4) 有一個不同 : 118
交叉資料表查詢 的進階應用 119
交叉資料表查詢 的進階應用 另外在最後一個步驟中, 我們將此查詢另外取個名稱 : 120
交叉資料表查詢 的進階應用 按完成鈕 121
交叉資料表查詢 的進階應用 由於我們是希望以月份做為第一個列標題, 所以請按功能區的切換檢視鈕切換到設計檢視視窗 : 122
交叉資料表查詢 的進階應用 切換到工作表檢視視窗 123
運算式中的 & 及 Month() & 是字串連結運算子, 可將二個字串合併起來, 例如 : "a" & "b" 運算結果為 "ab" Month() 函數可以取得日期資料的月份, 例如 : Month(#2007/05/01#) 結果為 5 另外, Year() 函數則可取得年份, 而 Day() 函數可取得日期 124