VI. TRUY VẤN HÀNH ĐỘNG - ACTION
4. Truy vấn thêm - Append
• Sử dụng khi ta muốn nối dữ liệu vào một Table bất kỳ, có thể chỉ gồm những mẫu tin thỏa điều kiện.
Khi đó ta phải chỉ ra các Table chứa dữ liệu nguồn để nối.
Cách tạo Append:
Thực hiện việc tạo một Query mới, đến bước 3 chọn kiểu Query là Append.
Khi đó click vào nút Table Name để chọn tên Table sẽ được nối dữ liệu vào, xong chọn OK
Trong cửa sổ thiết kế Append ta chọn các vùng của
các bảng dữ liệu nguồn mà giá trị của chúng sẽ nối vào bảng dữ liệu khác, ta lần lượt kéo chúng vào vùng lưới. Nhập vào điều kiện cho Append.
Thực hiện Query bằng lệnh Run
Trả lời Yes cho các hộp thông báo tiếp theo.
Cuối cùng lưu và đặt tên cho bảng Query.
Ví dụ: Thiết kế Query để nối thêm vào Table NV_HC những nhân viên ở Phòng Tài Vụ có Hesoluong nhỏ hơn 2. Đặt tên Query là AP1.
• Thực hiện: Chọn Tab Create → Query Design trong nhóm Queries.
Chọn các Table NHAN VIEN , DON VI làm dữ liệu nguồn.
Chọn Tab Design trong nhóm Queries Type → Delete.
Click vào nút Table Name và chọn Table NV_HC → OK
Thiết kế Query như sau:
Lưu và đặt tên cho Query là AP1.
Thực hiện Append bằng lệnh Run hoặc Open.
Khi đó sẽ có thông báo nhắc nhở: Số mẫu tin được nối vào bảng.
Chú ý :
Các lần thực hiện Query sau thì sẽ có các thông báo nhắc nhở như sau:
Append sẽ làm thay đổi dữ liệu trong Table.
• Ta chọn Yes để tiếp tục.Nếu chọn No thì hủy bỏ lệnh.
Số dòng sẽ được nối vào.
• Ta chọn Yes để kết thúc lệnh. Nếu chọn No thì hủy bỏ lệnh.
BÀI THỰC HÀNH
Bài 1: Mở CSDL QUAN LY NHAN SU, tạo các bảng vấn tin sau đây, lưu lại và đặt tên theo từng câu Query:
1. Cho biết danh sách đầy đủ thông tin về những nhân viên có nơi sinh ở Trà Vinh hoặc Vĩnh long và có Năm sinh sau tháng 04.
2. Cho biết danh sách những nhân viên sinh từ năm 1976 đến năm 1978. Gồm các Field:
MaNV, Holot, Ten, Phai, Noisinh, Namsinh
3. Cho biết thông tin đầy đủ về những nhân viên có họ Nguyễn.
4. Cho biết danh sách những nhân viên Phòng hành chánh và Phòng Tài vụ có trình độ chuyên môn là Cử nhân.
5. Tạo Update Query tính Tiền lương cho tất cả nhân viên, với Tien luong = Hesoluong * 1150000.
6. Cho biết một sách gồm : Họ và tên, Năm sinh, Giới tính, Tiền Lương, BHYT, BHXH, Tiền Thực lĩnh của tất cả nhân viên. Trong đó : BHYT=2%Tiền lương; BHXH=8% Tiền lương; Thực lĩnh= Tiền Lương – BHYT – BHXH, nếu phai= nam thì giới tính là Yes, ngược lại giới tính là No.
7. Thêm từng cột liệt kê các nhân viên ở một đơn vị nào đó, với tên đơn vị được nhập từ bàn phím lúc chạy Query, trong đó: hệ số lương lấy phần nguyên.
8. Thống kê tổng số nhân viên theo từng đơn vị.
9. Cho biết bình quân tiền lương của các nhân viên ở Phòng Hành Chánh.
10. Cho biết tiền lương lớn nhất ở Phòng Tài Vụ.
11. Tính các giá trị Tổng lương, Bình quân lương, Lương thấp nhất, Lương cao nhất cho mỗi đơn vị.
12. Thống kê số nhân viên của từng đơn vị chia theo trình độ chuyên môn.
13. Thống kê số nhân viên là kỹ sư xây dựng ở phòng kỹ thuật.
14. Thống kê tổng cộng Tiền lương của các nhân viên theo từng đơn vị.
15. Cho biết tổng Tiền lương của những nhân viên ở Phòng hành chánh.
16. Thống kê số lượng nhân viên của từng đơn vị theo chức vụ.
17. Trích ra một Table mới gồm những nhân viên có tuổi từ 35 trở xuống, đặt tên Table là CAN BO - QUI HOACH. Dữ liệu nguồn được lấy từ Table NHAN VIEN.
18. Trong Table NHAN VIEN, tăng HESOLUONG thêm 0.28 cho những nhân viên Nữ ở Phòng tài vụ.
19. Từ Table NHAN VIEN, hãy nối thêm vào CAN BO_QUI HOACH những nhân viên Nam là Kỹ sư.
20. Thiết kế Query để xóa những nhân viên có nơi sinh ở Sóc Trăng.
21. Xóa những nhân viên có nơi sinh ở tỉnh Trà Vinh và có trình độ Cử nhân chính trị.
22. Từ bảng NHAN VIEN và CHUC VU hãy tạo ra bảng mới là BANG LUONG THANG. Trong đó gồm các cột: MaNV, Họ và tên, Phai, MaDV, Hesoluong, Hspccv, TienLuong, Tien PCCV, Tien BHYT, Tien BHXH và Thuc linh của tất cả nhân viên. Với : Tien PCCV=HSPCCV*1150000; Tien BHYT=2%TienLuong; Tien BHXH=8%
TienLuong; Thuc linh= TienLuong + Tien PCCV – Tien BHYT – Tien BHXH.
Bài 2 : Tạo lập một tập tin cơ sở dữ liệu mới: QUẢN LÝ XUẤT NHẬP KHO
Câu 1: Hãy tạo tập tin có tên là QUAN LY XUAT NHAP KHO vào trong thư mục của mình.
Câu 2: Tạo các Tables sau đây:
a). T01 NHAN VIEN có trúc như sau:
Field name Data Type Field Properties
Field size Format Input mask Caption
MaNV Text 4 > >L000 Mã nhân viên
Holot Text 25 Họ lót
Ten Text 15 Tên
Phai Text 3 Phái
Ngaysinh Date/time Short date 99/99/9999 Ngày sinh
Diachi Text 50 Địa chỉ
Luong Number Double Standard Lương
Ghichu Text 50 Ghi chú
Anh OLE Object Hình ảnh
Nhập vào dữ liệu gồm các nhân viên sau :
MANV HoLot Ten Phai Ngaysinh Diachi Luong Ghichu
A001 Nguyễn Văn Vui Nam 19/09/1988 Vĩnh Long 500000 Tổ trưởng A002 Nguyễn Thị Bé Nữ 20/05/1970 Trà Vinh 400000 Tổ Phó A003 Nguyễn Duy Trinh Nam 05/06/1988 Cần Thơ 300000 Tổ viên A004 Đồng Như Khôi Nam 10/11/1990 Sóc Trăng 360000 Tổ viên
b). T02 DANH MUC KHO có trúc như sau:
Field name Data Type Field Properties
Makho Text Field size : 2 Format : >
Caption : Mã Kho
Tenkho Text Field size : 20 Caption : Tên kho
DCkho Text Filed size : 50 Caption : Địa chỉ Kho
Nhập vào dữ liệu gồm các Kho sau:
Makho Tenkho DCKho
LB LONG BÌNH Long Bình – Đồng Nai TD THỦ ĐỨC Tăng Phú Nhơn – Thủ Đức PX PHÚ XUÂN Nhà Bè – Bình Chánh PP PHONG PHÚ Rạch Lá – Cần Giờ CT VP công ty 336 Lê Văn Sỹ Q1
c). T03 VAT TU có trúc như sau:
Field Name Data Type Field Properties
MaVT Text Field size : 4
Format : >
Caption : Mã vật tư
TenVT Text Field size : 25
Caption : Tên Vật tư
Qcach Text Field size : 10
Caption : Qui cách
DVTinh Text Field size : 10
Caption : Đơn vị tính
Nhập vào dữ liệu gồm các vật tư sau:
MaVT TenVT QCach DVTinh
SB01 Sơn Bạch Tuyết Thùng Lít
TK05 Thiết tráng kẽm 1,2 x 0,8m Mét
TN01 Tole nhựa xanh 1,2 x 0,8m Mét
TS03 Tole sóng 3 ly 2 x 1,6m Mét
TN02 Tole nhựa trắng 1,2 x 0,8m Mét
VE03 Ván Ép Con Ngựa 3 ly 1,5 Mét
VE04 Ván Ép Tân Mai 5 ly 1,5 Mét
d). T04 DU LIEU có trúc như sau:
Field Name
Data Type Field Properties
Field size Format Input mask Caption Stt Auto Number Long Interger
Ngay Date/Time Short Date 99/99/9999 Ngày chứng từ
Loai Text 1 > >L Loại phiếu
Fieu Text 4 Mã số Phiếu
HotenKH Text 30 Họ tên Khách hàng
Dgiai Text 30 Diễn giải
Makho Text 2 Mã kho
Mavt Text 4 Mã Vật tư
Solg Number Double Số lượng
Dgia Number Double Đơn giá
Manv Text 4 > >L000 Mã Nhân viên
Nhập vào dữ liệu gồm các phiếu chứng từ sau:
Lưu ý: Các phiếu loại Thu hay Chi đều có Makho là CT và không có MaVT (để trống)
STT NGAY LOAI FIEU HOTENKH DGIAI MAKHO MAVT SOLG DGIA MANV
1 06/05/2012 C C522 Khải Hoàn Phí vệ sinh Quý 02 CT SB01 32 100 A002 2 06/05/2012 C C523 Uy Tín Tw Sữa chữa phòng CT TN01 80 100 A002 3 07/05/2012 C C537 Yến Trân Chi cơ sở y tế CT TN02 200 100 A002 4 18/05/2012 N N001 Hai lúa Nhập hàng cứu trợ LB TS03 10 900 A003 5 22/05/2012 N N002 Bảy Nếp Mua Văn phòng phẩm LB TS03 50 1000 A003 6 01/05/2012 N N010 Thành Nên Mượn hàng tết TD SB01 12 500 B001 7 01/05/2012 N N011 Kim Chi Nhập Uy thác TD TN02 20 1000 A004 8 02/05/2012 N N012 Bảy Tuyết Nhập theo HD LB VE03 50 500 B001 9 03/05/2012 N N015 Thanh Thúy Chuyển hàng ký gởi LB VE03 10 1000 A004 10 18/05/2012 N N030 Hồng Phước Nhập trao đổi LB VE03 10 100 B002 11 15/05/2012 N N034 Ngọc Hà Gởi hàng Cần Thơ TD SB01 5 5000 B003 12 11/05/2012 T T452 Thanh Nam Thu HD 103 CT TK05 300 100 A001 13 11/05/2012 T T453 Bảo Anh Thu HD 233 CT TN01 400 100 A001 14 15/05/2012 X X008 Hoài thu Xuất bán HD 02 LB VE04 10 4000 A004 15 02/05/2012 X X011 Sáu Thông Xuất vãng lai LB VE03 10 1200 A003 16 03/05/2012 X X011 Thanh Bình Chuyển hàng ký gởi TD TS03 5 1000 B002
17 06/05/2012 X X013 Hai Lúa Bán CD TD TS03 5 4000 B003
18 07/05/2012 X X023 Bảy Nếp Cho XN Hải TD TS03 2 1000 A003
3. Mở Table T01 NHAN VIEN nhập thêm vào những mẫu tin sau:
MaNV HoLot Ten Phai Ngaysinh Diachi Luong
A005 Lý Mỹ Duyên Nữ 10/02/1980 Cần Thơ 600000
A006 Hứa Nhớ Nam 20/07/1979 Bạc Liêu 500000
A007 Hoàng Lan Nam 15/07/1990 Trà Vinh 500000
A008 Văn Yến Lan Nữ 06/11/1982 Trà Vinh 400000
B001 Lê Vy Nam 17/05/1975 Vĩnh Long 400000
B002 Lý Như Anh Nữ 20/06/1985 Vĩnh Long 300000
B003 Lê Ý Nhi Nữ 01/06/1980 Sóc Trăng 200000
B004 Trần Bình Nam 05/10/1984 Tp.HCM 450000
C001 Tạ Bích Loan Nữ 12/12/1982 Trà Vinh 500000 C002 Nguyễn Khoa Nam 01/01/1981 Đồng Nai 300000
4. Mở cửa sổ Relationships đưa các bảng của CSDL vào cửa sổ này. Sau đó tạo mối quan hệ giữa các bảng một cách hợp lý. Chú ý: Trước khi tạo quan hệ ta cần kiểm tra sự tồn tại giá trị của các cột MaKho, MaVT, MaNV trong T04 DU LIEU ở các Tabe tương ứng.
5. Mở Table T04 DU LIEU ở chế độ thiết kế và thêm một Field Thanhtien nằm trước Field MaNV: Với: Filed name: Thanhtien ; Data Type : Number ; Field size : Double;
Decimal places: 3.
Câu 3. Trong CSDL QUAN LY XUAT NHAP KHO, hãy tạo các Query sau đây:
1. Chọn lọc những phiếu loại N của ngày 18/05/2012 tại kho Long bình 2. Chọn lọc những phiếu loại X xảy ra sau ngày 02/05/2012.
3. Chọn lọc những KHÁCH HÀNG có tên bắt đầu bằng chữ H, trong đó tên đổi thành chữ thường
4. Chọn lọc những Phiếu loại N và X Vật tư Tole sóng 3 ly.
5. Chọn lọc Những phiếu loại N vật tư Sơn Bạch tuyết tại kho Thủ Đức 6. Chọn lọc tất cả Phiếu loại N hoặc X do nhân viên Lê Vy thực hiện.
7. Chọn lọc các vùng: Ngay, Loai, Fieu, MaNV, Ho va Ten NV, Dgiai, MaKho, TenKho, MaVT, TenVT, Solg, Dgia, Thanhtien cho các phiếu loại nhập hay xuất.
Trong đó: Thanhtien = Solg*Dgia.
* Sau đó hãy Open Table T04 DU LIEU và xem cột Thanhtien đã được tính toán giá trị chưa? nếu chưa thì giải thích tại sao?
8. Tạo Query cập nhật dữ liệu, tính cột Thanhtien trong T04 DU LIEU, với Thanhtien = Solg*Dgia.
* Sau đó hãy Open Table T04 DU LIEU và xem cột Thanhtien đã được tính toán giá trị chưa?
9. Tìm những phiếu nhập hoặc xuất do một nhân viên thực hiện, gồm các vùng: Ngay, Loai, Fieu, hotenkh, Dgiai, MaKho, TenKho, MaVT, TenVT, Solg, Ho va Ten NV với Họ và tên nhân viên được nhập lúc chạy Query. Với Ho va Ten NV là tham số.
10. Đếm tổng số phiếu theo từng loại phiếu nhập, xuất, thu, chi.
11. Tính tổng thành tiền theo từng loại phiếu nhập, xuất, thu, chi.
12. Đếm số phiếu loại chi trong ngày 06/05/2012.
13. Thống kê số lượng phiếu theo ngày và theo từng loại phiếu nhập, xuất, thu, chi 14. Thống kê số lượng phiếu theo kho và theo từng loại phiếu nhập, xuất, thu, chi 15. Trích ra một Table mới với tên T05 THU CHI gồm những phiếu loại thu hoặc chi.
16. Từ các Table T04 DU LIEU, T02 DANH MUC KHO, T03 VAT TU, T01 NHAN VIEN hãy tạo ra một Table mới có tên là T06 CHUNG TU NHAP XUAT. Gồm các Field: Ngay, Loai, Fieu, Hotenkh, Dgiai, MaKho, TenKho, MaVT, TenVT, Solg, Dgia, Thanhtien, MaNV của những phiếu loại nhập hoặc xuất.
17. Từ Table T06 CHUNG TU NHAP XUAT, hãy thiết kế một Update Query để tăng 10% đơn giá cho các phiếu loại xuất đồng thời tính lại Field Thanhtien.
18. Xóa các phiếu loại thu hoặc chi trong Table T04 DU LIEU.
19. Mở T04 DU LIEU và nhập thêm các phiếu sau đây :
NGAY LOAI FIEU HOTENKH DGIAI MAKHO MAVT SOLG DGIA MANV
01/06/2012 T T556 Khải Hoàn Thu hợp đồng 11/02 CT SB01 32 100 A001
01/06/2012 C C557 Yến Trân Chi VPP CT TS03 200 100 A001
02/06/2012 C C558 Thanh Bình Chi cứu trợ CT VE03 300 100 A002 03/06/2012 T T559 Bảo Anh Thu hợp đồng 12/02 CT VE04 400 100 A002
20. Thiết kế Query để nối thêm những phiếu thu hoặc chi vào Table T05 THU CHI.
21. Trong T06 CHUNG TU NHAP XUAT, xóa những chứng từ loại nhập trong một ngày nào đó, với ngày xóa được nhập lúc chạy Query.