Giúp cho dữ liệu tóm tắt trở nên dễ đọc hơn bằng cách dùng truy vấn chéo bảng

Bằng cách sử dụng dạng xem Thiết kế để tạo truy vấn chéo bảng, bạn có thể sử dụng nhiều nguồn bản ghi (bảng và truy vấn) như bạn muốn. Tuy nhiên, bạn có thể duy trì thiết kế đơn giản bằng cách trước tiên tạo một truy vấn chọn trả về tất cả dữ liệu mà bạn muốn, rồi sử dụng truy vấn đó làm nguồn bản ghi duy nhất cho truy vấn chéo bảng của bạn. Để biết thêm thông tin về cách tạo truy vấn chọn, hãy tham khảo mục Xem Thêm.

Khi bạn xây dựng một truy vấn chéo bảng trong dạng xem Thiết kế, bạn sử dụng hàng Tổng và Chéo bảng trong lưới thiết kế để xác định giá trị của trường nào sẽ trở thành đề mục cột, giá trị trường nào sẽ trở thành đề mục hàng và giá trị trường nào dùng để cộng, tính trung bình, đếm hoặc tính toán khác.

1. Thiết đặt trong những hàng này quyết định xem trường này sẽ là đề mục hàng, cột hay giá trị tóm tắt.

2. Thiết đặt này hiển thị giá trị của trường làm đề mục hàng.

3. Thiết đặt này hiển thị giá trị của trường làm đề mục cột.

4. Những thiết đặt này cung cấp giá trị tóm tắt.

Tạo truy vấn

1. Trên tab Tạo, trong nhóm Truy vấn, bấm vào Thiết kế Truy vấn.

   

2. Trong hộp thoại Hiện Bảng, bấm đúp vào từng bảng hoặc truy vấn mà bạn muốn dùng làm nguồn bản ghi.

   Nếu bạn dùng nhiều nguồn bản ghi, đảm bảo rằng các bảng hoặc truy vấn được liên kết trên các trường mà chúng cùng có. Để biết thêm thông tin về cách kết hợp truy vấn và bảng, tham khảo mục Xem Thêm.

3. Đóng hộp thoại Hiện Bảng.

4. Trên tab Thiết kế, trong nhóm Kiểu Truy vấn, hãy bấm Chéo bảng.

5. Trong cửa sổ thiết kế truy vấn, bấm đúp vào mỗi trường mà bạn muốn dùng làm nguồn của đề mục hàng. Bạn có thể chọn tối đa ba trường cho đề mục hàng.

6. Trong lưới thiết kế truy vấn, trong hàng Chéo bảng cho mỗi trường đề mục hàng, chọn Đề mục Hàng.

   Bạn có thể nhập một biểu thức trong hàng Tiêu chí để hạn chế kết quả cho trường đó. Bạn cũng có thể dùng hàng Sắp xếp để xác định thứ tự sắp xếp cho một trường.

7. Trong cửa sổ thiết kế truy vấn, bấm đúp vào mỗi trường mà bạn muốn dùng làm nguồn của đề mục cột. Bạn có thể chọn một trường duy nhất cho đề mục cột.

8. Trong lưới thiết kế truy vấn, trong hàng Chéo bảng cho mỗi trường đề mục cột, chọn Đề mục cột.

   Bạn có thể nhập một biểu thức trong hàng Tiêu chí để hạn chế kết quả cho trường đó. Tuy nhiên, dùng một biểu thức tiêu chí với trường đề mục cột không hạn chế số lượng cột mà truy vấn chéo bảng trả về. Thay vào đó, nó hạn chế cột có chứa dữ liệu. Ví dụ: giả sử bạn có một trường đề mục cột có chứa ba giá trị có thể: đỏ, lục và lam. Nếu bạn áp dụng tiêu chí ='lam' cho trường đề mục cột, chéo bảng vẫn hiển thị một cột màu đỏ và một cột màu lục nhưng chỉ cột màu lam là có chứa dữ liệu.

   Nếu bạn muốn giới hạn các giá trị hiển thị dưới dạng đầu đề cột, bạn có thể xác định danh sách các giá trị cố định bằng thuộc tính Đầu đề Cột của truy vấn. Để biết thêm thông tin, hãy xem mục tiếp theo.

9. Trong cửa số thiết kế truy vấn, bấm đúp vào trường mà bạn muốn dùng để tính giá trị tóm tắt. Bạn có thể chọn một trường duy nhất để dùng cho giá trị tóm tắt.

10. Trong lưới thiết kế truy vấn, trong hàng Tổng của trường giá trị tóm tắt, bạn chọn một hàm tập hợp dùng để tính giá trị.

