Microsoft Word - AED001800_CH07.doc

Size: px

Start display at page:

Download "Microsoft Word - AED001800_CH07.doc"

唯芮
7 years ago
Views:

1 查詢初步 7-1 查詢在資料庫的角色查詢算是 Access 資料庫中的第二種組件, 目的是在資料表含有一定筆數的記錄後, 依特定條件取出記錄, 同時查詢可跨越多個資料表, 也就是透過關聯在多個資料表間尋找相關記錄查詢與資料表 Access 系統核心將資料表及查詢視為同類型物件, 因為一個資料庫中的資料表及查詢名稱不可重複, 如現有客戶資料表, 則不可再建立名為客戶的查詢, 此時將會發生錯誤因為查詢也是可做為資料來源的物件, 資料來源的意思是可輸出記錄至表單報表資料頁等例如欲使用兩個資料表建立表單, 此時就必須先以兩個資料表建立一個查詢, 再以查詢建立表單, 因為表單報表資料頁等均只可使用一個物件做為來源, 可以是資料表或查詢

2 PART 2 建立關聯式資料庫在 Access 資料庫的許多位置, 可看到資料表及查詢名稱會同時出現, 如圖 7-1: 圖 7-1 設定表單的資料來源圖 7-1 是表單的屬性表, 記錄來源屬性可顯示目前資料庫的所有資料表及查詢, 所以資料表及查詢的名稱不可相同, 查詢與資料表雖然在資料庫中的設計方式不同, 但在歸類上,Access 將兩者視為同類型物件, 不可同名, 因為 Access 在很多位置會如圖 7-1, 列出所有資料表及查詢重點是資料表負責儲存記錄, 查詢負責取出記錄, 兩者在目的上可說完全相同, 都可將記錄以表格形式顯示在螢幕上, 這些記錄的進一步處理是用來製作表單報表查詢的原理首先說明查詢的原理, 讀者必須了解資料庫中的查詢, 僅是儲存條件, 而非查詢結果, 因為結果會因資料表的記錄改變而改變不可以有孤兒查詢可由多個資料表依特定條件取出記錄在使用多個資料表時, 每一資料表都必須有關聯, 不可有孤兒, 如圖 7-2: 7-2

3 07 查詢初步圖 7-2 錯誤的查詢設計圖 7-2 是 Ch7\Exercise\Ch7-1.accdb 檔案的訂單及客戶及訂單及客戶 _ 錯誤示範等查詢, 兩者結構類似, 但後者的產品資料表沒有關聯, 是為孤兒, 此一查詢結果必定不正確, 如圖 7-3: 圖 7-3 錯誤的查詢結果 7-3

4 PART 2 建立關聯式資料庫正確的執行結果共有 905 筆記錄, 錯誤的執行結果則有筆因為有孤兒, 所以執行結果的筆數會相當多, 且多數欄位的資料均相同查詢中的關聯所以查詢中的關聯相當重要, 在預設情況下, 查詢中的多個資料表, 會在兩個時機自動產生關聯一個時機是當 Access 發現兩個資料表有同名欄位, 且其中一方為主索引時, 此功能名稱為自動連接讀者可以在 Access 選項的物件設計師中, 設定此項功能預設是否有效, 如圖 7-4: 圖 7-4 自動連接功能的設定但自動連接的結果不一定正確, 如 ID 經常是做為主索引的欄名, 而不同資料表的 ID 可能互不相關, 即不須關聯所以在啟動自動連接時, 我們仍然必須在查詢設計視窗, 檢查自動連接之後的關聯是否正確若不正確, 須予以刪除及重新建立. 操作方式與在資料庫關聯圖中相同 7-4

