Microsoft PowerPoint - QueryForm_ [相容模式]

Size: px

Start display at page:

Download "Microsoft PowerPoint - QueryForm_ [相容模式]"

伐番延
7 years ago
Views:

1 << 會計資訊系統課程講義 >> Access 2007 DBMS ~ 查詢 SQL 語法及表單 ~ 周國華屏東商業技術學院會計系初版 :2007/11/25 本次修正 :2011/11/26

2 智慧財產權聲明本文件係由周國華老師獨自撰寫, 除引用之概念屬於原文作者外, 其餘文字及圖形內容之智慧財產權當然屬於周老師獨有任何機構或個人, 在未取得周老師同意前, 不得直接以本文件做為學校研究機構企業會計師事務所政府機關或財團法人機構舉辦教學或進修課程之教材, 否則即屬侵權行為任何機構或個人, 在未取得周老師同意前, 不得在自行編撰的教材中直接大量引用本文件的內容若屬單頁內部分內容之引用, 亦請註明出處屏東商業技術學院周國華 2

3 第一部份查詢及 SQL 語法屏東商業技術學院周國華 3

4 查詢物件查詢 (query) 是 Access 2007 的六種資料庫物件之一, 目的在將常用的查詢條件預存起來, 以備後續使用查詢物件是由查詢條件所構成在點選已儲存之查詢物件後, 使用者看到的是一份與資料表 (table) 極為類似的查詢結果表格 ( 在 Oracle DB 中稱為視觀表 [View]), 此份表格並非真正的資料表, 其內容資料並非儲存在該表格中, 而是動態擷取自來源資料表或其他查詢物件查詢的結構如下 : 資料表 A 資料表 B 透過查詢 D 產生視觀表查詢 C 註 : 來源物件可多可少屏東商業技術學院周國華 4

5 建立查詢物件在 Access 2007 中建立查詢物件, 可從下列方式二選一 : 使用查詢精靈 : 由查詢精靈協助建立查詢物件使用查詢設計 : 以手動方式建立查詢物件說明範例 : 根據前一份講義建立的出版社資料庫, 包含以下四份資料表訂單編號訂單日期客戶編號收款條件客戶編號客戶名稱送貨地址客戶電話書籍編號書籍名稱銷貨定價訂單編號書籍編號訂購數量查詢範例 : 查出每筆訂單的內容, 依次列示訂單編號客戶名稱書籍名稱訂購數量及銷貨定價等五個欄位屏東商業技術學院周國華 5

6 使用查詢精靈按下功能區的建立索引標籤, 再按下其他群組的按鈕, 在新增查詢對話窗中選擇簡單查詢精靈, 按確定在簡單查詢精靈的資料表 / 查詢下拉式選單中選取資料表 : 訂單 ( 註 : 這個對話窗顯示查詢的資料可以來自資料表或其他查詢 ) 屏東商業技術學院周國華 6

7 在 ( 上左圖 ) 可用的欄位中, 選取訂單編號欄位 ( 上右圖 ) 然後依序從客戶書籍及訂單 - 條列 - 書籍資料表中選取客戶名稱書籍名稱訂購數量及銷貨定價等欄位 ( 右圖 ) 按完成屏東商業技術學院周國華 7

8 右圖為已完成之訂單內容查詢物件, 表格內所有欄位之資料均動態擷取自前述各資料表在資料工作表檢視視窗的右下角有等五個按鈕, 按下 SQL 按鈕後, 即可顯示此一查詢物件的 SQL 語法 SQL 語法如下 : SELECT 訂單. 訂單編號, 客戶. 客戶名稱, 書籍. 書籍名稱, [ 訂單 - 條列 - 書籍 ]. 訂購數量, 書籍. 銷貨定價 FROM 書籍 INNER JOIN (( 客戶 INNER JOIN 訂單 ON 客戶.[ 客戶編號 ] = 訂單.[ 客戶編號 ]) INNER JOIN [ 訂單 - 條列 - 書籍 ] ON 訂單.[ 訂單編號 ] = [ 訂單 - 條列 - 書籍 ].[ 訂單編號 ]) ON 書籍.[ 書籍編號 ] = [ 訂單 - 條列 - 書籍 ].[ 書籍編號 ]; 屏東商業技術學院周國華 8

