Phát triển HTTT kế toán bằng MS Access - Chương 4 - Truy vấn tự nối bằng SQL

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Truy vấn tự nối bằng SQL

Truy vấn tự nối (self-join query) là truy vấn nối một bảng với bản sao của chính nó. Trong truy vấn Access QBE chuẩn, bạn thêm bản sao thứ hai của bảng vào khung thiết kế truy vấn rồi trả về các cột thuộc bảng và bản sao của nó. Chẳng hạn, bảng nhân viên thường có trường (nhưng không phải bảng tblEmployee của ta) cho biết người quản lý nhân viên (chẳng hạn ManagerID). Đó là khóa ngoại trỏ đến khóa chính trong cùng bảng. Truy vấn tự nối sau sẽ liệt kê từng nhân viên và tên người quản lý nhân viên đó:

SELECT w.FirstName, w.LastName, m.FirstName, m.LastName
FROM Employee w INNER JOIN Employee m ON w.ManagerID = m.EmployeeID;

Truy vấn đó sẽ cho kết quả mong muốn, trả về tên nhân viên (đặt là w) cùng tên người quản lý (đặt là m). Bạn sẽ không thực hành với truy vấn như thế bằng SQL.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Cú pháp lệnh chọn SELECT

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Cú pháp lệnh chọn SELECT

Mục đích của phần này không phải giúp bạn thành thạo SQL. Phần này giúp bạn ý thức được là bạn có thể, nếu cần, viết được những lệnh SQL khá đơn giản qua giao diện QBE để để trả lời các câu hỏi khó hơn, cần kết quả của một truy vấn để gửi vào một truy vấn khác, bao bên ngoài truy vấn thứ nhất. Vì thế, bạn được giới thiệu vài lệnh chọn SELECT trong SQL để tra cứu dữ liệu “ấn tượng hơn”. Lệnh SELECT tạo tập động dynaset dựa trên một hay nhiều bảng, các trường trong những bảng đó, các tiêu chí lọc dòng, và các mệnh đề sắp tập động dynaset trước khi trả về. Cú pháp cơ bản của lệnh SELECT như sau:

SELECT [ALL | DISTINCT | TOP n] danh-sách-chọn
FROM tên-bảng
WHERE tiêu-chí
ORSER BY danh-sách-trường;

SELECT là một từ khóa SQL nhằm thực hiện việc tra cứu. Các từ khóa trong cặp ngoặc vuông là các tùy chọn chỉ định việc tra cứu tất cả ALL, các giá trị phân biệt DISTINCT, hay n dòng đầu tiên. Danh-sách-chọn là danh sách cột cách nhau bằng dấu phẩy cần được trả về. Bạn có thể dùng ký tự thay thế là dấu hoa thị (*) để trả về tất cả các cột. Mệnh đề WHERE liệt kê tất cả các tiêu chí (chẳng hạn Gender = “M”). Cuối cùng, mệnh đề ORDER BY liệt kê, từ trái sang phải, các cột dùng để sắp tập động dynaset. Quan trọng nhất là cột trái nhất. Các cột tiếp theo trong danh sách cách nhau bằng dấu phẩy được dùng trong trường hợp không thể xác định thứ tự dựa trên những cột trước.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Làm việc ở góc nhìn SQL

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Làm việc ở góc nhìn SQL

Bất kỳ khi nào bạn tạo một truy vấn trong Access, thật ra Access đang tạo một lệnh bằng ngôn ngữ truy vấn có cấu trúc (Structured Query Language – SQL) đằng sau hậu trường. SQL là ngôn ngữ phổ quát dùng trong mọi hệ quản trị cơ sở dữ liệu quan hệ để ra lệnh cho hệ cơ sở dữ liệu. SQL có lệnh đọc thêm xóa sửa các bản ghi trong bảng. Ngoài ra, ngôn ngữ SQL còn có lệnh tạo, xóa, và sửa bảng. Sau đây là ví dụ lệnh SQL để hiển thị vài cột trong bảng khách hàng tblCustomer được sắp giảm dần theo họ CustLastName:

SELECT CustID, CustFirstName, CustLastName
FROM tblCustomer
ORDER BY CustLastName DESC;

May thay, bạn không cần biết SQL để tạo hầu hết các truy vấn lấy thông tin từ một hay nhiều bảng. Tuy nhiên, đôi khi sẽ khó hay không thể dùng môi trường Access QBE để viết một truy vấn cần thiết. Thường sẽ rất tiện nếu dùng kết quả của truy vấn này trong truy vấn kia. Cách duy nhất để tạo truy vấn như thế là tạo truy vấn trực tiếp bằng SQL. Thỉnh thoảng, bạn có thể dùng lưới QBE để trả lời các kiểu truy vấn này, nhưng thường phải dùng SQL để tạo trực tiếp chúng. Chẳng hạn, giả sử bạn muốn liệt kê mọi nhân viên bán nhiều hơn doanh thu trung bình của mọi nhân viên. Kiểu truy vấn đó đầu tiên yêu cầu Access tính doanh thu trung bình của mọi nhân viên. Rồi Access dùng giá trị kết quả làm tiêu chí cho truy vấn thứ hai. Bằng việc dùng SQL, bạn có thể trả về kết quả bằng một truy vấn.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Tạo và dùng truy vấn nối ngoại

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Tạo và dùng truy vấn nối ngoại

Khi nối hai bảng, bạn có thể thấy rằng một hay nhiều dòng trong một bảng không khớp với các dòng trong bảng kia. Các dòng không khớp có thể hé lộ thông tin quan trọng. Chẳng hạn, quản lý viên bán hàng có thể muốn biết liệu có nhân viên bán hàng nào chẳng bán được gì trong một chu kỳ cụ thể. Có những ví dụ khác về thông tin ẩn chứa trong cơ sở dữ liệu của bạn. Trong cơ sở dữ liệu Chương 4, chẳng hạn, bạn không thể dò ra những thành phố không có nhân viên bằng cách thi hành phép nối thông thường nối hai bảng tblEmployee và tblEmployeeDivision trên việc so khớp khóa chính khóa ngoại. Thay vào đó, bạn tạo dạng thông tin này bằng cách dùng một truy vấn nối các bảng bằng phép nối ngoại (outer join), nhằm liệt kê tất cả các dòng trong một bảng và chỉ những dòng khớp ở bảng kia.

Bài tập kế sẽ chỉ cách tạo phép nối ngoại. Bắt đầu bằng việc dùng câu truy vấn bạn đã tạo ở Bài tập 4.10 có tên MyTotalSales. Bạn sẽ sửa thuộc tính nối của cả hai phép nối hiện thời để tạo nên phép nối ngoại. (Hai phép nối đó gọi là phép nối nội inner join). Câu truy vấn mới, khi hoàn tất, sẽ hé lộ tên các nhân viên bán hàng không bán được gì cả.

BÀI TẬP 4.11: TẠO MỘT TRUY VẤN NỐI NGOẠI

1. Hãy tạo một bản sao của MyTotalSales rồi đổi tên: Kích MyTotalSales trong Navigation Pane, nhấn Ctrl+C, nhấn Ctrl+V, gõ MyOuterJoin vào hộp thoại Paste As để đặt tên cho truy vấn vừa sao chép, rồi kích OK.

2. Kích phải MyOuterJoin rồi kích Design View.

3. Kích kép đường nối giữa hai khung tblEmployee và tblInvoice ở phần trên cửa sổ truy vấn để mở hộp thoại Join Properties.

4. Chọn tùy chọn thứ hai trong hộp thoại (xem Hình 4.26), rồi kích OK. Bây giờ bạn cần thấy mũi tên trên đường nối trỏ từ khung tblEmployee đến khung tblInvoice, cho biết là bạn đã yêu cầu một phép nối ngoại với mọi bản ghi trong tblEmployee bất chấp có bản ghi tương ứng trong tblInvoice hay không.

Hình 4.26 Tạo một phép nối ngoại.

5. Lặp lại các bước 3 và 4 cho đường nối giữa các khung tblInvoice và tblInvoiceLine.

6. Hiển thị truy vấn ở góc nhìn Datasheet, và cuộn màn hình nếu cần, để bạn có thể thấy các dòng 20 đến 22 của tập động dynaset. Để ý cột doanh thu Sales trống với Melinda English, Giles Bateman, và Brad Shoenstein. Nghĩa là họ không có bản ghi nào trong bảng tblInvoiceLine và vì thế không có doanh thu trong chu kỳ đó.

Ở bước kế, bạn sẽ bảo Access chỉ hiển thị những dòng có tổng doanh thu là trống.

7. Hiển thị truy vấn ở góc nhìn Design, chuyển đến dòng tiêu chí Criteria bên dưới cột Sales (tổng doanh thu), rồi gõ Is Null vào ô Criteria. Hiển thị truy vấn ở góc nhìn Datasheet. Nó hiển thị chỉ những nhân viên không báo cáo doanh thu trong chu kỳ đó. (Xem Hình 4.27.) Bạn có thể bỏ cột Sales trong truy vấn này – nó trống và bạn biết rõ – bằng cách xóa đánh dấu trong ô Show. Ta vẫn để đó để biết câu truy vấn hoạt động tốt.

Hình 4.27 Dùng Is Null để hiển thị các dòng có trường trống.

8. Lưu rồi đóng câu truy vấn.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Nhóm và tổng kết dữ liệu

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.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Dùng tiêu chí trong câu truy vấn

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Dùng tiêu chí trong câu truy vấn

Tâm điểm của câu truy vấn là tập hợp tiêu chí để lọc kết quả tập động dynaset, chỉ cho phép những dòng mà giá trị cột của chúng khớp với tiêu chí đã đề ra. Các tiêu chí được đặt vào một hay nhiều dòng bên dưới cột mà chúng áp dụng trong lưới QBE. Các tiêu chí có thể là hằng đơn giản, chẳng hạn “F” để lọc các dòng nhân viên nữ, hay có thể là những biểu thức phức tạp cho kết quả có cùng kiểu dữ liệu với cột mà chúng áp đặt. Mọi tiêu chí đều có cùng dạng. Chúng gồm một hay nhiều toán hạng, có thể là hằng (hằng chuỗi, hằng số, hay thời điểm), các danh hiệu như tên cột hay hàm Access, cùng các toán tử toán học và logic (+, -, *, /, And, Or, Not, …). Công thức tiêu chí trả về kết quả là đúng hay sai khi áp dụng vào từng bản ghi trong bảng hay trong tổ hợp nhiều bảng có quan hệ với nhau. Tập động dynaset của truy vấn chỉ hiển thị những bản ghi mà kết quả tiêu chí là đúng.