5 07 查詢初步第二種情況, 是在資料庫關聯圖中建立關聯之後, 也會自動顯示在查詢中, 此類關聯是正確而必須的所以查詢的關聯不只是依靠 Access 自動完成即可, 在筆者經驗中, 經常需要自行完成關聯, 尤其是在以查詢建立查詢時, 通常必須手動建立關聯取出記錄的方式查詢取出記錄的原則有二 :(1) 是依準則執行篩選 ;(2) 是依關聯做搜尋前者較易理解, 後者則須進一步說明讀者可以開啟 Ch7\Exercise\Ch7-1.accdb 資料庫的訂單及員工查詢設計視窗, 如圖 7-5: 圖 7-5 唯一值查詢此查詢的結果共有 10 筆記錄, 但員工資料表有 11 筆記錄, 因為有 1 位員工沒有處理過訂單記錄, 以資料庫專業術語而言, 就是員工所有 11 筆記錄的員工編號欄位值中, 共有 10 個值出現在訂單的處理員工欄位中換言之, 若查詢使用多個資料表, 則查詢取出記錄的方式是出現在關聯雙方的資料, 才會顯示在查詢結果中, 也就是交集若只存在於一方, 就不會列為查詢結果這一點相當重要, 圖 7-5 的查詢還算簡單, 只有兩個資料表, 資料表數量愈多, 關聯就愈複雜, 取出記錄的原則也就愈嚴謹 7-5

6 PART 2 建立關聯式資料庫如何判斷查詢結果是否正確? 資料表愈多, 關聯愈複雜, 也愈難判斷查詢結果是否正確一般而言, 關聯愈多, 查詢結果筆數會愈少, 若筆數較正常多出許多, 必定是錯誤結果, 請開啟 Ch7\Exercise\Ch7-1.accdb 檔案的關聯不夠 _ 示範查詢, 如圖 7-6: 圖 7-6 關聯不夠的錯誤示範此查詢的執行結果共有 9946 筆, 但此資料庫中筆數最多的訂單明細資料表只有 905 筆, 所以資料庫的查詢不論如何設計, 其結果應不會超出 905 筆, 查詢設計視窗如圖 7-7: 這是以客戶及員工資料表建立的查詢兩個客戶編號應該建立關聯圖 7-7 少了關聯的查詢錯誤示範 7-6

7 07 查詢初步在圖 7-7 的查詢中, 共使用三個來源, 其中員工及客戶是查詢, 其他均為資料表, 但此一查詢在圖 7-7 的設計中少了一個關聯, 即兩個客戶編號欄位也應該有關聯, 讀者可以自行操作, 為這兩個欄位建立關聯, 再按下按鈕執行如何分析查詢筆數是否正確? 筆者歸納以下兩點 : 筆數過多且資料重複如圖 7-6 所示,9466 筆記錄中, 有許多記錄的各欄位值是重複的, 這必定是錯誤的查詢結果, 有資料表成為沒有關聯的孤兒或關聯不夠, 都會形成此一狀況以最大筆數的資料表為參考如前所述,Ch7\Exercise\Ch7-1.accdb 檔案中記錄筆數最多的是訂單明細, 共有 905 筆, 故查詢結果必定不會超過這個數字但這不是絕對的準則, 因為 Ch7\Exercise\Ch7-1.accdb 檔案的結構很簡單, 只有 8 個資料表, 一般實務上使用的資料庫必定不只 8 個, 因此, 最多筆數只能做為參考所以查詢的重點是關聯的正確定義, 筆者無法說明何種關聯才是正確, 因為視資料庫結構及需求而異總之, 在測試時, 資料表的記錄最好不要太多, 只要保持足以判斷查詢結果是否正確即可, 筆者的習慣是測試時約使用數十筆或百筆記錄, 若有千筆記錄, 就較難以查詢結果對照資料表原始記錄, 也無從判斷查詢結果是否正確開發的順序開發時, 通常在定義資料表及結構 ( 即關聯 ) 之後, 就會製作表單, 不會毫無目的建立查詢而是在建立表單及報表時, 依需求回頭建立查詢換言之, 查詢通常是依附於表單或報表, 這是使用 Access 資料庫的常見方式本書仍在資料表之後說明查詢, 目的是便於讀者了解, 以免落差太大 7-7

8 PART 2 建立關聯式資料庫 7-2 使用查詢設計視窗 Access 的查詢設計視窗相當方便, 多數動作使用滑鼠即可完成, 本節說明重點即為查詢設計視窗的操作及如何執行查詢建立新查詢與其他物件相同, 建立新查詢時, 你也可以使用精靈, 快速完成查詢設計工作以下先使用精靈完成設計, 再說明設計視窗的操作使用簡單查詢精靈 ( 各客戶之訂單總額 ) 練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 :Ch7\Result\Ch7-2.accdb 之 Q7211 查詢開啟 Ch7\Exercise\Ch7-2.accdb 檔案將功能區切換至建立, 再按下查詢查詢精靈按鈕 7-8