9 Inner join ( 內部連結 ): 當兩個資料表的共同欄位 (i.e., 一方的主索引 = 多方的外來鍵 ) 中有相符的值時, 可以透過 inner join 合併這兩個資料表的紀錄連結兩個資料表之語法 : SELECT **** FROM 資料表 1 INNER JOIN 資料表 2ON 資料表 1. 欄位 1= 資料表 2. 欄位 1; 連結多個資料表 ( 以 4 個資料表為例 ) 之語法 : SELECT **** FROM 資料表 1 INNER JOIN (( 資料表 2 INNER JOIN 資料表 3ON 資料表 2. 欄位 2= 資料表 3. 欄位 2) INNER JOIN 資料表 4ON 資料表 3. 欄位 3= 資料表 4. 欄位 3) ON 資料表 1. 欄位 1= 資料表 4. 欄位 1; 屏東商業技術學院周國華 9

10 使用查詢設計按下功能區的建立索引標籤, 再按下其他群組的按鈕在顯示資料表視窗中, 新增全部資料表, 畫面如下 : 屏東商業技術學院周國華 10

11 在右上圖下方的欄位中, 可透過下拉式選單選取欲顯示的欄位名稱 ; 或者, 可將滑鼠指標指向上方資料表的特定欄位名稱, 然後按住滑鼠左鍵, 以拖曳方式拉至下方特定欄位上放開, 即可選定該欄位已完成查詢設定的五個欄位如右下圖然後, 選按功能區設計索引標籤內的按鈕, 即出現查詢結果 ( 下圖 ) 存檔時, 再將查詢物件改為較符合語意內涵的名稱 (: 訂單內容查詢 ) 屏東商業技術學院周國華 11

12 查詢的種類 Access 2007 內的查詢共有四種類別 : 選取查詢 : 是最常見的查詢類型, 可從一個或多個資料表及查詢中擷取資料, 並可新增欄位分組統計設定條件過濾資料等等前述的訂單內容查詢範例, 即屬於選取查詢參數查詢 : 執行時會出現對話窗讓使用者輸入參數值, 再根據參數值進行查詢交叉查詢 : 可產生同時具有欄標題及列標題的資料工作表動作查詢 : 此類查詢的目的是在對資料表內的多筆記錄進行變更或移除, 下達執行指令後會將結果直接套用在指定的資料表內, 且不可回復! * 本講義將針對前三種查詢類別舉例說明其建立步驟及功能含意屏東商業技術學院周國華 12

13 選取查詢查詢範例 : 從上述出版社資料庫中, 查出每筆訂單的總金額, 依次列示訂單編號客戶名稱訂購總額等欄位首先, 在前述客戶訂單查詢物件中, 增加小計欄 : 在銷貨定價欄位旁之空白欄位, 按滑鼠右鍵, 選擇建立幫手屏東商業技術學院周國華 13

14 在運算式建立幫手窗格中, 輸入 : 小計 :[ 訂購數量 ]*[ 銷貨定價 ] 按確定此時, 訂單內容查詢物件即增加小計欄位, 右下圖為執行此項查詢之結果以右圖之查詢內容為基礎, 繼續用查詢精靈的簡單查詢精靈選項建立一個新的訂單總額查詢物件 : 屏東商業技術學院周國華 14

15 在簡單查詢精靈對話窗中, 選取訂單編號客戶名稱及小計三個欄位按下一步在下一個頁面中選擇摘要, 並點選摘要選項在摘要選項對話窗中勾選總計, 按確定, 回到查詢精靈再按下一步屏東商業技術學院周國華 15

16 在右圖對話窗格中輸入訂單總額查詢, 做為此查詢物件的名稱按完成右下圖為已完成之訂單總額查詢物件, 但最後一個欄位名稱為小計之總計, 不符合原先設定的欄位名稱將畫面轉至設計檢視, 滑鼠指標移至小計之總計欄位, 按滑鼠右鍵, 選按屬性屏東商業技術學院周國華 16

