建立以多個資料表為基礎的查詢

在 Access 中,有時候建立和使用查詢的過程只需從表格中選取欄位,並套用幾項準則,接著就能檢視結果。但通常的情況是,如果您需要的資料分散在多個資料表中呢?幸好,您可以建立能合併多個來源的查詢。本主題探討的是您需要從一個以上的資料表擷取資料的情況,並示範該如何執行。

您想要做什麼?

使用相關資料表中的資料來加強查詢中的資訊

使用兩張資料表與第三張資料表之間的關聯,來連結前兩者中的資料

檢視來自兩個相似資料表中的所有記錄

使用相關資料表中的資料來加強查詢中的資訊

您可能會遇到這樣的情況:以某張資料表為基礎建立的查詢能提供您所需的資訊,但從另一張資料表中擷取的資料,能讓查詢的結果更清楚且實用。例如,假設您的查詢結果中顯示了一份員工識別碼清單。您發現,如果能在結果中看到員工姓名的話會更有用,但員工姓名卻位在另一張不同的資料表上。為了讓員工姓名出現在查詢結果裡,您需要將兩張資料表都納入查詢之中。

使用查詢精靈建立來自主要資料表以及相關資料表中的查詢

  1. 請確保資料表在資料庫關聯圖視窗中具有已定義的關聯圖。

    做法

    1. 在 [資料庫工具] 索引標籤上,按一下 [顯示/隱藏] 群組中的 [資料庫關聯圖]。

    2. 在 [設計] 索引標籤上,按一下 [資料庫關聯圖] 群組中的 [所有關聯]。

    3. 識別應該擁有已定義之關聯的資料表。

      • 如果資料表顯示在 [資料庫關聯圖] 視窗中,請檢查已經定義關聯。

        關聯會在常見欄位中顯示為連結兩張資料表的線段。您可以按兩下關聯線,來查看該關聯連結了資料表中哪些欄位。

      • 如果資料表並未顯示在 [資料庫關聯圖] 視窗中,您就必須新增。

        在 [設計] 索引標籤的 [顯示/隱藏] 群組中,按一下 [資料表名稱]。

        對您要顯示的每張資料表按兩下,然後按一下 [關閉]。

    4. 如果您找不到兩張資料表之間的關聯,請將其中一張資料表上的欄位拖曳至另一張資料表上的欄位,以建立關聯。用來建立資料表之間關聯的欄位必須擁有相同的資料類型。

      附註: 如果欄位的整數欄位大小較大的話,您就可以建立自動編號資料類型欄位和數字資料類型欄位之間的關聯。當您建立一對多關聯性時,常會遇到這種情況。

      隨即出現 [編輯關聯] 對話方塊。

    5. 按一下 [建立] 來建立關聯。

      如需您在建立關聯時擁有之選項的詳細資訊,請參閱建立、編輯或刪除關聯

    6. 關閉 [資料庫​​關聯圖] ​​視窗。

  2. 在 [建立] 索引標籤上,按一下 [查詢] 群組中的 [查詢精靈]。請注意,如果您使用的是 Access 2007,請在 [建立] 索引標籤的 [其他] 群組中,按一下 [查詢精靈]。

  3. 在 [新增查詢] 對話方塊中,按一下 [簡單查詢精靈] ,然後按一下 [確定]。

  4. 在 [資料表/查詢] 下拉式方塊中,按一下含有您要納入查詢之基本資訊的資料表。

  5. 在 [可用的欄位] 清單中,按一下要納入查詢的第一個欄位,然後再按一下向右單箭號,以將該欄位移動到 [已選取的欄位] 清單。對該資料表中每一個您想要納入查詢的其他欄位執行同樣動作。它們可以是您想要在查詢輸出中傳回的欄位,或是您想要用來套用準則,以限制輸出結果之列數的欄位。

  6. 在 [資料表/查詢] 下拉式方塊中,按一下含有您想要用來強化查詢結果之相關資料的資料表。

  7. 將您想要用來強化查詢結果的欄位新增到 [已選取的欄位] 清單,然後按一下 [下一步]。

  8. 按一下 [您要詳細或摘要查詢?] 底下的 [詳細資料] 或 [摘要]。

    如果您不想要查詢執行任何彙總函數 (SumAvgMinMaxCountStDevVar),請選擇詳細資料查詢。如果您想要查詢執行彙總函數,請選擇摘要查詢。選好之後,請按一下 [下一步]。

  9. 按一下 [完成] 來檢視結果。