9 07 查詢初步在圖 7-8 選取簡單查詢精靈, 按下確定按鈕圖 7-8 以簡單查詢精靈建立新查詢設定查詢使用之資料表, 首先開啟資料表 / 查詢清單, 選取客戶, 再選取客戶編號公司名稱欄位及按下按鈕, 結果如圖 7-9: 2 1 圖 7-9 選擇資料表及欄位 7-9

10 PART 2 建立關聯式資料庫開啟資料表 / 查詢清單, 選取訂單, 再選取未稅價營業稅含稅價欄位及按下按鈕, 加入已選取的欄位, 如圖 7-10: 圖 7-10 選取來自多個資料表的欄位在圖 7-11 選取摘要及按下摘要選項按鈕 1 2 圖 7-11 選取摘要 7-10

11 07 查詢初步在圖 7-12 選取總計, 再按下確定按鈕圖 7-12 選取計算方式輸入查詢名稱, 如輸入 Q7211, 再按下完成按鈕圖 7-13 輸入新查詢名稱 7-11

12 PART 2 建立關聯式資料庫以上就是簡單查詢精靈的設計過程首先, 讀者必須在圖 7-9 至 7-10 指定一或多個來源, 可用來源就是資料表或另一查詢, 若指定多個來源, Access 會自動為多個來源建立關聯指定來源之後, 還須指定欲使用的欄位, 只有被指定的欄位會顯示在查詢結果如圖 7-14 是本例完成結果圖 7-14 查詢執行結果其實本例是較複雜的簡單查詢精靈, 因為筆者在步驟 7 選擇摘要, 若選擇詳細, 將會更簡單, 就不會顯示圖 7-12, 但查詢結果只會列出所有記錄, 不會計算而本例目的是計算各客戶訂單總額, 這是具分析及計算功能的查詢, 所以必須在圖 7-12 告訴 Access 被計算總和的欄位是未稅價等三個欄位, 就可以在結果中列出每位客戶的訂單總額若被計算的欄位型態為日期, 也可指定依年季月日等不同單位, 進行計算以上就是簡單查詢精靈的操作, 這個精靈也是查詢的入門, 其他精靈將在本章稍後說明 7-12

13 07 查詢初步為求統一, 本章各範例所製作完成的查詢, 都冠以英文字母 Q, 再加上章節編號, 如 Q7211, 表示其為第節的第一個查詢, 依此類推, 以便查閱以設計視窗建立新查詢 ( 顯示所有客戶之訂單記錄 ) 練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 :Ch7\Result\ Ch7-2.accdbb 之 Q7212 查詢開啟 Ch7\Exercise\Ch7-2.accdb 檔案將功能區切換至建立, 再按下其他查詢設計按鈕按住 Ctrl 鍵, 再以滑鼠於圖 7-15 選取客戶與訂單等資料表, 再按下新增及關閉按鈕按住 Ctrl 鍵, 再選取多個資料表圖 7-15 加入資料表 7-13

14 PART 2 建立關聯式資料庫在圖 7-16 的客戶資料表中, 選取客戶名稱欄位, 按住滑鼠左鍵, 向下拖曳至第一個欄位後放開, 再拖曳訂單資料表的訂單編號欄位至第二個欄位後放開左鍵可以由資料表拖曳欄位圖 7-16 加入欄位也可在此按下滑鼠, 開啟清單後, 選擇欄位以滑鼠在下方欄位內按一下左鍵, 出現清單中選取連絡電話欄位按鈕後, 按一下左鍵, 再於按下按下按鈕, 在對話方塊內輸入 Q7212 及按下確定按鈕按鈕, 執行查詢本例是手動建立新查詢的基本操作, 也就是使用設計視窗, 此時會立即顯示圖 7-15, 要求指定新查詢使用之資料表, 此對話方塊也可在設計視窗內, 按下按鈕開啟之本例的操作很簡單, 共使用兩個欄位, 讀者可以由資料表將欄位拖出, 也可在下方欄位的下拉式清單內選取另一個方法是選取及拖曳多個欄位, 如圖 7-17: 7-14