17 在屬性表的標題欄中輸入訂購總額按 X 關閉屬性表轉回工作表檢視畫面, 此時訂單總額查詢物件的三個欄位名稱就完全符合原先設計要求了本查詢範例心得 : 查詢物件的資料來源物件, 可包含資料表及其他查詢物件小計總計這種可推論屬性不必儲存在資料表內, 透過查詢方式即可在需要時動態取得最新數值屏東商業技術學院周國華 17

18 參數查詢查詢範例 : 從上述出版社資料庫中, 查出特定期間的訂單資料, 依次列示訂單編號訂單日期客戶名稱書籍名稱及訂購數量等欄位首先, 開啟查詢精靈的簡單查詢精靈對話窗, 選取訂單編號訂單日期客戶名稱書籍名稱及訂購數量等欄位按下一步兩次屏東商業技術學院周國華 18

19 在查詢標題窗格中, 輸入特定期間訂單查詢, 並選擇修改查詢的設計, 按完成在設計檢視畫面中, 選按功能區顯示 / 隱藏群組的參數按鈕在查詢參數窗格中, 輸入開始日期及結束日期兩個參數, 並將資料類型設定為日期時間按確定屏東商業技術學院周國華 19

20 在訂單日期欄位的準則屬性中, 輸入 : Between [ 開始日期 ] And [ 結束日期 ] 按按鈕, 系統會先後出現兩個要求輸入參數值的對話窗假設分別輸入 2007/11/10 及 2007/11/15, 按確定後之查詢結果如下圖 : 屏東商業技術學院周國華 20

21 交叉查詢查詢範例 : 從上述出版社資料庫中, 查出每家書局訂購每本書的數量, 查詢結果以書籍名稱做為列標題, 以客戶名稱做為欄標題 * 注意事項 : 交叉查詢的來源物件僅限於一個, 因此, 若個別資料表或查詢物件的欄位內容不符需求, 必須先新增一個包含所需欄位的查詢物件首先, 開啟查詢精靈後, 在新增查詢對話窗中, 選擇交叉資料表查詢精靈, 按確定屏東商業技術學院周國華 21

22 在交叉資料表查詢精靈對話窗的檢視窗格中, 選擇查詢, 然後在上方顯示的查詢物件中, 選擇查詢: 訂單內容查詢按下一步在可用的欄位中, 選擇書籍名稱做為列標題按下一步繼續選擇客戶名稱做為欄標題按下一步屏東商業技術學院周國華 22

23 在欄位窗格中, 選擇訂購數量做為交叉表的欄列交點數值 ; 在函數窗格中, 選擇合計在圖中的你要為每列做合計嗎? 詢答中, 勾選是, 加上列合計按下一步將查詢命名為客戶訂購書籍數量交叉查詢, 按完成已完成之交叉查詢表如下圖屏東商業技術學院周國華 23

24 現階段資料庫內容在完成前述查詢設定後, 現階段出版社資料庫包含四個資料表物件及四個查詢物件 ( 如下圖 ) 屏東商業技術學院周國華 24

25 SQL 語法彙整 2-1 前述訂單內容查詢物件的 SQL 語法如下 : SELECT 訂單. 訂單編號, 客戶. 客戶名稱, 書籍. 書籍名稱, [ 訂單 - 條列 - 書籍 ]. 訂購數量, 書籍. 銷貨定價, [ 訂購數量 ]*[ 銷貨定價 ] AS 小計 FROM 書籍 INNER JOIN (( 客戶 INNER JOIN 訂單 ON 客戶. 客戶編號 = 訂單. 客戶編號 ) INNER JOIN [ 訂單 - 條列 - 書籍 ] ON 訂單. 訂單編號 =[ 訂單 - 條列 - 書籍 ]. 訂單編號 ) ON 書籍. 書籍編號 =[ 訂單 - 條列 - 書籍 ]. 書籍編號 ; 前述訂單總額查詢物件的 SQL 語法如下 : SELECT DISTINCTROW [ 訂單內容查詢 ]. 訂單編號, [ 訂單內容查詢 ]. 客戶名稱, Sum([ 訂單內容查詢 ]. 小計 ) AS [ 小計之總計 ] FROM [ 訂單內容查詢 ] GROUP BY [ 訂單內容查詢 ]. 訂單編號, [ 訂單內容查詢 ]. 客戶名稱 ; 屏東商業技術學院周國華 25