11. Trong hàng Chéo bảng cho trường giá trị tóm tắt, chọn Giá trị.

    Bạn không thể xác định tiêu chí hoặc sắp xếp trong trường giá trị tóm tắt.

12. Trên tab Thiết kế, trong nhóm Kết quả, bấm Chạy.

Xác định giá trị cố định cho đầu đề cột

Nếu bạn muốn xác định giá trị cố định để dùng cho đề mục cột, bạn có thể đặt thuộc tính Đề mục Cột của truy vấn.

1. Mở truy vấn chéo bảng trong dạng xem Thiết kế.

2. Nếu bạn không nhìn thấy bảng thuộc tính, bấm F4 để hiển thị nó.

3. Trong bảng thuộc tính, ngay ở trên tab Chung, đảm bảo rằng loại Vùng chọn là Thuộc tính Truy vấn. Nếu không phải, bấm vào điểm trống trong dấu cách bên trên lưới thiết kế truy vấn.

4. Trong bảng thuộc tính, trên tab Chung, trong thuộc tính Đề mục Cột, nhập danh sách giá trị phân tách bằng dấu phẩy mà bạn muốn dùng làm đề mục cột.

   Trong đề mục cột, có vài ký tự (ví dụ như hầu hết các dấu chấm câu) bạn không được phép sử dụng. Nếu bạn dùng những ký tự này trong danh sách giá trị , Access dùng dấu gạch dưới (_) thay thế cho mỗi ký tự đó.

Cú pháp SQL cho truy vấn chéo bảng

Truy vấn chéo bảng được thể hiện trong SQL dưới dạng câu lệnh TRANSFORM. Câu lệnh TRANSFORM có cú pháp sau đây:

Selectstatement chuyển đổi chế
độ chuyển đổi
Pivotfield [IN (value1[, value2[, ...]])]

Câu lệnh TRANSFORM có các phần sau:

Phần	Mô tả
aggfunction	Hàm tổng hợp SQL hoạt động trên dữ liệu đã chọn.
selectstatement	Câu lệnh SELECT.
pivotfield	Trường hoặc biểu thức bạn muốn sử dụng để tạo đầu đề cột trong tập kết quả của truy vấn.
value1, value2	Các giá trị cố định được sử dụng để tạo đầu đề cột.

Dạng xem SQL không giới hạn số bảng hay truy vấn mà bạn có thể sử dụng làm nguồn bản ghi cho truy vấn chéo bảng. Tuy nhiên, bạn có thể giúp duy trì thiết kế đơn giản bằng cách tạo một truy vấn chọn trả về tất cả dữ liệu mà bạn muốn sử dụng trong truy vấn chéo bảng, rồi sử dụng truy vấn chọn đó làm nguồn bản ghi. Để biết thêm thông tin về cách tạo truy vấn chọn, hãy tham khảo mục Xem Thêm.

1. Trên tab Tạo, trong nhóm Khác, bấm vào Thiết kế Truy vấn.

2. Đóng hộp thoại Hiện Bảng.

3. Trên tab Thiết kế, trong nhóm Dạng xem, bấm vào Dạng xem, rồi bấm vào Dạng xem SQL.

4. Trong tab Đối tượng SQL, nhập hoặc dán SQL sau đây:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. Trên dòng đầu tiên, sau TRANSFORM, nhập biểu thức sử dụng để tính toán giá trị tóm tắt; ví dụ: Sum([Số lượng]).

   Nếu bạn đang sử dụng nhiều bảng hoặc truy vấn làm nguồn bản ghi, hãy bao gồm tên bảng hoặc truy vấn như một phần của từng tên trường; ví dụ: Sum([Chi phí].[ Số lượng]).

6. Trên dòng thứ hai, sau SELECT, nhập danh sách các trường hoặc biểu thức trường mà bạn muốn sử dụng cho đầu đề hàng. Tách riêng các mục danh sách bằng dấu phẩy; ví dụ: [Ngân sách].[ID_Bộ_phận], [Chi phí].[ Loại].

7. Trên dòng thứ ba, sau FROM, nhập danh sách các bảng hoặc truy vấn mà bạn đang sử dụng làm nguồn bản ghi; ví dụ: Ngân sách, Chi phí.

8. Trên dòng thứ tư, sau GROUP BY, nhập cùng danh sách các trường mà bạn đã sử dụng trong mệnh đề SELECT, ở bước 6.

