Tài Liệu Đề Thi Access Có Lời Giải ), Bài Tập Access

N THI S 1 Thi gian lm bi : 120 pht Cu 1 : Thit k bng – To mi quan h – Nhp d liu vo bng theo cc yu cu sau – khachhang : MAKH(T,4); TENKHACH(T,10); DIACHI(T,40) – Sudung : MASD(T,2); LOAISD(T,15); DONGIA(N,Int) – Chitiet : MASD(T,2); MAKH(T,4); SOKW(N,Int); THANHTIEN(N,Double)

Cu 2 : To cc Query thc hin cc yu cu sau 1. Tnh ct THANHTIEN trong bng CHITIET bit THANHTIEN = SOKW*DONGIA, nu SOKW;0,95;1) Save query t tn Query1 b. u tin to mt truy vn ph (Query 2phu) tnh tng s tin thu c ca tng tuyn : a bng QUERY1 vo tham gia truy vn Chn field matuyen (group by), tongtien: thanhtien (sum). – To mt truy vn tm tm S tin thu nhiu nht : a bng Query 2phu vo tham gia truy vn chn field tongtien (MAX) chn ch SQL VIEW copy cu lnh SQL (b du 😉 ng truy vn tm khng cn SAVE. – To truy vn chnh tm tuyn ng thu c nhiu tin nht : Chn Query 2phu v bng tuyenduong a vo li truy vn cc field MATUYEN; TENTUYEN; TONGTIEN ti phn criteria ca tongtien dn cu lnh copy vo trong cp du ngoc n. c. a bng DMXE vo tham gia truy vn a cc field MAXE; TENXE; TENLAIXE; SOCHO vo li QBE. Trong phn Criteria ca MAXE nhp cu lnh sau : NOT IN (SELECT NHATTRINH.MAXE FROM NHATTRINH) SAVE QUERY d. a bng DMXE vo tham gia truy vn chn loi truy vn Append query nhp X005; 611080; LI; 45 vo field Chn MAXE; TENXE; TENLAIXE; SOCHO trong phn append to Click lnh Run SAVE query Cu 3 : To form TONGHOP a. Click create form by using wizard ln lt chn cc field MATUYEN, TENTUYEN; SOKM; GIAVE (bng tuyenduong); MAXE (bng query1); TENXE; TENLAIXE (bng MAXE); SOVE THANHTIEN (query1) Click Next click Next click Next Chn kiu Industrial click next t tn form chnh l THVTK; form ph THVTK subforrm click finish. b. V ch design view sa i thit k li ging nh trong bi c. Right click text box MATUYEN Change to combo box Right click text box MATUYEN Properties data xa trng Control source nhp cu lnh SELECT 18

TUYENDUONG.MATUYEN FROM TUYENDUONG; (c du chm phy cui cu lnh) nhp BD01 vo default value (xem hnh minh ha trang sau) Click Event chn After Update Code builder nhp on code sau vo gia Private sub v End sub

Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst “ = “” & Me! & “”” If Not rs.EOF Then Me.Bookmark = rs.Bookmarkd. To cc nt lnh bng Wizard (khng nn s dng code) e. Chn phn form footer ca subform to mt textbox rightclick chn properties chn th OTHER t tn TONGCON trong phn NAME ng hp thoi properties nhp cng thc =SUM() vo textbox TONGCON V textbox tng tin trn FORM chnh nhp cng thc = .Form!TONGCON Cu 4 : To bo co THEO DI NHT TRNH a. Click create REPORT by using wizard ln lt chn cc field SONT, NGAY; MAXE (bng nhattrinh); TENLAIXE (bng dmxe); MATUYEN(bng QUERY1); TENTUYEN, SOKM (tuyenduong); SOVE, THANHTIEN(QUERY1) Click Next click Next click Next Click chn Summary option nh du kim vo phn Sum – thanhtien click OK CLick Next Chn kiu Align Left 2 CLick Next Chn hnh thc Bold t tn cho report THEODOINHATTRINH Click Finish b. Tr v ca s design trnh by report nh hnh sau

Cu 5 : To menu (Xem li hng dn trong Hng dn gii s 2) 19

TRNG CAO NG KINH T K THUT BNH DNG

N THI S 5 Thi gian lm bi : 120 pht Cu 1 : Thit k bng – To mi quan h – Nhp d liu vo bng theo cc yu cu sau – Nxkho : SOHD(T,4); NGAY(D,Short date); LOAIHD(T,1);. – Dmkho : MAKHO(T,3); TENKHO(T,20); DIACHI(T,30). – Dmhh : MAHH(T,4); TENHH(T;25); DONGIA(N,Single) – CTNX : SOHD(T,4);MAHH(T;4);MAKHO(T;3); SOLUONG(N;Integer)

Cu 2 : To Query thc hin nhng yu cu sau 1. Tnh thnh tin cho cc hng ha xut bn, gm cc thng tin sau : SOHD; MAHH; MAKHO; SOLUONG; THANHTIEN vi thanhtien tnh theo cng thc : THANHTIEN=SOLUONG * DONGIA vi iu kin nu SOLUONG>=300 c gim 10% . 2. Thng tin danh sch nhng mt hng ng k nhng cha nhp/xut : MAHH; TENHH, DONGIA 3. Tm danh sch kho nhp nhiu tin hng nht : MAKHO; TENKHO; DIACHI; TSTIEN 4. To truy vn tng hp th hin thng tin nh sau :

Cu 3 : To Form THEODOIHANGHOA nh hnh trang sau, yu cu : 20

1. Thit k form ng yu cu. 2. MAHANG, MAKHO dng combo box; TENHANG; DONGIA, TENKHO, DIACHI, SOLUONG, THANHTIEN t ng hin th. 3. To cng thc tnh tng tin- Yu cu : tng tin t ng cp nht khi d liu trong forrm con thay i 4. Cc nt lnh thc hin ng chc nng, nt xa c cnh bo ting Vit.

Cu 4 : To bo co HOADON nh hnh sau :

Cu 5 : To form dieukhien nh hnh sau , yu cu khi chn i tng v nhn nt thc hin s kch hot i tng tng ng.

21

22

HNG DN GII N THI S 05 Cu 1 : a. Thit k bng d liu : – Bng NXKHO : Vi SOHD chn Require = YES; Index = Yes, no duplicate. (t kha SOHD) – Bng DMKHO : Vi MAKHO chn Require = YES; Index = Yes, no duplicate. (t kha MAKHO) – Bng DMHH : Vi MAHH chn Require = YES; Index = Yes, no duplicate. (t kha MAHH) – Bng CTNX : Vi SOHD; MAHH; MAKHO chn Require = YES; Index = Yes, duplicates OK (t kha SOHD; MAHH; MAKHO) b.

Đang xem: Tài liệu đề thi access có lời giải

Xem thêm: Giáo Trình Luật Quốc Tế

Xem thêm: đơn xin ra khỏi đoàn thanh niên

To mi quan h : To mi quan h nh hnh sau

Right click trn bng CTNX chn Table design chn SOHD Click th Lookup Click phn display Control chn Combo box trong phn Row Source click chn bng NXKHO Chn MAHH Click th Lookup Click phn display Control chn Combo box trong phn Row Source click chn bng DMHH click chn MAKHO Click th Lookup Click phn display Control chn Combo box trong phn Row Source click chn bng DMKHO Click nt Save Close thit k bng ng ca s to mi quan h. c. Nhp d liu : Nhp bng NXKHO DMKHO DMHH CTNX. Cu 2 : Thc hin cc query a. Chn cc bng tham gia truy vn CTNX; DMHH a cc field SOHD, MAHH, MAKHO, SOLUONG; THANHTIEN : **IIf(>=300;0,9;1) vao li QBE SAVE QUERY vi tn QUERY1 b. a bng DMHH vo tham gia truy vn a cc field MAHH; TENHH; DONGIA vo li QBE. Trong phn Criteria ca MAHH nhp cu lnh sau : NOT IN (SELECT CTNX.MAHH FROM CTNX) c. u tin to mt truy vn ph (Query 3phu) tnh tng s tin hng ca tng kho : a bng QUERY1 vo tham gia truy vn Chn field MAKHO (group by), TONGTIEN: THANHTIEN (sum). – To mt truy vn tm tm S tin hng nhiu nht : a bng Query 3phu vo tham gia truy vn chn field tongtien (MAX) chn ch SQL VIEW copy cu lnh SQL (b du 😉 ng truy vn tm khng cn SAVE. – To truy vn chnh tm kho nhp nhiu tin hng nht : Chn Query 3phu v bng DMKHO a vo li truy vn cc field MAKHO; TENKHO; DIACHI; TONGTIEN ti phn criteria ca TONGTIEN dn cu lnh copy vo trong cp du ngoc n. d. a bng CTNX; DMHH; DMKHO vo tham gia truy vn Chn loi truy vn l Crosstab query Chn field a vo li QBE : MAHH; TENHH (group by Row heading), TENKHO (groupby column heading); SOLUONG (sum – value). Cu 3 : To form THEODOI a. Click create form by using wizard ln lt chn cc field MAHH, TENHANG; DONGIA (bng DMHH); SOHD; MAKHO (bng QUERY1); TENKHO; DIACHI (bng DMKHO); SOLUONG THANHTIEN (bng QUERY1) Click Next click Next click Next Chn kiu Industrial click next t tn form chnh l THEODOIHANGHOA, form ph l TDHH subform click finish. b. V ch design view sa i thit k li ging nh trong bi 23

c. Right click text box MAHH Change to combo box Right click text box MAHH Properties data xa trng Control source click chn Row source nhp cu lnh SELECT DMHH.MAHH FROM DMHH; (c du chm phy cui cu lnh) nhp A001 vo default value (xem hnh minh ha trang sau) Click Event chn After Update Code builder nhp on code sau vo gia Private sub v End sub

Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst “ = “” & Me! & “”” If Not rs.EOF Then Me.Bookmark = rs.Bookmarkd. To cc nt lnh bng Wizard (khng nn s dng code), ring nt xa to bng Macro ging nh hng dn trong Bi n thi s 01 e. Chn phn form footer ca subform to mt textbox rightclick chn properties chn th OTHER t tn TONGCON trong phn NAME ng hp thoi properties nhp cng thc =SUM() vo textbox TONGCON V textbox tng tin trn FORM chnh nhp cng thc = .Form!TONGCON Cu 4 : To bo co HOADON a. Click create REPORT by using wizard ln lt chn cc field SOHD, NGAY; LOAIHD (bng NXKHO); MAHH (bng QUERY1); TENKHO(bng DMKHO); TENHH(bng DMHH), SOLUONG (bng QUERY1); DONGIA (bng DMHH), THANHTIEN(bng QUERY1) Click Next click Next click Next Click chn Summary option nh du kim vo phn SUM – THANHTIEN click OK CLick Next Chn kiu Align Left 2 CLick Next Chn hnh thc Bold t tn cho report HOADON Click Finish b. Tr v ca s design trnh by report nh hnh sau, trong phn Textbox LOAIHD nhp cng thc sau : =IIf(DLookUp(“LOAIHD”;”NXKHO”;”SOHD=REPORT.SOHD”)=”N”;”NHP”;”XUT”)

Cu 5 : To Form DIEUKHIEN 24

Click Create form in Design View to Label CHNG TRNH Click chn Option Group trong hp cng c v Option Group trn mn hnh Trong ca s Wizard ln lt nhp 3 hng : M Form theo di; M Bo co ha n; Thot Click Next Click chn I dont want a default click Next click Next Click chn CHECK BOX Click Finish. Xa label Frame1 Right click trn ng khung ca Option Group chn Properties click Other t tn DIEUKHIEN trong Name To nt lnh thc hin (khng dng Wizard) Right Click nt lnh chn Properties Events On Click Macro Builder t tn Macro l DIEUKHIEN . Click View Conditions nhp Macro nh cc hnh sau

Click save close macro tr v ca s form. Chuyn qua ch View ta c c bng iu khin nh hnh sau

25

26

TRNG CAO NG KINH T K THUT BNH DNG —————————– N THI HT MN MS ACCESS S 6 THI GIAN : 120 PHT 1. To cc bng , thit lp mi quan h v nhp d liu vo bng theo yu cu sau a. Bng Nhaxuatban: MANXB(T,4); TENNXB(T,40) b. Bng Tacpham :MATP(T,4); TENTP(T,40); MANXB(T;4); TACGIA(T; 50); TSLUONG(N;Integer); NGAYNHAP(D;Short date) c. Bng The : MATHE(T,4); TENBANDOC(T;40); DIACHI(T;50); SODT(T;10) d. Bng Chitietthe : MATHE(T,4); MATP(T,4); NGAYMUON(D;Short date); NGAYTRA(D;Short date)

2. To cc truy vn theo yu cu sau : a. To truy vn th hin thng tin sau : MATHE; TENBANDOC;MATP;TENTP;NGAYMUON; NGAYTRA b. Tm bn c mn sch nhng cha tr :MATHE, TENBANDOC, TENTP, NGAYMUON c. Tm bn c no mn nhiu sch nht : MATHE, TENBANDOC, TSOSACH d. To truy vn th hin thng tin s sch mn mi ngy nh sau

Viết một bình luận