F5720

Size: px

Start display at page:

Download "F5720"

兴轼戚
7 years ago
Views:

1 第 14 章進階的查詢應用著作權所有旗標出版股份有限公司

2 本章提要 14-1 什麼是查詢? 14-2 以手動方式建立查詢 14-3 使用簡單查詢精靈 14-4 在查詢中建立計算欄位 14-5 替查詢的欄位取別名 14-6 運算式的使用技巧 2

3 本章提要 14-7 以簡單查詢精靈做統計分析工作 14-8 建立參數式的查詢 14-9 建立交叉資料表查詢交叉資料表查詢的進階應用 3

4 14-1 什麼是查詢? 查詢 (query) 是萃取資料的方法, 它可從一或多個資料表中選出我們想要的資料, 必要時並做一些排序計算或統計, 然後將結果放入虛擬的資料表中供我們使用查詢所產生的資料, 實際上仍存放在資料表中 4

5 單一資料表的查詢查詢可只從一個資料表 ( 或多個資料表 ) 中萃取資料, 就像我們以前使用的排序及篩選功能, 都是利用查詢來完成的以下是由書籍資料表中萃取資料的查詢 : 5

6 單一資料表的查詢 6

7 多個資料表的查詢經由資料表間的關聯, 我們也可利用查詢從多個資料表中萃取出相關的資料, 例如 : 7

8 多個資料表的查詢透過查詢 8

9 14-2 以手動方式建立查詢雖然 Access 提供好幾種查詢精靈來幫我們建立各式各樣的查詢, 但為了讓各位能實際體會, 接下來我們要利用查詢的設計檢視模式, 來分別建立單一資料表與多資料表的查詢在建立查詢之前, 我們要先準備好相關的資料表才行, 請開啟書籍訂單資料庫 ( 或將書附光碟中 Ch14 範例資料.accdb 中的 4 個資料表匯入 ): 9

10 建立單一資料表的查詢首先我們看看如何建立查詢來找出單價小於 450 元的書籍, 並將結果依單價做遞增排序 : 10

11 建立單一資料表的查詢 11

12 建立單一資料表的查詢 12

13 建立單一資料表的查詢 13

14 建立單一資料表的查詢 14

15 建立單一資料表的查詢 15

16 什麼是 SQL 檢視? 當您拉下檢視鈕時, 可以看到第 4 個項目為 SQL 檢視 : 16

17 什麼是 SQL 檢視? 以上視窗中顯示的就是 SQL 指令 SQL (Structured Query Language) 是一種應用在關聯式資料庫中的語言, 語法非常簡單且容易使用, 而 Access 的查詢設定便是使用這種語言來描述的 17

18 建立單一資料表的查詢接著 14-6 頁, 請按視窗右上方的關閉鈕, 則會出現如下視窗 : 18

19 建立單一資料表的查詢 19

20 建立單一資料表的查詢篩選和排序其實都可算是查詢的一種, 不過篩選和排序的設定是儲存在資料表中 ; 而查詢則是存成獨立的物件, 因此不會影響到其所使用到的資料表由於查詢也可當成一般資料表來操作, 所以在查詢的資料工作表檢視視窗中一樣可設定各種排序及篩選條件操作方法和資料表完全相同, 且這些設定也可儲存在查詢的版面設定中, 方便下次開啟時能產生同樣的效果 20

21 建立多資料表的查詢我們的訂單資料和客戶資料分別放在兩個不同的資料表中, 因此在查看訂單資料表時, 無法看到相關的客戶名稱及聯絡人姓名以下我們就建立一個可同時檢視訂單及相關客戶資料的查詢 : 21

22 建立多資料表的查詢 22

23 建立多資料表的查詢若在建立多個資料表查詢前並未建立永久性關聯, 則當我們在查詢視窗中加入資料表時, Access 會自動幫我們尋找各資料表間是否有相同名稱及資料類型的欄位, 若找到且其中一個欄位是主鍵, 便會以此欄位來設定關聯接著, 請分別在兩個資料表中選取要顯示的欄位 : 23

24 建立多資料表的查詢 24

25 建立多資料表的查詢 2 在檢視區的檢視鈕上按一下, 切到資料工作表檢視模式 25

26 建立多資料表的查詢最後請以 " 訂單 / 客戶查詢 " 為名儲存起來看到這裡, 您應該可體會到查詢最大的好處, 就是可整合分散於各資料表中的資料, 成為一份有意義的資訊! 26

27 14-3 使用簡單查詢精靈簡單查詢精靈的目的在簡化建立查詢的步驟以下我們就建立一個書籍訂單查詢, 將各種分散的資料整合起來請按照下列步驟執行 : 27

