查詢初步 7-1 查詢在資料庫的角色 查詢算是 Access 資料庫中的第二種組件, 目的是在資料表含有一定筆數的記錄後, 依特定條件取出記錄, 同時查詢可跨越多個資料表, 也就是透過關聯在多個資料表間尋找相關記錄 7-1-1 查詢與資料表 Access 系統核心將資料表及查詢視為同類型物件, 因為一個資料庫中的資料表及查詢名稱不可重複, 如現有 客戶 資料表, 則不可再建立名為 客戶 的查詢, 此時將會發生錯誤 因為查詢也是可做為 資料來源 的物件, 資料來源的意思是可輸出記錄至表單 報表 資料頁等 例如欲使用兩個資料表建立表單, 此時就必須先以兩個資料表建立一個查詢, 再以查詢建立表單, 因為表單 報表 資料頁等均只可使用一個物件做為來源, 可以是資料表或查詢
PART 2 建立關聯式資料庫 在 Access 資料庫的許多位置, 可看到資料表及查詢名稱會同時出現, 如圖 7-1: 圖 7-1 設定表單的資料來源 圖 7-1 是表單的屬性表, 記錄來源 屬性可顯示目前資料庫的所有資料表及查詢, 所以資料表及查詢的名稱不可相同, 查詢與資料表雖然在資料庫中的設計方式不同, 但在歸類上,Access 將兩者視為同類型物件, 不可同名, 因為 Access 在很多位置會如圖 7-1, 列出所有資料表及查詢 重點是資料表負責儲存記錄, 查詢負責取出記錄, 兩者在目的上可說完全相同, 都可將記錄以表格形式顯示在螢幕上, 這些記錄的進一步處理是用來製作表單 報表 7-1-2 查詢的原理 首先說明查詢的原理, 讀者必須了解資料庫中的查詢, 僅是儲存條件, 而非查詢結果, 因為結果會因資料表的記錄改變而改變 不可以有孤兒 查詢可由多個資料表依特定條件取出記錄 在使用多個資料表時, 每一資料表都必須有關聯, 不可有孤兒, 如圖 7-2: 7-2
07 查詢初步圖 7-2 錯誤的查詢設計 圖 7-2 是 Ch7\Exercise\Ch7-1.accdb 檔案的 訂單及客戶 及 訂單及客戶 _ 錯誤示範 等查詢, 兩者結構類似, 但後者的 產品 資料表沒有關聯, 是為孤兒, 此一查詢結果必定不正確, 如圖 7-3: 圖 7-3 錯誤的查詢結果 7-3
PART 2 建立關聯式資料庫 正確的執行結果共有 905 筆記錄, 錯誤的執行結果則有 71495 筆 因為有孤兒, 所以執行結果的筆數會相當多, 且多數欄位的資料均相同 查詢中的關聯 所以查詢中的關聯相當重要, 在預設情況下, 查詢中的多個資料表, 會在兩個時機自動產生關聯 一個時機是當 Access 發現兩個資料表有同名欄位, 且其中一方為主索引時, 此功能名稱為 自動連接 讀者可以在 Access 選項 的 物件設計師 中, 設定此項功能預設是否有效, 如圖 7-4: 圖 7-4 自動連接功能的設定 但自動連接的結果不一定正確, 如 ID 經常是做為主索引的欄名, 而不同資料表的 ID 可能互不相關, 即不須關聯 所以在啟動自動連接時, 我們仍然必須在查詢設計視窗, 檢查自動連接之後的關聯是否正確 若不正確, 須予以刪除及重新建立. 操作方式與在資料庫關聯圖中相同 7-4
07 查詢初步第二種情況, 是在資料庫關聯圖中建立關聯之後, 也會自動顯示在查詢中, 此類關聯是正確而必須的 所以查詢的關聯不只是依靠 Access 自動完成即可, 在筆者經驗中, 經常需要自行完成關聯, 尤其是在以查詢建立查詢時, 通常必須手動建立關聯 取出記錄的方式查詢取出記錄的原則有二 :(1) 是依準則執行篩選 ;(2) 是依關聯做搜尋 前者較易理解, 後者則須進一步說明 讀者可以開啟 Ch7\Exercise\Ch7-1.accdb 資料庫的 訂單及員工 查詢設計視窗, 如圖 7-5: 圖 7-5 唯一值查詢 此查詢的結果共有 10 筆記錄, 但員工資料表有 11 筆記錄, 因為有 1 位員工沒有處理過訂單記錄, 以資料庫專業術語而言, 就是 員工 所有 11 筆記錄的 員工編號 欄位值中, 共有 10 個值出現在 訂單 的 處理員工 欄位中 換言之, 若查詢使用多個資料表, 則查詢取出記錄的方式是 出現在關聯雙方的資料, 才會顯示在查詢結果中, 也就是交集 若只存在於一方, 就不會列為查詢結果 這一點相當重要, 圖 7-5 的查詢還算簡單, 只有兩個資料表, 資料表數量愈多, 關聯就愈複雜, 取出記錄的原則也就愈嚴謹 7-5
PART 2 建立關聯式資料庫 如何判斷查詢結果是否正確? 資料表愈多, 關聯愈複雜, 也愈難判斷查詢結果是否正確 一般而言, 關聯愈多, 查詢結果筆數會愈少, 若筆數較正常多出許多, 必定是錯誤結果, 請開啟 Ch7\Exercise\Ch7-1.accdb 檔案的 關聯不夠 _ 示範 查詢, 如圖 7-6: 圖 7-6 關聯不夠的錯誤示範 此查詢的執行結果共有 9946 筆, 但此資料庫中筆數最多的 訂單明細 資料表只有 905 筆, 所以資料庫的查詢不論如何設計, 其結果應不會超出 905 筆, 查詢設計視窗如圖 7-7: 這是以客戶及員工資料表建立的查詢 兩個客戶編號應該建立關聯 圖 7-7 少了關聯的查詢錯誤示範 7-6
07 查詢初步在圖 7-7 的查詢中, 共使用三個來源, 其中 員工及客戶 是查詢, 其他均為資料表, 但此一查詢在圖 7-7 的設計中少了一個關聯, 即兩個 客戶編號 欄位也應該有關聯, 讀者可以自行操作, 為這兩個欄位建立關聯, 再按下按鈕執行 如何分析查詢筆數是否正確? 筆者歸納以下兩點 : 筆數過多且資料重複 如圖 7-6 所示,9466 筆記錄中, 有許多記錄的各欄位值是重複的, 這必定是錯誤的查詢結果, 有資料表成為沒有關聯的孤兒或關聯不夠, 都會形成此一狀況 以最大筆數的資料表為參考如前所述,Ch7\Exercise\Ch7-1.accdb 檔案中記錄筆數最多的是 訂單明細, 共有 905 筆, 故查詢結果必定不會超過這個數字 但這不是絕對的準則, 因為 Ch7\Exercise\Ch7-1.accdb 檔案的結構很簡單, 只有 8 個資料表, 一般實務上使用的資料庫必定不只 8 個, 因此, 最多筆數只能做為參考 所以查詢的重點是關聯的正確定義, 筆者無法說明何種關聯才是正確, 因為視資料庫結構及需求而異 總之, 在測試時, 資料表的記錄最好不要太多, 只要保持足以判斷查詢結果是否正確即可, 筆者的習慣是測試時約使用數十筆或百筆記錄, 若有千筆記錄, 就較難以查詢結果對照資料表原始記錄, 也無從判斷查詢結果是否正確 開發的順序 開發時, 通常在定義資料表及結構 ( 即關聯 ) 之後, 就會製作表單, 不會毫無目的建立查詢 而是在建立表單及報表時, 依需求回頭建立查詢 換言之, 查詢通常是依附於表單或報表, 這是使用 Access 資料庫的常見方式 本書仍在資料表之後說明查詢, 目的是便於讀者了解, 以免落差太大 7-7
PART 2 建立關聯式資料庫 7-2 使用查詢設計視窗 Access 的查詢設計視窗相當方便, 多數動作使用滑鼠即可完成, 本節說明重點即為查詢設計視窗的操作及如何執行查詢 7-2-1 建立新查詢 與其他物件相同, 建立新查詢時, 你也可以使用精靈, 快速完成查詢設計工作 以下先使用精靈完成設計, 再說明設計視窗的操作 使用簡單查詢精靈 ( 各客戶之訂單總額 ) 練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 :Ch7\Result\Ch7-2.accdb 之 Q7211 查詢 開啟 Ch7\Exercise\Ch7-2.accdb 檔案 將功能區切換至 建立, 再按下 查詢 查詢精靈 按鈕 7-8
07 查詢初步在圖 7-8 選取 簡單查詢精靈, 按下 確定 按鈕 圖 7-8 以簡單查詢精靈建立新查詢 設定查詢使用之資料表, 首先開啟 資料表 / 查詢 清單, 選取 客戶, 再選取 客戶編號 公司名稱 欄位及按下按鈕, 結果如圖 7-9: 2 1 圖 7-9 選擇資料表及欄位 7-9
PART 2 建立關聯式資料庫 開啟 資料表 / 查詢 清單, 選取 訂單, 再選取 未稅價 營業稅 含稅價 欄位及按下按鈕, 加入 已選取的欄位, 如圖 7-10: 圖 7-10 選取來自多個資料表的欄位 在圖 7-11 選取 摘要 及按下 摘要選項 按鈕 1 2 圖 7-11 選取摘要 7-10
07 查詢初步在圖 7-12 選取 總計, 再按下 確定 按鈕 圖 7-12 選取計算方式 輸入查詢名稱, 如輸入 Q7211, 再按下 完成 按鈕 圖 7-13 輸入新查詢名稱 7-11
PART 2 建立關聯式資料庫 以上就是簡單查詢精靈的設計過程 首先, 讀者必須在圖 7-9 至 7-10 指定一或多個來源, 可用來源就是資料表或另一查詢, 若指定多個來源, Access 會自動為多個來源建立關聯 指定來源之後, 還須指定欲使用的欄位, 只有被指定的欄位會顯示在查詢結果 如圖 7-14 是本例完成結果 圖 7-14 查詢執行結果 其實本例是較複雜的簡單查詢精靈, 因為筆者在步驟 7 選擇 摘要, 若選擇 詳細, 將會更 簡單, 就不會顯示圖 7-12, 但查詢結果只會列出所有記錄, 不會計算 而本例目的是 計算各客戶訂單總額, 這是具分析及計算功能的查詢, 所以必須在圖 7-12 告訴 Access 被計算 總和 的欄位是 未稅價 等三個欄位, 就可以在結果中列出每位客戶的訂單總額 若被計算的欄位型態為日期, 也可指定依年 季 月 日等不同單位, 進行計算 以上就是簡單查詢精靈的操作, 這個精靈也是查詢的入門, 其他精靈將在本章稍後說明 7-12
07 查詢初步為求統一, 本章各範例所製作完成的查詢, 都冠以英文字母 Q, 再加上章節編號, 如 Q7211, 表示其為第 7-2-1 節的第一個查詢, 依此類推, 以便查閱 以設計視窗建立新查詢 ( 顯示所有客戶之訂單記錄 ) 練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 :Ch7\Result\ Ch7-2.accdbb 之 Q7212 查詢 開啟 Ch7\Exercise\Ch7-2.accdb 檔案 將功能區切換至 建立, 再按下 其他 查詢設計 按鈕 按住 Ctrl 鍵, 再以滑鼠於圖 7-15 選取 客戶 與 訂單 等資料表, 再按下 新增 及 關閉 按鈕 按住 Ctrl 鍵, 再選取多個資料表 圖 7-15 加入資料表 7-13
PART 2 建立關聯式資料庫 在圖 7-16 的 客戶 資料表中, 選取 客戶名稱 欄位, 按住滑鼠左鍵, 向下拖曳至第一個 欄位 後放開, 再拖曳 訂單 資料表的 訂單編號 欄位至第二個 欄位 後放開左鍵 可以由資料表拖曳欄位 圖 7-16 加入欄位 也可在此按下滑鼠, 開啟清單後, 選擇欄位 以滑鼠在下方欄位內按一下左鍵, 出現清單中選取 連絡電話 欄位 按鈕後, 按一下左鍵, 再於 按下 按下 按鈕, 在對話方塊內輸入 Q7212 及按下 確定 按鈕 按鈕, 執行查詢 本例是手動建立新查詢的基本操作, 也就是使用設計視窗, 此時會立即顯示圖 7-15, 要求指定新查詢使用之資料表, 此對話方塊也可在設計視窗內, 按下按鈕開啟之 本例的操作很簡單, 共使用兩個欄位, 讀者可以由資料表將欄位拖出, 也可在下方欄位的下拉式清單內選取 另一個方法是選取及拖曳多個欄位, 如圖 7-17: 7-14
07 查詢初步也可拖曳選取的多個欄位 圖 7-17 拖曳多個欄位 如圖 7-17 所示, 讀者可以先在資料表中按住 Shift 或 Ctrl 鍵, 再以滑鼠選取多個欄位, 再拖曳至 欄位 中, 此時的滑鼠游標是 圖 7-18 的查詢共使用兩個資料表, 此一查詢的意義是在 客戶 及 訂單 間尋找相關記錄, 假設 客戶 中有 10 筆記錄, 但 10 位客戶中只有 8 位客戶在 訂單 資料表有相關記錄, 則查詢結果就只會顯示這 8 位客戶的資料, 也就是兩者的交集 所以照理說, 使用資料表愈多的查詢, 查詢結果記錄可能愈少, 因為交集會愈來愈小 反之, 若查詢結果的筆數較原始資料表的筆數多出數倍, 表示查詢設計錯誤 所以建議在使用 Access 之初, 練習查詢時, 資料表記錄不要過多, 因為若筆數太多, 較不容易檢查查詢設計是否有誤, 只要保持在可確定查詢結果無誤的少部份記錄即可 7-15
PART 2 建立關聯式資料庫 7-2-2 設計視窗操作說明 查詢設計視窗如圖 7-17, 此視窗可分為兩部份, 分別是上半部的 來源區 及下半部的 設定區, 這兩個名詞是筆者的定義 Access 將查詢設計視窗的下半部稱為 設計格線, 筆者認為不太符合此區的真意, 所以本書將以來源區及設定區稱呼這兩個區域 如圖 7-18: 來源區 : 顯示使用的資料表 查詢及關聯 設定區 : 顯示使用的欄位及準則等各種設定, 會影響查詢結果 圖 7-18 來源及設定區的簡易說明 如圖 7-18 所示, 來源區就是查詢的資料來源, 此處主要操作就是資料表 ; 設定區則是查詢設計視窗的主要操作位置 在查詢中插入新欄位 練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 : 無 7-16
07 查詢初步在 Ch7\Exercise\Ch7-2.accdb 中選取例前例製作的 Q7212 查詢, 按下右鍵後再選擇 設計檢視 在 訂單編號 欄位中按一下滑鼠左鍵, 再按下 設計 查詢設定 插入欄 按鈕, 如圖 7-19: 圖 7-19 插入新欄位 空出新欄位後, 在新欄位內按下 按鈕, 選取 客戶. 地址 欄位 本範例目的是在現有欄位之間加入新欄位, 有很多種方法可以達成, 本範例是標準而不太快速的操作 最快速的方式是直接將欄位拖曳到所需位置, 放開滑鼠左鍵後, 該位置原有欄位就會向右移動, 原位置即為新欄位 7-17
PART 2 建立關聯式資料庫 在查詢中移動欄位 練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 : 無 在 Ch7\Exercise\Ch7-2.accdb 中選取例前例製作的 Q7212 查詢, 按下右鍵後再選擇 設計檢視 將滑鼠移至 連絡電話 欄位上方, 滑鼠指標顯示為後, 按住左鍵並向左拖曳, 選取 地址 及 訂單編號 等兩個欄位後放開左鍵, 如圖 7-20: 圖 7-20 選取多個欄位 按住 Shift 鍵及選取多個欄位 7-18
07 查詢初步將滑鼠移至選取的黑色區域內, 再按住左鍵及向右拖曳至 連絡人姓名 欄位的右方, 如圖 7-21: 圖 7-21 拖曳欄位 拖曳至新位置 本例有兩個動作, 分別是選取及搬移 只要滑鼠指標顯示為, 即表示可選取整欄, 再向左或向拖曳, 則可選取多欄位 ; 另一選取多欄的方法如圖 7-20 的說明, 即按住 Shift 鍵, 再以滑鼠選取欄位 執行搬移時, 而搬移的對象就是已被選取的一或多個欄位, 如圖 7-21 表示選取的兩個欄位將置於 連絡人姓名 及 縣市 之間 選取的後續處理可以是搬移或刪除, 例如在圖 7-20 的狀態下, 按下 Del 鍵, 即可刪除欄位, 但只是在查詢中刪除欄位 ( 不顯示在查詢執行結果 ), 被刪除的欄位仍存在於資料表內 隱藏欄位 此一操作很簡單, 如圖 7-21 的設定區中, 每個欄位加入至設定區後, 顯示 均為開啟狀態, 表示該欄將顯示在查詢結果內, 在特殊情況下, 7-19
PART 2 建立關聯式資料庫 也可指定不顯示, 只要在方格內按一下左鍵, 清除 符號即可 如圖 7-22: 共五個欄位, 隱藏一個 執行結只有四個欄位 圖 7-22 隱藏之後 隱藏之後, 該欄的所有其他設定仍然有效, 包括 : 使用函數 準則等, 所以顯示及隱藏的更改動作, 不會影響查詢結果 設定遞增及遞減排序 練習檔 :Ch7\Exercise\Ch7-2.accdb 完成檔 : 無 在 Ch7\Exercise\Ch7-2.accdb 中選取例前例製作的 Q7212 查詢, 按下右鍵後再選擇 設計檢視 7-20
07 查詢初步在 客戶名稱 欄位設定 遞增 排序, 再於 連絡人姓名 欄位設定 遞減 排序, 如圖 7-23: 圖 7-23 設定排序 本例結果是以 客戶名稱 做遞增排序, 若有同名客戶, 再以 連絡人姓名 欄位做 遞減, 使用多個欄位排序時, 排序順序是由左而右, 所以最左方的排序欄位會最先被排序, 依此類推 查詢與資料表名稱不可相同 查詢中的關聯相當重要, 若關聯不足, 筆數會過多而不正確 ; 若關聯過多, 筆數會太少而不正確 查詢不會毫無目的的存在, 通常會搭配表單或報表 新上手時, 可使用簡單查詢精靈, 協助製作查詢 查詢設計視窗分為來源及設定區 在設定區更改任一設計後, 可按下 按鈕, 查看執行結果 7-21
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
07 查詢初步( )7. 在查詢中拖曳新欄位後, 若新欄位所在位置已有欄位, 則此舊欄位會 (A) 刪除 (B) 向左移 (C) 向右移 ( )8. 以下有關選取多個欄位的說明, 何者錯誤? (A) 按住 Shift 鍵及以滑鼠選取 (B) 按住 Ctrl 鍵及以滑鼠選取 (C) 按住滑鼠左鍵向左或向右拖曳滑鼠及選取之 以上圖回答 9-10 題 ( )9. 此一查詢的執行結果會顯示幾個欄位? (A)4 (B)3 (C)2 (D)1 7-23
PART 2 建立關聯式資料庫 ( )10. 以下何者可能是此查詢的執行結果? (A) (B) (C) 7-24