使用 [北風] 範本資料庫的範例

在下列範例中,您要使用 [查詢精靈] 來建立查詢,該查詢會顯示訂單清單、每筆訂單的運費,以及處理每筆訂單之員工的姓名。

附註: 此範例需要修改 [北風] 範本資料庫。建議您備份 [北風] 範本資料庫,並在此範例中使用該備份。

使用 [查詢精靈] 建立查詢

  1. 開啟 [北風] 範本資料庫。關閉登入表單。

  2. 在 [建立] 索引標籤上,按一下 [查詢] 群組中的 [查詢精靈]。請注意,如果您使用的是 Access 2007,請在 [建立] 索引標籤的 [其他] 群組中,按一下 [查詢精靈]。

  3. 在 [新增查詢] 對話方塊中,按一下 [簡單查詢精靈] ,然後按一下 [確定]。

  4. 在 [資料表/查詢] 下拉式方塊中,按一下 [資料表:訂單]。

  5. 按兩下 [可用的欄位] 清單中的 [訂單識別碼],將該欄位移動到 [已選取的欄位] 清單中。按兩下 [運費],將該欄位移到 [已選取的欄位] 清單。

  6. 在 [資料表/查詢] 下拉式方塊中,按一下 [資料表:員工]。

  7. 按兩下 [可用的欄位] 清單中的 [名字],將該欄位移動到 [已選取的欄位] 清單中。按兩下 [姓氏],將該欄位移到 [已選取的欄位] 清單。按一下 [下一步]。

  8. 因為您建立的是全部訂單的清單,建議您使用詳細資料查詢。如果您要根據員工摘要運費,或是執行其他彙總函數,請使用摘要查詢。按一下 [詳細 (顯示每筆記錄的每個欄位)],然後按一下 [下一步]。

  9. 按一下 [完成] 來檢視結果。

查詢會傳回一份訂單清單,其中含有每筆訂單的運費,以及處理人員的姓氏、名字。

頁面頂端

使用兩張資料表與第三張資料表之間的關聯,來連結前兩者中的資料

兩張資料表中資料的關聯大部分都會透過第三張資料表,這常是因為前兩張資料表間的資料都處於多對多關聯性中。通常,將兩個資料表之間的多對多關聯性分成兩個涉及三個資料表的一對多關聯性,是較好的資料庫設計方式。您可以透過建立第三個資料表來這麼做,而在這個稱為「聯合資料表」或「資料庫關聯資料表」的資料表中,其他的資料表都會有自己的主索引鍵和外部索引鍵。系統會隨後在聯合資料表中的每個外部索引鍵,以及另外一張資料表中的對應主索引鍵之間,建立一對多關聯性。在這些情況下,您需要將三張資料表都納入您的查詢中,即使您只想要從其中兩張擷取資料。

使用具有多對多關聯性的資料表來建立選取查詢

  1. 在 [建立] 索引標籤上,按一下 [查詢] 群組中的 [查詢設計]。請注意,如果您使用的是 Access 2007,請在 [建立] 索引標籤的 [其他] 群組中,按一下 [查詢設計]。

    [顯示資料表] 對話方塊隨即開啟。

  2. 在 [顯示資料表] 對話方塊中,按兩下那兩張含有您要納入查詢之資料的資料表,以及連結它們的聯合資料表,然後按一下 [關閉]。

    三張資料表都會出現在查詢設計工作區中,並聯結適當的欄位。

  3. 按兩下您要用於查詢結果中的各個欄位。每個欄位會隨後出現在查詢設計格線中。

  4. 在查詢設計格線中,使用 [準則] 列來輸入欄位準則。若要使用欄位準則,但不想要讓欄位內容出現在查詢結果中,請清除該欄位之 [顯示] 列中的核取方塊。

  5. 若要依據欄位中的值來排序結果,請在查詢設計格線中,(視您想要排序記錄的方式) 按一下該欄位之 [排序] 列中的 [遞增] 或 [遞減]。

  6. 在 [設計] 索引標籤上的 [結果] 群組中,按一下 [執行]。

    Access 會在資料工作表檢視中顯示查詢輸出。