15 07 查詢初步也可拖曳選取的多個欄位圖 7-17 拖曳多個欄位如圖 7-17 所示, 讀者可以先在資料表中按住 Shift 或 Ctrl 鍵, 再以滑鼠選取多個欄位, 再拖曳至欄位中, 此時的滑鼠游標是圖 7-18 的查詢共使用兩個資料表, 此一查詢的意義是在客戶及訂單間尋找相關記錄, 假設客戶中有 10 筆記錄, 但 10 位客戶中只有 8 位客戶在訂單資料表有相關記錄, 則查詢結果就只會顯示這 8 位客戶的資料, 也就是兩者的交集所以照理說, 使用資料表愈多的查詢, 查詢結果記錄可能愈少, 因為交集會愈來愈小反之, 若查詢結果的筆數較原始資料表的筆數多出數倍, 表示查詢設計錯誤所以建議在使用 Access 之初, 練習查詢時, 資料表記錄不要過多, 因為若筆數太多, 較不容易檢查查詢設計是否有誤, 只要保持在可確定查詢結果無誤的少部份記錄即可 7-15

16 PART 2 建立關聯式資料庫設計視窗操作說明查詢設計視窗如圖 7-17, 此視窗可分為兩部份, 分別是上半部的來源區及下半部的設定區, 這兩個名詞是筆者的定義 Access 將查詢設計視窗的下半部稱為設計格線, 筆者認為不太符合此區的真意, 所以本書將以來源區及設定區稱呼這兩個區域如圖 7-18: 來源區 : 顯示使用的資料表查詢及關聯設定區 : 顯示使用的欄位及準則等各種設定, 會影響查詢結果圖 7-18 來源及設定區的簡易說明如圖 7-18 所示, 來源區就是查詢的資料來源, 此處主要操作就是資料表 ; 設定區則是查詢設計視窗的主要操作位置在查詢中插入新欄位練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 : 無 7-16

17 07 查詢初步在 Ch7\Exercise\Ch7-2.accdb 中選取例前例製作的 Q7212 查詢, 按下右鍵後再選擇設計檢視在訂單編號欄位中按一下滑鼠左鍵, 再按下設計查詢設定插入欄按鈕, 如圖 7-19: 圖 7-19 插入新欄位空出新欄位後, 在新欄位內按下按鈕, 選取客戶. 地址欄位本範例目的是在現有欄位之間加入新欄位, 有很多種方法可以達成, 本範例是標準而不太快速的操作最快速的方式是直接將欄位拖曳到所需位置, 放開滑鼠左鍵後, 該位置原有欄位就會向右移動, 原位置即為新欄位 7-17

18 PART 2 建立關聯式資料庫在查詢中移動欄位練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 : 無在 Ch7\Exercise\Ch7-2.accdb 中選取例前例製作的 Q7212 查詢, 按下右鍵後再選擇設計檢視將滑鼠移至連絡電話欄位上方, 滑鼠指標顯示為後, 按住左鍵並向左拖曳, 選取地址及訂單編號等兩個欄位後放開左鍵, 如圖 7-20: 圖 7-20 選取多個欄位按住 Shift 鍵及選取多個欄位 7-18

19 07 查詢初步將滑鼠移至選取的黑色區域內, 再按住左鍵及向右拖曳至連絡人姓名欄位的右方, 如圖 7-21: 圖 7-21 拖曳欄位拖曳至新位置本例有兩個動作, 分別是選取及搬移只要滑鼠指標顯示為, 即表示可選取整欄, 再向左或向拖曳, 則可選取多欄位 ; 另一選取多欄的方法如圖 7-20 的說明, 即按住 Shift 鍵, 再以滑鼠選取欄位執行搬移時, 而搬移的對象就是已被選取的一或多個欄位, 如圖 7-21 表示選取的兩個欄位將置於連絡人姓名及縣市之間選取的後續處理可以是搬移或刪除, 例如在圖 7-20 的狀態下, 按下 Del 鍵, 即可刪除欄位, 但只是在查詢中刪除欄位 ( 不顯示在查詢執行結果 ), 被刪除的欄位仍存在於資料表內隱藏欄位此一操作很簡單, 如圖 7-21 的設定區中, 每個欄位加入至設定區後, 顯示均為開啟狀態, 表示該欄將顯示在查詢結果內, 在特殊情況下, 7-19