Toán hạng hằng trong các tiêu chí có thể là văn bản. Nếu bạn có văn bản trong biểu thức, chúng phải được bao bằng cặp nháy kép. Nếu bạn quên dấu nháy kép, Access sẽ tự động bổ sung (ngoại trừ một vài trường hợp kỳ dị). Toán hạng hằng có thể là số như 67.79 hay 43227. Không được có dấu phân cách trên các toán hạng số. Cặp dấu # cần bao quanh giá trị thời điểm. Chẳng hạn, #12/14/2011# là cách viết hằng thời điểm. True, False, và Null là các toán hạng hằng đặc biệt. Hai hằng đầu là hiển nhiên, nhưng Null có thể không. Null xuất hiện trong tiêu chí nghĩa là trường mà nó xuất hiện bên dưới phải trống mới xuất hiện trong tập động dynaset. Bạn có thể muốn biết những bản ghi nhân viên nào chưa có ngày sinh, chẳng hạn. Hay bạn muốn biết những hóa đơn nào chưa có giá trị trong cột InvoiceID.

Danh hiệu là tên trường bảng xuất hiện trong câu truy vấn. Bất kỳ khi nào bạn dùng danh hiệu trong tiêu chí, hãy luôn bao chúng bằng cặp móc vuông. Chẳng hạn, hãy dùng >[OnHand] để xác định nếu cột lớn hơn giá trị của trường OnHand hay không. Việc bỏ qua cặp móc vuông có thể làm Access hiểu lầm hoặc cho kết quả sai.

Các truy vấn chứa điều kiện sẽ rất hữu ích. Điều kiện là cách bạn qui định miền giá trị, hay giá trị nhỏ nhất và lớn nhất, cho một trường. Chẳng hạn, công ty thu mua cà phê đang đặt mua cà phê và trà nhiều hơn khả năng cung ứng của các trang trại trên toàn thế giới. Một trong những câu hỏi quan trọng mà công ty thu mua phải trả lời là những sản phẩm nào được đặt mua sau (back-ordered). Trong hệ thống bảng của The Coffee Merchant, một sản phẩm sẽ được đặt mua sau nếu trường OnHand trong tblInventory là âm. Để quyết định mua một cách thông minh, công ty thu mua cần biết mã sản phẩm, tên sản phẩm, và giá trị đặt mua sau. Từ danh sách đó, nhân viên thu mua có thể đặt mua đúng số lượng và kiểu sản phẩm từ nhiều nhà cung ứng khác nhau.

Để trả lời cho nhân viên thu mua về số lượng hiện có, ta phải tạo một truy vấn lên bảng tblInventory. Ta sẽ dùng một toán tử đặc biệt gọi là toán tử so sánh để tạo tiêu chí chọn trong câu truy vấn mới. Toán tử so sánh (comparison operator) là một ký hiệu đặc biệt nhằm so sánh hai giá trị rồi trả về kết quả là đúng true hay sai false. Các toán tử so sánh được trình bày ở Hình 4.20.

Hình 4.20 Các toán tử so sánh.

BÀI TẬP 4.9: TẠO MỘT TRUY VẤN DÙNG TOÁN TỬ SO SÁNH ĐỂ LỌC CÁC DÒNG

1. Kích CREATE tab, kích Query Design, rồi kích kép tblInventory trong hộp thoại Show Table. Kích nút Close trong hộp thoại Show Table.

2. Kích kép các trường sau trong khung bảng để thêm chúng, từ trái sang phải, vào dòng Field thuộc lưới QBE: mã tồn kho InventoryID, tên mặt hàng ItemName, và số lượng hiện có UnitsOnHand.

3. Kích dòng Sort bên dưới cột UnitsOnHand, kích mũi tên sổ xuống, rồi kích Descending.

4. Kích dòng Criteria trên cùng bên dưới cột UnitsOnHand rồi gõ <0 (ký hiệu nhỏ hơn theo sau là số không).

5. Kích View trong nhóm Result thuộc tab ngữ cảnh DESIGN để hiển thị tập động dynaset. Di chuyển chuột đến đường phân cách giữa các tên cột ItemName và UnitsOnHand. Khi nó chuyển thành mũi tên hai đầu, kích kép để mở rộng cột ItemName theo độ rộng tối ưu. Hình 4.21 hiển thị thiết kế truy vấn và tập động dynaset kết quả. Màn hình của bạn có thể được bố trí khác, nhưng tập động dynaset cần giống các dòng trong hình.

Hình 4.21 Lọc dòng bằng toán tử so sánh.

6. Sau khi quan sát kết quả, kích phải datasheet tab rồi kích Close All. Kích No khi được hỏi bạn muốn lưu các thay đổi trên câu truy vấn không.

Ký tự thay thế (wildcard) cho phép bạn tìm thông tin khi bạn không chắc nhớ đầy đủ nội dung của trường văn bản. Được dùng với toán tử Like (xem Hình 4.20), ký tự thay thế định nghĩa vị trí mà có thể chứa một ký tự hoặc 0 hay nhiều ký tự trong một mẫu để so khớp chuỗi văn bản. Hình 4.22 trình bày ba ký tự thay thế có thể dùng trong Access. Sau đây là một ví dụ bạn có thể dùng chúng. Giả sử bạn muốn kiểm tra trong kho của The Coffee Merchant có thức uống nào có tên chứa từ chocolate hay không – chẳng hạn, Dutch Chocolate. Bạn quan tâm đến số lượng hiện có. Bạn có thể dùng ký tự thay thế * (dấu hoa thị) và một phần từ là choc để trả về bất kỳ thức uống nào mà trường tên Name chứa từ choc trong đó.

Hình 4.22 Các ký tự thay thế.

Câu truy vấn được mô tả ở trên bao gồm một bảng là tblInventory. Tiêu chí lọc là biểu thức *choc*, chứa hai ký tự thay thế dấu hoa thị ở hai đầu chuỗi bộ phận. Hãy đặt tiêu chí này vào dòng Criteria ngay dưới cột ItemName ở lưới QBE. (Sau khi bạn gõ biểu thức trên, Access sẽ tự động bao nó bằng cặp ngoặc kép và thêm đằng trước từ Like.) Dấu hoa thị đứng trước choc cho biết từ, ngữ, hay chuỗi ký tự bất kỳ có thể xuất hiện trước nó – hoặc không có gì. Tương tự, dấu hoa thị đứng sau choc cho biết ký tự bất kỳ có thể xuất hiện sau nó – hoặc không có gì. Nghĩa là, tiêu chí tìm yêu cầu dòng bất kỳ phải chứa từ choc đâu đó trong tên. Hình 4.23 hiển thị câu truy vấn và tập động dynaset kết quả.

Hình 4.23 Câu truy vấn dùng từ thay thế trong tiêu chí của nó.

Một số toán tử khác hữu ích cho việc tạo tiêu chí chọn là các toán tử logic (logical operators). Toán tử logic cho phép ràng buộc hai tiêu chí so sánh hay có ký tự thay thế. Có một số toán tử logic, nhưng những toán tử thường dùng nhất là AND, OR, và NOT. Dùng toán tử AND, bạn có thể qui định điều kiện trong đó cả hai tiêu chí phải thỏa. Chẳng hạn, giả sử bạn muốn xem xét các hóa đơn được lập trong tuần đầu của tháng 11/2011. Một nhân viên tạm thời đã được giao xử lý các hóa đơn trong tuần đó, và bạn biết rằng một số hóa đơn bị xử lý sai. Bạn có thể dùng toán tử AND để giới hạn miền giá trị của thời điểm lập hóa đơn. Cụ thể, bạn viết biểu thức tiêu chí sau

>= #11/1/2011# And <= #11/7/2011#

vào dòng Criteria bên dưới InvoiceDate trong lưới QBE. Tiêu chí chọn này qui định miền giá trị các thời điểm lập hóa đơn cần thỏa. Miền giá trị bao gồm các thời điểm lớn hơn hay bằng ngày 1/11 /2011 và (đồng thời) nhỏ hơn hay bằng ngày 7/11/2011. Như vậy, tiêu chí giới hạn các dòng từ bảng hóa đơn Invoice về những hóa đơn được lập trong tuần đầu.

Lúc nào cũng dùng dấu # để bao thời điểm để Access không nhầm với biểu thức số học. Một cách tương đương và đơn giản hơn tiêu chí ở trên là dùng toán tử so sánh Between:

Between #11/1/2011# And #11/7/2011#

Thời điểm bất kỳ ở giữa hay khớp với hai thời điểm đó thì thỏa tiêu chí.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Dùng các hàm thời điểm

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Dùng các hàm thời điểm

Các hàm thời điểm gồm hàm quen thuộc Date() nhằm hiển thị thời điểm hiện tại. Đó là hàm hữu ích để tính số ngày quá hạn từ thời điểm lưu trong hóa đơn (chẳng hạn, Date() – InvoiceDate). Bên cạnh việc dùng hàm trong ô Field của câu truy vấn, hàm có thể được dùng trên dòng tiêu chí Criteria của câu truy vấn để lọc kết quả. Chẳng hạn, việc đặt biểu thức < Date() - 60 vào ô tiêu chí Criteria bên dưới InvoiceDate trong câu truy vấn trên bảng tblInvoice sẽ hiển thị các hóa đơn quá hạn hơn 60 ngày. Để trích thông tin từ một thời điểm, bạn có thể dùng hàm DatePart. Hàm DatePart có dạng:

DatePart(“khoảng”, biểu-thức)

Trong đó, khoảng có thể là chuỗi bất kỳ ở Hình 4.17 và biểu-thức, là hằng hoặc trường từ bảng hay câu truy vấn có giá trị là thời điểm.

Hình 4.17 Các tham biến chọn lọc của hàm DatePart.