使用 [北風] 範本資料庫的範例

附註: 此範例需要修改 [北風] 範本資料庫。建議您備份 [北風] 範本資料庫,並在此範例中使用該備份。

假設您擁有一個新的銷售商機:里約熱內盧的某個供應商發現了您的網站,並可能想要與您進行業務往來,但是他們的營業地區只在里約以及聖保羅周邊。他們能夠供應您代理之食物產品中的每一項類別。他們是相當大的公司,要求您保證能向其提供值得花費處理資源以賺取足夠的潛在銷售額:至少每年 R$20,000.00 巴西雷亞爾 (大約 $9,300.00 美元)。您是否能提供他們要求的市場呢?

您回答這個問題所需的資料位於兩個地方:[客戶] 資料表以及 [訂單詳細資料] 資料表。這些資料表都透過 [訂單] 資料表彼此連結,且資料表之間的關聯性都已經定義好了。在 [訂單] 資料表中,每筆訂單都只有一位客戶,並與 [客戶] 資料表的 [客戶識別碼] 欄位相關聯。而 [訂單詳細資料] 資料表上的每筆訂單,都只會和 [訂單] 資料表中 [訂單識別碼] 欄位的一筆訂單相關聯。因此,特定客戶可以擁有多筆訂單,而每筆訂單都有許多訂單詳細資料。

在這個範例中,您會建立一個交叉資料表查詢,其中顯示里約熱內盧以及聖保羅兩座城市每一年度的總銷售額。

在 [設計] 檢視中建立查詢

  1. 開啟 [北風] 資料庫。關閉登入表單。

  2. 在 [建立] 索引標籤上,按一下 [查詢] 群組中的 [查詢設計]。請注意,如果您使用的是 Access 2007,請在 [建立] 索引標籤的 [其他] 群組中,按一下 [查詢設計]。

    [顯示資料表] 對話方塊隨即開啟。

  3. 在 [顯示資料表] 對話方塊中,按兩下 [客戶]、[訂單] 和 [訂單詳細資料],然後按一下 [關閉]。

    三張資料表都會出現在查詢設計工作區中。

  4. 在 [客戶] 資料表中,按兩下 [城市] 欄位,將它新增至查詢設計格線。

  5. 在查詢設計格線中的 [城市] 資料行以及 [準則] 資料列,輸入「In ("里約熱內盧","聖保羅")」。這會只將位於這兩個城市其中之一的客戶記錄納入查詢之中。

  6. 在 [訂單詳細資料] 資料表中,按兩下 [出貨日期] 以及 [單價] 欄位。

    將欄位新增到查詢設計方格。

  7. 在查詢設計格線的 [出貨日期] 資料欄中,選取 [欄位] 資料列。將 [出貨日期] 取代為「年度:Format([出貨日期],"yyyy")」。這會建立欄位別名「年度」,可讓您只使用 [出貨日期] 欄位中屬於年度的部分。

  8. 在查詢設計格線的 [單價] 資料欄中,選取 [欄位] 資料列。以「銷售額: [訂單詳細資料].[單價]*[數量]-[訂單詳細資料].[單價]*[數量]*[折扣]」取代 [單價]。這會建立能夠計算每筆記錄之銷售額的欄位別名「銷售額」。

  9. 在 [設計] 索引標籤上,按一下 [查詢類型] 群組中的 [交叉資料表]。

    查詢設計格線中會顯示兩個新的資料列,[合計] 和 [交叉資料表]。

  10. 在查詢設計格線中的 [城市] 資料行中,按一下 [交叉資料表] 資料列,然後按一下 [列名]。

    這會讓 [城市] 的值顯示為列名 (也就是說,查詢會針對每個城市傳回一個資料列)。

  11. 在 [年度] 資料行中,按一下 [交叉資料表] 資料列,然後按一下 [欄名]。

    這會讓 [年度] 的值顯示為欄名 (也就是說,查詢會針對每個年度傳回一個資料欄)。

  12. 在 [銷售額] 資料行中,按一下 [交叉資料表] 資料列,然後按一下 [值]。

    這會將 [銷售額] 的值顯示在資料列和資料行的交集處 (也就是說,查詢會針對每個城市和年度組合,傳回一個銷售額的值)。

  13. 在 [銷售額] 資料行中,按一下 [合計] 資料列,然後按一下 [加總]。

    這會讓查詢加總此資料行中的值。

    因為您想要查看的是這些資料行的值,而非彙總的值,所以您可以讓另外兩個資料行中 [合計] 資料列保持為 [群組依據] 的預設值。

  14. 在 [設計] 索引標籤上的 [結果] 群組中,按一下 [執行]。

