James Perry, Richard Newmark
Chương 4
Tạo và dùng câu truy vấn
Nhóm và tổng kết dữ liệu
Đến nay, mọi truy vấn đều phát sinh thông tin cho các bản ghi đơn lẻ và có chọn lọc. Nếu bảng hóa đơn có 500 dòng, thì câu truy vấn trên bảng đó sẽ hiển thị tập động dynaset gồm 500 dòng nếu không dùng tiêu chí lọc. Thường thì thông tin tổng kết sẽ quan trọng để ra quyết định quản lý. Thông tin tổng kết có thể hé lộ những tình huống không hiển nhiên nếu xét dữ liệu chi tiết. Các ví dụ về truy vấn tổng kết bao gồm
⦁ Đếm số hóa đơn quá hạn hơn 60 ngày.
⦁ Hiển thị tổng trị giá hóa đơn của từng khách hàng.
⦁ Tính tổng doanh thu theo tên sản phẩm.
⦁ Tính tổng trị giá đặt hàng theo khách hàng ở một hay nhiều bang.
⦁ Hiển thị khách hàng có tổng trị giá đặt hàng lớn nhất thuộc một chu kỳ thời gian đã cho.
Khả năng tính tổng, trung bình, đếm, … được gọi là gộp thông tin, và một dạng truy vấn đặc biệt của Access gọi là truy vấn tổng (totals query), cung cấp khả năng nhóm và tổng kết thông tin. Access cung cấp một số thao tác gộp và lọc thông tin trên nhóm dữ liệu hay trên toàn bảng. Hình 4.24 liệt kê tất cả các thao tác gộp kèm theo mô tả vắn tắt của từng thao tác. Trong số các thao tác đã liệt kê, hữu ích nhất với kế toán viên là các hàm trung bình Avg, đếm Count, giá trị lớn nhất Max, giá trị nhỏ nhất Min, và tổng Sum.
Hình 4.24 Các thao tác gộp.
Đôi lúc bạn muốn thống kê trên tất cả các dòng trong bảng hay trên tập hợp bảng có quan hệ với nhau. Tuy nhiên, đôi khi bạn cần thống kê trên những nhóm bản ghi nhỏ hơn. Chẳng hạn, có thể hữu ích nếu biết tổng cân nặng của từng loại trà và cà phê đã đặt mua trong từng tháng – được sắp từ cao xuống thấp. Thông tin này hé lộ các loại được ưa chuộng. Hay bộ phận công nợ có thể quan tâm đến thống kê chẳng hạn số ngày trung bình, theo từng khách hàng, giữa thời điểm gửi hóa đơn và thời điểm nhận được chi trả. Khi Access tổng kết thông tin trên một số tập hợp dòng, tính toán đó cần gom nhóm. Thông tin gom nhóm (grouping) chẳng qua là việc tạo các nhóm dòng có cùng đặc trưng nào đó chẳng hạn cùng tên khách hàng, mã khách hàng, mã hóa đơn, hay thuộc tính lưu trong cột bảng.
Hàm tổng kết thường được dùng trong các truy vấn (nhưng cũng có thể được dùng trong forms và báo cáo). Trước khi có thể làm việc với hàm gộp trong câu truy vấn, bạn phải thêm dòng Total vào lưới QBE (góc nhìn Design). Dòng Total xuất hiện khi bạn kích nút Totals trong nhóm Show/Hide thuộc tab ngữ cảnh DESIGN. Nhắc lại rằng tab ngữ cảnh DESIGN tự động xuất hiện khi bạn hiển thị câu truy vấn ở góc nhìn Design. Các ô thuộc dòng Total sẽ chứa danh sách các thao tác gộp được trình bày ở Hình 1.24.
Sau khi bạn thêm dòng Total vào lưới QBE ở góc nhìn Design, bạn có thể tính trung bình của một trường số trong câu truy vấn. Khi dòng xuất hiện, bạn gõ Avg vào dòng Total bên dưới trường mà bạn muốn tính trung bình, rồi thi hành câu truy vấn. Bạn có thể tính nhiều giá trị thống kê trên một trường cụ thể miễn là bạn thêm nhiều bản sao tên trường vào dòng Field trên lưới QBE.
Trong bài tập kế, bạn sẽ thử nghiệm một hàm tổng kết và học cách kết hợp hai trường ký tự thành một. Bạn sẽ nối ba bảng để có thể tính tổng doanh thu nhóm theo tên nhân viên bán hàng. Ba bảng xuất hiện trong câu truy vấn là nhân viên tblEmployee, hóa đơn tblInvoice, và dòng hóa đơn tblInvoiceLine. Các trường cần tổng kết doanh thu theo từng nhân viên chỉ ở hai bảng – tblEmployee và tblInvoiceLine. Tuy nhiên, quan hệ giữa tblEmployee và tblInvoiceLine là nhiều-nhiều. Vì Access không thể liên kết trực tiếp các bảng nhiều-nhiều, tblInvoice là bảng “bắc cầu” giữa tblEmployee và tblInvoiceLine. Nó tái cấu trúc quan hệ M-M thành hai quan hệ 1-M.
Bất kỳ khi nào muốn kết hợp nhiều trường văn bản từ một bảng vào một trường truy vấn duy nhất, bạn chỉ việc viết biểu thức chứa các trường đó cách nhau bằng dấu “&” (không có cặp ngoặc kép). Giả sử bạn muốn tạo danh bạ nhân viên bằng cách hiển thị họ nhân viên, dấu phẩy, khoảng cách, và tên. Biểu thức sau sẽ cho một trường văn bản duy nhất theo yêu cầu:
EmployeeName: [LastName] & ", " & [FirstName]
Dấu “&” sẽ gom chuỗi này vào chuỗi kia. Trong trường hợp này, biểu thức đã “cộng” ba chuỗi văn bản lại, nối đuôi nhau. Đây là công cụ hữu ích cần nhớ bất kỳ khi nào bạn cần kết hợp nhiều trường bảng thành một trường duy nhất trong câu truy vấn, báo cáo, form, hay trang Web.
BÀI TẬP 4.10: DÙNG THAO TÁC GỘP TRONG CÂU TRUY VẤN
1. Kích CREATE tab rồi kích Query Design trong nhóm Queries.
2. Trong hộp thoại Show Table, kích kép tblEmployee, tblInvoice, và tblInvoiceLine để thêm các bảng đó vào khung thiết kế truy vấn, rồi đóng hộp thoại Show Table. Kéo cạnh dưới cửa sổ các khung bảng nếu cần để hiển thị tất cả các trường.
3. Kích Totals trong nhóm Show/Hide thuộc tab ngữ cảnh DESIGN để chèn dòng Total vào lưới QBE.
4. Ở ô đầu tiên trên dòng Field, nhập biểu thức sau nhằm kết hợp hai trường tên, cách nhau bằng khoảng trắng. Hãy chắc là có khoảng cách giữa cặp ngoặc kép.
EmployeeName: [EmpFName] & " " & [EmpLName]
(Bạn có thể đặt khoảng cách ở hai phía của dấu &, nhưng không bắt buộc. Access tự động làm điều đó sau khi bạn chuyển sang ô khác trong lưới QBE.)
5. Hãy chắc chắn là ô Show của trường vừa tạo trong lưới QBE được đánh dấu.
6. Trong ô thứ hai trên dòng Field, hãy nhập biểu thức sau để tính và hiển thị tổng doanh thu, bao gồm phần trăm giảm giá áp dụng cho từng khách hàng. (Bạn có thể muốn nhấn Shift+F2 mở hộp thoại Zoom để có không gian rộng hơn. Không gian rộng giúp dễ dàng thấy toàn bộ biểu thức.)
Sales: Round([Quantity] * [UnitPrice] * (1 – [Discount]), 2)
7. Kích dòng Total trên lưới QBE bên dưới ô thứ hai – biểu thức bạn vừa tạo – rồi kích mũi tên danh-sách-sổ-xuống. Kích Sum từ danh-sách-sổ-xuống.
8. Kích dòng Sort trên lưới QBE bên dưới ô thứ hai, kích mũi tên danh-sách-sổ-xuống, rồi kích Descending trong danh-sách-sổ-xuống. Bạn muốn thấy các tổng doanh thu được sắp từ cao xuống thấp.
9. Để định dạng tiền tệ Currency cho ô thứ hai, kích phải ô chứa biểu thức trên dòng Field, kích Properties, kích thuộc tính Format, kích mũi tên danh-sách-sổ-xuống của thuộc tính Format, rồi kích Currency từ danh-sách-sổ-xuống.
10. Đóng hộp thoại Property Sheet, rồi kích nút View trong nhóm Results thuộc tab ngữ cảnh DESIGN để xem kết quả. Hãy điều chỉnh độ rộng cột để xem toàn bộ tên nhân viên. Hình 4.25 trình bày câu truy vấn cùng tập động dynaset của nó.
Hình 4.25 Các tổng doanh thu được sắp cho từng nhân viên.
11. Nhấn Ctrl+S để lưu câu truy vấn rồi gõ MyTotalSales để đặt tên cho câu truy vấn mới. Đóng câu truy vấn.
Tập động dynaset tính tổng doanh thu cho 19 nhân viên bán hàng. Microsoft Access có thể tính tổng doanh thu của mọi hóa đơn trong cơ sở dữ liệu được không? Có. Bạn có thể sửa một tí vào truy vấn vừa tạo trong bài tập trước để cho ra doanh thu tổng. Một cách khác, bạn có thể thêm tổng vào dòng cuối ở góc nhìn Datasheet của câu truy vấn. Bạn sẽ được hướng dẫn thực hiện phương pháp thứ hai. Phương pháp này đơn giản hơn và hiển thị nhiều thông tin hơn.
THỬ NGHIỆM
Hãy hiển thị câu truy vấn MyTotalSales ở góc nhìn Datasheet. Kích Totals trong nhóm Records thuộc HOME tab. Kích ô trống kế từ Total ở cuối tập động dynaset. Kích mũi tên danh-sách-sổ-xuống rồi kích Sum từ danh-sách-sổ-xuống. Giá trị tổng cột xuất hiện $176,728.07 – tổng hiện thời của mọi hóa đơn. Kích phải MyTotalSales tab rồi kích Close All để đóng mọi cửa sổ. Kích No khi hộp thoại hiển thị thông điệp hỏi bạn có muốn lưu thiết kế truy vấn không, vì bạn muốn duy trì nguyên bản truy vấn bạn đã tạo ở trên.
Không có nhận xét nào:
Đăng nhận xét