Hàm DateDiff đặc biệt hữu ích trong các ứng dụng kế toán vì hàm trả về khoảng, hay độ lệch, giữa hai thời điểm. DateDiff có thể tính sự trễ hạn 30, 60, hay 90 ngày. Hàm có dạng

DateDiff(“khoảng”, thời-điểm-1, thời-điểm-2)

Trong đó, khoảng là chuỗi bất kỳ ở Hình 4.17, thời-điểm-1 và thời-điểm-2 là hằng hay tên trường có giá trị là thời điểm. Tham biến thứ hai, thời-điểm-1, thường là thời điểm “xưa hơn” và thời-điểm-2 là thời điểm gần đây hơn. Khoảng được trả về theo đơn vị mà bạn qui định (số năm, số quí, số tháng, số tuần, …). Chẳng hạn, biểu thức sau sẽ trả về số tuần nguyên giữa thời điểm đặt hàng và thời điểm lập hóa đơn:

DateDiff(“w”, OrderDate, InvoiceDate)

Tương tự, biểu thức sau sẽ tính số ngày quá hạn, dựa trên thời điểm hôm nay, cho từng hóa đơn trong bảng tblInvoice. Hàm có sẵn Date() không có tham biến và trả về thời điểm hôm nay.

DateDiff(“d”, InvoiceDate, Date())

Một bài tập sẽ giúp bạn hiểu cách vận hành của những hàm này. Hãy nối hai bảng tblInvoice và tblInvoiceLine rồi hiển thị giá trị tính toán của các dòng được chọn. Bạn sẽ dùng tiêu chí để lọc sao cho chỉ có dòng được chọn xuất hiện trong tập động dynaset.

BÀI TẬP 4.8: DÙNG HÀM DATEDIFF

1. Kích CREATE tab, kích Query Design, kích kép tblInvoice rồi kích kép tblInvoiceLine trong hộp thoại Show Table. Kích nút Close để đóng hộp thoại Show Table. Hai khung bảng xuất hiện trong khung thiết kế truy vấn.

2. Kích kép các trường sau trong các khung bảng để thêm chúng, từ trái sang phải, vào dòng Field thuộc lưới QBE: CustomerID, OrderDate, và InvoiceDate.

3. Kích ô trống bên phải ô InvoiceDate thuộc lưới QBE rồi gõ biểu thức tính giá thành mà bạn đã gõ trước đây. Tuy nhiên, lần này bạn không cần chỉ định tên bảng cho trường UnitPrice vì đó là trường có tên duy nhất trong hai khung bảng. Nhấn Shift+F2 để mở cửa sổ Zoom rồi gõ biểu thức sau:

ExtendedPrice: Round([Quantity] * [UnitPrice] * (1 - [Discount]), 2)

4. Kích OK để đóng cửa sổ Zoom. Kích phải ô Field trống kế tiếp, ô thứ năm tính từ trái, rồi kích Zoom để mở cửa sổ Zoom. Gõ biểu thức sau để tính và hiển thị số ngày giữa thời điểm đặt hàng OrderDate và thời điểm lập hóa đơn:

OrderToInvoiceDays: DateDiff(“d”, [OrderDate], [InvoiceDate])

Hình 4.18 hiển thị cửa sổ Zoom (có cỡ chữ hơi lớn hơn một tí) với biểu thức hoàn chỉnh.

Hình 4.18 Tính số ngày đã qua giữa hai thời điểm.

5. Kích OK để đóng cửa sổ Zoom.

6. Kích ô Sort bên dưới cột biểu thức OrderToInvoiceDays, kích mũi tên danh-sách-sổ-xuống, rồi kích Descending. Hành động này sẽ sắp các hóa đơn theo thứ tự số ngày đã qua từ nhiều đến ít.

7. Kích biểu tượng Save trên thanh công cụ truy xuất nhanh Quick Access Toolbar, gõ MyDateDifference vào ô Query Name, rồi kích OK để lưu truy vấn.

8. Kích View trong nhóm Results. Tập động dynaset hiển thị các dòng hóa đơn được sắp giảm dần theo số ngày đã qua giữa thời điểm đặt hàng và thời điểm lập hóa đơn (xem Hình 4.19).

Hình 4.19 Tập động dynaset hiển thị số ngày đã qua.

9. Đóng tập động dynaset. Kích phải tab MyDateDifference rồi kích Close All trong pop-up menu.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Xử lý trường để trống null trong biểu thức

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Xử lý trường để trống null trong biểu thức

Khi bạn dùng các hàm toán học để tính toán, vấn đề có thể phát sinh khi một hay nhiều trường bảng, dùng trong biểu thức, không chứa giá trị. Khi một trường để trống, bạn nói rằng nó bằng null. Giá trị null trong tính toán sẽ trả về giá trị trống, không phải số không. Chẳng hạn, nếu một hay nhiều trường UnitPrice của một hóa đơn cụ thể để trống – chứa null – thì kết quả tính giá thành ExtendedPrice cũng bằng trống. Có thể hay hơn nếu hiển thị số không trong biểu thức có giá trị trống. Bạn có thể trải nghiệm điều này bằng cách làm Thử nghiệm sau.

THỬ NGHIỆM

Mở câu truy vấn MyExtendedPrice ở góc nhìn Datasheet. Kéo chuột qua giá trị UnitPrice $3.90 ở dòng đầu – dòng ứng với InvoiceID 214010 và giá trị InventoryID 1195. Nhấn phím Delete để thay giá trị đó bằng giá trị rỗng. Cập nhật dòng bằng cách kích bất kỳ nơi nào trong dòng thứ hai. Bây giờ quan sát ô ExtendedPrice ở dòng đầu. Nó cũng trống. Kích nút Undo ở thanh công cụ truy xuất nhanh Quick Access Toolbar để hồi phục giá trị UnitPrice về giá trị ban đầu. Giá trị ExtendedPrice tái xuất hiện là $66.30. Đóng câu truy vấn.

Hàm Nz sẽ cho kết quả 0 bất kỳ khi nào giá trị tham biến đầu là null. Dạng của hàm là

Nz(biểu-thức, giá-trị-nếu-null)

Tham biến đầu, biểu-thức, là giá trị bạn muốn kiểm tra có bằng null hay không. Nếu bằng null thì tham biến thứ hai, giá-trị-nếu-null, là kết quả trả về. Trái lại, biểu thức ở tham biến đầu được tính và trả về. Bạn sẽ không được yêu cầu sửa câu truy vấn MyExtendedPrice, nhưng sau đây là biểu thức điều chỉnh để cho giá trị 0 nếu biểu thức cho ra giá trị null do UnitPrice để trống:

ExtendedPrice: Nz(Round([Quantity]*[tblInvoiceLine]![UnitPrice]*(1-[Discount]),2),0)

Tức đặt toàn bộ biểu thức vào trong hàm Nz làm tham biến đầu, gõ dấu phẩy, số 0, rồi đóng ngoặc.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Dùng hàm toán học

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Dùng hàm toán học

Bài tập kế nhẹ nhàng giới thiệu một hàm toán học thường dùng. Đó là hàm làm tròn Round. Việc làm tròn giá thành về hai chữ số thập phân sẽ dễ theo dõi hơn và cho kết quả tốt hơn – đặc biệt khi gộp các giá thành.

BÀI TẬP 4.5: DÙNG HÀM ACCESS CÓ SẴN

1. Hiển thị MyExtendedPrice ở góc nhìn Design, kích phải ô chứa trường ExtendedPrice rồi kích Build để mở trình xây dựng biểu thức Expression Builder.

2. Kích dấu + (ký hiệu mở rộng) bên trái Functions trong khung Expression Elements. Kích Built-In Functions ở khung bên trái, kích Math trong khung Expression Categories (ở giữa), rồi kích Round trong khung bên phải.

3. Thay vì dán hàm, bạn sẽ gõ vào. Khi soạn biểu thức, với hàm quen thuộc thì sẽ dễ hơn nhiều nếu gõ thay vì dán. Việc dán sẽ đặt hàm sai vị trí trong biểu thức trừ phi bạn bắt đầu biểu thức bằng hàm rồi điền tham biến từ trái sang phải.

4. Trong ô văn bản ở phần trên của trình xây dựng biểu thức Expression Builder, kích ngay sau dấu hai chấm để xác định vị trí chèn. Gõ round( rồi nhấn phím End để chuyển điểm chèn đến biên phải của biểu thức – trước đó là ngoặc đóng, và trước ngoặc đóng là trường Discount.

5. Gõ ,2) (dấu phẩy, 2, và ngoặc đóng) để hoàn tất hàm. Biểu thức đầy đủ của bạn, với giá thành là tham biến đầu tiên của hàm làm tròn Round, cần khớp với Hình 4.16.

Hình 4.16 Giá thành được làm tròn.

6. Kích OK để đóng trình xây dựng biểu thức Expression Builder và xác nhận là đã hoàn thành biểu thức.

7. Kích nút View trong nhóm Results thuộc tab ngữ cảnh DESIGN để hiển thị tập động dynaset.

Giá thành của dòng đầu là 66.3 và dòng thứ hai là 73.78. Hãy kiểm tra kết quả của bạn để đảm bảo rằng chúng khớp với hai giá trị này. Để ý rằng việc làm tròn đến hai chữ số thập phân không nhất thiết hiển thị đầy đủ hai chữ số đó. Giá trị 66.3 cho biết chữ số thập phân thứ hai bằng không. Bạn có thể định dạng (format) để kết quả trông dễ chấp nhận hơn.

Có nhiều hàm toán học hữu ích như Abs, Fix, Rnd, Val, và Format. Abs trả về trị tuyệt đối của tham biến truyền vào. Fix trích phần nguyên của tham biến, bỏ qua phần thập phân. Rnd phát sinh một số ngẫu nhiên giữa 0 và 1 – hữu ích để phát sinh dữ liệu kế toán kiểm thử. Val chuyển chuỗi thành số để bạn có thể dùng trong tính toán. Format chuyển một số thành chuỗi định dạng. Chẳng hạn, biểu thức

Format(123.456, Currency)

sẽ trả về kết quả $123.46.

BÀI TẬP 4.7: ĐỊNH DẠNG CỘT TRUY VẤN

1. Kích phải MyExtendedPrice rồi kích Design View để hiển thị thiết kế truy vấn.