現在,您就有了一個能夠依照年度傳回里約熱內盧和聖保羅總銷售額的查詢了。

頁面頂端

檢視來自兩個相似資料表中的所有記錄

有時候,面對兩張結構相同的資料表,但其中之一位於另一個資料庫時,您會想要將它們的資料進行合併。請考量以下情況。

假設您是負責處理學生資料的分析師,正在著手貴校以及另一所學校之間的資料共用計畫,讓兩校都能改善各自的課表。在您將要探索的部分問題中,較好的做法是同時查看兩所學校的所有記錄,而不是分別查看每所學校的記錄。

您可以將另一所學校的資料匯入您資料庫的新資料表中,不過這樣一來,該校資料的任何變更都無法反映在您的資料庫中。較好的解決方案是連結另一所學校的資料表,然後建立能夠在您執行時合併這些資料的查詢。這樣您就能在單一操作中進行資料分析,而不需要執行兩項分析,然後又試著在解釋時將它們視為同一個。

若要檢視來自兩個相似資料表中的所有記錄,請使用聯集查詢。

聯集查詢無法顯示在 [設計] 檢視中,請使用進入 SQL 檢視物件索引標籤所用的 SQL 命令來進行建立。

使用兩張資料表建立聯集查詢

  1. 在 [建立] 索引標籤上,按一下 [查詢] 群組中的 [查詢設計]。請注意,如果您使用的是 Access 2007,請在 [建立] 索引標籤的 [其他] 群組中,按一下 [查詢設計]。

    隨即會開啟新的查詢設計格線,並顯示 [顯示資料表] 對話方塊。

  2. 在 [顯示資料表] 對話方塊中,按一下 [關閉]。

  3. 在 [設計] 索引標籤的 [查詢類型] 群組中,按一下 [聯集]。

    查詢會隨即從 [設計檢視] 切換到 [SQL 檢視]。此時,[SQL 檢視] 物件索引標籤會是空的。

  4. 在 [SQL 檢視] 中輸入 SELECT,後面接著您要在查詢中使用之第一個資料表的欄位清單。請將各欄位名稱以方括號括住,並以逗號分開。輸入完欄位名稱後,請按 ENTER。游標會移至 [SQL 檢視] 的下一行。

  5. 輸入 FROM,後面接著您要在查詢中使用的第一個資料表名稱。按 ENTER。

  6. 如果您想要為第一張資料表中的欄位指定準則,請輸入 WHERE,後面接著欄位名稱、一個比較運算子 (通常是等號 (=)) 以及準則。您可以使用 AND 關鍵字以及與第一個準則相同的語法,來在 WHERE 子句的結尾新增其他準則;例如,WHERE [課程等級]="100" AND [學分]>2。指定完準則後,請按 ENTER。

  7. 輸入 UNION,然後按下 ENTER。

  8. 輸入 SELECT,後面接著您要在查詢中使用之第二個資料表的欄位清單。請以同樣的順序納入此資料表中來自第一個資料表的相同欄位。請將各欄位名稱以方括號括住,並以逗號分開。輸入完欄位名稱後,請按 ENTER。

  9. 輸入 FROM,後面接著您要納入查詢中的第二個資料表名稱。按 ENTER。

  10. 如果您想要的話,可以依照本程序步驟 6 中所述,新增一個 WHERE 子句。

  11. 輸入一個分號 (;) 來表示查詢的結尾。

  12. 在 [設計] 索引標籤上的 [結果] 群組中,按一下 [執行]。

    您的結果會出現在 [資料工作表] 檢視中。

頁面頂端

請參閱

連接資料表與查詢

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×