20 PART 2 建立關聯式資料庫也可指定不顯示, 只要在方格內按一下左鍵, 清除符號即可如圖 7-22: 共五個欄位, 隱藏一個執行結只有四個欄位圖 7-22 隱藏之後隱藏之後, 該欄的所有其他設定仍然有效, 包括 : 使用函數準則等, 所以顯示及隱藏的更改動作, 不會影響查詢結果設定遞增及遞減排序練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 : 無在 Ch7\Exercise\Ch7-2.accdb 中選取例前例製作的 Q7212 查詢, 按下右鍵後再選擇設計檢視 7-20

21 07 查詢初步在客戶名稱欄位設定遞增排序, 再於連絡人姓名欄位設定遞減排序, 如圖 7-23: 圖 7-23 設定排序本例結果是以客戶名稱做遞增排序, 若有同名客戶, 再以連絡人姓名欄位做遞減, 使用多個欄位排序時, 排序順序是由左而右, 所以最左方的排序欄位會最先被排序, 依此類推查詢與資料表名稱不可相同查詢中的關聯相當重要, 若關聯不足, 筆數會過多而不正確 ; 若關聯過多, 筆數會太少而不正確查詢不會毫無目的的存在, 通常會搭配表單或報表新上手時, 可使用簡單查詢精靈, 協助製作查詢查詢設計視窗分為來源及設定區在設定區更改任一設計後, 可按下按鈕, 查看執行結果 7-21

22 PART 2 建立關聯式資料庫一選擇題 ( )1. 以下有關查詢的敘述, 何者有誤? (A) 查詢與資料表不可同名 (B) 查詢只可以資料表為來源 (C) 查詢結果視記錄變動而定 (D) 查詢可做為表單來源 ( )2. 若使用多個資料表做為查詢來源, 則資料表間必須有 : (A) 關聯 (B) 主索引 (C) 索引 (D) 同名欄位 ( )3. 以下有關查詢中關聯的敘述, 何者正確? (A) 每一資料表均須有關聯 (B) 若關聯不夠, 則執行結果筆數會過少 (C) 若關聯太多, 則執行結果筆數會過多 (D) 以上皆非 ( )4. 若查詢執行結果, 記錄筆數過多且重複, 表示 : (A) 關聯不夠 (B) 關聯太多 (C) 查詢的來源有誤 ( )5. 若有多個欄位設定排序, 則以何者具有排序最高優先權? (A) 最右方的欄位 (B) 最左方的欄位 (C) 最早設定排序的欄位 ( )6. 若使用多個欄位進行排序, 則排序順序是 (A) 由左而右 (B) 由右而左 7-22

23 07 查詢初步( )7. 在查詢中拖曳新欄位後, 若新欄位所在位置已有欄位, 則此舊欄位會 (A) 刪除 (B) 向左移 (C) 向右移 ( )8. 以下有關選取多個欄位的說明, 何者錯誤? (A) 按住 Shift 鍵及以滑鼠選取 (B) 按住 Ctrl 鍵及以滑鼠選取 (C) 按住滑鼠左鍵向左或向右拖曳滑鼠及選取之以上圖回答 9-10 題 ( )9. 此一查詢的執行結果會顯示幾個欄位? (A)4 (B)3 (C)2 (D)1 7-23

24 PART 2 建立關聯式資料庫 ( )10. 以下何者可能是此查詢的執行結果? (A) (B) (C) 7-24

1-6 Access 2016 實力養成暨評量解題秘笈 102. 書籍與作者 Step1 按建立索引標籤資料表群組的資料表設計按鈕 Step2 由上而下分別建立下列欄位並設定資料類型 : 欄位名稱 : ID, 資料類型 : 自動編號欄位名稱 : BName, 資料類型 :

1-6 Access 2016 實力養成暨評量解題秘笈 102. 書籍與作者 Step1 按建立索引標籤資料表群組的資料表設計按鈕 Step2 由上而下分別建立下列欄位並設定資料類型 : 欄位名稱 : ID, 資料類型 : 自動編號欄位名稱 : BName, 資料類型 : 1-6 102. 書籍與作者 102-1. Step1 按建立索引標籤資料表群組的資料表設計按鈕 Step2 由上而下分別建立下列欄位並設定資料類型 : 欄位名稱 : ID, 資料類型 : 自動編號欄位名稱 : BName, 資料類型 : 簡短文字欄位名稱 : AuthorID, 資料類型 : 數字欄位名稱 : Publisher, 資料類型 : 簡短文字欄位名稱 : Price,