Về kỹ thuật tạo báo cáo hàng ngang, dữ liệu động dùng access, tôi xin phép được trình bày vài điều căn bản nhé.
1. Đặt vấn đề
+ Tôi có một bảng dữ liệu có một số cột là Tên lớp, Số học sinh ….
+ Tôi muốn in ra báo cáo theo đó các cột về lớp sẽ hiển thị.
+ Tôi muốn lọc ra các danh sách trong đó điều kiện là số học sinh.
2. Phân tích
Do việc chúng ta chọn danh sách lớp làm số cột và có quản lý điều kiện thông qua số học sinh vì thế với các điều kiện khác nhau thì số lớp hiển thị cũng khác nhau.
Về mặt kỹ thuật, điều này có thể giải quyết tốt đối với các báo cáo có số cột cố định và lớp sẽ hiển thị theo dòng.
Trong báo cáo của access, chúng sử dụng các cột làm các nhân tố phát xuất dữ liệu để in vì thế nó đòi hỏi dữ liệu nguồn cho cột báo cáo là cố định, vì thế ta có thể tiến tới một kết luận có vẻ khá buồn bã là: Muốn hiển thị được điều bạn làm thì phải thiết kế ra tất cả các báo cáo ứng với mỗi điều kiện bạn lựa chọn… Khó quá – điều này là không khả thi.
Vâng, quả thật làm sao mà không loay hoay được cơ chứ… Nhưng thật may mắn – công cụ VBA và các dạng truy vấn lại có thể giúp chúng ta làm điều này một cách dễ dàng. Các bạn xem này.
Trong lần trả lời của ai đó trước đấy có đề cập đến Pivot table, vâng đúng vậy, Access cũng có một công cụ như vậy song nó đựơc đặt tên là Crosstab Querry.
Ta hãy xem một đoạn Query như thế này:
TRANSFORM Sum(tbl_Basic.Figures) AS SumOfFigures
SELECT tbl_Basic.Heading, tbl_Basic.UpperID
FROM tbl_Basic
WHERE (((nz([IsFeedBack],0))=0) AND ((tbl_Basic.Level)=4) AND ((tbl_Basic.UpperID)=2))
GROUP BY tbl_Basic.Heading, tbl_Basic.UpperID
PIVOT tbl_Basic.ID;
TRANSFORM Sum(tbl_Basic_His.Figures) AS SumOfFigures
SELECT tbl_Basic_His.ID, tbl_Basic_His.Heading
FROM tbl_Basic_His
WHERE (((tbl_Basic_His.Level)=3) AND ((tbl_Basic_His.ID)=2))
GROUP BY tbl_Basic_His.ID, tbl_Basic_His.Heading
ORDER BY tbl_Basic_His.Heading
PIVOT tbl_Basic_His.Year In (2005,2004,2003);
Cách tạo ra truy vấn Crosstab
Cú pháp
TRANSFORM [hàm tính tổng]
[mệnh đề select]
PIVOT [trường hiển thị dạng cột] [IN (giá trị 1[, giá trị 2[, ...]])]
..
Các phần của cú pháp
Hàm tính tổng: Là một hàm tính tổng dạng SQL sẽ thực thi tính toán đối với dữ liệu đã lựa chọn.
Mệnh đề Select: Là một mệnh đề truy vấn Select.
Trường hiển thị dạng cột: Là trường hay biểu thực bạn muốn sử dụng làm cột trong kết quả truy vấn.
Giá trị 1, giá trị 2: Giá trị cố định dùng để làm đề mục tên cột hiển thị.
Lưu ý
Khi bạn tóm tắt dữ liệu bằng truy vấn crosstab, bạn sẽ chọn các giá trị từ các trường cụ thể nào đó hay biểu thức làm cột vì thế bạn có thể xem được dữ liệu dạng gọn gàng hơn so với một truy vấn dạng Select.
Cú pháp Transform là tuỳ chọn nhưng khi được đưa vào làm mệnh đề đầu tiên trong chuỗi truy vấn, nó sẽ thực hiện truy vấn Select theo trường được đặc tả để hiển thị dòng và mệnh đề GROUP BY theo đó quy định nhóm dòng lại.
Ngoài ra, bạn có thể bổ sung các mệnh đề khác như WHERE để đặc tả điều kiện. Các giá trị trả về trong trường đã chọn làm cột theo một dãy giá trị đặc tả trong cú pháp IN().
Ví dụ bạn có thể chuyển dòng cột các số liệu bán hàng của các tháng trong năm bằng cách sử dụng một cú pháp truy vấn Crosstab để hiển thị lượng bán trong 12 tháng với việc dùng thêm cấu trúc IN (1,2,3....11,12).
Quay lại phần trên các bạn có thể thấy việc tạo ra một báo cáo chung nhất để hiển thị dữ liệu theo hàng ngang là tương đối dễ dàng bất kể số lượng cột có thể thay đổi. Tuy nhiên chúng ta cần lưu ý một số vấn đề sau:
+ Xác định số cột tối đa mà khổ giấy A4 có thể hiển thị để giới hạn kết quả in ra. Ví dụ ta có 16 lớp trong khi chỉ có thể in được 12 cột trên một trang giấy chẳng hạn.
+ Đặt sẵn ra 12 textbox có mục controlsource để dạng Unbound.
+ Thiết kế truy vấn Crosstab.
+ Sau đó bạn tiến hành viết một số thủ tục Visual Basic để gán cho textbox controlSource là các trường kết quả của Query trên (Lưu ý là tên lớp cần phải được chuẩn hoá ví dụ 11A, 12B, 11C, 9A...)
Về thủ tục này - các bạn có thể tham khảo trong phần mềm VDP tôi có đăng tại
www.sfdp.net hoặc trao đổi cụ thể với tôi qua email, vì nó cũng khá dài và phức tạp do yêu cầu của phần mềm đó.
Và thế là xong ....
Tư tưởng của thủ tục này cũng không có gì khá phức tạp. Tôi mạn phép trích ra vài dòng để các bạn xem nhé:
Quy trình tiến hành như sau:
- Tạo lập một Crosstab Query với các tham số sau:
TRANSFORM Sum(tbl_class.AverageMark) AS SumOfAverageMark
SELECT tbl_class.NumberofStudent, Sum(tbl_class.AverageMark) AS [Total Of AverageMark]
FROM tbl_class
GROUP BY tbl_class.NumberofStudent
PIVOT tbl_class.ClassName;
- Dùng wizard để thiết kế báo cáo với querry trên (chọn tối đa 8 trường thôi) và thay đổi các đối tượng như sau:
- Các Textbox được đặt tên là txtxx trong đó xx là số thứ tự ví dụ txt1, txt2
- Đặt vào báo cáo các nhãn có tên cũng tương tự ví dụ lblxx
Trong phần sự kiện report_open, ta đặt các thủ tục để tiến hành gán thuộc tính controlsource cho các textbox.
Lưu ý:
Vì các cột có dữ liệu sẽ thay đổi do đó nhãn sẽ cần có thay đổi tương ứng. Chúng ta hoàn toàn có thể làm điều này thông qua việc mở một đối tượng recordset để xem tên trường là gì và thay đổi nhãn tương ứng cho phù hợp nhé
Gửi kèm theo đây là ví dụ của tôi, các bạn tải về và xem nhé - nhớ chọn điều kiện để có thể xem được báo cáo…
Chúc vui vẻ hen
Còn đây là đoạn mã nguồn của form
Option Compare Database
Private Sub Command2_Click()
' Tao ra cross tab query nay
Dim tSql As String
tSql = "TRANSFORM Sum(tbl_class.AverageMark) AS SumOfAverageMark " & _
"SELECT tbl_class.NumberofStudent, Sum(tbl_class.AverageMark) AS [Total Of AverageMark] " & _
"FROM tbl_class " & _
"WHERE Val(tbl_class.ClassName)=" & Combo0 & " " & _
"GROUP BY tbl_class.NumberofStudent " & _
"PIVOT tbl_class.ClassName;"
On Error Resume Next
DoCmd.DeleteObject acQuery, "tbl_class_Crosstab"
CurrentDb.CreateQueryDef "tbl_class_Crosstab", tSql
DoCmd.OpenReport "rpt_Test", acViewPreview
End Sub
Mã nguồn báo cáo đây
Option Compare Database
Private Sub Report_Open(Cancel As Integer)
' Xac dinh xem hien co nhung cot nao xuat hien, neu it hon 8 thi cac cot con lai se
' de nhan la trang va textbox co control source la unbound
Dim rs As Recordset, i As Long
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
Dim iFld As Field
For Each iFld In rs.Fields
'Debug.Print iFld.Name
If i > 7 Then Exit For
' chu y dieu kien de loc cho dung truong nhe
If Val(iFld.Name) <> 0 Then
' Trước khi làm việc này lưu ý - cần phải ..... không thì chúng ta không nhìn thấy các cột nữa .. hihi
epsi:
' gan cac truong va nhan o day
Me.Controls("lbl" & i + 1).Caption = iFld.Name
Me.Controls("txt" & i + 1).ControlSource = iFld.Name
i = i + 1
End If
Next
' bay gio xoa cac nhan
While i <= 7
Me.Controls("lbl" & i + 1).Caption = ""
Me.Controls("txt" & i + 1).ControlSource = ""
i = i + 1
Wend
Set rs = Nothing
End Sub
':dance2:
Ở đây tôi còn thiếu một việc - đố các bạn biêt là gì?:drummer:
Xin phép được không giải thích ở đây mà tôi sẽ giới thiệu sau nhé.
Nay xin tạm dừng.