9. Trên dòng thứ năm, sau PIVOT, nhập tên trường hoặc biểu thức mà bạn muốn sử dụng cho đầu đề cột; ví dụ: PIVOT [Ngân sách].[Năm].

Đôi khi, thay vì sử dụng mỗi giá trị của một trường cho đầu đề hàng và cột, bạn muốn nhóm các giá trị của trường thành phạm vi, rồi dùng phạm vi đó cho đầu đề hàng hoặc cột. Ví dụ: giả sử bạn dùng trường "Tuổi" cho đầu đề cột. Thay vì dùng một cột cho mỗi độ tuổi, bạn có thể muốn sử dụng các cột đại điện cho phạm vi độ tuổi.

Bạn có thể dùng Hàm IIf trong một biểu thức để tạo phạm vi sử dụng cho đầu đề hàng hoặc cột.

1. Mở truy vấn chéo bảng trong dạng xem Thiết kế.

2. Trong lưới thiết kế truy vấn, trong hàng Trường, bấm chuột phải vào một cột trống rồi bấm Thu phóng trên menu lối tắt.

3. Trong hộp Thu phóng, nhập bí danh trường, sau đó là dấu hai chấm (:).

4. Nhập IIf().

5. Bên trong dấu ngoặc đơn đằng sau IIf, nhập một biểu thức so sánh xác định phạm vi đầu tiên của giá trị trường.

   Ví dụ: giả sử bạn đang tạo phạm vi cho trường Tuổi và bạn muốn mỗi phạm vi là hai mươi năm. Biểu thức so sánh cho phạm vi đầu tiên là [Tuổi]<21.

6. Nhập một dấu phẩy sau biểu thức so sánh rồi nhập tên cho phạm vi, nằm trong dấu ngoặc kép. Tên bạn cung cấp là đề mục chéo bảng cho những giá trị nằm trong phạm vi.

   Ví dụ: sau [Tuổi]<21, nhập dấu phẩy, rồi nhập "0-20 tuổi".

7. Nhập một dấu phẩy sau tên phạm vi (bên ngoài dấu ngoặc kép) rồi làm theo một trong những cách sau:

   • Để tạo phạm vi khác, nhập IIf(), rồi lặp lại bước 5, 6 và 7.

   • Với phạm vi cuối cùng, chỉ nhập tên cho phạm vi.

     Ví dụ: biểu thức IIf được lồng hoàn toàn vào nhau phân tách trường Tuổi thành phạm vi hai mươi tuổi có thể trông như thế này (thêm ngắt dòng cho dễ đọc):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     Lưu ý: Khi đánh giá biểu thức, Access ngừng đánh giá ngay khi một trong những câu lệnh IIf đánh giá biểu thức là đúng. Bạn không cần xác định mức thấp nhất của mỗi phạm vi vì bất cứ giá trị nào rơi xuống dưới mức thấp nhất của một phạm vi đã cho đều được đánh giá là đúng.

8. Trong lưới thiết kế truy vấn, trong hàng Tổng, chọn Nhóm Theo.

9. Trong hàng Chéo bảng, xác định xem liệu có dùng phạm vi làm đề mục hàng hoặc cột hay không. Hãy nhớ là bạn có thể xác định từ một đến ba đề mục hàng và một đề mục cột.

Có thể bạn muốn truy vấn chéo bảng nhắc nhập dữ liệu nhập khi nó chạy. Ví dụ: giả sử bạn đang sử dụng vài đề mục hàng, một trong số đó là Quốc gia/Vùng. Thay vì luôn hiển thị dữ liệu cho mỗi quốc gia hoặc vùng, có thể bạn muốn truy vấn nhắc nhập tên rồi hiển thị dữ liệu theo dữ liệu nhập của người dùng.

Bạn có thể thêm dấu nhắc tham số vào bất cứ trường đề mục hàng nào.

1. Mở truy vấn chéo bảng trong dạng xem Thiết kế.

2. Trong hàng Tiêu chí cho trường đề mục hàng mà bạn muốn nhắc người dùng về dữ liệu nhập, hãy nhập nội dung câu hỏi nằm trong dấu ngoặc vuông. Nội dung câu hỏi sẽ xuất hiện dưới dạng dấu nhắc khi chạy truy vấn.

   Ví dụ: nếu bạn nhập [Which country or region?] trong hàng Tiêu chí, khi chạy truy vấn, một hộp thoại sẽ xuất hiện bao gồm câu hỏi "Which country or region?," hộp dữ liệu nhập và nút OK.

   Mẹo: Nếu bạn muốn tham số linh hoạt, hãy kết hợp biểu thức của bạn với một ký tự đại diện bằng cách dùng toán tử Like. Ví dụ: thay vì sử dụng [Which country or region?] làm tiêu chí của mình, bạn có thể dùng Like [Which country or region?]&"*" để làm cho tham số khớp với phạm vi lớn hơn của dữ liệu nhập. Dùng Like không làm thay đổi hình thức của dấu nhắc tham số.