28 使用簡單查詢精靈按確定鈕繼續 28

29 使用簡單查詢精靈 29

30 使用簡單查詢精靈按下一步鈕 30

31 使用簡單查詢精靈 31

32 使用簡單查詢精靈按下一步鈕 32

33 使用簡單查詢精靈最後請將此查詢儲存起來 33

34 14-4 在查詢中建立計算欄位查詢的資料除了可從其他資料表或查詢取得外, 也可從運算式取得例如在前面的書籍訂單查詢中, 我們想多增加一個金額小計欄, 來統計每筆訂單細目的銷售總額 : 34

35 在查詢中建立計算欄位這時就必須使用計算欄位了以下我們就來看看如何修改書籍訂單查詢吧! 請先開啟書籍訂單查詢的設計視窗, 然後依下面的方式操作 : 35

36 在查詢中建立計算欄位將非必要的是否付款及備註欄取消顯示, 並設定以訂單序號做遞增排序, 然後切換到工作表檢視視窗 : 36

37 在查詢中建立計算欄位最後, 請按 Office 按鈕鈕, 執行另存新檔 / 另存物件為命令, 將此查詢以 " 訂單金額查詢 " 為名存檔 : 37

38 在查詢中建立計算欄位 38

39 14-5 替查詢的欄位取別名 Access 允許我們在查詢的欄位名稱前加上別名, 賦予欄位較有意義適當的名稱, 例如 : 39

40 替查詢的欄位取別名使用查詢的計算欄位時, 若不指定別名, Access 會自動幫我們取一個別名 :Expr?, 40

41 替查詢的欄位取別名其中 Expr 就是 Expression ( 運算式 ) 的意思, "?" 代表一個數字, 例如 : 41

42 替查詢的欄位取別名切換到資料工作表檢視視窗 42

43 14-6 運算式的使用技巧運算式的構造什麼地方可以用運算式運算式建立幫手 43

44 運算式的構造在說明使用運算式之前, 先介紹運算式的二大組成單元 : 運算子及運算元運算子運算元運算式 44

45 運算子運算子是一種符號, 用來作為資料項目之間的計算方式在 Access 中常用的大致可分為幾類 : 算術運算子 (+ - * /) 連結運算子 (+ &) 邏輯運算子 (and or) 及比較運算子 (> = < between...and...) 等等 45

46 運算元運算元是指運算子處理的資料項目, 一般來說, 只要能提供值, 就可以當作運算元包括數字字串... 等例如 : 46

47 Access 的函數在 Access 中內建了許多函數, 可用來幫我們做複雜的運算, 減少撰寫程式碼, 例如 : Int () 可求取數值的整數部份 Date () 傳回今天的日期 Year () 可求得日期資料的年份 Month () 可求得日期資料的月份 UCase () 可將字串中小寫的英文字轉成大寫... 47

48 運算式運算式則是由運算元及運算子所組成, 它能進行各種資料的運算, 在 Access 中應用的非常廣泛, 包括一些篩選準則的設定條件判斷欄位計算... 等, 都是運用運算式來設定例如 : 48

49 運算式請注意! 上面運算式的識別名稱是指資料庫物件或欄位的名稱以資料表及查詢的欄位名稱為例, 這些名稱要以方括弧括起來, 例如 :[ 單價 ] [ 數量 ] 若資料庫中有好幾個資料表或查詢物件, 則還要指明所屬的資料表或查詢, 例如 :[ 書籍 ].[ 單價 ] [ 訂單細目 ].[ 數量 ], 中間以句點或驚嘆號作為連接 49

50 什麼地方可以用運算式我們在上一節所提到的金額小計欄位及篩選的條件 ( 如 :<450) 就算是運算式所有需要經過比較或計算後才能得到結果的欄位都可使用運算式以查詢為例 : 50

51 什麼地方可以用運算式 51

52 運算式建立幫手運算式建立幫手可方便我們建立運算式您只要將輸入焦點移到要輸入運算式的地方, 然後按查詢設定區的建立幫手鈕 : 52

53 運算式建立幫手 53

54 運算式建立幫手 54

55 運算式建立幫手那要怎麼操作呢? 您可在上圖的編輯框中直接輸入或編輯運算式的內容, 或由編輯框下方的一排按鈕及在最下面的列示窗中雙按項目來加入成員舉個例子來說, 假設我們又要在訂單金額查詢中多加一個欄位, 來計算每筆交易的利潤, 可以輸入下面這個運算式 : 請依照下面的步驟操作 : 55

56 運算式建立幫手 56

57 運算式建立幫手 57