2. Kích ô chứa trường ExtendedPrice trong lưới QBE rồi kích Property Sheet trong nhóm Show/Hide thuộc tab ngữ cảnh DESIGN. (Bạn cũng có thể nhấn Alt+Enter là cách nhanh nhất để hiển thị bảng thuộc tính Property Sheet.)

3. Kích ô Format (ô trống bên phải tên thuộc tính), kích mũi tên sổ xuống, rồi kích Currency trong danh-sách-sổ-xuống. Hành động này sẽ định dạng biểu thức tính toán để hiển thị kết quả làm tròn đến hai chữ số thập phân, thêm ký hiệu tiền tệ cũng như dấu phân cách cần thiết.

4. Kích nút đóng (X) của bảng thuộc tính Property Sheet.

5. Kích nút View trong nhóm Results để hiển thị góc nhìn Datasheet của câu truy vấn. Giá thành bây giờ trông tuyệt.

6. Đóng câu truy vấn và lưu.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Giới thiệu các hàm Access có sẵn

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Giới thiệu các hàm Access có sẵn

Các hàm Access rất tiện dụng vì chúng cung cấp rất nhiều các xử lý số, thời điểm, và văn bản theo những cách sẽ khó làm nếu dùng các toán tử đơn giản. Hàm Access có sẵn nhận dữ liệu bạn cung cấp, gọi là tham biến, chuyển đổi hay tính toán trên dữ liệu nhập, rồi trả về kết quả.

Hàm có thể thực hiện các tính toán phức tạp. Có hàng chục hàm đơn lẻ, được nhóm vào mười bốn nhóm. Một số nhóm có tên quen như thời điểm Date/Time, tài chính Financial, và toán học Math, trong khi các tên nhóm khác như Inspection hay Domain Aggregate thì ít quen. Việc khảo sát nhiều hay toàn bộ hàm sẽ khiến bạn nản lòng nhanh chóng. Ta sẽ thảo luận một số hàm thường dùng và những hàm quan trọng trong hệ thông tin kế toán.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Dùng trình xây dựng biểu thức Expression Builder

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Dùng trình xây dựng biểu thức Expression Builder

Trình xây dựng biểu thức Expression Builder có sẵn trong Access để giúp bạn tạo biểu thức. Đó là công cụ có thể dùng bất kỳ khi nào bạn cần một biểu thức gồm các thuộc tính trường bảng, các biểu thức trường khi thiết kế câu truy vấn, cũng như trong forms và báo cáo. Nó cho bạn dễ dàng truy xuất tên trường và thành phần điều khiển trong cơ sở dữ liệu. Ngoài ra, nó còn tổ chức các hàm Access thành những nhóm hàm có liên quan. Dùng trình xây dựng biểu thức Expression Builder, bạn có thể tạo biểu thức từ đầu, hay bạn có thể chọn từ hàng trăm biểu thức tùy chỉnh để hiển thị thời điểm, số trang, … Bạn dùng trình xây dựng biểu thức Expression Builder ở bài tập kế để chọn các trường từ một truy vấn hiện có từ đó viết biểu thức hiển thị thông tin tính được từ các trường bảng hiện có trong bảng tblInvoiceLine.

BÀI TẬP 4.5: VIẾT BIỂU THỨC TÍNH GIÁ THÀNH

1. Với truy vấn MyExtendedPrice đang mở ở góc nhìn Design, kích phải ô trống đầu tiên trên dòng Field (kéo ô cuộn ngang nếu cần).

2. Kích Build trong pop-up menu. Trình xây dựng biểu thức Expression Builder hiện lên.

3. Nếu cần, kích MyExtendedPrice ở khung trái của trình xây dựng biểu thức Expression Builder. Các trường trong câu truy vấn đang tạo xuất hiện ở khung giữa.

4. Kích kép Quantity trong khung giữa, kích biên phải của biểu thức đang tạo, gõ *, rồi nhấn thanh ngang Spacebar. (Bạn có thể chọn toán tử bằng cách mở Operators trong khung Expression Elements, nhưng gõ sẽ dễ hơn.)

5. Vì trường đơn giá UnitPrice xuất hiện ít nhất ở hai bảng, ta phải qui định bảng chứa UnitPrice mà ta muốn (tblInvoiceLine trong trường hợp này): Kích nút mở rộng (dấu cộng) bên trái tên cơ sở dữ liệu Ch04.accdb. Kích nút mở rộng bên trái nhóm Tables, rồi kích tblInvoiceLine – tất cả đều trong khung trái Expression Elements. (Bạn có thể phải cuộn xuống để thấy bảng tblInvoiceLine trong danh sách.) Các trường trong tblInvoiceLine xuất hiện trong khung giữa, Expression Categories.

6. Kích kép UnitPrice trong khung giữa để đặt tên đầy đủ của nó vào biểu thức đang tạo. Access sẽ thêm “[tblInvoiceLine]![UnitPrice]” vào biểu thức. Dấu chấm than phân cách tên bảng và tên cột.

7. Kéo ô cuộn lên trên để bạn có thể thấy MyExtendedPrice ở đầu danh sách trong khung Expression Elements. Kích MyExtendedPrice.

8. Kích khung biểu thức, gõ * rồi nhấn thanh ngang Spacebar để tiếp tục tạo biểu thức. Gõ (1 -, kích kép Discount ở khung giữa, rồi gõ ) (dấu ngoặc tròn đóng) để hoàn tất biểu thức. Biểu thức bạn đang tạo cần khớp với biểu thức trong Hình 4.14.

Hình 4.14 Tạo biểu thức dùng trình xây dựng biểu thức Expression Builder.

9. Nhấn phím Home để chuyển điểm chèn sang biên trái của biểu thức rồi gõ ExtendedPrice: (một từ sau đó là dấu chấm) để gán tên cho cột tính toán.

10. Kích OK để đóng hộp thoại Expression Builder và đặt biểu thức hoàn tất vào ô trên dòng Field.

11. Kích phải MyExtendedPrice tab rồi kích Datasheet View trên pop-up menu. Tập động dynaset hiển thị cột mới ExtendedPrice. Kéo cạnh phải của cột ExtendedPrice để mở rộng nó (xem Hình 4.15).

Hình 4.15 Giá thành có nhiều chữ số thập phân.

Access hiển thị giá thành với nhiều chữ số thập phân. Điều đó không thỏa mãn khách hàng. Vì vậy hãy tìm cách làm tròn về hai chữ số thập phân – tức định dạng tiền tệ ở đó mọi chữ số thập phân bên phải chữ số thứ hai đều bằng không. Microsoft Access, như Excel, cung cấp hàng trăm hàm có sẵn để bạn có thể dùng trong biểu thức.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Dùng các toán tử

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Dùng các toán tử

Ta hãy viết một biểu thức để biết chính xác cách tính toán trong một truy vấn bằng cách dùng các trường truy vấn và hiển thị chúng trong tập động dynaset. Trong bài tập tới, bạn viết câu truy vấn để nối hai bảng tblInvoiceLine với tblInventory và hiển thị các mục trong dòng hóa đơn đối với các hóa đơn trong cơ sở dữ liệu. Câu truy vấn sẽ hiển thị giá thành, trong số các cột khác, theo công thức:

ExtendedPrice: [Quantity] * [UnitPrice] * (1 – [Discount])

Các cột được đặt vào câu truy vấn và biểu thức là cột bổ sung mà bạn sẽ viết vào một ô trống trong dòng Field. Hãy thử tính: nếu hóa đơn được làm cho 20 pounds cà phê với giá $10.00 mỗi pound và được giảm giá 5 phần trăm, thì giá thành sẽ bằng:

ExtendedPrice: 20 * 10.00 * (1 – 0.05)

Bảng tồn kho tblInventory chứa trường đơn giá UnitPrice cho từng mặt hàng trong kho. Tuy nhiên, khách hàng có thể được hay không được tính theo giá đề xuất đó. Giá tính thực tế được lưu trong UnitPrice thuộc bảng tblInvoiceLine, và có thể thay đổi theo khách hàng. Vì trường UnitPrice xuất hiện ở cả hai bảng tblInventory và tblInvoiceLine, bạn phải phân biệt bằng cách dùng tên bảng phía trước. Chẳng hạn, bạn có thể tham chiếu đến trường một cách đầy đủ, dùng tên bảng phía trước, như sau:

[tblInvoiceLine]![UnitPrice]

Cách đó không thể gây hiểu lầm về trường được tham chiếu – trong trường hợp này là trường thuộc tblInvoiceLine. Dấu chấm than là cú pháp tách tên bảng khỏi tên cột.

Kế đến, bạn sẽ viết câu truy vấn chứa biểu thức theo hai bước. Ở bước đầu, bạn sẽ thêm trường ở cả hai bảng. Ở bước hai, bạn sẽ viết biểu thức chứa trường và toán tử.

BÀI TẬP 4.4: VIẾT CÂU TRUY VẤN CƠ BẢN

1. Kích CREATE tab, kích Query Design, thêm các bảng tblInventory và tblInvoiceLine vào khung thiết kế truy vấn bằng cách kích kép tên của chúng, rồi đóng hộp thoại Show Table.

2. Kéo các trường InvoiceID, InventoryID, Quantity, UnitPrice, và Discount từ bảng tblInvoiceLine vào năm ô đầu tiên trên dòng Field trong lưới QBE (theo thứ tự đã liệt kê).

3. Kích kép ItemName trong bảng tblInventory để thêm nó vào ô bìa phải trên dòng Field.

4. Kích ô Sort bên dưới cột InvoiceID trong lưới QBE, kích mũi tên sổ xuống, rồi chọn Ascending. Điều này sẽ sắp các hóa đơn theo thứ tự tăng dần của mã hóa đơn.

5. Kích Save trên thanh công cụ truy xuất nhanh Quick Access Toolbar, và gõ MyExtendedPrice trong hộp thoại Save As, rồi kích OK để lưu thiết kế truy vấn.

6. Kích nút View trong nhóm Results thuộc tab ngữ cảnh DESIGN để duyệt qua tập động dynaset. Để ý rằng cột ItemName không đủ rộng để hiển thị toàn bộ tên mặt hàng. Điều đó sẽ được sửa trong bài tập kế.