26 SQL 語法彙整 2-2 前述特定期間訂單查詢物件的 SQL 語法如下 : PARAMETERS 開始日期 DateTime, 結束日期 DateTime; SELECT 訂單. 訂單編號, 訂單. 訂單日期, 客戶. 客戶名稱, 書籍. 書籍名稱, [ 訂單 - 條列 - 書籍 ]. 訂購數量 FROM 書籍 INNER JOIN (( 客戶 INNER JOIN 訂單 ON 客戶.[ 客戶編號 ] = 訂單.[ 客戶編號 ]) INNER JOIN [ 訂單 - 條列 - 書籍 ] ON 訂單.[ 訂單編號 ] = [ 訂單 - 條列 - 書籍 ].[ 訂單編號 ]) ON 書籍.[ 書籍編號 ] = [ 訂單 - 條列 - 書籍 ].[ 書籍編號 ] WHERE ((( 訂單. 訂單日期 ) Between [ 開始日期 ] And [ 結束日期 ])); 前述客戶訂購書籍數量交叉查詢物件的 SQL 語法如下 : TRANSFORM Sum([ 訂單內容查詢 ].[ 訂購數量 ]) AS 訂購數量之總計 SELECT [ 訂單內容查詢 ].[ 書籍名稱 ], Sum([ 訂單內容查詢 ].[ 訂購數量 ]) AS [ 合計訂購數量 ] FROM [ 訂單內容查詢 ] GROUP BY [ 訂單內容查詢 ].[ 書籍名稱 ] PIVOT [ 訂單內容查詢 ].[ 客戶名稱 ]; 屏東商業技術學院周國華 26

27 第二部份表單簡介屏東商業技術學院周國華 27

28 表單物件表單 (form) 是 Access 2007 的六種資料庫物件之一, 資料表建立完成並設定好關聯性後, 應進一步為各個資料表建立對應的表單, 以做為資料輸入的介面為維護資料表的安全性, 資料庫系統的建構者應盡可能避免讓一般使用者直接在資料表內進行資料編修作業建立與資料表對應的表單, 一方面可讓使用者透過較具親和力的介面來操作資料庫, 另方面因表單只能逐筆 ( 一筆或多筆 ) 增修資料, 可避免使用者因一時大意或蓄意而造成整份資料表的毀損當特定主題的資料內容 ( 例如 : 銷貨訂單 ) 因資料表正規化的的結果而分散儲存到多張資料表時, 可透過單一表單進行資料編修, 資料即可自動儲存到對應的資料表欄位屏東商業技術學院周國華 28

29 建立表單物件在 Access 2007 中建立表單物件, 可從下列方式二選一 : 選定個別資料表或查詢物件後, 按建立索引標籤內的按鈕, Access 即自動產生一份與該物件對應的表單經過適當的編修處理, 即可使用該表單做為資料表內容的維護介面若表單內容來自多個資料表, 則可選按建立索引標籤內按鈕的選項, 透過精靈指引來完成表單的製作說明範例 : 根據講義第一部份建立的出版社資料庫首先, 選定客戶資料表, 按按鈕建立客戶表單 ; 再選定書籍資料表, 依樣建立書籍表單兩份表單均先按預設畫面存檔然後透過建立銷貨訂單表單, 內容包含訂單編號客戶編號客戶名稱 ( 書籍編號書籍名稱銷貨定價訂購數量小計 ) 總計等欄位括弧 () 內之欄位可透過子表單連結方式一次輸入多筆資料屏東商業技術學院周國華 29