3. Trên tab Thiết kế, trong nhóm Hiện/Ẩn, bấm Tham số.

4. Trong hộp thoại Tham số Truy vấn, trong cột Tham số, nhập cùng một dấu nhắc tham số mà bạn đã dùng trong hàng Tiêu chí. Bao gồm dấu ngoặc vuông nhưng không bao gồm bất cứ ký tự đại diện đã kết hợp nào hoặc toán tử Like.

5. Trong cột Loại Dữ liệu, chọn loại dữ liệu cho tham số. Loại dữ liệu phải khớp với loại dữ liệu của trường đề mục hàng.

Nếu trường mà bạn dùng để tính giá trị tóm tắt trong truy vấn chéo bảng có chứa giá trị null, bất cứ hàm tập hợp nào mà bạn dùng cũng bỏ qua những giá trị này. Một vài hàm tập hợp có thể làm ảnh hưởng đến kết quả của bạn. Ví dụ: để tính trung bình, bạn thêm tất cả giá trị và chia kết quả theo số lượng giá trị. Nhưng nếu trường có chứa bất cứ giá trị null nào thì những giá trị null này không được tính là một phần của số lượng giá trị.

Trong một số trường hợp, có thể bạn thích thay thế bất cứ giá trị null nào bằng số không hơn để tính giá trị trong các phép tính tập hợp. Bạn có thể dùng hàm Nz để thay thế giá trị null bằng số không.

Cú pháp Nz

Nz ( variant [, valueifnull ] )

Cú pháp hàm Nz có những đối số sau:

Tham đối	Mô tả
variant	Bắt buộc. Biến số của kiểu dữ liệuVariant.
valueifnull	Tùy chọn (trừ khi được dùng trong truy vấn). Một Variant cung cấp giá trị được trả về nếu đối số variant là Null. Đối số này cho phép bạn trả về một giá trị ngoài số không hoặc một chuỗi có độ dài bằng không. Lưu ý: Nếu bạn dùng hàm Nz trong một biểu thức trong truy vấn mà không dùng đối số valueifnull, kết quả sẽ là một chuỗi có độ dài bằng không trong trường có chứa giá trị null.

1. Với truy vấn đang mở trong dạng xem Thiết kế, trong lưới thiết kế truy vấn, bấm đúp vào trường Giá trị.

2. Trên menu lối tắt, bấm Thu phóng.

3. Trong hộp Thu phóng, dùng dấu ngoặc đơn quanh tên trường hoặc biểu thức, rồi nhập Nz trước dấu ngoặc đơn.

4. Nhập , 0 ngay bên trong dấu ngoặc đơn phải.

   Ví dụ: nếu bạn đã dùng Nz với một trường có tên là "Hours Lost" để biến giá trị null thành số không, biểu thức đã hoàn chỉnh sẽ trông như sau:

Nz([Hours Lost], 0)

• Hãy đơn giản hóa    Truy vấn chéo bảng có thể trở nên khó đọc khi số lượng kết hợp các hàng tăng lên. Không dùng nhiều đề mục hàng hơn mức cần thiết.

• Cân nhắc việc xây dựng chéo bảng của bạn theo các bước    Đừng giới hạn chính mình bằng việc chỉ sử dụng bảng. Bạn thường bắt đầu bằng cách xây dựng một truy vấn tổng rồi dùng truy vấn đó làm nguồn bản ghi cho truy vấn chéo bảng.

• Chọn trường đề mục cột của bạn thật cẩn thận    Biểu dữ liệu chéo bảng có xu hướng dễ đọc hơn khi số lượng đề mục cột vẫn còn khá nhỏ. Khi bạn xác định các trường mà bạn muốn dùng làm đề mục, hãy cân nhắc dùng trường có giá trị riêng biệt ít nhất để tạo đề mục cột. Ví dụ: nếu truy vấn của bạn tính toán một giá trị theo tuổi và giới tính, hãy cân nhắc dùng giới tính cho đề mục cột thay cho tuổi vì giới tính thường có ít giá trị hơn tuổi.

• Sử dụng truy vấn con trong mệnh đề WHERE    Bạn có thể sử dụng truy vấn con như một phần của mệnh đề WHERE trong truy vấn chéo bảng.

Đầu Trang