7. Kích phải (là cách khác để đổi góc nhìn) MyExtendedPrice tab rồi kích Design View trong pop-up menu.

Sau khi đã lưu câu truy vấn, bạn có thể hoàn thành câu truy vấn bằng cách viết biểu thức để tính giá thành.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Dùng biểu thức trong câu truy vấn

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Dùng biểu thức trong câu truy vấn

Đôi khi bạn muốn xem kết quả tính toán dẫn xuất từ một truy vấn. Chẳng hạn, bạn muốn hiển thị tuổi một người từ trường ngày sinh trong bảng. Có thể bạn muốn biết tổng trị giá của từng mặt hàng trong kho, là tích giữa số lượng và đơn giá. Dù Access cho phép, bạn biết là không nên thêm một cột bảng mà có thể được tính hay được dẫn xuất từ các cột khác trong cùng bảng. Điều này sẽ vi phạm qui tắc chuẩn hóa, và quan trọng hơn, sẽ phát sinh dữ liệu sai hay lỗi thời. Chẳng hạn, thiết kế cơ sở dữ liệu và qui tắc chuẩn hóa tốt sẽ ngăn việc thêm cột chứa giá thành (extended price) trong bất kỳ bảng hóa đơn của The Coffee Merchant. Vì giá thành được tính từ các trường số lượng Quantity, đơn giá UnitPrice, và phần trăm giảm giá Discount trong bảng tblInvoiceLine, giá thành không nên lưu trong bảng. Tại sao vậy? Giả sử được lưu trong tblInvoiceLine cùng với Quantity, UnitPrice, và Discount. Điều gì xảy ra nếu có người phát hiện một sai sót trong giá trị Quantity hay UnitPrice trong một hay nhiều hóa đơn? Việc thay đổi sẽ làm sai giá thành. Chuyên gia cơ sở dữ liệu bảo rằng cơ sở dữ liệu đó bất nhất.

Điều đó đưa ta đến câu hỏi sau: Làm sao bạn cho ra giá thành hay các tính toán hữu ích khác? Một đáp án là bạn bỏ tính toán bất kỳ vào câu truy vấn. Access cho phép bạn viết biểu thức tính tổng, trung bình, và đếm giá trị cũng như viết biểu thức chứa các toán tử số học, trường, hằng số, và toán tử so sánh. Toán tử số học là các toán tử quen thuộc: cộng (+), trừ (-), nhân (*), và chia (/). Có ba toán tử so sánh để hình thành sáu tổ hợp so sánh nhằm đưa ra kết quả đúng hay sai. Sáu tổ hợp đó là nhỏ hơn <, nhỏ hơn hay bằng <=, lớn hơn >, lớn hơn hay bằng >=, không bằng <>, và bằng =. Bạn tham chiếu đến các trường bảng bằng cách đặt tên của chúng trong cặp ngoặc vuông. Điều này không bắt buộc đối với những trường không chứa khoảng cách trong tên, nhưng đây là cách tốt và nhất quán khi tham chiếu một trường. Nó có lợi ích phụ là tên trường bạn viết trong cặp ngoặc vuông sẽ chuyển thành chữ hoa nếu bạn viết đúng tên trường. Để tính một kết quả và hiển thị nó trong câu truy vấn, bạn chỉ việc viết biểu thức vào ô trên dòng Field thuộc lưới QBE trong cửa sổ thiết kế truy vấn.

Bạn định nghĩa trường tính toán bằng cách cung cấp cho Access hai điều: tên trường tính toán và biểu thức tính kết quả. Tên đứng trước biểu thức trong lưới QBE. Theo sau là dấu hai chấm rồi đến biểu thức. Nếu bạn không cung cấp tên trường, Access sẽ tự đặt bắt đầu bằng exp1 và đánh số tăng dần. Cú pháp trường biểu thức như sau:

Tên-trường: biểu-thức

Tên-trường không được trùng với từ khóa Access và biểu-thức là tổ hợp tùy ý gồm các tên trường bảng, toán tử, và hàm Access. Ví dụ, bạn có thể tạo trường tính toán tính giá bán từ giá bán lẻ nhưng bớt 20 phần trăm như sau:

SalePrice: [RetailPrice] * 0.80

SalePrice là tên cột mới trong kết quả truy vấn và RetailPrice là trường thuộc một bảng bao gồm trong câu truy vấn. Giá trị 0.80 là hằng số. Mỗi dòng trong tập động dynaset sẽ hiển thị giá bán, vì vậy nếu có 120 dòng trong tập động dynaset, thì biểu thức sẽ tính 120 giá bán. Biểu thức tính toán có một nhược điểm. Bạn không thể sửa giá trị của biểu thức tính toán trong tập động dynaset, không như các cột phi biểu thức khác trong câu truy vấn.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Xử lý quan hệ nhiều-nhiều

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Xử lý quan hệ nhiều-nhiều

Quan hệ giữa bảng hóa đơn tblInvoice và bảng tồn kho tblInventory là nhiều-nhiều (M-M). Nghĩa là, một hóa đơn bất kỳ có thể chứa nhiều mặt hàng lấy từ kho, và một mặt hàng bất kỳ trong kho có thể xuất hiện trong nhiều hóa đơn. Bất kỳ khi nào xuất hiện M-M giữa hai bảng, bạn phải tạo bảng-quan-hệ. Tối thiểu bảng-quan-hệ phải chứa các khóa chính từ cả hai bảng tblInvoice và tblInventory cho mọi hạng mục trên một hóa đơn cụ thể và cho mọi hóa đơn.

Số dòng trong bảng-quan-hệ sẽ bằng số dòng hóa đơn trong mọi hóa đơn. Các dòng hóa đơn được lưu trong bảng-quan-hệ tblInvoiceLine. Access sẽ khớp số hóa đơn với thuộc tính InvoiceID trong tblInvoiceLine để tra mọi mặt hàng thuộc một hóa đơn cụ thể. InvoiceID là khóa chính của bảng tblInvoice, trong khi thuộc tính InventoryID trong tblInvoiceLine là khóa chính ở tblInventory. Như vậy, InventoryID trong tblInvoiceLine là khóa ngoại. Các thuộc tính khác trong bảng-quan-hệ là số lượng Quantity, đơn giá UnitPrice, và phần trăm giảm giá Discount. Quantity là số lượng một mặt hàng cụ thể trên một dòng hóa đơn đã cho. Đơn giá là giá áp lên mặt hàng đó. Đơn giá có thể khác với cột giá Price lưu trong bảng tblInventory. Trường Discount sẽ lưu phần trăm giảm giá cho một dòng mặt hàng trên một hóa đơn cụ thể. Phần trăm giảm giá thay đổi theo khách hàng và theo năm. Hai thuộc tính thuộc bảng tblInvoiceLine, InvoiceID và InventoryID, tạo nên khóa chính phức hợp, vì cả hai bắt buộc phải có để tạo nên khóa chính cho bảng-quan-hệ tblInvoiceLine.

Nếu bạn gặp các bảng khác có quan hệ nhiều-nhiều nhưng không được hệ quản trị cơ sở dữ liệu quan hệ (RDBMS) xử lý dễ dàng thì việc sửa chữa thật đơn giản. Hãy tạo bảng-quan-hệ chứa khóa chính phức hợp tạo nên từ các khóa chính của hai bảng có quan hệ nhiều-nhiều – như ta đã làm với bảng tblInvoiceLine. Một khi đã tạo bảng-quan-hệ, cả hai bảng ban đầu sẽ có quan hệ một-nhiều với bảng-quan-hệ. Nói cách khác, bảng-quan-hệ là “keo” kết nối hai bảng theo quan hệ một-nhiều.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Hiểu quan hệ một-nhiều

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Hiểu quan hệ một-nhiều

Quan hệ thông dụng nhất là một-nhiều. Trong quan hệ một-nhiều, một dòng thuộc bảng này, gọi là bảng cha, có thể quan hệ với 0, một, hay nhiều dòng thuộc bảng kia, gọi là bảng con. Mặt khác, mỗi dòng hay bản ghi thuộc bảng con quan hệ với đúng một dòng thuộc bảng cha. Thông thường, không bao giờ một dòng trong bảng con chẳng quan hệ với dòng nào trong bảng cha. Nếu có, đây là lỗi khiến dòng bị mồ côi. Chẳng hạn, trong quan hệ một-nhiều giữa khách hàng và hóa đơn, một dòng trong bảng khách hàng có thể quan hệ với 0, một, hay nhiều dòng hóa đơn. Trong trường hợp cuối, khách hàng đã đặt nhiều đơn hàng và có nhiều hóa đơn được xuất. Tuy nhiên, một hóa đơn không bao giờ được phát hiện là không có khách hàng. Nếu xảy ra, công ty có một hóa đơn mà chẳng gắn với khách hàng nào. Nghĩa là mất doanh thu do giao dịch mồ côi. Các ví dụ khác về quan hệ một-nhiều là nhân viên và giao dịch bán hàng, người dân và những lần khám sức khỏe, giảng viên và học viên.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Hiểu quan hệ một-một

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Hiểu quan hệ một-một

Quan hệ một-một tồn tại giữa hai bảng khi mỗi bản ghi trong bảng này có quan hệ với 0 hay tối đa một bản ghi ở bảng kia. Kiểu quan hệ này không thông dụng trong hệ quản trị cơ sở dữ liệu. Thật ra, bảng một-một vi phạm qui tắc chuẩn hóa. Tuy nhiên, đôi khi có lúc nên tách bảng có nhiều cột thành hai hay nhiều bảng có ít cột hơn – tức quan hệ một-một. Phân tách này thường phát sinh để tăng sự an toàn. Chẳng hạn, một số thông tin nhân viên như ngày sinh, tên, và địa chỉ có thể công khai. Tuy nhiên, những thông tin nhạy cảm hơn như lương bổng, vợ chồng con cái, và bảo hiểm y tế cần đặt trong bảng khác dù chúng thường thuộc cùng bảng như các thông tin nhân viên khác. Quan hệ giữa bảng chứa thông tin công khai và bảng chứa thông tin riêng tư là một-một. Tương tự, nếu số cột trong một bảng Access vượt quá 255 cột, bạn buộc phải tách bảng thành hai bảng có quan hệ một-một.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Hiểu quan hệ giữa các bảng

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Hiểu quan hệ giữa các bảng