30 表單 : 新增檢視與更正表單在開啟時, 可預設其為新增資料表單檢視資料表單或資料更正表單 ( 因此, 同一來源資料表可產生多個對應表單 ): 新增表單 : 表單開啟時所有欄位內容均為空值, 以供新增資料輸入用設定方式 : 將屬性表中的選取類型設定為表單, 將資料索引標籤內的資料輸入屬性改為是並確定允許新增允許刪除允許編輯三個屬性的值均為是檢視表單 : 表單開啟後預設顯示第一筆資料, 可設定上一筆下一筆按鈕以方便檢視在檢視表單中不能進行新增刪除或編輯動作設定方式 : 將屬性表中的選取類型設定為表單, 將資料索引標籤內的資料輸入允許新增允許刪除允許編輯四個屬性的值均設定為否資料更正表單 : 表單開啟後預設顯示第一筆資料, 可設定上一筆下一筆按鈕以搜尋欲更正之紀錄設定方式 : 將屬性表中的選取類型設定為表單, 將資料索引標籤內的資料輸入設定為否, 允許新增允許刪除允許編輯三個屬性的值均設定為是屏東商業技術學院周國華 30

31 複雜表單 : 內容包含多個來源物件若要在單一表單內包含不同資料表, 這些資料表之間必須先建立一對多或多對多的關係換言之, 兩個資料表之間若無關連存在, 就不能放在同一個表單內進行資料增修作業表單內若包含兩個來源資料表, 通常會以一方做為表單的主檔, 以多方做為子表單, 子表單即可同時輸入多筆資料查詢物件也是表單的來源物件之一, 所以事先設定好查詢物件, 即可在表單內輸入或檢視結構複雜的資料屏東商業技術學院周國華 31

32 以表單整合系統在為各個資料表建立好對應的表單後, 應進一步建立整合性表單, 以做為系統整合的介面資料庫系統通常會設定一個開啟系統後的預設表單畫面, 此畫面內包含系統簡介, 以及各項功能按鈕每個功能按鈕指向不同表單, 故透過設定多個功能按鈕即可將各表單的功能整合在整合性表單畫面中當系統漸趨複雜後, 可設定多層次的整合性表單畫面, 讓使用者在不同的整合性表單畫面之間轉換欲建立整合性表單, 應選按建立索引標籤頁面的空白表單按鈕, 在設計檢視頁面中建立按鈕, 並將按鈕設定成指向特定表單在建立整合性表單後, 可在特定功能表單內建立回主畫面按鈕, 以方便使用者在特定功能表單與整合性表單之間做轉換屏東商業技術學院周國華 32

33 起始畫面表單設定為了讓資料庫開啟後能直接進入預設起始畫面, 須進行以下設定 : 選按建立索引標籤頁面的巨集按鈕, 在巨集編輯頁面的巨集指令欄中選擇 OpenForm 指令, 然後在畫面下方巨集指令引數的表單名稱中, 選擇做為系統預設開啟畫面的表單此時, 上方的引數欄中即出現該選定的表單名稱將巨集存檔, 名稱設定為 AutoExec 此後, 只要每次一開啟資料庫, 就會自動進入預設的起始表單畫面此處所謂資料庫係指由單一檔案所代表的資料庫而言, 而非指開啟 Access 2007 資料庫軟體屏東商業技術學院周國華 33

34 單一表單畫面以表單做為系統使用介面, 可透過表單之間轉換的設定, 讓螢幕上只呈現單一表單畫面, 一方面可降低系統負荷, 另方面可維持螢幕畫面的簡潔設定方式 : 在每一個轉換表單畫面的按鈕中, 增加一個巨集指令 Close 順序 : 先 Close 目前表單, 再 OpenForm 新的表單注意 : 若以上兩個指令順序顛倒, 系統會先開啟新表單再關閉新表單, 因此畫面上仍是原先的舊表單解決方式 : 可在巨集編輯畫面中調整指令順序或者, 不更動順序, 但在 Close 指令的引數中先將物件類型選定為表單再從物件名稱中選擇欲關閉的表單屏東商業技術學院周國華 34

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,