58 運算式建立幫手注意! 輸入焦點是資料插入的地方, 所以插入資料前請先確認輸入焦點的位置是否正確接著, 請利用同樣的技巧, 將完整的運算式輸入如下 : 58

59 運算式建立幫手完成之後, 我們切換到工作表檢視視窗來看結果 : 59

60 運算式建立幫手最後, 請將這個查詢存檔並關閉 60

61 設定查詢欄位的顯示格式我們也可設定查詢欄位的顯示格式, 方法如下 : 61

62 設定查詢欄位的顯示格式切換到工作表檢視 62

63 設定查詢欄位的顯示格式 63

64 14-7 以簡單查詢精靈做統計分析工作簡單查詢精靈的功能其實並不簡單, 它還可幫我們做資料的統計分析工作! 例如要查看每本書的銷售量, 這項功能就可派上用場了請在建立頁次的其他區中按查詢精靈鈕 : 64

65 以簡單查詢精靈做統計分析工作 65

66 以簡單查詢精靈做統計分析工作按下一步鈕 66

67 以簡單查詢精靈做統計分析工作 67

68 以簡單查詢精靈做統計分析工作按下一步鈕 68

69 以簡單查詢精靈做統計分析工作 69

70 以簡單查詢精靈做統計分析工作 70

71 統計分析查詢和一般查詢的差異接續前面的書籍銷售排行查詢, 請切換到查詢的設計檢視視窗, 我們要看看統計分析查詢和一般查詢有何不同之處 : 71

72 統計分析查詢和一般查詢的差異 72

73 統計分析查詢和一般查詢的差異當查詢中多了一個合計列時, 這個查詢便具有分組統計分析的功能, 您可按設計頁次的合計鈕來開啟或關閉合計列在合計列中, 我們可以為每一欄位設定要做為分組條件或做為合計運算 : 73

74 統計分析查詢和一般查詢的差異 74

75 統計分析查詢和一般查詢的差異經過以上的解說, 書籍銷售排行查詢的設定就很容易瞭解了 : 75

76 群組函數群組函數又稱為 SQL 聚合函數或總和函數, 它是專門用來與群組式查詢搭配使用例如我們前面計算書籍的銷售量時 : 76

77 揭開分組查詢的真面目像以上這樣將粹取出的資料做分組計算的查詢, 我們稱為群組式查詢以下是群組式查詢的整個流程 : 77

78 揭開分組查詢的真面目粹取資料 78

79 揭開分組查詢的真面目 79

80 群組函數在群組式查詢中, 所有的計算欄位都必須以群組函數來運算群組函數的使用方式有二種, 底下我們以 " 筆數 " 功能為例來說明 : 80

81 群組函數如果您不遵循以上的規則, 那麼在執行查詢時, 就會顯示錯誤訊息 : 81

82 群組函數 82

83 其他常用的群組函數 (SQL 聚合函數 ) 底下是一些常用的群組函數 : 83

84 在群組式查詢中設定排序篩選條件在群組式查詢的設計視窗中, 我們仍可任意在各欄位中設定排序和篩選, 例如我們只想列出總銷售量大於 200 本的書籍銷售排行, 則可依照下圖操作 : 84

85 在群組式查詢中設定排序篩選條件如此, 只有總銷售量大於 200 本的書籍會被取出來做排序但是, 如果還要在上述條件中篩選書籍單價大於 550 元的交易呢? 由於我們的分組式查詢中並沒有此欄位, 因此必須再加上一個單價欄 : 85

86 在群組式查詢中設定排序篩選條件 86

87 其他常用的運算子除了上述介紹的運算子外, 我們也常會用到 Like And Or... 等運算子 : Like: 可找出包含指定字串的資料, 例如我們要找出所有 Window 系列書籍的銷售狀況, 便可如下操作 : 87

88 其他常用的運算子 And: 利用 And 運算子, 我們可為一個欄位設定一個以上的篩選條件, 例如設定如下 : 則系統會幫我們找出單價大於 300 元, 但是小於 500 元的書籍 88

89 其他常用的運算子 Or: 利用 Or 運算子來做篩選, 只要內容符合其中一個篩選條件即會被選取出來, 設定如下 : 則系統會幫我們找出單價大於 500 元的書籍與小於 200 元的書籍 89

90 14-8 建立參數式的查詢依前一節的範例, 我們可查出書籍的總銷售排行, 若只想看某一期間內的銷售排行 ( 例如 2 月 1 日到 3 月 10 日的銷售排行 ) 呢? 由於每次要看的時間範圍可能都不一樣, 所以最好的方法就是在執行查詢的時候, 用輸入參數的方式來指定時間範圍 : 90

91 建立參數式的查詢 91