Bạn hiếm khi gặp một hay nhiều bảng chẳng quan hệ với bảng nào khác. Nếu có một bảng như thế trong hệ cơ sở dữ liệu, bạn phải hỏi tại sao ban đầu lại để xảy ra hiện tượng đó. Thông thường, mọi bảng trong hệ cơ sở dữ liệu đều có quan hệ tối thiểu với một bảng khác. Nếu không, ngoại lệ đó gọi là file phẳng. Một ví dụ về file phẳng là cơ sở dữ liệu đơn bảng chứa thông tin về Incredible Cheesecake Company bạn gặp lần đầu ở Chương 1. Ở đó, các bảng đều ở dạng chuẩn. Hình 4.13 trình bày một phần phiên bản dữ liệu đơn bảng. Hãy để ý mọi trùng lặp trong giá trị cột – nơi lỗi nhập liệu có thể phát sinh. File phẳng này chưa chuẩn hóa vì mỗi dòng có thể chứa thông tin về sản phẩm, khách hàng, và hóa đơn trộn lẫn trong cùng một bảng. Chẳng hạn, rất khó liệt kê danh sách sản phẩm của công ty. Tương tự, có thể “đánh mất” khách hàng khi hóa đơn đã được thanh toán và một hay nhiều dòng hóa đơn được xóa – vì có thể mất thông tin khách hàng. Như vậy, điều đó cho thấy hiếm khi có một bảng độc lập trong hệ thống vận hành thực sự và nếu có, hệ thống đó rất có thể đang có vấn đề!

Hình 4.13 Một cơ sở dữ liệu đơn bảng.

Bảng quan hệ với nhau theo ba cách. Đó là: một-một, một-nhiều, và nhiều-nhiều. Trong khi một-nhiều là kiểu quan hệ thông dụng nhất trong hệ cơ sở dữ liệu quan hệ, một-một và nhiều-nhiều cũng có thể có.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Làm việc với các truy vấn đa bảng

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Làm việc với các truy vấn đa bảng

Cơ sở dữ liệu The Coffee Merchant chứa một số bảng được gọi là ở dạng chuẩn để tránh những vấn đề như dư thừa và bất nhất dữ liệu vốn phát sinh khi dùng bảng phi chuẩn. Dữ liệu xuất hiện trong nhiều bảng có quan hệ với nhau, và cơ sở dữ liệu phải nối các bảng có quan hệ với nhau để tra cứu thông tin xuất hiện trong nhiều bảng. Bạn nối các bảng có quan hệ với nhau bằng cách chỉ định cột chung trong cặp bảng. Trong Access, bạn có thể nối bảng bằng cách hiển thị cấu trúc bảng trong cửa sổ Relationships rồi kéo khóa chính từ bảng này thả vào khóa ngoại của bảng kia (hay ngược lại). Cách này áp dụng cho mọi cặp bảng có quan hệ với nhau. Chẳng hạn, bạn có thể nối bảng khách hàng tblCustomer với bảng hóa đơn tblInvoice trên cột CustID thuộc bảng tblCustomer và trường CustomerID thuộc bảng tblInvoice. Cột xác định dòng nào thuộc bảng này quan hệ với dòng nào thuộc bảng kia ở đây không được đặt trùng tên, nhưng có thể có tên trùng. Sẽ thuận tiện nếu dùng cùng tên để đặt cho các cột liên hệ hai bảng; điều này giúp dễ dàng phát hiện khóa chính khóa ngoại và dễ dàng nối bảng.

Liên kết hai hay nhiều bảng thì dễ. Bạn nối khóa chính của bảng này với khóa ngoại của bảng kia để chỉ định rõ là hai bảng có quan hệ với nhau. Trong nhiều trường hợp, Access có thể tự động xác định mối quan hệ khi hai bảng có trường cùng tên hay chúng có quan hệ thường trực được hình thành nhờ cửa sổ quan hệ Relationships. Nếu Microsoft Access không tự động tạo quan hệ cho bạn, bạn có thể tự nối bảng. Mặt khác, nếu Microsoft Access áp đặt một quan hệ sai giữa hai bảng, bạn có thể dễ dàng phá bỏ quan hệ đó và tạo quan hệ đúng.

Bạn có thể tạo quan hệ thường trực giữa các bảng bằng cách áp đặt các liên kết trong cửa sổ Relationships, hay bạn có thể tạo quan hệ tạm thời giữa các bảng bên trong từng truy vấn. Cách làm sau chỉ tác động đến truy vấn nào có nối bảng, không tác động đến các truy vấn khác. Bạn tự nối bảng trong câu truy vấn bằng cách thêm tất cả các bảng có quan hệ với nhau qua hộp thoại Show Table. Sau đó bạn có thể tạo liên kết giữa từng cặp bảng bằng cách chọn trường khóa chính ở bảng này rồi kéo đến trường khóa ngoại tương ứng ở bảng kia. Bạn tạo quan hệ thường trực bằng cửa sổ quan hệ Relationships để Access ghi nhớ cho mọi đối tượng có tham chiếu đến bảng quan hệ. Chỉ việc kích DATABASE TOOLS rồi kích Relationships trong nhóm Show/Hide. Một khi bạn nối bảng trong cửa sổ Relationships, bạn không cần làm thế nữa trong từng truy vấn. Access sẽ nhớ các quan hệ bảng mà bạn đã tạo trong cửa sổ Relationships cho đến khi bạn xóa chúng một cách tường minh.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính hướng Orientation

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính hướng Orientation

Thuộc tính này qui định liệu các trường được liệt kê từ trái sang phải (mặc định) như trong thiết kế truy vấn hay ngược lại – từ phải sang trái.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính sắp xếp Order By

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính sắp xếp Order By

Hãy gõ tên trường của cột bất kỳ trong câu truy vấn, cách nhau bằng dấu phẩy, để định nghĩa thứ tự sắp ở góc nhìn Datasheet. Việc đặt thứ tự sắp ở góc nhìn Datasheet của câu truy vấn hay bảng sẽ khiến lệnh sắp đã định được lưu vào thuộc tính Order By.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính bộ lọc Filter

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính bộ lọc Filter

Giá trị thuộc tính bộ lọc Filter cung cấp một cách định nghĩa bộ lọc dữ liệu mà không xuất hiện trong lưới QBE. Chẳng hạn, nếu bạn đặt bộ lọc ở góc nhìn Datasheet của một truy vấn hay bảng (kích phải một giá trị cột rồi chọn bộ lọc bất kỳ trong pop-up menu), thì bộ lọc đó được lưu vào thuộc tính Filter. Bộ lọc có thể được bật/tắt trong nhóm Sorter & Filter thuộc HOME tab. Kích Advanced trong nhóm Sort & Filter thuộc HOME tab rồi kích Clear All Filters để xóa giá trị trong thuộc tính Filter.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính dòng duy nhất Unique Rows

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính dòng duy nhất Unique Rows

Mặc định là No, nghĩa là kết quả tập động dynaset gồm nhiều cột có thể trùng lắp khi hiển thị, nhưng thực ra chúng là duy nhất trong các trường không xuất hiện trong tập động dynaset. Nếu đặt thuộc tính này về Yes, các dòng trùng lắp sẽ không được hiển thị. Các giá trị cột trùng lắp vẫn có thể xuất hiện.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính giá trị duy nhất Unique Values

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính giá trị duy nhất Unique Values

Mặc định No nghĩa là các giá trị cột trùng lắp có thể xuất hiện trong tập động dynaset. Đặt về Yes, thì việc xuất hiện giá trị nhiều lần sẽ bị lược bỏ. Chẳng hạn, nếu bạn tạo một truy vấn chỉ hiển thị tên mặt hàng ItemName từ bảng tồn kho tblInventory, được sắp theo thứ tự từ điển, thì nhiều giá trị trùng lắp sẽ xuất hiện trong tập động dynaset. Hầu hết sản phẩm đều có loại có caffeine và không có caffeine. Brazil Bourbon Santos sẽ xuất hiện hai lần trong danh sách đó. Đặt thuộc tính Unique Values về Yes sẽ cho các tên duy nhất.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính các giá trị trên cùng Top Values

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính các giá trị trên cùng Top Values

Giá trị này xác định số dòng hay phần trăm số dòng hiển thị từ trên xuống hay ngược lại. Giá trị 5 nghĩa là 5 dòng trên cùng (hay dưới cùng). Giá trị 5% nghĩa là 5% trên cùng (chẳng hạn 50 dòng trong bảng 1000 dòng). Để có tác dụng, câu truy vấn phải qui định việc sắp xếp trên một hay nhiều trường. Nếu không, số đếm dòng hay phần trăm số dòng trên cùng/dưới cùng sẽ hoàn toàn vô nghĩa. Mặc định All sẽ hiển thị mọi dòng – dĩ nhiên phụ thuộc tiêu chí lọc.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính xuất tất cả các trường Output All Fields

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính xuất tất cả các trường Output All Fields

Được đặt là No, Access sẽ chỉ hiển thị các trường mà bạn chỉ định trên dòng Field thuộc lưới thiết kế truy vấn. Trái lại, Access sẽ hiển thị tất cả các cột vào tập động dynaset từ bảng hay câu truy vấn bên dưới – bất chấp lưới thiết kế.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thuộc tính góc nhìn mặc định Default View

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thuộc tính góc nhìn mặc định Default View

Các chọn lựa là Datasheet, PivotTable, và PivotChart. Vì mặc định là Datasheet, khi bạn bạn đóng và mở lại câu truy vấn pivot table, nó sẽ hiển thị ở góc nhìn Datasheet. Nếu bạn muốn pivot table là góc nhìn mặc định khi kích kép tên câu truy vấn, hãy đặt thuộc tính Default View là PivotTable.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Đặt thuộc tính truy vấn

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Đặt thuộc tính truy vấn

Mọi truy vấn đều có các thuộc tính có sẵn nhằm kiểm soát cách hiển thị câu truy vấn và cách nó tạo tập động dynaset. Bạn có thể rà soát và sửa thuộc tính bất kỳ của câu truy vấn bằng cách mở câu truy vấn ở góc nhìn Design rồi kích Property Sheet (hay nhấn Alt+Enter). Access hiển thị các thuộc tính hiện thời của câu truy vấn trong cửa sổ Property Sheet như trình bày ở Hình 4.12.

