Bài thực hành Access chương 1, 2: databse & Tables

1/4 BÀI THỰC HÀNH ACCESS CHƯƠNG 1 & 2: DATABSE & TABLES Bài 1 Tạo, mở và đĩng cơ sở dữ liệu 1. Tạo cơ sở dữ liệu BanHang trong thư mục D:\BaiTapAccess. Nếu chưa cĩ thì hãy tạo thư mục BaiTapAccess. 2. Đĩng CSDL BanHang 3. Mở lại CSDL BanHang. 4. Đĩng CSDL BanHang. Bài 2 Tạo table 1. Mở CSDL BanHang trong D:\BaiTap_Access. 2. Tạo table NhaCungCap gồm các fields sau: Field Name Data Type Field Properties MaNCC Number Field Size: Long Integer Khĩa chính (Primary Key) TenNCC Text Field Size: 40 DiaChi Text Field Size: 60 DienThoai Text Field Size: 20 Fax Text Field Size: 20 3. Tạo table SanPham gồm các fields sau: Field Name Data Type Field Properties MaSP Number Field Size: Long Integer Khĩa chính (Primary Key) TenSP Text Field Size: 40 MaNCC Number Field Size: LongInteger DVT Text Field Size: 20 DonGiaMua Currency Default Value: 0 Validation Rule: >=0 Validation Text: Phai la so lon hon hoac bang zero. 4. Tạo relationship giữa hai tables NhaCungCap và SanPham trong database BanHang dựa trên field MaNCC. Bài 3 Nhập dữ liệu vào table 1. Nhập dữ liệu vào table NhaCungCap MaNCC TenNCC DiaChi DienThoai Fax 1 Cơng ty TNHH Bình châu 15 Nguyễn Đình Chiểu, P4, Q3 9625012 2 DNTN Trường Thanh 169/2 Lê Quang Định, P24, Q.BT 8300945 3 Cơng ty TNHH Tiến Trung 65/4 Võ Thị Sáu, P6, Q3 9645512 9645512 4 DNTN Đơng Hải 23 Hai Bà Trưng, P9, Q1 9123008 2/4 2. Nhập dữ liệu vào table SanPham MaSP TenSP MaNCC DVT DonGia 1 Bia SaiGon 1 chai 4,500 2 Rượu Red Label 1 chai 75ml 150,000 3 Bột nêm Knor 1 túi 250g 9,000 4 Bột nêm Ajnomoto 2 túi 250g 10,000 5 Nước tương Chinsu 2 chai 125ml 2,000 6 Nước tương Đào Tiên 3 chai 125ml 2,000 7 Chả lụa 3 kg 28,000 8 Bột ngọt Vedan 3 túi 500g 12,000 9 Thịt Nạc 4 kg 60,000 10 Tơm sú 4 kg 120,000 Bài 4 Trong CSDL BanHang, tạo các tables, relationship và nhập dữ liệu 1. Tạo table KhachHang gồm các fields sau: Field Name Data Type Field Properties MaKH Text Field Size: 5 InputMask: >LL000 AllowZeroLength: No Khĩa chính (Primary Key) TenKH Text Field Size: 40 AllowZeroLength: No DiaChi Text Field Size: 50 DienThoai Text Field Size: 20 Fax Text Field Size: 20 2. Tạo table NhanVien gồm các fields sau: Field Name Data Type Field Properties MaNV Number Field Size: Long Integer Khĩa chính (Primary Key) HoNV Text Field Size: 20 TenNV Text Field Size: 10 NgaySinh Date/Time Format: dd/mm/yy NamNu Yes/No Default value: Yes ChucVu Text Field Size: 50 DiaChi Text Field Size: 50 DienThoai Text Field Size: 20 3. Tạo table HoaDon gồm các fields sau: Field Name Data Type Field Properties MaHD Number Field Size: Long Integer Khĩa chính (Primary Key) MaKH Text Field Size: 5 MaNV Number Field Size: Long Integer NgayDatHang Date/Time Format: dd/mm/yy NgayGiaoHang Date/Time Format: dd/mm/yy 3/4 4. Tạo table ChiTietHD gồm các fields sau: Field Name Data Type Field Properties MaHD Number Field Size: Long Integer MaSP Number Field Size: Long Integer DonGiaBan Currency Validation Rule: >=0 SoLuong Number Field Size: Integer GiamGia Number Field Size: Single Khĩa chính là field MaHD và MaSP 5. Tạo relationship giữa các tables. 6. Nhập dữ liệu vào các tables. MaNV HoNV TenNV NgaySinh NamNu ChucVu DiaChi DienThoai 1 Nguyễn Văn Bình 08/12/58 Yes Tổ trưởng 12 Nguyễn Văn Bảo Q.GV 9540015 2 Trần Ngọc Bảo Châu 19/02/52 No Phụ trách 19/1 Lê Thị Riêng Q1 9845112 3 Lý Thị Thanh Vân 30/08/63 No Nhân viên 159 Nguyễn Đình Chiểu Q1 9642310 4 Nguyễn Thanh Hùng 19/09/47 Yes Nhân viên 410 Điện Biên Phủ Q10 9458870 5 Phan Ngọc Hải 04/03/55 Yes Nhân viên 663 Nguyễn Thiện Thuật Q3 9598663 6 Lê Minh Tâm 02/07/63 Yes Nhân viên 15/2 Nguyễn Cư Trinh Q1 9253300 7 Nguyễn Thị Thu 29/05/60 No Nhân viên 19/3/2 Cao Đạt Q5 8994512 8 Lê Ngọc Tú 09/01/58 No Nhân viên 442/1 Hưng Phú Q8 8398806 9 Nguyễn Khánh Bảo 27/01/66 No Nhân viên 46/2 Phạm Thế hiển Q8 9832512 MaKH TenKH DiaChi DienThoai Fax KH001 Nguyễn Đức Trung 427/10 Lê Văn Sỹ P6, Q3 8422650 8422651 KH002 Phạm Thị Ngọc Trang 16/4 Cao Thắng P4, Q3 8398801 KH003 Lê Văn Bảo 320 Nguyễn Đình Chiểu P4, Q3 8334705 8334705 KH004 Trần Văn Tư 17A Hồ Xuân Hương P6, Q3 9566123 KH005 Lê Thị Hồng Anh 81 Phan Đình Phùng Q.PN 9845512 9845522 MaHD MaKH MaNV NgayDatHang NgayGiaoHang 11078 KH001 2 15/2/2003 12/2/2003 11079 KH003 4 15/2/2003 15/2/2003 11084 KH001 5 20/3/2003 22/3/2003 11085 KH003 6 21/3/2003 22/3/2003 11091 KH002 7 9/4/2003 10/4/2003 11096 KH002 4 15/4/2003 15/4/2003 KhachHang MaKH NhanVien MaNV ChiTietHD MaHD MaSP SanPham MaSP MaNCC HoaDon MaHD MaKH MaNV 1 ∞ 1 ∞ 1 ∞ 1 ∞ NhaCungCap MaNCC 1 ∞ 4/4 MaHD MaSP DonGiaBan SoLuong GiamGia 11078 1 5,400 10 0 11078 6 2,400 50 0 11078 7 33,600 7 0 11079 8 14,400 27 0 11079 9 48,000 8 0 11084 8 14,400 13 0 11085 1 5,400 40 0 11085 2 180,000 1 0 11085 8 14,000 45 0 11091 3 12,000 24 0 11091 6 2,400 100 0 11091 9 48,000 32 0 11096 2 180,000 1 0 11096 3 12,000 24 0 11096 7 33,600 1 0 11096 9 48,000 16 0 7. Đĩng tất cả các tables. 8. Đĩng CSDL BanHang. Bài 5 Thay đổi thuộc tính của các tables 1. Mở CSDL BanHang. 2. Mở table NhanVien ở cửa sổ Design. Thay đổi các thuộc tính của field NamNu: − Trong tab General, đổi thuộc tính Format là ;"Nam";"Nu" − Trong tab Lookup, đổi thuộc tính Display Control là Text Box 3. Mở table HoaDon ở cửa sổ Design. Thay đổi các thuộc tính của field MaKH: − Trong tab Lookup, đổi các thuộc tính như sau: Display Control Combo Box Row Source Type Table/Query Row Source KhachHang Bound Column 1 Column Count 2 Column Heads No Column Widths 0;1 1/2 BÀI THỰC HÀNH ACCESS CHƯƠNG 3: QUERIES Mở cơ sở dữ liệu BanHang. Bài 1 Tạo Select Query 1. Xem danh sách nhân viên gồm các thơng tin mã nhân viên, họ, tên và ngày sinh. Sắp thứ tự danh sách theo tên. 2. Xem danh sách nhân viên cĩ tên bắt đầu bằng chữ “T” (gồm các thơng tin mã nhân viên, họ, tên, ngày sinh). 3. Xem danh sách nhân viên cĩ tên bắt đầu bằng chữ “T” hoặc “H” (gồm các thơng tin mã nhân viên, họ, tên, ngày sinh). 4. Xem danh sách nhân viên nữ sinh trước năm 1950 (gồm các thơng tin mã nhân viên, họ, tên, ngày sinh, giới tính). 5. Xem danh sách nhân viên gồm các thơng tin mã nhân viên, họ, tên, tuổi và địa chỉ. 6. Cĩ bao nhiêu nhân viên tuổi từ 40 đến 50. 7. Cĩ bao nhiêu nhân viên cĩ ngày sinh nhật trong tháng 2. 8. Xem danh sách nhân viên sinh từ năm 1950 đến 1960 (gồm các thơng tin họ, tên, năm sinh, địa chỉ). Bài 2 Tạo Parameter Query 1. Xem danh sách nhân viên dựa theo mã nhân viên được nhập vào (gồm các thơng tin mã nhân viên, họ, tên, ngày sinh, chức vụ). 2. Xem danh sách nhân viên sinh dựa theo khoảng năm sinh được nhập vào (gồm các thơng tin mã nhân viên, họ, tên, năm sinh). Bài 3 Tạo Crosstab Query 1. Xem mỗi khách hàng mua mỗi sản phẩm với số lượng bao nhiêu? 2. Xem mỗi khách hàng mua mỗi sản phẩm với số tiền bao nhiêu? Bài 4 Tạo Action Query 1. Tăng giá các mặt hàng “thịt” và “tơm” lên 10%. 2. Tạo table lưu các hĩa đơn và chi tiết hĩa đơn của tháng 2. Đặt tên table là HoaDonT2. 3. Tạo một table mới gồm các nhân viên cĩ tuổi từ 50 đến 60. 4. Xĩa các hĩa đơn đặt hàng tháng 2. 5. Nối table HoaDonT2 vào table HoaDon và ChiTietHD. Bài 5 Tạo query cĩ field tính tốn 1. Xem danh mục các sản phẩm (mã sản phẩm, tên sản phẩm, đơn vị tính, đơn giá mua, đơn giá bán, số lượng đã bán, thành tiền: số lượng x đơn giá bán x (1 – giảm giá)). 2. Xem chi tiết của mỗi hĩa đơn dựa theo số hĩa đơn được nhập vào (tên sản phẩm, đơn vị tính, số lượng, đơn giá bán, thành tiền). 2/2 3. Xem tổng thành tiền của các hĩa đơn (mã hĩa đơn, tên khách hàng, ngày đặt hàng, ngày giao hàng, tên nhân viên lập hĩa đơn, tổng thành tiền). 4. Xem các hĩa đơn cĩ tổng thành tiền từ 1,000,000 trở lên (mã hĩa đơn, tên khách hàng, ngày đặt hàng, ngày giao hàng, tổng thành tiền). 5. Xem các hĩa đơn đặt hàng trong tháng 3 (mã hĩa đơn, tên khách hàng, ngày đặt hàng, ngày giao hàng, tổng thành tiền). 6. Xem các hĩa đơn của từng khách hàng dựa theo mã khách hàng nhập vào (mã hĩa đơn, ngày đặt hàng, ngày giao hàng, tổng thành tiền). 7. Đếm số hĩa đơn trong tháng 3 và 4. 8. Tính tổng thành tiền các hĩa đơn theo từng tháng. 9. Tính doanh số của mỗi nhân viên. 10. Tính xem mỗi nhà cung cấp cung cấp bao nhiêu mặt hàng? Bài 6 Tạo SQL Query 1. Xem mã và tên các sản phẩm chưa bán (khơng cĩ trong table ChiTietHD). 2. Xem họ tên nhân viên khơng cĩ trong hĩa đơn đặt hàng tháng 4. 1/3 BÀI THỰC HÀNH ACCESS CHƯƠNG 4: FORMS Mở cơ sở dữ liệu BanHang. Bài 1 Tạo form hiển thị và điều chỉnh dữ liệu về nhân viên theo dạng sau: Dùng form nhập dữ liệu của một nhân viên mới: − Mã NV: 10 − Họ tên: Hồng Thu Nga − Ngày sinh: 20/5/80 − Giới tính: Nữ − Chức vụ: Nhân viên − ðịa chỉ: 114/7 ðinh Tiên Hồng, P1, Q.Bình Thạnh Bài 2 Tạo form hiển thị và điều chỉnh dữ liệu về khách hàng theo dạng sau: Dùng form nhập dữ liệu của một khách hàng mới: − Mã NV: KH006 − Họ tên: Phạm Ngọc ðức − ðịa chỉ: 10/7 ðinh Bộ Lĩnh, P.22, Q. Bình Thạnh − ðiện thoại: 9007763 − Fax: 9007763 2/3 Bài 3 Tạo form xem và hiệu chỉnh các hĩa đơn dạng main-sub. 1. Sử dụng form HoaDon nhập thêm 1 hĩa đơn mới: − MaHD: 11100 − MaKH: KH006 − MaNV: 10 − NgayDatHang: 20/4/2003 − NgayGiaoHang: 22/4/2003 − Chi tiết hĩa đơn như sau: MaSP SoLuong DonGiaBan GiamGia 3 30 12,000 5% 4 5 11,000 0% 5 10 2,500 5% 6 60 24,000 0% 2. Thêm label HĨA ðƠN vào phần Form Header 3. Thêm 2 nút lệnh cĩ chức năng thêm 1 record mới và xĩa 1 record vào phần Form Footer. 4. Tạo Combo box cho người sử dụng chọn tên khách hàng khi nhập dữ liệu. Bài 4 Tạo form dạng form là main-sub hiển thị thơng tin về nhà cung cấp và sản phẩm: 3/3 1. Thêm label hiển thị tiêu đề của form là NHÀ CUNG CẤP – SẢN PHẨM ở phần Form Header. 2. Thêm các nút lệnh cĩ chức năng đĩng form, thêm record mới, xĩa record ở phần Form Footer. Bài 5 Tạo form cĩ chức năng điều khiển (switch board). Trên form cĩ các nút lệnh dùng để mở các form đã tạo ở Bài 1, Bài 2, Bài 3 và Bài 4. Bài 6 (khơng bắt buộc) Tạo form dạng hộp thoại (dialog box) như sau: Trên form cĩ: − 1 option group Chọn cĩ 3 giá trị − 3 combo box: Tháng, Quy, Nam − 2 command button: Xem và Thoat Khi người sử dụng click chọn “Theo Tháng” thì combo box “Chọn tháng” hiện ra, trong combo box này cĩ các tháng đã lập hĩa đơn. Tương tự, khi click chọn “Theo Quý” thì combo box “Chọn quý” hiện ra. Tương tự, khi click chọn “Theo Năm” thì combo box “Chọn năm” hiện ra. Sau khi click chọn 1 tháng (1 quý hoặc 1 năm) và click nút “Xem” thì mở query gồm các thơng tin mã sản phẩm, tên sản phẩm, số lượng đã bán, đơn giá bán và thành tiền (theo tháng đã chọn). 1/3 BÀI THỰC HÀNH ACCESS CHƯƠNG 5: REPORT Mở cơ sở dữ liệu BanHang. Tạo các report sau: Bài 1 Danh sách nhân viên. Bài 2 Danh sách nhân viên theo 2 nhĩm nam và nữ. 2/3 Bài 3 Danh mục sản phẩm: Bài 4 Báo cáo doanh số mỗi khách hàng: 3/3 Bài 5 In các hĩa đơn bán hàng: Bài 6 Báo cáo sản phẩm đã bán. Dựa trên cơ sở dữ liệu QLBanHang.mdb thực hiện tạo các truy vấn sau: Truy vấn một bảng: Qry1. Cho biết các thông tin về khách hàng Qry2. Cho biết tên Sản phẩm và đơn giá của các sản phẩm Qry3. Cho biết họ, tên, địa chỉ và số điện thoại của các Nhân viên Qry4. Cho biết mã khách hàng, tên khách hàng, địa chỉ khách hàng mà trong tên có từ “an” Qry5. Cho biết tên khách hàng, số điện thoại của những khách hàng có địa chỉ ở Quận 8 Qry6. Cho biết mã sản phẩm, tên sản phẩm, đơn giá của các sản phẩm nước tương Qry7. Lập danh sách các sản phẩm (TENSP, DVT, GIA) của các sản phẩm có đơn vị tính là Chai và Túi Qry8. Cho biết các sản phẩm Bột nêm có đơn giá trong khoảng từ 9000 đến 9500 Qry9.Lập danh sách các nhân viên nữ (MANV, HONV, TENNV, DIACHI) thuộc các nhân viên chưa có số điện thọai Qry10. Cho biết các thông tin về các nhân viên có tuổi từ 30 đến 40 Qry11. Cho biết các Hoá đơn (SOHD, NGAYHD) được giao hàng trong vòng 5 ngày Qry12. Cho biết các sản phẩm có đơn giá từ 1 triệu trở lên Truy vấn trên nhiều Table : Qry13: Liệt kê họ tên nhân viên và số hoá đơn, ngày hoá đơn, mã khách hàng do nhân viên đó đảm nhận Qry14: Liệt kê số hoá đơn,ngày hoá đơn, tên sản phẩm, đơn vị tính, đơn giá, số lượng của tất cả hoá đơn do nhân viên Hùng thực hiện. Qry15: Liệt kê số hoá đơn, ngày hoá đơn, tên khách hàng, tên nhân viên của các hoá đơn phát sinh trong quí 3/ 2003 Qry16: Cho biết tên khách hàng, tên sản phẩm mà khách hàng đó mua với số lượng trên 30 Qry17: Cho biết họ tên nhân viên đã lập những hoá đơn nào (số hoá đơn), tên sản phẩm, đơn vị tính,số lượng, đơn giá Qry18: Liệt kê số hoá đơn, ngày hoá đơn, tên sản phẩm, thành tiền ((1-giảm giá) x (số lượng x đơn gia bánù) ) Qry19: Cho biết các khách hàng (tên khách hàng) đã mua Chả lụa có số hoá đơn, ngày hoá đơn, số lượng, đơn giá, thành tiền ((1-giảm giá) x (số lượng x đơn giá)) trên 200000 đồng Qry20: Cho biết những khách hàng nào (tên khách hàng) và số hoá đơn, ngày hoá đơn, số lượng, đơn giá, thành tiền ((1-giảm giá) x (số lượng x đơn gia bán )) đã mua bất kỳ sản phẩm nào có số lượng trên 10 Truy vấn có tham số Qry21: Liệt kê số hoá đơn, ngày hoá đơn, trị giá hoá đơn của nhân viên có tên được nhập khi thực hiện Qry22: Liệt kê số hoá đơn, tên khách hàng, số lượng, đơn giá, thành tiền ((1- giamgia ) x (Soluong X Dongiaban)) đã mua sản phẩm có tên khi thực hiện. Qry23: Liệt kê tên khách hàng, tên sản phẩm, số lượng trong khoảng thời gian T1...T2 được nhập khi thực hiện Qry24: Liệt kê doanh số bán hàng của bộ phận có tên được biết khi thực thi Qry25: Liệt kê số hoá đơn, doanh số của năm được biết khi thực thi Qry26: Liệt kê họ tên các nhân viên chưa lập (bán ) được hoá đơn (mặt hàng) nào Qry27: Liệt kê các sản phẩm chưa bán được Truy vấn có nhóm dữ liệu (GROUP BY) Qry28: Liệt kê số hoá đơn, ngày hoá đơn, tên khách hàng, họ tên nhân viên, trị giá của các hoá đơn phát sinh trong tháng 2/2003 Qry29: Liệt kê tên khách hàng, tên sản phẩm và số lượng mà khách hàng đó đã mua Qry30: Liệt kê 5 sản phẩm có số lượng bán cao nhất Qry31: Cho biết doanh số bán hàng của từng năm Qry32: Liệt kê 3 khách hàng có doanh số cao nhất Qry33: Thống kê nhân viên theo mẫu sau: Tên bộ phận Tổng số Nhân viên Số Nhân viên nam Số Nhân viên nữ Qry34: Thống kê số lượng hoá đơn và doanh số của Nhân viên theo mẫu sau: Họ tên Nhân viên Tổng số Hoá đơn Doanh số Với doanh số được tính theo công thức sau: (1-giảm gíá)* (số lượng* đơn giâ bán) Qry35: Thống kê số lượng, trị giá sản phẩm theomẫu sau: Tên sản phẩm Số lượng Trị giá Qry36: Thống kê số lượng của mỗi quí trong năm 2001 theo mẫu sau: Tên sản phẩm Quí Số lượng Qry37: Thống kê doanh số của của khách hàng từng năm: Tên khách hàng Năm Doanh số Qry38: Tạo bảng Tbl_NhanvienNam gồm các trường Mã nhân viên, họ tên, ngày sinh, số năm làm việc, bộ phận chứa dữ liệu Nhân viên nam được trích từ bảng Tbl_NhanVien Qry39: Tạo bảng Tbl_sanPhamTmp chứa dữ liệu bột nêm, bia được trích từ Tbl_SanPham. Qry40: Tạo bảng Tbl_KHSPTmp gồm có các trường tên khách hàng, tên sản phẩm, trị giá (1-giamgia)* (soluong*dongiaban) Qry41: Tạo bảng Tbl_SPTmp gồm có các trường Tên sản phẩm,tổng số lượng Qry42: Thêm các sản phẩm Bia và nước ngọt có đơn giá trên 1 triệu đồng vào bảng Tbl_SanPhamTmp từ bảng Tbl_SanPham Qry43 Tbl_SanPhamTmp Qry44: Xoá các nhân viên Nam không thuộc Phòng kinh doanh trong bảng Tbl_NhanvienNam Qry45: Xoá các sản phẩm Tivi, Cassette trong bảng Tbl_SanPhamTmp