›
›

Bài giảng Access - Chương 4: Sử dụng vấn tin (Query)

ppt 64 trang ngocly 2260
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Access - Chương 4: Sử dụng vấn tin (Query)", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pptbai_giang_access_chuong_4_su_dung_van_tin_query.ppt

Nội dung text: Bài giảng Access - Chương 4: Sử dụng vấn tin (Query)

  1. Chương 4 SỬ DỤNG VẤN TIN (QUERY) 1
  2. Query (Vấn tin) n Là trích lọc một phần dữ liệu theo một điều kiện n Tạo ra một bảng dữ liệu mới ứng với điều kiện n Chỉnh sửa (thêm, xố, sửa )dữ liệu thoả một điều kiện n Phân tích thống kê tổng hợp dữ liệu 2
  3. Ví dụ về query ¡ hãy tìm các sách phân loại là tin học ¡ Hãy in ra danh sách các sinh viên cĩ điểm trung bình tích luỹ <4.0 ¡ Hãy tìm ra nhân viên nào cĩ ngày sinh trong tháng này ¡ Hàng tháng tính lương cho nhân viên ¡ Xố hàng hố mà khơng cịn sản xuất nữa ¡ Nhập dữ liệu từ 1 bảng tạm vào bảng chính ¡ Thơng kế trong cơng ty cĩ bao nhiêu người nam, nữ 3
  4. 1.1 Màn hình vấn tin VẤN TIN CÁC VẤN TIN ĐANG CĨ TRONG CSDL 4
  5. Menu Query & Các biểu tượng Run : thực hiện vấn tin Show table : thêm bảng vào vấn tin Các loại vấn tin Select, Crosstab, Make-Table, Update, Append, Delete CHỌN LOẠI VẤN TIN 5
  6. Các loại vấn tin n Select Tìm kiếm, hiển thị, điều chỉnh dữ liệu từ 1 hoặc nhiều bảng n Crosstab Sử dụng để phân tích thống kê dữ liệu từ bảng n Make Table Vấn tin tạo ra bảng mới n Update Vấn tin cho phép tự động điều chỉnh nội dung bảng. n Append Vấn tin thực hiện thêm mẫu tin mới vào bảng. n Delete Vấn tin xĩa mẫu tin của bảng 6
  7. Tạo query tự động n Create query by using wizard nChọn table cần tạo query và các cột cần lấy dữ liệu 7
  8. Chọn kiểu thể hiện 8
  9. Đặt tên cho query 9
  10. Lưới QBE n Khi chọn tạo vấn tin bằng Design View người sử dụng sẽ mơ tả vấn tin trên một cơng cụ gọi là lưới QBE. n Lưới QBE là khác nhau theo loại vấn tin đang thiết kế (Select, Crosstab, Make Table v/v) 10
  11. Lưới QBE Vấn tin Vấn tin SELECT CROSSTAB UPDATE 11
  12. 2. VẤN TIN SELECT n Field Tên vùng n Table Các bảng sử dụng Tên bảng trong vấn tin n Sort Thứ tự n Show Hiện /Khơng hiện n Criteria Điều liện lọc 12
  13. 2.1 Vấn tin đơn giản n Chỉ sử dụng 1 bảng n Hiện thơng tin một số vùng của bảng Ví dụ Hiện danh sách nhân viên gồm họ, tên, ngày sinh. Thứ tự danh sách theo ABC của tên 13
  14. Kết quả thực hiện vấn tin LƯỚI QBE 14
  15. Lưu vấn tin (Save) Nhập tên vấn tin - Bấm OK 15
  16. Vấn tin sử dụng nhiều bảng n Hiện thơng tin một số vùng của bảng này và một số vùng thuộc bảng khác n Giữa hai bảng cĩ liên kết Ví dụ Hiện danh sách nhân viên gồm họ, tên, ngày sinh, tên phịng ban . Thứ tự danh sách theo ABC của tên nhanvien phongban 16
  17. TẠO VẤN TIN TRONG DESIGN VIEW 1. CHỌN HAI BẢNG NHANVIEN VÀ PHONGBAN 2. BẤM NÚT ADD 17
  18. 2.3 Vấn tin cĩ vùng tính tốn n Hiện thơng tin một số vùng của bảng và một số vùng khác được tính trên giá trị các vùng đã cĩ. Ví dụ Hiện bảng lương gồm họ, tên, hệ số lương, lương, phụ cấp Thứ tự danh sách theo từng phịng Vùng đã cĩ sẵn Khơng cĩ nhưng biết : lương = hệ số lương x 540000 ho, ten, hsluong phụ cấp = lương x phụ cấp chức vụ 21
  19. Tiếp tục phân tích Hiện bảng lương gồm họ, tên, hệ số lương, lương, phụ cấp Thứ tự danh sách theo từng phịng ho, ten, hsluong thuộc bảng nhanvien lương = hệ số lương x 350000 phụ cấp = lương x phụ cấp chức vụ (thuộc bảng chucvu) Vấn tin sẽ cần hai bảng : nhanvien, chucvu Hai bảng này phải cĩ liên kết với nhau 22
  20. Tiếp tục phân tích Cách viết cơng thức Tiêu đề cột : cơng thức tính lương = hệ số lương x 350000 Lương : nhanvien ! hsluong * 350000 Lương : [Nhanvien]![Hsluong]*350000 phụ cấp = lương x phụ cấp chức vụ (thuộc bảng chucvu) Phụ cấp : lương * chucvu ! Hesopc Phụ cấp : [Lương]*[chucvu]![hesopc] 23
  21. BIỂU TƯỢNG EXPRESSION BUILDER ĐỂ TẠO CƠNGTHỨC 24
  22. TIÊU ĐỀ CỘT : CƠNG THỨC NHẬP CƠNG THỨC VÀO ĐÂY RỒI BẤM PHÍM OK 25
  23. LƯU Ý : KHƠNG THỂ THAY ĐỔI NỘI DUNG Ở 2 VÙNG TÍNH TỐN LÀ VÙNG LUONG, PHUCAP 26
  24. Một số nội dung thường gặp trong cơng thức n Chuổi : trong dấu nháy kép chức vụ :”PP” n Tên vùng [phongban]![diachi] n Ghép nhiều vùng chuổi [nhanvien]![ho] & “ “ & [nhanvien]![ten] n Phép tốn trên nhiều vùng [xuatkho]![soluong] * [xuatkho]![gia] n Phép tốn trên nhiều vùng, hằng [nhanvien]![hsluong] * 350000 n Sử dụng dấu # cho kiểu ngày ¡ Between #1/10/2004# and #25/10/2004# 27
  25. Sử dụng hàm IIF n IIF(điều kiện, trị thứ 1, trị thứ 2) n nếu điều kiện đúng sẽ nhận trị thứ 1 n nếu điều kiện sai sẽ nhận trị thứ 2 n Cĩ thể sử dụng nhiểu hàm IIF lồng nhau VÍ DỤ Hiện ra chữ NAM hoặc NỮ ở phần giới (dù phái là 0,1) GIỚI : IIF(NHANVIEN!PHAI=0, “NAM”, “NỮ”) Chú ý: nếu trong regional chọn là vietnamese thi phần list separator chọn là chấm phẩy (;) và dấu phẩy (,) trong điều kiện dổi thành dấu chấm phẩy (;) 28
  26. 2.4 Điều kiện lọc và sắp thứ tự n Hàng criteria của lưới QBE Tìm người cĩ hệ số lương lớn hơn 4 29
  27. Điều kiện lọc • Ngang : AND - Dọc : OR • Cĩ thể hiện hoặc khơng (mục Show) nội dung vùng đặt điều kiện •Between giá trị and giá trị Xem ví dụ minh họa VÍ DỤ 1: HIỆN DANH SÁCH BAN GIÁM ĐỐC VÍ DỤ 2: HIỆN DANH SÁCH CÁC TRƯỞNG, PHĨ PHỊNG VÍ DỤ 3: HIỆN DANH SÁCH CÁC NHÂN VIÊN CAO TỪ 1,6 M ĐẾN 1,7M VÍ DỤ 4: HIỆN BẢNG LƯƠNG NHÂN VIÊN CỦA PHỊNG TỔ CHỨC , PHỊNG TÀI VỤ, PHỊNG KINH DOANH TIẾP THI 30
  28. Sắp thứ tự • Thứ tự theo một vùng nào đĩ Chọn mục Sort ở cột đĩ là Ascending/Descending • Thứ tự theo nhiều vùng Vùng viết trước là khĩa chính, vùng viết sau là khĩa phụ. Khi đĩ sắp thứ tự theo khĩa chính, khi nào giá trị ở cột khĩa chính bằng nhau mới sử dụng khĩa phụ để định thứ tự mẫu tin. • Ví dụ Vấn tin hiện danh sách trình bày theo thứ tự tăng dần theo ABC của tên. Nếu hai người cùng tên thì xét thêm thứ tự tăng dần của họ 31
  29. 2.5 Vấn tin cĩ tham số • Tham số là biến số, chỉ được cung cấp cụ thể khi thực hiện vấn tin. • Lúc thiết kế vấn tin, ta khơng biết giá trị thực của tham số. • Cách viết tham số : một câu hỏi nội dung vấn tin đặt trong cặp dấu ngoặc vuơng [ ] •Khi thực hiện vấn tin cĩ tham số, ACCESS sẽ lần lượt hỏi giá trị từng tham số để người sử dụng nhập vào. Sau khi nhập xong các tham số kết quả vấn tin mới xuất hiện 32
  30. Cột 1 là HoTen:Rtrim(Ho)& “ “ & Ten Cột 2 là Phái: IIF(phai=0,”Nam”, “Nữ”) Cột 3 là Ngaysinh Cột 4 là tình trạng gia đình Gia dinh:IIF(CoGiaDinh, “Cĩ”, “Khơng”) 33
  31. 2.6 SỬ DỤNG HÀM TRONG CƠNG THỨC n Hàm DAY(ngày) ¡ Lấy phần ngày trong ngày/tháng/năm 1-31 n Hàm MONTH(ngày) ¡ Lấy phần tháng trong ngày/tháng/năm 1-12 n Hàm YEAR(ngày) ¡ Lấy phần năm trong ngày/tháng/năm 100-9999 n Hàm WEEKDAY(ngày) ¡ Cho giá trị thứ trong tuần ¡ 1 - chủ nhật đến 7- thứ bảy n Hàm DATE() ¡ Cho ngày hiện tại lấy từ đồng hồ của máy tính n Hàm Now() : lấy ngày hiện tại n Dùng dấu # để phân biệt ngày 34
  32. 2.7 Vấn tin tổng hợp TÍNH TỔNG NGÀY CƠNG CỦA MỖI NHĨM TÍNH TRUNG BÌNH NGÀY CƠNG CỦA MỖI NHĨM MUỐN TỔNG HỢP THEO NHĨM 35
  33. 2.7 Vấn tin tổng hợp • Tạo ra các vấn tin tổng hợp theo nhĩm – khơng cịn chi tiết từng mẫu tin • Sử dụng dịng Total • Các giá trị ở mục Total là •Group By chỉ cột khĩa để phân nhĩm •Các trị khác để chỉ cơng thức tính 36
  34. Group By : khĩa phân nhĩm Kiểu dữ liệu có thể Hàm Ý nghĩa sử dụng được Number, Date, Sum Tổng nội dung của một cột Currency Number, Date, Avg Trung bình của một cột Currency Text, Number, Date, Min Giá trị nhỏ nhất trong cột Currency Text, Number, Date, Max Giá trị lớn nhất trong cột Currency Đếm số mẫu tin, Count Tất cả các kiểu không đếm nếu ô có trị Null First Mẫu tin đầu tiên Tất cả các kiểu Last Mẫu tin cuối Tất cả các kiểu 37
  35. THÊM DỊNG TOTAL 38
  36. Chỉ in những phịng cĩ số lượng nhân viên lớn hơn 10 39
  37. 2.8 Vấn tin cập nhật (Update) Vấn tin cập nhật THỰC HIỆN VIỆC THAY ĐỔI NỘI DUNG CỦA MỘT HOẶC NHIỀU VÙNG 40
  38. 2.8 Vấn tin cập nhật (Update) Field/Table : Tên vùng / thuộc bảng Update To : Giá trị hoặc cơng thức tính cho vùng Criteria : Điều kiện điều chỉnh chỉ chỉnh khi điều kiện đúng LƯU Ý : VÌ LÀ VẤN TIN CẬP NHẬT, TA CHỈ ĐƯA VÀO LƯỚI NHỮNG VÙNG PHẢI CẬP NHẬT. (Xem ví dụ) 41
  39. 2.9 Vấn tin xĩa (Delete) Field/Table : tên trường và điều kiện Delete : chọn trị Where Criteria : điều kiện để xĩa mẫu tin 42
  40. Vấn tin Cross-Tab n Thống kê theo chức vụ số người nam, hay nữ n Thơng kế theo chức vụ về độ tuổi trung bình 43
  41. 2.10 Vấn tin Cross-Tab n Tạo phân tích thống kê cĩ dạng như sau COLUMN HEADING Nam Nữ BGĐ 2 1 ROW CH1 20 25 GIÁ TRỊ HEADING CẦN TÍNH CH2 30 12 PHC 3 6 PKD 7 4 44
  42. 2.10 Vấn tin Cross-Tab n Dịng Crosstab : Row Heading/Column Heading/Value n Kết hợp với dịng Total : Group By/hàm thống kê 45
  43. Vấn tin Cross-Tab n Thống kê theo 1 cơng thức tính Expression n Nếu cĩ điều kiện thống kê dùng : where n Các hàm thường dùng ¡ Count : đếm ¡ Max/min : phần tử lớn nhất/ nhỏ nhất ¡ Sum : tính tổng giá trị ¡ Agv: tính trung bình 46
  44. Ví dụ n bảng độ tuổi trung bình thống kê theo chức vụ và phái 47
  45. Vấn tin Cross-Tab Thống kế số người theo trình độ và phái 48
  46. Vấn tin Cross-Tab Thống kế số người theo trình độ và phái 49
  47. 2.11 Vấn tin thêm mẫu tin (Append) n Lấy mẫu tin của một bảng để đưa vào một bảng khác BẢNG 1 BẢNG 2 aaa aaa aaa aaa aaa bbb bbb bbb bbb bbb ccc ccc ccc ccc ccc ddd ddd ddd ddd ddd aaa aaa aaa aaa aaa bbb bbb bbb bbb bbb ccc ccc ccc ccc ccc ddd ddd ddd ddd ddd 50
  48. 2.11 Vấn tin thêm mẫu tin (Append) n Hạn chế số cột (field) BẢNG 1 BẢNG 2 111 aaa aaa aaa xxx aaa aaa 222 bbb bbb bbb yyy bbb bbb 333 acc acc acc zzz acc acc 444 ddd ddd ddd kkk ddd ddd aaa aaa aaa aaa aaa bbb bbb bbb bbb bbb ccc ccc ccc ccc ccc ddd ddd ddd ddd ddd 51
  49. 2.11 Vấn tin thêm mẫu tin (Append) n Cĩ điều kiện (ví dụ :bắt đầu với ký tự a) BẢNG 1 BẢNG 2 aaa aaa aaa aaa aaa bbb bbb bbb bbb bbb acc acc acc acc acc ddd ddd ddd ddd ddd aaa aaa aaa aaa aaa acc acc acc acc acc 52
  50. 2.11 Vấn tin thêm mẫu tin (Append) n Khi thiết kế vấn tin - chọn bảng nào ? ¡ Chọn bảng 1 – là bảng cung cấp mẫu tin. n Nội dung lưới ¡ Chứa các vùng dữ liệu gửi sang bảng 2. ¡ Cĩ thể đặt điều kiện để chỉ cho bảng 2 những mẫu tin thỏa điều kiện. 53
  51. Các bước thực hiện 1. Chọn lệnh tạo vấn tin mới 2. Đưa bảng-cho vào lưới 3. Chọn loại vấn tin Append, bước này sẽ làm xuất hiện hộp thoại hỏi tên bảng- nhận. 4. Trả lời hộp thoại 54
  52. Table Name : tên bảng-nhận Current : chọn nếu bảng nhận cùng chung cơ sở dữ liệu với bảng-cho Another : chọn nếu bảng nhận khác cơ sở dữ liệu với bảng-cho. Phải cho tên cơ sở dữ liệu chứa bảng nhận. 55
  53. ) Phongban2 : bảng cho (đơn vị mới) Phongban : bảng - nhận (đơn vị cũ) Field/Table vùng của phongban2 sẽ được mang sang bảng phongban Append To vùng nhận thơng tin ở bảng phongban 56
  54. Vấn tin này chỉ cung cấp 3 vùng mã phịng, tên phịng, địa chỉ cho bảng phongban 57
  55. Chú ý !!! n Khi thêm mẫu tin mới phải khơng vi phạm những quy tắc đã biết : ¡ Khĩa chính : khơng được trùng ¡ Khơng vi phạm quy tắc về tính nhất quán dữ liệu ¡ Khơng vi phạm những quy định về dạng dữ liệu, kiểm tra dữ liệu 58
  56. Make table query n Tạo ra bảng mới cĩ dữ liệu được lọc từ CSDL n Bảng mới tạo ra được tồn tài trong CSDL 59
  57. Make table query n Cách thực hiện ¡ Tương tự như select query ¡ Khi thực hiện chọn kiểu query là make table query 60
  58. Cách thực hiện n Nhập tên của bảng mới cần tạo 61
  59. n Các bước cịn lại tương tự như select query 62
  60. Các loại query 63
  61. n bảng độ tuổi trung bình thống kê theo chức vụ và phái n Tính lương nhân viên n Xố lương nhân viên trong bảng nhân viên 64