Hình 4.12 Các thuộc tính của một truy vấn.

Một vài thuộc tính sẽ kiểm soát cách hiển thị câu truy vấn. Các thuộc tính khác kiểm soát lượng và hướng của tập động dynaset. Chẳng hạn thuộc tính Top Value kiểm soát việc hiển thị tất cả các dòng hay chỉ N phần trăm hoặc N bản ghi đầu tiên. Thuộc tính Orientation qui định các cột trong thiết kế hiển thị từ trái sang phải hay ngược lại – từ phải sang trái. Sau đây là giải thích ngắn gọn về các thuộc tính khác.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Thiết kế và dùng câu truy vấn có tham biến

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Thiết kế và dùng câu truy vấn có tham biến

Đến nay bạn đã khảo sát và tạo truy vấn với các tiêu chí chọn trực tiếp trong khung tiêu chí ở lưới thiết kế truy vấn. Tuy nhiên, bạn có thể tạo một kiểu truy vấn đặc biệt mà cho bạn qui định tiêu chí chọn khi bạn thi hành câu truy vấn, gọi là truy vấn có tham biến (parameter query). Nó sẽ nhắc bạn nhập tiêu chí chọn ngay trước khi thi hành câu truy vấn. Ưu điểm chính của truy vấn có tham biến so với truy vấn thông thường là tính linh hoạt. Chẳng hạn, bạn có thể tạo và thi hành một truy vấn để liệt kê mọi khách hàng ở Minesota. Bằng việc dùng truy vấn làm cơ sở để báo cáo, Access có thể tạo thư mẫu để bạn gửi đến người dân Minesota. Khi bộ phận bán hàng ở vùng tây bắc quyết định thực hiện một xúc tiến thương mại tương tự, họ có thể sửa câu truy vấn, thay “Minesota” bằng “Oregon” trong dòng tiêu chí để truy xuất khách hàng ở vùng đó. Hãy tưởng tượng việc tạo 50 truy vấn như thế để phát sinh danh sách khách hàng ở từng bang. Việc tạo các truy vấn đó sẽ mất thời gian, và cơ sở dữ liệu của bạn sẽ chứa 50 truy vấn hầu như đồng nhất, chỉ khác phần tiêu chí. Truy vấn có tham biến sẽ giảm 50 truy vấn về 1 bằng cách dùng tên bang trong tiêu chí làm “tham biến” được qui định lúc thi hành.

Bạn có thể tạo một truy vấn có tham biến để thay thế toàn bộ 50 truy vấn. Hành động duy nhất yêu cầu người dùng khi thi hành câu truy vấn là gõ tên bang hay tên tắt khi được Access nhắc. Một truy vấn làm công việc cho nhiều câu. Bạn có thể dùng truy vấn có tham biến cho vô vàn ứng dụng kế toán khác. Truy vấn có tham biến là cách hoàn hảo để trích nhóm hóa đơn ở những chu kỳ khác nhau. Chỉ việc tạo truy vấn có tham biến với hai tham biến – thời điểm bắt đầu và kết thúc chu kỳ lập hóa đơn – và ai cũng có thể thi hành câu truy vấn để tra cứu các hóa đơn từ vùng thời điểm qui định. Hơn nữa, bạn có thể hình dung một truy vấn đơn giản nhằm tra cứu thuế suất kinh doanh từ một bảng khi người dùng nhập tên bang hay tên tắt. Chỉ việc nhập tên bang, câu truy vấn sẽ trả về thuế suất kinh doanh ở bang đó.

Cách tốt nhất để hiểu truy vấn có tham biến là tạo và thi hành nó. Bạn sẽ tạo một truy vấn có tham biến để hiển thị danh sách khách hàng ở bang bất kỳ mà người dùng muốn. Khi thi hành câu truy vấn, người dùng được nhắc nhập tên bang viết tắt gồm hai ký tự. Câu truy vấn sẽ tra cứu địa chỉ khách hàng ở bang đó và hiển thị chúng trong tập động dynaset.

BÀI TẬP 4.3: TẠO TRUY VẤN CÓ THAM BIẾN

1. Với cơ sở dữ liệu Chương 4 đang mở, kích CREATE tab, kích Query Design rồi kích kép tblCustomer trong hộp thoại Show Table để thêm danh sách trường của bảng vào vùng truy vấn. Kích nút Close trong hộp thoại Show Table.

2. Để thêm trường vào lưới QBE, kích kép các trường trong danh sách trường theo thứ tự sau: CustFirstName, CustLastName, CustAddress, CustCity, CustState, và CustZip. Access sẽ đặt từng trường vào dòng Fields trong lưới QBE, từ trái sang phải, theo cùng thứ tự khi chúng được kích kép.

3. Gõ [Enter a two-character state abbreviation:] (nhập tên bang tắt gồm hai ký tự) vào dòng Criteria đầu tiên bên dưới cột CustState. Chuỗi ký tự này, được bao bằng cặp ngoặc vuông, là lời nhắc mà người dùng sẽ thấy để nhập tham biến. (Gợi ý: Nhấn Shift+F2 để mở hộp thoại Zoom để dễ dàng thấy được tiêu chí hơn. Bạn có thể kích nút Font để tăng kích cỡ font nhằm đọc dễ hơn. Kích OK khi đọc xong để đóng hộp thoại Zoom.)

4. Kích nút View trong nhóm Results thuộc tab ngữ cảnh DESIGN. Hộp thoại nhập giá trị tham biến Enter Parameter Value xuất hiện.

5. Gõ NE (chữ thường hay hoa đều được) vào hộp thoại Enter Parameter Value. Điều này ngụ ý bạn muốn hiển thị địa chỉ các khách hàng ở Nebraska (xem Hình 4.10). Nhắc lại là chữ hoa hay thường của chuỗi tìm kiếm không quan trọng. Chữ hoa “NE” cũng như “Ne” hay “nE” đều như nhau.

Hình 4.10 Một thiết kế truy vấn có tham biến và lời nhắc lúc thi hành.

6. Kích OK trong hộp thoại Enter Parameter Value để thi hành truy vấn. Nếu bạn tạo đúng câu truy vấn, Access sẽ hiển thị danh sách gồm 11 khách hàng – tất cả đều ở Nebraska (xem Hình 4.11).

Hình 4.11 Tập động dynaset của một truy vấn có tham biến.

7. Kích biểu tượng Save ở thanh công cụ truy xuất nhanh Quick Access Toolbar, gõ MyParameterQuery; rồi kích OK để lưu truy vấn.

8. Kích phải tab truy vấn rồi kích Close All từ pop-up menu để đóng tập động dynaset và tất cả cửa sổ đang mở.

9. Hãy thử một tham biến mới: Kích kép MyParameterQuery trong nhóm Queries. Khi hộp thoại Enter Parameter Value xuất hiện, gõ MT rồi nhấn Enter để thi hành truy vấn nhằm hiển thị mọi khách hàng ở Montana. Có hai khách hàng sống ở Montana.

10. Kích kép tab truy vấn rồi kích Close All từ pop-up menu.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Tạo truy vấn để tìm các dòng trùng lặp

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Tạo truy vấn để tìm các dòng trùng lặp

Các dòng bảng trùng lặp đôi khi lẻn vào bảng, vì vậy truy vấn tìm trùng lặp Find Duplicates có sẵn để định vị chúng cho bạn. Trong khi bạn có thể tự tạo kiểu truy vấn này – bạn cũng có thể tự tạo truy vấn crosstab – sẽ dễ hơn nhiều nếu để wizard làm cho bạn.

Ở đây không yêu cầu bạn thực hành wizard này, nhưng sau đây là các bước. Bạn kích CREATE, kích Query Wizard, kích Find Duplicates Query Wizard, rồi kích OK để mở lên. Kích bảng bạn muốn kiểm tra dòng trùng lặp rồi đánh dấu những trường bạn muốn kiểm tra trùng lặp. Vấn đề chính ở đây là xác định đủ số trường để nhận diện các dòng trùng lặp thật sự, không chỉ các giá trị trùng lặp. Chẳng hạn, nếu bạn kiểm tra trùng lặp trong bảng tồn kho (tblInventory) và chỉ qui định trường ItemName, hầu như mọi dòng sẽ được cho là trùng lặp. Vì hầu hết cà phê trong kho đều có hai dạng có và không có caffeine. Vì thế, dĩ nhiên ngoại trừ khóa chính, hãy chọn hầu hết cột trên dòng để xem có trùng lặp hay không. Nếu bạn tạo truy vấn Find Duplicates trong bảng khách hàng Customer và chỉ chọn trường State để kiểm tra trùng lặp, câu truy vấn sẽ trả về tên bang viết tắt và số khách hàng ở từng bang (Hình 4.9). Truy vấn tiện lợi này dùng cho lý do khác: nó đếm số khách hàng ở từng bang! (Chú thích của người dịch: Truy vấn này không đếm đúng trong trường hợp bang nào đó chỉ có một khách hàng, vì ở đây không có trùng lặp.)

Hình 4.9 Kết quả của một truy vấn Find Duplicates.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Tạo truy vấn Crosstab

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Tạo truy vấn Crosstab

Truy vấn crosstab là một truy vấn tổng kết đặc biệt nhằm duyệt qua lượng lớn dữ liệu và lập tương quan các giá trị giữa hai hay nhiều tập giá trị trường. Có dạng và chức năng tương tự bảng pivot Excel, truy vấn crosstab của Access cung cấp kết quả tổng kết dưới dạng spreadsheet dễ dùng. Crosstab có thể tính tổng, trung bình, đếm, và các giá trị gộp khác, được nhóm thành hai kiểu thông tin, một ở bên trái của datasheet và một ở trên đỉnh. Ô giao giữa dòng và cột hiển thị kết quả tính toán của câu truy vấn. Chẳng hạn, bạn có bảng doanh số sales lưu doanh số bán ô tô, phương tiện đi lại để tiêu khiển, và tàu thuyền theo năm, theo quý, và theo địa phương. Dữ liệu thô chỉ có thế - không cung cấp nhiều thông tin. Việc tạo truy vấn crosstab tổng kết doanh số theo quý và theo sản phẩm có thể hé mở qui luật theo mùa, nếu có. Bài tập kế sẽ chỉ cách tạo truy vấn crosstab trên doanh số. Mặc dù bảng này không liên quan các bảng khác trong cơ sở dữ liệu The Coffee Merchant, nó minh họa cách bạn có thể áp dụng kỹ thuật tương tự để phân tích doanh số của The Coffee Merchant để phát hiện qui luật.