92 建立參數式查詢的方法要建立參數式查詢, 我們可先設計一個普通的查詢, 再加上要使用的參數設定底下請先將書籍銷售排行查詢複製一份, 並以期間銷售排行為名, 然後在此查詢的設計檢視視窗中設定 : 92

93 建立參數式查詢的方法 93

94 建立參數式查詢的方法接著, 請按設計頁次隱藏 / 顯示功能區的參數鈕, 即開啟查詢參數視窗, 然後依下圖輸入 : 94

95 建立參數式查詢的方法輸入完成請按確定鈕在上圖中我們定義了二個日期 / 時間類型的參數, 這樣我們便可以將之做為日期欄的篩選條件了 : 95

96 建立參數式查詢的方法注意, 當我們將參數加在運算式中時, 要記得用方括弧把參數名稱給括起來 ( 因為它們也是識別名稱的一種 ), 這樣 Access 才知道它們是參數 ( 否則會被視為字串值 ) 好了, 我們就來看看成果吧! 請按檢視鈕 : 96

97 建立參數式查詢的方法 97

98 建立參數式查詢的方法最後請將這個查詢存檔並關閉以後您只要開啟這個查詢, 就會出現我們設定好的參數輸入視窗, 您可視需要輸入起始及終止日期, 那麼 Access 就會依照輸入的期間來做查詢了 98

99 參數式查詢的應用參數式查詢也可配合函式來製作出實用的查詢, 以期間銷售排行的查詢為例, 我們可以利用 Month() 函數將其改建立成單月銷售排行的參數式查詢首先, 開啟上一節建立期間銷售排行的設計檢視視窗 : 99

100 參數式查詢的應用 100

101 參數式查詢的應用 101

102 參數式查詢的應用 102

103 參數式查詢的應用接著, 按設計頁次顯示 / 隱藏功能區的參數鈕, 並如下修改 : 103

104 參數式查詢的應用按檢視區的檢視鈕來看成果 : 104

105 參數式查詢的應用 105

106 14-9 建立交叉資料表查詢交叉資料表查詢可用來幫我們做交叉分析, 然後產生一個具有欄標題及列標題的交叉資料表例如我們想查詢每一個客戶購買每一種書的數量, 就很適合使用交叉資料表查詢 : 106

107 建立交叉資料表查詢 107

108 建立交叉資料表查詢這樣一來, 我們所要的資訊就可以一目了然了那麼, 要如何建立交叉資料表查詢呢? Access 提供了交叉資料表查詢精靈來幫我們的忙首先, 在建立頁次的其他區中按查詢精靈鈕, 然後依下圖步驟操作 : 108

109 建立交叉資料表查詢 109

110 建立交叉資料表查詢 110

111 建立交叉資料表查詢按下一步鈕 111

112 建立交叉資料表查詢按下一步鈕 112

113 建立交叉資料表查詢按下一步鈕 113

114 建立交叉資料表查詢按下一步鈕 114

115 建立交叉資料表查詢按完成鈕 115

116 建立交叉資料表查詢您可按常用頁次的檢視鈕切換到設計檢視視窗, 看看這個交叉查詢的詳細內容 : 116

117 14-10 交叉資料表查詢的進階應用其實, 交叉資料表的列標題可以包含好幾個 ( 最多 3 個 ), 例如我們想在前面的分析表中再加一個 " 月份 " 的分組條件, 以產生如下的分析表 : 117

118 交叉資料表查詢的進階應用這樣我們就可以用月份為單位, 來檢視每個客戶的書籍訂購情形了要製作這樣的一個分析表, 其步驟和前一節相同, 只除了在選取列標題的地方 (14-45 頁步驟 4) 有一個不同 : 118

119 交叉資料表查詢的進階應用 119

120 交叉資料表查詢的進階應用另外在最後一個步驟中, 我們將此查詢另外取個名稱 : 120

121 交叉資料表查詢的進階應用按完成鈕 121

122 交叉資料表查詢的進階應用由於我們是希望以月份做為第一個列標題, 所以請按功能區的切換檢視鈕切換到設計檢視視窗 : 122

123 交叉資料表查詢的進階應用切換到工作表檢視視窗 123

124 運算式中的 & 及 Month() & 是字串連結運算子, 可將二個字串合併起來, 例如 : "a" & "b" 運算結果為 "ab" Month() 函數可以取得日期資料的月份, 例如 : Month(#2007/05/01#) 結果為 5 另外, Year() 函數則可取得年份, 而 Day() 函數可取得日期 124

840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00

Excel - - Excel - -4-5 840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00 ( 0 ) 智慧標籤相關說明提示 -5 -- Excel 4 5 6 7 8 + - * / % ^ = < >= & 9 0 (:) (,) ( ) Chapter - :,