Đôi khi, bạn có thể muốn kết hợp các bản ghi từ một bảng hoặc truy vấn với các bản ghi từ một hoặc nhiều bảng khác vào một kết quả duy nhất. Đó là chức năng của truy vấn hợp trong Access.
Để hiểu truy vấn hợp một cách hiệu quả, trước tiên bạn sẽ làm quen với việc thiết kế truy vấn chọn cơ bản trong Access. Để tìm hiểu thêm thông tin về thiết kế truy vấn chọn, hãy xem mục Tạo truy vấn chọn đơn giản.
Nghiên cứu ví dụ truy vấn hợp hoạt động
Nếu bạn chưa bao giờ tạo truy vấn hợp trước đây, việc nghiên cứu một ví dụ hoạt động trong mẫu Northwind Access trước tiên có thể hữu ích. Bạn có thể tìm kiếm mẫu mẫu Northwind trên trang bắt đầu của Access bằng cách chọn Tệp>Mới. Bạn cũng có thể tải xuống bản sao trực tiếp từ mẫu mẫu Northwind.
Sau khi Access mở cơ sở dữ liệu Northwind, hãy bỏ qua hộp thoại đăng nhập xuất hiện đầu tiên, rồi bung rộng Ngăn Dẫn hướng. Chọn phần trên cùng của Ngăn Dẫn hướng, rồi chọn Loại Đối tượng để sắp xếp tất cả các đối tượng cơ sở dữ liệu theo loại. Tiếp theo, bung rộng nhóm Truy vấn và bạn sẽ thấy một truy vấn có tên là Giao dịch Sản phẩm.
Truy vấn hợp rất dễ phân biệt với các đối tượng truy vấn khác vì có biểu tượng đặc biệt giống như hai vòng tròn cuộn vào nhau đại diện cho một bộ liên kết từ hai bộ:
Không giống như truy vấn chọn và thực hiện thông thường, bảng không liên quan trong truy vấn hợp. Điều đó có nghĩa là bạn không thể sử dụng trình thiết kế truy vấn đồ họa Access để xây dựng hoặc chỉnh sửa truy vấn hợp. Nếu bạn mở truy vấn hợp từ Ngăn Dẫn hướng, Access sẽ mở truy vấn và hiển thị kết quả ở dạng xem biểu dữ liệu. Bên dưới Dạng xem trên tab Trang đầu, hãy lưu ý rằng Dạng xem Thiết kế không sẵn dùng khi bạn làm việc với truy vấn hợp. Bạn chỉ có thể chuyển đổi giữa Dạng xem biểu dữ liệu và Dạng xem SQL.
Để tiếp tục nghiên cứu về ví dụ truy vấn hợp này, hãy bấmdạng >xem> Trang đầu Dạng xemSQL để xem SQL cú pháp xác định truy vấn. Trong hình minh họa này, chúng SQL tôi đã thêm một số giãn cách bổ sung vào để bạn có thể dễ dàng xem các phần khác nhau tạo nên truy vấn hợp.
Chúng ta hãy xem chi tiết cú SQL pháp của truy vấn hợp này từ cơ sở dữ liệu Northwind:
SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]
UNION
SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
Phần đầu tiên và phần thứ ba của câu lệnh SQL này cơ bản là hai truy vấn chọn. Các truy vấn này truy xuất hai bộ bản ghi khác nhau; một từ bảng Đơn hàng sản phẩm và một từ bảng Mua sản phẩm.
Phần thứ hai của câu lệnh SQL này là từ UNION khóa, yêu cầu Access kết hợp hai bộ bản ghi này.
Phần cuối cùng của điều khoản này SQL xác định thứ tự của các bản ghi kết hợp bằng cách sử dụng một câu ORDER BY lệnh. Trong ví dụ này, Access sẽ sắp xếp mọi bản ghi theo trường Ngày Đặt hàng theo thứ tự giảm dần.
Lưu ý
Truy vấn hợp luôn ở trạng thái chỉ đọc trong Access; bạn không thể thay đổi bất kỳ giá trị nào ở dạng xem biểu dữ liệu.
Tạo một truy vấn hợp bằng cách tạo và kết hợp các truy vấn chọn
Ngay cả khi bạn có thể tạo SQL truy vấn hợp bằng cách viết cú pháp trực tiếp trong Dạng xem SQL, bạn vẫn có thể thấy dễ dàng hơn khi xây dựng truy vấn hợp thành các phần với truy vấn chọn. Sau đó, bạn có thể sao chép và dán các phần SQL vào một truy vấn hợp đã kết hợp.
Nếu bạn muốn bỏ qua đọc các bước và thay vào đó là xem ví dụ, hãy xem mục tiếp theo, Xem ví dụ về cách dựng truy vấn hợp.
- Trên tab Tạo, trong nhóm Truy vấn, bấm Thiết kế Truy vấn.
- Bấm đúp vào bảng có các trường bạn muốn đưa vào. Thêm bảng vào cửa sổ thiết kế truy vấn.
- Trong cửa sổ thiết kế truy vấn, hãy bấm đúp vào mỗi trường mà bạn muốn bao gồm. Khi bạn chọn trường, hãy đảm bảo rằng bạn thêm cùng số trường, trong cùng một thứ tự mà bạn thêm vào truy vấn chọn khác. Hết sức chú ý đến kiểu dữ liệu của trường và đảm bảo rằng chúng có kiểu dữ liệu tương thích với trường trong cùng một vị trí, trong truy vấn khác mà bạn đang kết hợp. Ví dụ: Nếu truy vấn chọn đầu tiên của bạn có năm trường, trường đầu tiên chứa dữ liệu ngày/thời gian, hãy đảm bảo rằng mỗi truy vấn chọn khác mà bạn đang kết hợp cũng có năm trường, trường đầu tiên chứa dữ liệu ngày/thời gian v.v.
- Bạn có thể tùy ý thêm tiêu chí vào các trường của mình bằng cách nhập biểu thức phù hợp vào hàng Tiêu chí của lưới trường.
- Sau khi đã hoàn tất việc thêm trường và tiêu chí trường, bạn nên chạy truy vấn chọn và xem lại đầu ra của truy vấn đó. Trên tab Thiết kế, trong nhóm Kết quả, bấm vào Chạy.
- Chuyển truy vấn sang dạng xem Thiết kế.
- Lưu truy vấn chọn và để truy vấn mở.
- Lặp lại thủ tục này cho mỗi truy vấn chọn mà bạn muốn kết hợp.
Giờ đây, bạn đã tạo các truy vấn chọn của mình, đã đến lúc kết hợp các truy vấn đó. Trong bước này, bạn tạo truy vấn hợp bằng cách sao chép và dán các SQL câu lệnh.
- Trên tab Tạo, trong nhóm Truy vấn, bấm Thiết kế Truy vấn.
- Trên tab Thiết kế, trong nhóm Truy vấn, bấm vào Kết hợp. Access ẩn cửa sổ thiết kế truy vấn và hiển thị tab đối tượng Dạng xem SQL . Tại thời điểm này, tab là trống.
- Hãy bấm vào tab cho truy vấn chọn đầu tiên mà bạn muốn kết hợp trong truy vấn hợp.
- Trên tab Trang đầu , hãy bấm Dạng xem>SQL.
- Sao chép câu
SQLlệnh cho truy vấn chọn. Bấm vào tab dành cho truy vấn hợp bạn đã bắt đầu tạo trước đó. - Dán câu
SQLlệnh cho truy vấn chọn vào tab đối tượng Dạng xem SQL của truy vấn hợp. - Xóa dấu chấm phẩy (
;) ở cuối câu lệnh truy vấn chọnSQL. - Nhấn Enter để di chuyển con trỏ xuống một dòng, rồi
UNIONnhập trên dòng mới. - Hãy bấm vào tab cho truy vấn chọn tiếp theo mà bạn muốn kết hợp trong truy vấn hợp.
- Lặp lại các bước từ 5 đến 10
SQLcho đến khi bạn đã sao chép và dán tất cả các câu lệnh cho truy vấn chọn vào cửa sổ SQL View của truy vấn hợp. Không xóa bỏ dấu chấm phẩy hoặc nhập bất kỳ nội dung nào theo sau câuSQLlệnh cho truy vấn chọn cuối cùng. - Trên tab Thiết kế, trong nhóm Kết quả, bấm Chạy.
Kết quả của truy vấn hợp sẽ xuất hiện ở Dạng xem biểu dữ liệu.
Xem ví dụ về cách dựng truy vấn hợp
Đây là ví dụ bạn có thể tạo lại trong cơ sở dữ liệu mẫu Northwind. Truy vấn hợp này thu thập tên người từ bảng Khách hàng và kết hợp với tên người từ bảng Nhà cung cấp. Nếu bạn muốn làm theo, hãy thực hiện các bước này trong bản sao cơ sở dữ liệu mẫu Northwind của mình.
Đây là những bước cần thiết để xây dựng ví dụ này:
Tạo hai truy vấn chọn có tên là Truy vấn_1 và Truy vấn_2 với các bảng Khách hàng và Nhà cung cấp tương ứng theo thứ tự làm nguồn dữ liệu. Sử dụng trường Tên và Họ làm giá trị hiển thị.
Tạo một truy vấn mới có tên là Truy vấn_3 không có nguồn dữ liệu ban đầu, rồi bấm vào lệnh Kết hợp trên tab Thiết kế để đưa truy vấn này vào Truy vấn hợp.
Sao chép và dán các câu lệnh SQL từ Truy vấn_1 và Truy vấn_2 vào Truy vấn_3. Hãy đảm bảo loại bỏ dấu chấm phẩy thừa và thêm từ
UNIONkhóa. Sau đó, bạn có thể kiểm tra kết quả của mình ở dạng xem biểu dữ liệu.Thêm mệnh đề thứ tự vào một trong các truy vấn, rồi dán câu lệnh
ORDER BYvào truy vấn hợp trong Dạng xem SQL. Lưu ý rằng trong Truy vấn_3, truy vấn hợp, khi xếp thứ tự được gắn thêm, trước tiên, các dấu chấm phẩy, sau đó là tên bảng sẽ bị loại bỏ khỏi tên trường.Sau đây là
SQLphần cuối cùng kết hợp và sắp xếp tên cho ví dụ truy vấn hợp này:SELECT Customers.Company, Customers.[Last Name], Customers.[First Name] FROM Customers UNION SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name] FROM Suppliers ORDER BY [Last Name], [First Name];
Nếu bạn viết cú pháp rất thoải mái SQL , bạn có thể viết câu lệnh của SQL riêng mình cho truy vấn hợp trực tiếp trong Dạng xem SQL. Tuy nhiên, bạn có thể thấy hữu ích khi làm theo phương pháp tiếp cận về sao chép và dán SQL từ các đối tượng truy vấn khác. Từng truy vấn có thể phức tạp hơn nhiều so với các ví dụ truy vấn chọn đơn giản được sử dụng tại đây. Đây có thể là lợi thế của bạn khi tạo và kiểm tra từng truy vấn một cách cẩn thận trước khi kết hợp các truy vấn trong truy vấn hợp. Nếu truy vấn hợp không chạy, bạn có thể điều chỉnh từng truy vấn riêng lẻ cho đến khi thành công, rồi xây dựng lại truy vấn hợp của mình bằng cú pháp đúng.
Xem lại các mục còn lại của bài viết này để tìm hiểu thêm các mẹo và bí kíp về cách sử dụng truy vấn hợp.
Kết hợp ba hoặc nhiều bảng hoặc truy vấn trong một truy vấn hợp
Trong ví dụ từ mục trước sử dụng cơ sở dữ liệu Northwind, dữ liệu từ hai bảng sẽ được kết hợp. Tuy nhiên, bạn có thể kết hợp ba hoặc nhiều bảng rất dễ dàng trong một truy vấn hợp. Ví dụ: dựng trên ví dụ trước, bạn có thể cũng muốn kèm theo tên của nhân viên trong đầu ra truy vấn. Bạn có thể thực hiện tác vụ đó bằng cách thêm một truy vấn thứ ba và kết hợp với câu lệnh SQL trước đó với từ khóa UNION bổ sung như thế này:
SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers
UNION
SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers
UNION
SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees
ORDER BY [Last Name], [First Name];
Khi bạn xem kết quả ở dạng xem biểu dữ liệu, tất cả nhân viên sẽ được liệt kê cùng với tên công ty mẫu, vốn có thể không rất hữu ích. Nếu bạn muốn trường đó hiển thị một người là nhân viên nội bộ, từ nhà cung cấp hoặc từ khách hàng, bạn có thể đưa vào giá trị cố định thay vì tên công ty. Dưới đây là giao diện SQL :
SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
UNION
SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers
UNION
SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees
ORDER BY [Last Name], [First Name];
Đây là giao diện kết quả sẽ xuất hiện ở dạng xem biểu dữ liệu. Access sẽ hiển thị năm bản ghi ví dụ này:
| Công việc | Họ | Tên |
|---|---|---|
| Nội bộ | Freehafer | Nancy |
| Nội bộ | Giussani | Laura |
| Nhà cung cấp | Glasson | Stuart |
| Khách hàng | Goldschmidt | Daniel |
| Khách hàng | Gratacos Solsona | Antonio |
Bạn có thể giảm truy vấn thêm nữa vì Access chỉ đọc tên của các trường đầu ra từ truy vấn đầu tiên trong truy vấn hợp. Ở đây, đầu ra từ các mục truy vấn thứ hai và thứ ba bị loại bỏ:
SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers
UNION
SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
UNION
SELECT "In-house", [Last Name], [First Name]
FROM Employees
ORDER BY [Last Name], [First Name];
Lọc trong truy vấn hợp
Trong truy vấn hợp Access, chỉ cho phép xếp thứ tự một lần nhưng bạn có thể lọc từng truy vấn riêng lẻ. Xây dựng trên truy vấn hợp của mục trước đó, đây là ví dụ lọc từng truy vấn bằng cách thêm một mệnh WHERE đề.
SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"
UNION
SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"
UNION
SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"
ORDER BY [Last Name], [First Name];
Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả có dạng như sau:
| Công việc | Họ | Tên |
|---|---|---|
| Nhà cung cấp | Andersen | Elizabeth A. |
| Nội bộ | Freehafer | Nancy |
| Khách hàng | Hasselberg | Jonas |
| Nội bộ | Hellung-Larsen | Anne |
| Nhà cung cấp | Hernandez-Echevarria | Amaya |
| Khách hàng | Mortensen | Sven |
| Nhà cung cấp | Sandberg | Mikael |
| Nhà cung cấp | Sousa | Luis |
| Nội bộ | Thorpe | Steven |
| Nhà cung cấp | Weiler | Cornelia |
| Nội bộ | Zare | Robert |
Loại dữ liệu hỗn hợp
Nếu các truy vấn bạn kết hợp rất khác nhau, bạn có thể gặp phải tình huống trường đầu ra phải kết hợp dữ liệu của các kiểu dữ liệu khác nhau. Nếu vậy, truy vấn hợp hầu như thường sẽ trả về kết quả dưới dạng loại dữ liệu văn bản do loại dữ liệu có thể lưu giữ cả văn bản và số.
Để tìm hiểu cách truy vấn này hoạt động, chúng ta sẽ sử dụng truy vấn hợp Giao dịch sản phẩm trong cơ sở dữ liệu mẫu Northwind. Mở cơ sở dữ liệu mẫu đó, rồi mở truy vấn Giao dịch sản phẩm ở dạng xem biểu dữ liệu. Mười bản ghi cuối cùng phải tương tự như đầu ra này:
| ID Sản phẩm | Ngày đặt hàng | Tên công ty | Giao dịch | Số lượng |
|---|---|---|---|---|
| 77 | 22/01/2006 | Nhà cung cấp B | Mua | 60 |
| 80 | 22/01/2006 | Nhà cung cấp D | Mua | 75 |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 125 |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 200 |
| 7 | 20/01/2006 | Công ty D | Bán | 10 |
| 51 | 20/01/2006 | Công ty D | Bán | 10 |
| 80 | 20/01/2006 | Công ty D | Bán | 10 |
| 34 | 15/01/2006 | Công ty AA | Bán | 100 |
| 80 | 15/01/2006 | Công ty AA | Bán | 30 |
Hãy giả định rằng bạn muốn tách trường Số lượng thành hai trường: Mua và Bán. Cũng hãy giả định rằng bạn muốn có giá trị số không cố định cho trường không có giá trị. Đây là diện mạo của SQL truy vấn hợp này:
SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]
UNION
SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
Nếu bạn chuyển đổi sang dạng xem biểu dữ liệu, bạn sẽ thấy mười bản ghi cuối cùng giờ đây được hiển thị giống như sau:
| ID Sản phẩm | Ngày đặt hàng | Tên công ty | Giao dịch | Mua | Bán |
|---|---|---|---|---|---|
| 74 | 22/01/2006 | Nhà cung cấp B | Mua | 20 | 0 |
| 77 | 22/01/2006 | Nhà cung cấp B | Mua | 60 | 0 |
| 80 | 22/01/2006 | Nhà cung cấp D | Mua | 75 | 0 |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 125 | 0 |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 200 | 0 |
| 7 | 20/01/2006 | Công ty D | Bán | 0 | 10 |
| 51 | 20/01/2006 | Công ty D | Bán | 0 | 10 |
| 80 | 20/01/2006 | Công ty D | Bán | 0 | 10 |
| 34 | 15/01/2006 | Công ty AA | Bán | 0 | 100 |
| 80 | 15/01/2006 | Công ty AA | Bán | 0 | 30 |
Tiếp tục ví dụ này, điều gì sẽ xảy ra nếu bạn muốn các trường có giá trị không trống? Bạn có thể sửa đổi để không SQL hiển thị gì thay vì bằng không bằng cách thêm từ khóa Null , như minh họa ở đây:
SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]
UNION
SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
Tuy nhiên, khi bạn có thể quan sát việc chuyển đổi sang dạng xem biểu dữ liệu, lúc này bạn đã có một kết quả không mong muốn. Trong cột Mua, mỗi trường đều bị xóa:
| ID Sản phẩm | Ngày đặt hàng | Tên công ty | Giao dịch | Mua | Bán |
|---|---|---|---|---|---|
| 74 | 22/01/2006 | Nhà cung cấp B | Mua | ||
| 77 | 22/01/2006 | Nhà cung cấp B | Mua | ||
| 80 | 22/01/2006 | Nhà cung cấp D | Mua | ||
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | ||
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | ||
| 7 | 20/01/2006 | Công ty D | Bán | 10 | |
| 51 | 20/01/2006 | Công ty D | Bán | 10 | |
| 80 | 20/01/2006 | Công ty D | Bán | 10 | |
| 34 | 15/01/2006 | Công ty AA | Bán | 100 | |
| 80 | 15/01/2006 | Công ty AA | Bán | 30 |
Lý do mà điều này xảy ra là vì Access xác định loại dữ liệu của các trường từ truy vấn đầu tiên. Trong ví dụ này, Null không phải là một số.
Vì vậy, điều gì sẽ xảy ra nếu bạn tìm cách chèn một chuỗi trống cho giá trị trống của các trường? Đối với SQL nỗ lực này có thể trông như thế này:
SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]
UNION
SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
Khi bạn chuyển đổi sang dạng xem biểu dữ liệu, bạn sẽ thấy rằng Access truy xuất các giá trị Mua nhưng đã chuyển đổi các giá trị này thành văn bản. Bạn có thể biết đây là giá trị văn bản vì được căn trái ở dạng xem biểu dữ liệu. Chuỗi trống trong truy vấn đầu tiên không phải là số là vì bạn thấy các kết quả này. Bạn cũng sẽ nhận thấy rằng các giá trị Bán cũng được chuyển đổi thành văn bản vì các bản ghi mua có chứa chuỗi trống.
| ID Sản phẩm | Ngày đặt hàng | Tên công ty | Giao dịch | Mua | Bán |
|---|---|---|---|---|---|
| 74 | 22/01/2006 | Nhà cung cấp B | Mua | 20 | |
| 77 | 22/01/2006 | Nhà cung cấp B | Mua | 60 | |
| 80 | 22/01/2006 | Nhà cung cấp D | Mua | 75 | |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 125 | |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 200 | |
| 7 | 20/01/2006 | Công ty D | Bán | 10 | |
| 51 | 20/01/2006 | Công ty D | Bán | 10 | |
| 80 | 20/01/2006 | Công ty D | Bán | 10 | |
| 34 | 15/01/2006 | Công ty AA | Bán | 100 | |
| 80 | 15/01/2006 | Công ty AA | Bán | 30 |
Vật thì bạn giải quyết vấn đề này như thế nào?
Một giải pháp là buộc truy vấn phải có giá trị trường là một số. Bạn có thể thực hiện điều đó với biểu thức này:
IIf(False, 0, Null)
Điều kiện để kiểm tra, False, không bao giờ là True, vì vậy biểu thức luôn trả về Null. Tuy nhiên, Access vẫn đánh giá cả hai tùy chọn đầu ra và coi đầu ra là số hoặc Null.
Đây là cách chúng ta có thể sử dụng biểu thức này trong ví dụ hoạt động của mình:
SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]
UNION
SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
Bạn không cần sửa đổi truy vấn thứ hai.
Nếu bạn chuyển đổi sang dạng xem biểu dữ liệu, lúc này bạn sẽ thấy kết quả mà chúng ta mong muốn:
| ID Sản phẩm | Ngày đặt hàng | Tên công ty | Giao dịch | Mua | Bán |
|---|---|---|---|---|---|
| 74 | 22/01/2006 | Nhà cung cấp B | Mua | 20 | |
| 77 | 22/01/2006 | Nhà cung cấp B | Mua | 60 | |
| 80 | 22/01/2006 | Nhà cung cấp D | Mua | 75 | |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 125 | |
| 81 | 22/01/2006 | Nhà cung cấp A | Mua | 200 | |
| 7 | 20/01/2006 | Công ty D | Bán | 10 | |
| 51 | 20/01/2006 | Công ty D | Bán | 10 | |
| 80 | 20/01/2006 | Công ty D | Bán | 10 | |
| 34 | 15/01/2006 | Công ty AA | Bán | 100 | |
| 80 | 15/01/2006 | Công ty AA | Bán | 30 |
Phương pháp thay thế để đạt được cùng một kết quả là thêm các truy vấn trong truy vấn hợp với một truy vấn khác:
SELECT
0 As [Product ID], Date() As [Order Date],
"" As [Company Name], "" As [Transaction],
0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False
Đối với từng trường, Access trả về các giá trị cố định của loại dữ liệu bạn xác định. Tất nhiên, bạn không muốn đầu ra của truy vấn này gây cản trở đến kết quả, do đó bí kíp để tránh điều này là thêm vào mệnh đề WHERE thành False:
WHERE False
Chỉ là một trò nhỏ. Vì điều kiện luôn là sai nên truy vấn không trả về bất kỳ giá trị nào. Kết hợp câu lệnh này với SQL hiện có và chúng ta có được câu lệnh được hoàn thành như sau:
SELECT
0 As [Product ID], Date() As [Order Date],
"" As [Company Name], "" As [Transaction],
0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False
UNION
SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]
UNION
SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
Lưu ý
Trong ví dụ này, truy vấn kết hợp trong cơ sở dữ liệu Northwind trả về 100 bản ghi, trong khi hai truy vấn riêng lẻ trả về 58 và 43 bản ghi cho tổng cộng 101 bản ghi. Sự khác biệt này xảy ra vì hai bản ghi không phải là duy nhất. Hãy xem Làm việc với bản ghi riêng biệt trong truy vấn hợp bằng union ALL để tìm hiểu cách giải quyết kịch bản này bằng cách sử dụng UNION ALL.
Thêm tổng vào truy vấn hợp
Một cách sử dụng đặc biệt cho truy vấn hợp là kết hợp một tập hợp các bản ghi với một bản ghi có chứa tổng của một hoặc nhiều trường.
Đây là một ví dụ khác mà bạn có thể tạo trong cơ sở dữ liệu mẫu Northwind để minh họa cách nhận tổng trong truy vấn hợp.
Tạo một truy vấn đơn giản mới để xem phần mua bia (ID Sản phẩm=34 trong cơ sở dữ liệu Northwind) bằng cú pháp SQL sau đây:
SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity FROM [Purchase Order Details] WHERE ((([Purchase Order Details].[Product ID])=34)) ORDER BY [Purchase Order Details].[Date Received];Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy bốn sản phẩm mua:
Ngày nhận Số lượng 22/01/2006 100 22/01/2006 60 04/04/2006 50 05/04/2006 300 Để có được tổng, tạo một truy vấn tổng hợp đơn giản bằng SQL sau đây:
SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity FROM [Purchase Order Details] WHERE ((([Purchase Order Details].[Product ID])=34))Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ chỉ thấy một bản ghi:
Ngày nhận_tối_đa Tổng_số_lượng 05/04/2006 510 Kết hợp hai truy vấn này vào một truy vấn hợp để chắp thêm bản ghi có số lượng tổng vào các bản ghi mua:
SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity FROM [Purchase Order Details] WHERE ((([Purchase Order Details].[Product ID])=34)) UNION SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity FROM [Purchase Order Details] WHERE ((([Purchase Order Details].[Product ID])=34)) ORDER BY [Purchase Order Details].[Date Received];Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy bốn sản phẩm mua có tổng của từng sản phẩm kèm theo là một bản ghi tính tổng số lượng:
Ngày nhận Số lượng 22/01/2006 60 22/01/2006 100 04/04/2006 50 05/04/2006 300 05/04/2006 510
Bài viết đó đề cập đến những thông tin cơ bản về cách thêm tổng vào một truy vấn hợp. Bạn cũng có thể muốn bao gồm các giá trị cố định trong cả hai truy vấn như "Chi tiết" và "Tổng" để phân tách trực quan bản ghi tổng từ các bản ghi khác. Bạn có thể xem lại các giá trị cố định đang sử dụng trong mục Kết hợp ba hoặc nhiều bảng hoặc truy vấn trong truy vấn hợp.
Làm việc với các bản ghi riêng biệt trong truy vấn hợp bằng UNION ALL
Các truy vấn hợp trong Access theo mặc định chỉ bao gồm các bản ghi riêng biệt. Nhưng điều gì sẽ xảy ra nếu bạn muốn bao gồm tất cả bản ghi? Một ví dụ khác có thể hữu ích ở đây.
Trong mục trước đó, chúng tôi đã chỉ cho bạn cách tạo tổng trong truy vấn hợp. Sửa đổi truy vấn hợp đó SQL để bao gồm Product ID = 48:
SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))
UNION
SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))
ORDER BY [Purchase Order Details].[Date Received];
Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả hơi sai khác một chút:
| Ngày nhận | Số lượng |
|---|---|
| 22/01/2006 | 100 |
| 22/01/2006 | 200 |
Tất nhiên, một bản ghi không trả về hai lần tổng số lượng.
Bạn thấy kết quả này vì, trong một ngày, cùng một số lượng sôcôla đã được bán hai lần, như được ghi lại trong bảng Chi tiết đơn đặt hàng. Đây là kết quả truy vấn chọn đơn giản hiển thị cả hai bản ghi trong cơ sở dữ liệu mẫu Northwind:
| ID Đơn đặt hàng | Product | Quantity |
|---|---|---|
| 100 | Northwind Traders Chocolate | 100 |
| 92 | Northwind Traders Chocolate | 100 |
Trong truy vấn hợp đã lưu ý trước đó, bạn có thể thấy rằng trường ID Đơn hàng không được bao gồm và hai trường không tạo thành hai bản ghi riêng biệt.
Nếu bạn muốn bao gồm tất cả các bản ghi, hãy sử UNION ALL dụng thay vì UNION trong .SQL Điều này rất có thể sẽ ảnh hưởng đến việc sắp xếp kết quả, vì vậy bạn cũng có thể muốn đưa vào một mệnh ORDER BY đề để xác định thứ tự sắp xếp. Dưới đây là sửa đổi dựa SQL trên ví dụ trước đó:
SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))
UNION ALL
SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))
ORDER BY [Total];
Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy tất cả các chi tiết ngoài tổng dưới dạng bản ghi cuối cùng:
| Ngày nhận | Tổng | Số lượng |
|---|---|---|
| 22/01/2006 | 100 | |
| 22/01/2006 | 100 | |
| 22/01/2006 | Tổng cộng | 200 |
Sử dụng truy vấn hợp để lọc các bản ghi trên biểu mẫu thông qua điều khiển hộp tổ hợp
Mức sử dụng phổ biến về truy vấn hợp đóng vai trò là nguồn bản ghi cho điều khiển hộp tổ hợp trên biểu mẫu. Bạn có thể sử dụng hộp tổ hợp đó để chọn một giá trị cần lọc các bản ghi của biểu mẫu. Ví dụ: lọc bản ghi nhân viên theo thành phố.
Để xem cách hoạt động, đây là một ví dụ khác mà bạn có thể tạo trong cơ sở dữ liệu mẫu Northwind để minh họa kịch bản này.
Tạo truy vấn chọn đơn giản bằng cú pháp
SQLsau:SELECT Employees.City, Employees.City AS Filter FROM Employees;Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả như sau:
Thành phố Bộ lọc Seattle Seattle Bellevue Bellevue Redmond Redmond Kirkland Kirkland Seattle Seattle Redmond Redmond Seattle Seattle Redmond Redmond Seattle Seattle Đang xem các kết quả mà bạn có thể không thấy nhiều giá trị. Bung rộng truy vấn và biến truy vấn thành truy vấn hợp bằng cách sử dụng như sau
SQL:SELECT Employees.City, Employees.City AS Filter FROM Employees UNION SELECT "<All>", "*" AS Filter FROM Employees ORDER BY City;Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả như sau:
Thành phố Bộ lọc <Tất cả> * Bellevue Bellevue Kirkland Kirkland Redmond Redmond Seattle Seattle Access thực hiện kết hợp chín bản ghi, được hiển thị trước đó, với các giá trị trường cố định là <Tất> cả và "*". Vì mệnh đề hợp này không chứa , Access chỉ
UNION ALLtrả về các bản ghi riêng biệt. Điều đó có nghĩa là mỗi thành phố chỉ được trả về một lần với các giá trị cố định giống hệt nhau.Lúc này, bạn có một truy vấn hợp đã hoàn thành hiển thị mỗi tên thành phố chỉ một lần, cùng với một tùy chọn sẽ chọn tất cả các thành phố một cách hiệu quả, bạn có thể sử dụng truy vấn này làm nguồn bản ghi cho hộp tổ hợp trên biểu mẫu. Sử dụng ví dụ cụ thể này làm mô hình, bạn có thể tạo điều khiển hộp tổ hợp trên biểu mẫu, đặt truy vấn này làm nguồn bản ghi, đặt thuộc tính Column Width của Cột bộ lọc thành 0 (số không) để ẩn một cách trực quan, rồi đặt thuộc tính Bound Column thành 1 để chỉ báo các chỉ mục của cột thứ hai. Trong thuộc tính
Filtercủa chính biểu mẫu, sau đó bạn có thể thêm mã như sau để kích hoạt bộ lọc biểu mẫu bằng cách sử dụng giá trị được chọn trong điều khiển hộp tổ hợp:Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'" Me.FilterOn = TrueSau đó, người dùng biểu mẫu có thể lọc các bản ghi biểu mẫu thành một <> tên thành phố cụ thể hoặc chọn Tất cả để liệt kê tất cả các bản ghi cho tất cả các thành phố.