BÀI TẬP 4.2: DÙNG QUERY WIZARD ĐỂ TẠO TRUY VẤN CROSSTAB

1. Kích CREATE tab, kích Query Wizard, kích Crosstab Query Wizard, rồi kích OK. Access sẽ thi hành Crosstab Query Wizard.

2. Định vị và kích bảng tblExampleCrosstab từ danh sách bảng trong cơ sở dữ liệu. (Nếu bạn chỉ thấy các truy vấn, kích nút radio Tables trong khung View để chỉ hiển thị bảng.) Kích Next.

3. Trong danh sách Available Fields, kích ItemQuarter – trường mà các giá trị duy nhất của nó sẽ xuất hiện trên đầu đề dòng  - rồi kích > để thêm trường vào danh sách Selected Fields. Kích Next để đến bước kế. (Kích Back nếu bạn muốn sửa bước trước.)

4. Kích ItemCategory, trường mà các giá trị duy nhất của nó sẽ xuất hiện trên đầu đề cột, rồi kích Next.

5. Kích ItemSaleAmount trong danh sách trường Fields, giá trị bạn muốn tổng kết, kích Count trong danh sách hàm Functions để đếm số lượt bán thay vì tính tổng. Hộp thoại Crosstab Query Wizard của bạn đến nay cần giống Hình 4.7. Kích Next để tiếp tục.

Hình 4.7 Định nghĩa một truy vấn crosstab.

6. Sửa tên truy vấn ở trên đỉnh thành MyCrosstabQuery, rồi kích Finish.

7. Kích cạnh phải của nhãn Total of ItemSaleAmount để thấy toàn bộ nhãn. Nếu bạn tạo truy vấn crosstab thành công, thì số lượt bán tàu thuyền Boat, ô tô Car, và phương tiện đi lại để tiêu khiển RV của quí một Quarter 1 là 128, 348, và 144.

8. Kích Save trên thanh công cụ truy xuất nhanh Quick Access Toolbar để lưu những thay đổi về dàn trang.

9. Đóng tập động dynaset bằng cách kích phải tab hiển thị câu truy vấn rồi kích Close All.

Tập động dynaset được hiển thị bởi truy vấn crosstab cho biết số lượt bán trong từng quí theo kiểu sản phẩm. Để ý rằng tàu thuyền Boat có số lượt bán thấp ở quí 1 và 4 - phương tiện đi lại để tiêu khiển RV cũng vậy (xem Hình 4.8).

Hình 4.8 Truy vấn crosstab hiển thị số lượt bán theo quí và theo sản phẩm.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Tạo truy vấn bằng Query Wizards

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Tạo truy vấn bằng Query Wizards

Nhờ hỗ trợ của Query Wizards mà ta có thể tạo ba kiểu truy vấn khó tạo bằng tay. Ba kiểu truy vấn của Query Wizards, bên cạnh “Simple Query Wizard”, là Crosstab, Find Duplicates, và Find Unmatched Query. Ba lựa chọn này xuất hiện khi bạn kích nút Query Wizard trong nhóm Queries thuộc CREATE tab – ở ngay bên trái nút Query Design. Ta sẽ xét hai trong số các wizards này ngay sau đây, qua các ví dụ minh họa sự dễ dàng khi tạo và thực hiện truy vấn với sự hỗ trợ của wizard. Sau đây là các bước cơ bản:

• Kích CREATE tab rồi kích Query Wizard.
• Chọn wizard bạn muốn từ danh sách bốn lựa chọn.
• Kích OK rồi theo lời nhắc và các bước mà wizard dùng để tạo truy vấn.

Tới đây, ta sẽ khảo sát hai kiểu truy vấn Crosstab và Find Duplicates. Ta bắt đầu với việc tạo truy vấn Crosstab.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - In tập động dynasets

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

In tập động dynasets

In kết quả truy vấn nghĩa là bạn in tập động dynaset do câu truy vấn tra cứu và hiển thị ở góc nhìn Datasheet. Bạn không cần mở câu truy vấn để hiển thị tập động dynaset của nó. Mặt khác, bạn có thể muốn xem trước kết quả và quyết định có muốn in tất cả các trang hay không. Trong cả hai trường hợp, kích tên câu truy vấn trong Navigation Pane, kích FILE tab, kích Print tab, rồi kích nút Print ở khung bên phải. Khi hộp thoại Print xuất hiện, hiệu chỉnh vùng trang, rồi kích OK. Tuy nhiên, bạn nên kích Print Preview ở khung bên phải, thay vì Print, để kiểm tra việc dàn trang và hình thức của tập động dynaset trước khi quyết định in. Để ý rằng bạn không thể sửa phần đầu đề và phần chân của trang in tập động dynaset. Thao tác in bảng và tập động dynaset là cách xuất nhanh kết quả mà không có các đặc trưng hấp dẫn như trong báo cáo Access.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Lưu câu truy vấn

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Lưu câu truy vấn

Bạn nên lưu những truy vấn nào có thể được dùng lại. Bạn sẽ phát hiện cách lưu mà mình ưa thích. Một cách đơn giản là kích phải tab rồi kích Save. Save cũng có mặt trên thanh công cụ truy xuất nhanh Quick Access Toolbar. Nếu bạn đã lưu câu truy vấn trước đây, thì Access sẽ lưu với tên đó, thay bản cũ bằng bản mới. Nếu bạn muốn lưu thiết kế truy vấn dưới tên khác, thì kích FILE tab, kích Save As, kích Save Object As, rồi kích nút Save As. Gõ tên mới vào ô được đánh dấu rồi kích OK để lưu dưới tên mới.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Sửa thuộc tính hiển thị cột

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Sửa thuộc tính hiển thị cột

Như những đặc trưng tập động dynaset khác, các thuộc tính hiển thị cột cũng có thể được sửa. Đầu tiên, hãy hiển thị câu truy vấn ở góc nhìn Design. Rồi di chuột đến dòng Field trong lưới QBE và đến cột mà bạn muốn sửa định dạng. Khi bạn kích phải cột, một pop-up menu hiển thị một số chọn lựa. Kích Properties để mở Property Sheet của cột. Bạn có thể thử sửa các đặc trưng. Chẳng hạn, hãy sửa định dạng của cột tập động dynaset InventoryID để dữ liệu hiển thị dưới dạng tiền tệ Currency. Sau khi thử nghiệm, bạn không cần lưu câu truy vấn đã sửa.

Phát triển HTTT kế toán bằng MS Access - Chương 4 - Sửa thứ tự và kích cỡ cột

James Perry, Richard Newmark

Chương 4
Tạo và dùng câu truy vấn

Sửa thứ tự và kích cỡ cột

Bạn có thể sửa thứ tự cột trong tập động dynaset. Thứ tự cột trong tập động dynaset được thiết lập bởi câu truy vấn. Chẳng hạn, cột đầu tiên với ô Show được đánh dấu trong lưới QBE sẽ là cột đầu tiên trong tập động dynaset, nhưng bạn có thể sửa thứ tự cột trong tập động dynaset bằng cách sửa thiết kế của câu truy vấn hay sửa thứ tự cột trong tập động dynaset sau khi Access hiển thị tập động dynaset. Phương pháp đầu là cách tốt nhất.

Để bố trí lại các cột truy vấn, hãy hiển thị truy vấn ở góc nhìn Design rồi di con trỏ chuột đến nút chọn cột, nó chuyển thành mũi tên trỏ xuống. Kích cột. Toàn bộ cột được làm tối, báo hiệu là bạn đã chọn nó. (Cẩn thận đừng di chuyển chuột.) Thả chuột nhưng vẫn để nó ở vị trí nút chọn cột. Rồi kích và kéo cột đến vị trí mới. Một hình chữ nhật xuất hiện bên dưới con trỏ, báo hiệu là bạn chuẩn bị di chuyển cột, và một đường thẳng đứng màu đen xuất hiện khi bạn kéo cột, chỉ ra vị trí cột nếu bạn thả chuột. Thả chuột khi cột ở đúng vị trí mới. Các cột bên phải đường thẳng đứng sẽ chuyển sang phải, tạo khoảng trống để đặt cột mới vào.

Bạn có thể mở rộng một cột bằng cách di đến vùng chọn cột của cột đó và đưa chuột đến cạnh phải của nút chọn cột. Khi chuột đổi từ mũi tên trỏ xuống thành mũi tên hai đầu, kích và kéo cạnh phải sang phải để mở rộng cột, hay kéo sang trái để thu hẹp cột. Ta đã đề cập trước đây là bạn có thể kích kép con trỏ chuột mũi tên hai đầu để điều chỉnh độ rộng cột theo giá trị dài nhất (hay theo nhãn cột).

Bạn có thể sửa thứ tự cột cũng như kích cỡ cột trong tập động dynaset – sau khi Access thi hành câu truy vấn. Khi kết quả truy vấn xuất hiện, bạn có thể di chuyển cột hay sửa kích cỡ cột bằng cách làm theo hướng dẫn ở đoạn trên. Bạn có thể kích phải tên cột bất kỳ (không phải giá trị trong cột), rồi kích Field Width trong pop-up menu. Hộp thoại Column Width xuất hiện (xem Hình 4.6). Kích nút Best Fit để định cỡ cột đủ rộng cho dữ liệu dài nhất. Bạn cũng có thể định cỡ nhiều cột một lần. Kéo chuột qua các nút chọn cột để chọn nhiều cột liên tục. Di chuyển chuột đến cạnh phải của cột bất kỳ đã chọn. Khi nó chuyển thành mũi tên hai đầu, kích kép con trỏ để tối ưu độ rộng cột cho tất cả các cột đã chọn.

Hình 4.6 Hộp thoại Column Width.