Bi ging Tin Hc Qun L - HLH2011 1 BI 1: TNG QUAN V CSDL V ACCESS Mc ch yu cu:Sau khi hc xong bi ny sinh vin nm c: -L do cn s dng CSDL qun l -Cc khi nim c bn v CSDL v H qun tr CSDL -Phn tch cc yu cu thit k mt CSDL n gin -Lm quen vi mi trng Access v cc i tng trong Access -Cc bc c bn thit k mt CSDL phn tch Kin thc yu cu: - hc qua mn Tin hc i cng -Nm vng kin thc v Microsoft Excel S tit ln lp: 5 Bng phn chia thi lng STTNI DUNGS TIT 1CSDL v nhng khi nim c bn1 2C bn v phn tch v thit k CSDL2 3Gii thiu tng quan v Access1 4Bi tp tho lun1 Trng tm bi ging: Bit vn dng t vic phn tch yu cu thit k theo ng trnh t v mt cu trc mt CSDL n gin trong Access. 1.C S D LIEU VA NHNG KHAI NIEM C BAN Dn nhp: S ra i ca my tnh in t v nhng tin ch do n mang li c nh hng tch cc n i sng con ngi trong mi lnh vc t khoa hc cng ngh cho n sinh hot gii tr, t cng vic cho n hc tp v rt nhiu lnh vc khc. Nhu cu lu tr v x l cc d liu ny sinh trong mi cng vic, mi hot ng ca con ngi ngy cng gia tng v tr nn kh khn khi thc hin bng th cngBi ging Tin Hc Qun L - HLH2011 2 V d:Qun l nhn s tin lng; Qun l ti sn, hng ho, sn phm vt t; QunlHschngt:Hpngkinht,Hshcsinh(Qun lgiov),Hs bnh n (Qun l y v), Qun l sch (th vin) Vi s xut hin ca my tnh in t, cho php lu tr v x l d liu mt cch tnglmchocngviccangiqunltrnnnhnhnghnbaogiht, ngy nay c rt nhiu phn mmng dng phc v cho cc cng tc qun l bng my tnh in t. thc hin cng vic ny, trc tin phi bit t chc lu tr d liu cn qun l sao cho c th x l v khai thc mt cch tt nht. V d: vi bi ton qun l im thi ca sinh vin; nu lu tr d liu theo dng bng vi cc ct MASV, HOTEN, MONHOC, TENKHOA, DIEMTHI th cc ga tr ca cc ct HOTEN, MONHOC, TENKHOA s b trng lp. MASVHOTENMONHOCTENKHOADIEMTHI 99001TRAN DAN THUCO SO DU LIEUCNG NGH THNG TIN3.0 99002NGUYEN HA DA THAOCO SO DU LIEUCNG NGH THNG TIN8.0 99001TRAN DAN THUTIN HOC VAN PHONGCNG NGH THNG TIN6.0 99005LE THANH TRUNGTIN HOC VAN PHONGANH VAN5.0 S trng lp ny gy nn mt s vn : -Ta khng th lu tr mt sinh vin mi khi sinh vin ny cha c im thi -Khi cn sa i h tn sinh vin th ta phi sa tt c cc dng c lin quan n sinh vin ny. iu ny d gy ra tnh trng d liu thiu nht qun. -Khi c nhu cu xa im thi ca mt sinh vin ko theo kh nng xa lun h tn sinh vin . Vi cch t chc ny, s xut hin nhiu d liu trng lp, chim dng khng gian lu tr v s c nhiu kh nng sai st khi thao tc. Do cn chn la mt hnh thc t chc khc tt hn chng hn tch bng d liu ln ra thnh nhiu bng con, nh m hnh di. MASVMAMHMAKHOADIEMTHIMASVHOTEN 99001CSDLCNTT3.099001TRAN DAN THU 99002CSDLCNTT8.099002NGUYEN HA DA THAO 99001THVPCNTT6.099005LE THANH TRUNG 99005THVPAV5.0 Bi ging Tin Hc Qun L - HLH2011 3 MAMHTENMHSOTIETMAKHOATENKHOA CSDLCO SO DU LIEU90CNTTCONG NGHE THONG TIN THVPTIN HOC VAN PHONG90AVANH VAN Tp hp cc bng ny v mi quan h gia chng to nn mt c s d liu (CSDL) 1.1 CSDL (DB_Database) l g ? CSDL l 1 h thng cc thng tin c cu trc c t chc 1 cch chn lc, ghi trnccthitbtrtin,nhmphcvngthichonhiungi,vinhiumcch khc nhau. C th hn mt c s d liu c th nh ngha tm nh sau: mt tp hp cc bng d liu cquan h vi nhau, mi bng gm cc mu tin (dng) v cc trng d liu (ct). 1.2 H qun tr CSDL (DBMS_Data Base Mangement System) Mt h qun tr CSDL (HQTCSDL) l: -mt tp cc phn mm qun l CSDL v cung cp cc dch v x l CSDL cho cc nhng ngi pht trin ng dng v ngi dng cui.-HQTCSDL cung cp mt giao din gia ngi s dng v d liu. chng trnh 1Chng trnh 2Chng trnh 3CSDL vatlyNSDHQTCSDLHnh 1.2.1 - HQTCSDL cung cap phan giao dien gia NSD va CSDLNgiQTCSDLNgiPTD 2.KHI NIM V BNG Bng l i tng ch yu cha cc thng tin cn qun l, c th ch l mt vi a ch n gin hay c vi chc nghn bn ghi cha ng thng tin lin quan n cc hot ng SXKD camt cng tyxut nhp khu no . Trc khi ta mun lm vic vi bt k mt CSDL no th ta phi c thng tin qun l, cc thng tin nm trong cc bng, n l c s cho ngi s dng to cc i tng khc trong CSDL nh truy vn, biu mu, bo biu... BnglnichadliuvmtitngthngtinnonhSINHVIN, HA N,... Mi hng trong bng gi l mt bn ghi (record) cha cc ni dung ring ca i tng . Mi bn ghi ca mt bng u c chung cu trc, tc l cctrng (field). V d: Cho bng di y qun l l lch khoa hc cn b trong trng i hc, Bi ging Tin Hc Qun L - HLH2011 4 ccctrngMACB(Mcnb),TRINHDOVH(Trnhvnha),CHUYENMON (Chuyn mn),... Trong mt CSDL c th cha nhiu bng, thng mi bng lu tr nhiu thng tin (d liu) v mt i tng thng tin no , mi mt thng tin u c nhng kiu c trng ring, m vi Access n s c th thnh nhng kiu d liu ca cc trng. 3.CC BC THIT K CSDL Bc 1: Xc nh mc tiu khai thc CSDL ca chng ta. iu ny quyt nh cc loi s kinchng ta s a vo MS Access. Bc 2: Xc nh cc bng d liu cn thit. Mi i tng thng tin s hnh thnh mt bng trong CSDL ca chng ta. Bc 3: Sau khi xc nh xong cc bng cn thit, tip n ta phi ch r thng tin no cn qun l trong mi bng, l xc nh cc trng. Mi loi thng tin trong bng gi l trng. Mi mu in trong cng mt bng u c chung cu trc cc trng. V d: Trong l lchkhoa hccnb,nhng trng(thngtin)cnqun l l:HVTN, CHUYN MN, HC V, HC HM,... Bc 4: Ch nh trng hoc cc trng lm kho cho bng Kho ca mt bng l mt hay nhiu trng c dng phn bit cc record trongbngtrnhtrnglpthngtin,nicchkhcvi2recordbtktrongbngth chng phi c gi tr kho khc nhau, ngoi ra kho cn dng ni thng tin c lu gi trong cc Table ring bitV d: mi khch hng phi c mt m khch phn bit V d, ni mt khch hng vi tt c cc n t hng ca khch hng mi Table trong c s d liu ca bn phi cha mt trng hoc tp hp cc trng c cc c tnh ng nht vi mi mt Record ring bit trong Table. Mt trng hoc tp hp cc trng nh th c gi l mt kha chnh (Primary key). Bc 5: Xc nh cc quan h gia cc Table By gi chng ta chia thng tin thnh cc Table v cc trng kha chnh nh danh, bn cn mt phng php bo cho Microsoft Access cch a thng tin c lin quan tr v vi nhau theo mt cch thc y ngha. thc hin iu ny, bn cn xc nh cc quan h gia cc Table trong mt c s d liu Microsoft Access. C ba loi quan h c bn gia cc table -Quan h 1--1 (mt--mt):mimt record trong bng ny c quan h vi mt record tng ng trong bng kia Bi ging Tin Hc Qun L - HLH2011 5 -Quan h 1-- (mt--nhiu): mi mt record trong bng ny c quan h vi mt hay nhiu record tng ng trong bng kia -Quanh-- (nhiu--nhiu):mimtrecordtrongbngnycquan hvi mt hay nhiu record tng ng trong bng kia v ngc li Bn s nhn thy rng vic xem cc quan h trong mt c s d liu hin ang c thit k tt l mt iu ch li.V d,m c s d liu mu Hoadon, ri nhp Relationships trn menu Tools xem cc quan h gia cc Table ca n. Bc 6: Tinh ch, hiu chnh li thit k. Phn tch li thit k ban u tm li, to bng d liu v nhp vo vi bn ghi, th xem CSDL phn nh th no vi nhng yucutruyxutcachngta,crtcktqungtnhngbngdliu khng. Thc hin cc chnh sa thit k nu thy cn thit. Bc 7. Nhp d liu v to cc i tng c s d liu khc Khi hi lng vi cu trc Table p ng c cc nguyn tc thit k c m t y, th n lc phi tip tc b sung tt c d liu ang c vo Table. Sau ,tip tc thit k cc Query, Form, Report, cc trang truy cp d liu, Macro v cc Module . 4.C BN V ACCESS 4.1 KHI ONG ACCESS Start Program Microsoft Office Microsoft Access 2003 4.2 C S D LIEU 4.2.1 Tao mi Chon File New Chon tiep Blank Access database, OK => File New Database Bi ging Tin Hc Qun L - HLH2011 6 File Name : nhap ten c s d lieu Save In : chon folder cha d lieu. 4.2.2 M file File Open=> hoi thoai Open4.3 TABLE 4.3.1 Thiet ke :New Design view OK hoac Double click vao Create Table in design view Filed Name : Ten Filed ( cot) Data type : kieu d lieu Description : ghi chu ( muon nhap c tieng viet phai chon Unicode) Lu y : Trong khi thiet ke phai chon khoa chnh : chon field - Khi thiet ke xong phai lu lai - 4.3.2 Tao quan he gia cac Table - T menu Tools RelationShip=> xuat hien ca so Show Table- Chon cac Table tham gia vao quan he : double click vao ten Table hoac chon Table va bam vao nut Add. - Tao quan he : Keo chut t field quan he cua Table chnh en field quan he cua Table quan he. Bi ging Tin Hc Qun L - HLH2011 7 Chon Enforce Refertial Integrity, OK 4.3.3 Nhap d lieu Tai ca so c s d lieu : chon table Open Tai ca so thiet ke : View Datasheet view 5.Bai Tap:5.1 Cc cu hi hiu bi Cu 1: Trong access bng (table) l g? A. L i tng lu d liu bao gm ct(trng), dng(bng ghi) B.L mt i tng hin th d liu C.L i tng dng in v tng hp d liu D.L mt i tng cha th tc lp trnh Cu 2: Tn trng(fields) trong access c quy nh nh th no? A.Khng bt ul du cch, khng cha k t :(.),(!),([),(]),ti a 64 k t B.Khng bt u l ch s C.Tu vo ngilp trnh nhng khng vt qu 255 k t. D.N khng theo mt quy cch no c Cu 3: Kiu trng (data type) dng lm g? A.Quy nh d liu cn a vo cho ph hp vi ngi s dng B.N khng tc ng gc vo d liu ngi dng C.N dng tc ng ni tng lp trnh sau ny D.N dng thit lp thuc tnh ng sai ca d liu Cu 4: Quy tc xy dng mt csdl (table)A.Mi trng trong bng phi m t mt hot ng duy nht B.Cc trng trong bng phi y v lin quan n kho c bn C.Mibngphicmttrngtithiumnhkhngcbnghinotrng nhau D.c th thay i cc trng bt k (tr trng kho )m khng lm nh hng n trng khc Bi ging Tin Hc Qun L - HLH2011 8 Cu 5: to cu trcbng (Table) michn cch no trong cc cch sau: A.design view B.datasheet wiew C.table wizard D.link table Cu 6: Kho c bn l ? A.L mt trng trong bng xc nh cc thuc tnh khc cu thc th B.L s trng ti thiu trong bng C.L xc nh mi quan h gia cc bng D.l mt hoc nhiu trng trong bng m tho mn tnh cht khng c bn ghi no trong bng c cc trng kho chnh trng nhau v kho chnh s xc nh cc thuc tnh cn li ca thc th Cu 7: CSDL l ? A.l mt tp hp cc bn ghi (records) v cc tp tin (files) c t chc theo mt mc ch no B.l Tp hp cc bng(tables) C. l nhng d liu ri rc, khng c quan h vi nhau D. l tp hp cc file cha thng tin c t chc theo mt mc ch no Cu 8: Access l mt phn mm: A. c tch hp lun trong h iu hnh Windows ca Microsoft B. c lp vi h diu hnh , ci t ring. khng c phn mm no khc i km C. Tch hp trong b Microsoft Office D. Tt c cc la chn trn Cu 9: Cc i tng chnh trong Access la. bng, truy vn, biu mu , bo biu b. bng , truy vn, biu mu, mdun c. bng, biu mu, bo biu, mdun, macro d. bng , truy vn, biu mu , bo biu, trang truy nhp d liu, macro, mun Cu 10: Cc thnh phn no trong cc thnh phn sau c ni n trong bng a. thuc tnh, trng, hng, m b. trng(ct), bn ghi(hng), kho chnh(primary key) c. gi tr, tham s d. Khng no ng 5.2 Bi tp trn lp Thit lp c s d liu vi cc table theo mu sau Yeu cau: T xac nh cau truc cac table can c vao d lieu cha trong o Xac nh kieu d lieu cho tng field. Cho biet ng vi kieu d lieu a chon th can phai hieu chnh cac thuoc tnh nao? Qui nh khoa chnh cho cac table Thiet lap quan he gia cac table. Cho biet th t nhap d lieu cua cac table.Bi ging Tin Hc Qun L - HLH2011 9 5.3 Bi tp thc hnh v v nh Thit k CSDK sau theo mu cho trc Table Nhan Vien Bi ging Tin Hc Qun L - HLH2011 10 Ma hvhoTenhuhgay hvBia chiBien thoaihinh anh 1 hguyen hgochgaTRuE8/2O/O1 1O hung vuJng F4 05 885G158 2 ha vinhFhatFALSE7/12/O1 8O Bong KhJi 01 8852O74 8 Tran Tuyet0anhTRuE2/27/O1 2G Le 0uy Bon FG 08 85577O8 4 hguyen KimhgocTRuE8/8O/O2 178 hau 0iang FG 0G 8558278 5 TruJng 0uyhungFALSEO/18/O2 77 TruJng Binh FG 08 8O4O2O5 G LuJng BaThangFALSEO/18/O2 O2 Le Thanh Ton 01 8O4O54O 7 Lam SJnhoangFALSE11/2O/O2 74 Ky Con 01 874O874 8 hguyen MinhhongFALSE1/8O/O8 224 Lac Long 0uan 8852578 O vuJng hgocLanTRuE1O/12/O8 227 hai Ba Trung FG 08 8G51G8G Table Khach Hang Ma KhTen CtyBia chiThanh hoBien thoai C00AC0CJ khi dan dung 584 Le van Sy F14 KhAhh h0A(O58) 8G472O7 C0TECCong nghe mJi 81 Trang Tuha hoi( O4) 88GO254 RuBMEXCong nghieo cao su 84 Binh Tien F8ha hoi( O4) 8452178 vTC0hoa nhua vinh Tien 11 van TuJng F18KhAhh h0A(O58) 87OG54O SAMAC0SXK0 0ich vu tong hJ 17O hau 0iang FGhai Fhong(O81) 8G817O2 Table San Pham Bi ging Tin Hc Qun L - HLH2011 11 Ma SFTen SFBJn vi tinhBJn gia hinh 1 RuJuChai28O.5O 8 Banh kemCai1O.OO 4 BJKg88.OO 5 Banh miCai8.OO G hemKg28.7O 7 TaoKg5.OO 8 Ca hoThungG2.5O O Suaho7OOO.OO Table Hoa Don Ma h0Ma KhMa hvhgay la h0hgay nha n ha ng 1O144RuBMEX71/5/O11/15/O1 1O145SAMAC0O1/15/O11/25/O1 1O148RuBMEX12/5/O22/2O/O2 1O15OC00AC042/15/O2 8/15/O2 1O15GC0TEC412/4/O812/5/O8 Table Chi Tiet Hoa Don Ma h0Ma SFSo luJng 1O144185 1O14481O 1O145112 1O145G2O 1O148724 1O148812 1O15OGO 1O15G125 Bi ging Tin Hc Qun L - HLH2011 12 CSDL KQTHI TABLEDSHSTABLE DIEM SOBDHOTENNUNGAYSINH LOPSO BDTOANLYHOAVAN 00001Nguyen VietHong04/6/7410A1000014.5748 00002Tran VanMinh04/19/7510A10000254.5107 00007Vu HuuTuan010/14/7510A10000744.544 00008Tran ThanhPhong03/23/7410A10000843510 00013Nguyen MinhQuang07/12/7510A10001384.555.5 00014Trang PhiHung011/4/7510A1000148.544.510 00019Nguyen KimToan-17/9/7510A1000194.544.56 00020Nguyen BichLien-15/29/7510A100020844.54.5 00025Luu VuCam08/23/7510A1000252.584.54.5 00026Doan DucChi010/27/7410A10002645.5810 00031Nguyen VanDoai08/14/7510A10003145.55.58.5 00032Lam VanXia01/3/7510A10003257.57.57.5 00037TranKhiem012/6/7410A1000374.5886.5 00038Le BichPhuong-17/26/7410A1000384.56.54.54 00043Pham HoaiBong010/13/7310A10004394.585 00044Vu Thi MyLinh-16/24/7410A10004467.5710 00049Ma Thi HongXuan-110/21/7410A10004945.54.55.5 00053Luong KhaiTruyen012/12/7410A2000534.55.569.5 00054Do VanThanh011/10/7410A2000544.54.54.59 00059Nguyen ThanhThu05/11/7510A2000594.51089 00060Nguyen Truong Son08/7/7510A2000602.544.58.5 LOPLOPChu NhiemHINH 10A1Nguyen Thanh Nhan 10A2Le Van Hien Bi ging Tin Hc Qun L - HLH2011 13 CSDLVANTAI TABLE KHO HANGMAKHOTENKHO 01Kho cang Tan Thuan 02Kho cang Nha Be 03Kho Tan cang 04Kho CTY XNK Vung Tau TABLE MAT HANG MAHANGTENHANG 01Thc an gia suc 02Vat lieu xay dng 03Vat lieu gia cong may mac 04Hoa chat 05Vat lieu phu tung thay the 06Hang may XNK 07Phu kien ien TABLEXE MAXECHUXEHINH 00o Minh Hoai 01Le Hong Hanh 02Khong Van Minh 03Gia Cat Lng 04Nguyen Van To 05Pham Th Van 06o Duy Ty 07Le Huynh c 08Nguyen Hong Sn 09Bui Th Nh Y TABLEHANG VAN CHUYEN NGAYMAXEMAKHOMAHANGKHOILUONG 6/1/940701013000 6/1/940702055020 6/1/940403023000 6/1/940901013000 6/1/940102015099 6/1/940404033000 6/1/940303066861 6/1/940703043000 6/1/940903046745 6/1/940103025374 6/1/940202055030 6/1/940301015502 6/1/940304045100 6/2/940804075060 6/2/940901035010 6/2/940702053000 6/2/940603063000 6/2/940202057995 6/2/940602055915 6/2/940801033700 6/2/940003045362 Bi ging Tin Hc Qun L - HLH2011 14 BI 2: TABLE Mc ch yu cu:Sau khi hc xong bi ny sinh vin nm c: -Hin thc ha mt CSDL phn tch bng Access -Cc bc c bn thit k mt table trong Access -Cch thit lp cc thuc tnh cho mt field trong Table -To kha v thit lp quan h (Relationship) gia cc table -Nhp d liu mt cch chnh xc theo ng trnh t thit k Kin thc yu cu: - hc qua bi 1 S tit ln lp: 5 Bng phn chia thi lng STTNI DUNGS TIT 1Cc kiu d liu trong Access 2 2Cc thao tc x l thit k Table2 3Bi tp tho lun1 Trng tm bi ging: T d liu cho trc bit cch thit k mt CSDL tng ng trong Access, cch la chn tn field, kiu d liu ph hp, thuc tnh ph hp, to kha, lin kt cc table vi nhau v cch nhp d liu mt cch chnh xc. 1. Ca so Design (thiet ke) 1.1 Cac kieu d lieu va ca c thuoc tnh 1.1.1 Text Field size : so ky t toi a Format : Qui nh d lieu xuat. V du: > : oi tat ca ky t nhap vao thanh ch hoa; < : oi tat ca ky t nhap vao thanh ch thng Caption : Ten cua field c hien ca so Datasheet Default value : gia tr mac nh Validation Rule : Rang buoc mien gia tr cua gia tr nhap Validation Text : Thong bao khi nhap d lieu sai so vi rang buoc tren. Required (Yes / No ) : Bat buoc nhap d lieu Allow Zero Length(Yes/No) : c nhap chuoi rong Indexes (No, Yes(duplicate OK), Yes(No Duplicate)) : sap xep d lieu Input Mask : Qui nh d lieu nhap. Ban co the phoi hp cac ky t ieu khien sau: Bi ging Tin Hc Qun L - HLH2011 15 0 : V tr danh cho mot ky so 0-9 (bat buoc nhap) 9 : V tr danh cho mot ky so 0-9 (khong bat buoc) # : V tr danh cho mot ky so 0-9, dau +, -, trong L : V tr danh cho mot ky t ch (bat buoc) ? : V tr danh cho mot ky t ch hoac trong (khong bat buoc) A : V tr danh cho mot ky t ch hoac so (bat buoc) a : V tr danh cho mot ky t ch hoac so (khong bat buoc) & :V tr danh cho mot ky t bat ky (bat buoc) C : V tr danh cho mot ky t bat ky (khong bat buoc) . ; : , Dau.;:, < : Cac ky t ben phai ky hieu bien thanh ch thng : Cac ky t ben phai ky hieu bien thanh ch hoa V du : Input MaskD lieu ungD lieu sai 00001234, 3568123, 123A 009912, 123, 12341, 12A Neu ban qui nh Input Mask la Password th nhng ky t anh vao se hien th dau* (dang mat khau) 1.1.2Number Field size Gia trMien gia trSo so leSo Byte Byte0 en 25501 Integer- 32768 en 3276702 Long Integer- 214783648 en 21478364704 Single - 3.4 x 1038 en 3.4 x 103874 Double-1.79 x 10308 en 1.79 x 10308158 Format DangHien thMo ta General Number3456.789 Currency$ 3,456.79Co ky hieu tien te au va co dau cach hang ngan Fixed3456.79So so le co nh Standard3,456.79Co dau cach hang ngan Percent79%Dng phn trm Scientific3.46E+03 DecimalPlace : So ch so thap phan 1.1.3 Date/Time Format DangHien th General Date19/6/945:34:23PM Bi ging Tin Hc Qun L - HLH2011 16 Long DateSunday, June 19, 1994 Medium Date19-June- 94 Short Date19/06/94 Long Time5:34:23 PM Medium Time5:34 PM Short Time17:34 1.1.4 Yes/No Format : Yes/NoTrue/FalseOn/Off bo trong: hien th -1/0Nam;N: hien th Nam/ N Trong cac trng hp tren eu phai chon Lookup Display control : Text box Khi nhp d liu : ch nhap 1 hoac 0.1.2 Thuc tnh Lookup Row source Type Table/Query Table/Query : d lieu lay t Table hay Query Value List : d lieu t nhap vao Field List: D lieu la ten cac field Row Source : ten Table/Query hoac d lieu t nhap KhachHang Bound Column : cot c chon e lay gia tr 1 Column Count : So cot hien th 1 Limit To List : No co the nhap gia tr khac noi dung trong Combo Box 1.3 Cac thao tac x lythiet ke Table 1.3.2Chen 1 Field vao gia cac Field hien co -Nhap Mouse o xam ben trai dong ng vi Field b chen -An phm Insert (hoac chon Menu Insert, chon Rows) -Khai bao ten va kieu d lieu cua field 1.3.3Xoa Field -Nhap Mouse o xam ben trai dong ng vi Field xoa -An phm Delete (hoac chon Menu Edit, Delete Rows). 1.3.4Thay oi v tr Field - Nhap Mouse o xam ben trai dong ng vi Field muon thay oi khi Mouse co dang mui ten- at Mouse v tr trenva keo Mouse Bi ging Tin Hc Qun L - HLH2011 17 1.4 Che o Datasheet 1.4.1 Vao/ ra che o Datasheet View -Che o Datasheet View cho phep xem va cap nhat d lieu cua Table. -Vao cheo Datasheet View : oNeu ang ca so Database :anh dau ten Table, Nhap Open oNeu ang che o Design View : Menu View, Datasheet View - Ra khoi che o Datasheet View : oChuyen sang che o Design View: Menu View, Design View oong ca so Table hoac Menu File, Close 1.4.2Cac thao tac cap nhat d lieu 1.4.2.1Them Record Ch c them vao cuoi Table 1.4.2.2Xoa Record Nhap Mouse tai o xam ben traicua Record xoa An Delete 1.4.2.3Tm kiem Bc 1 : Nhap mouse tai cot cha gia tr muon tm va tai dong bat au tm Bc 2 : Chon Menu Edit, Find hoac Ctrl - Fse hiep hop thoai Bc 3 : Khai bao gia tr tm va cach tm Find What : Go gia tr tm Look In : Ten field(tm trong 1 field) hoac Ten Table ( tm trong tat ca cac field) Search : Hng tm Up: tm len tren Down : tm xuong di All :Tm tren toan bo Bi ging Tin Hc Qun L - HLH2011 18 Match : Chon cach so sanh gia tr tm vigia tr trong o Start of Field : Gia tr tm la phan au cua o Any Part of Field: Gia tr tm la 1 phan (bat ky)trong oWhole Field : Gia tr tm bang gia tr trong o Match Case : Tm co phan biet ch hoa, ch thng (neu chon) Search Field as Formatted: Tm theo dang hien cuacot(neu chon) Bc 4 : Thi hanh lenh -Nhap Find First e bat au tm. Neu tm thay Access se anh dau khoi d lieu tai v tr tm thay. -Muon tm tiep nhap Find Next -Chon Close khi cham dt tm 1.4.2.4Thay the : Replace Tung t tm kiem nhng co them o Replace With (noi dung thay the) 1.4.2.5Sao chep va di chuyen d lieu - Sao chep : + anh dau d lieu can sao chep, chon Menu Edit, Copy + a con tro en v tr ch, chon Menu Edit, Paste - Di chuyen : + anh dau d lieu can di chuyen, chon Menu Edit, Cut + a con tro en v tr mi, chon Menu Edit, Paste 1.4.2.6Nhap va sa cha d lieu kieu OLE Object Bc 1 : Nhap o nhap Bc 2 : Chon Menu Insert, chon Object, hien hop thoai Bi ging Tin Hc Qun L - HLH2011 19 Bc 3 : Chon Create New: Tao mi oi tng va chon phan mem tao oi tng, OK. Create From File: Chon oi tng la 1 file va go ten tap tin hoac nhap nut lenh Browse e chon file. Bc 4: Neu xem/sa oi tng: Nhap up tai o cha oi tng. 1.4.3Cac thao tac ieu chnh tre n ca so Datasheet View 1.4.3.1Chon Font chChon Menu Format, Font vachon Font ch 1.4.3.2ieu chnh o rong cot -Chon cot can ieu chnh -Neu ieu chnh nhanh bang Mouse: oat mouse tai canh phai o ten cotoKeo mouse hoac nhap up (o rong cot va u) -Neu ieu chnh cot bang lenh Menu Format, Column Width se hien hop thoai va chon 1 trong 3 lenh sau: oColumn Width: o rong tuy chon oStandard Width: o rong mac nhien oBest Fit: o rong va u -Xong chon OK 1.4.3.3Thay oi v tr cot - Nhap tai ten cot chon (chon cot) - at Mouse o ten cot, keo Mouse 1.4.3.4An cot Nhap cot chon, chon Menu Format, Hide Column Neu can hien lai cot a an: Nhap Menu Format, chon Unhide Columnse hien hop thoai. oNhap tai hop chon ten cot can hien (co chon hien, bo chon an)oNhap Close khi cham dt 1.4.3.5Gi co nh cot Bc 1: Gi co nh cot Chon cac cot can co nh,chon Menu Format, Freeze Columns. Bc 2: Neu can thoi gi co nh cot: Chon Menu Format, UnFreeze All Columns 1.4.3.6Sap xep Record- Neu khoa sap xep la 1 cot , co the thc hien theo 2 cach sauBi ging Tin Hc Qun L - HLH2011 20 C1 : Nhap tai cot chon, nhap nut e sap xep tang dan hoac e sap xep giam dan. C2 : Chn Menu Records, Sort, Sort Ascending (tng dn)/Sort Descending (gim dn). - Neu khoa sap gom nhieu cot Bc 1:Menu Records, Filter, Advanced Filter/Sort se hien ca so Dong Field: nhap chon cot lam khoa sap bang phan tren, roi keo tha vao 1 cot tai dong nay (ca c cot tren dong Field c sap t trai sang phai theo th t u tien). DongSort:chonAscending(saptangdan)hoac Descending (sap giam dan). Vd:spxptheothttngdntheoTnvgimdn theo h Bc2:Thihanhlenh:chnMenuFilter, Apply Filter/Sort Bo sap xep : bam vao nut hoac chon Filter , Remove Filter/Sort 1.4.3.7Loc Record - Neu ieu kien loc theo 1 cot: Nhap nut phai Mouse tai cot nay. Neu loc theo 1 gia trcua cot : ban nh ap nut phai Mouse tai gia tr can loc , roi chon Filter By Selection hoac go ieu kien loc tai muc Filter For Xong go Enter. - Neu ieu kien loctheo nhieu cot: Bc 1: Khai bao ieu kien loc : Nhap Menu Records, Filter, Advanced Filter/Sort. Cac dong noi dung: Field: Chon cac cot lam ieu kien loc.Criteria, or...: Qui nh ieu kien locBc2:Thihanhlenhloc:bamvaonuthoacchonMenu Filter, Apply Filter/Sort). - Neu can hien lai tat ca cac mau tin th nhap nut phai Mouse tai 1 v tr nao o trong bang roichon Remove Filter/Sort hoac nhap tai nut. 1.4.4 In nhanh d lieu Chon Menu File, Print se hien hop thoai Print All: In tat ca cac mau tin Pages: In theo trang t trang o From en trang o To.SelectedRecord(s):Chincacmautinang chon 1.5 Sao chep, xoa, oi te n table e thc hien sao chep, xoa, oi ten Table, can phai tr ve ca so CSDL. Bi ging Tin Hc Qun L - HLH2011 21 1.5.1Sao chep Bc 1: anh dau ten TableBc 2: Chon Menu Edit, Copy Bc 3: Chon Menu Edit, Paste se hien 1 hop thoaiTable Name: Khai bao ten Table ch. Paste Options: Chon dang sao chep Structure Only: Ch lay cau tru Table Structure and data: Lay cau truc Table va d lieu Append Data to Existing Table: Them d lieu vao cuoi Table ch (ang ton tai). 1.5.2oi ten Nhap nut phai Mouse tai Table can oi ten, chon Rename. Go ten mi cho Table, xong go Enter1.5.3Xoa Chon Table, an Delete (hoac nhap nut phai mouse tai Table, chon Delete). 1.5.4Copy mot Table t CSDL khac ChonMenuFile,GetExternalData->Import(HoacnhapnutphaiMousetaiphantrong ca so Database, roi chon Import). Chon File cha d lieu can Import, chon Import th se hien hop thoai Import ObjectChon table hay oi tng khac , Ok 2. Bai tap 2.1 Cc cu hi hiu bi Cu 1:thit k (table) nhp d liu trng thangngay nh sau: S dng thuc tnh no? A.(fields size) g :mm/dd/yyyy, (input mask) g: 99/99/99 B.(fields size) g :dd/mm/yy, (input mask) g: ../../.. C.(fields size) g mm/dd/yyyy,(default value) g : ../../.. D.(fieldssize)chnGeneralDate,(defaultvalue)g 12/30/1990 E.tt c trn u sai Cu 2: thit k (table) nhpd liutrngdiemtoan , kiu number vi iu kin >=0 v =0and=0and20 C.trong mc validation rule g 20 D.trong mc validation rule g >20 Cu 5: thit k trng ngia kiu Currency khi nhp d liu c dng:Chn thuc tnh no trong cc thuc tnh sau A.trong mc formatg: 0vnd B.trong mc formatg:vnd C.trong mc input mask g :0vnd D.trong mc default value g:vnd Cu 6: thit k trng hoten kiu: text m khi nhp d liu lun lun i sang ch in hoa v d(khi g:hong hngthkt quHONG HNG) ta chn thuc tnh no : A.trong mc Format nhp:> B.trong mc Format nhp:upper([hoten]) C.trongmcvalidationrulenhp:upper([hoten]) D.trong mc validationrulenhp:< E.tt c cuu sai Cu 7: thit k trng sbdkiu text m khi nhp d liu thnh tn trng y s bodanh,chn thuc tnh no trong cc thuc tnh sau: A.Trong mc caption nhp:s bo danh B.Trong mc format nhp: s bo danh C.Trong mc input masknhp: s bo danh D.Trong mc default value nhp: s bo danhBi ging Tin Hc Qun L - HLH2011 23 2.2 Bi tp trn lp 1.Thay oi cac thuoc tnh cua cac field nh sau C s d lieu HoaDon.MDB FieldData TypeDecriptionCac thuoc tnh MaNVSo nguyenMa nhan vien HoTextHo nhan vienToi a 20 ky t, bat buoc nhap TenTextTen nhan vienToi a 8 ky t NuYes/NoPhai cua NVHien th Nam/N, mac nh N NgayNVDate/timeNgay nhan viec=0 C s d lieu KQTHI.MDB FieldData TypeDecriptionCac thuoc tnh LOPTextTen lp4 ky t : ky t au la ch va 3 ky t sau la soGIAOVIENTextHo ten giao vienToi a 25 ky t , bat buoc nhap MAHSTextMa so hoc sinh5 ky t so HOTextHo cua hoc sinhToi a 17 ky t , bat buoc nhap TENTextTen cua hoc sinhToi a 8 ky t , bat buoc nhap PHAIYes/NoPhai cua hoc sinhHien th Nam/N, mac nh N NGAYSINHDate/TimeNgay sinh cua HSHien th dang dd/mm/yyyy < ngay hien tai TOANSo thciem mon Toan Co 1 ch so thap phan, ieu kien >=0 va 15 g.Loc cac record co MaSP=1 va SoLuong > 1 Bi ging Tin Hc Qun L - HLH2011 27 BAI 3: QUERY - BANG VAN TIN Mc ch yu cu:Sau khi hc xong bi ny sinh vin nm c: -L do cn s dng truy vn khai thc d liu -Cch to mt truy vn bng cng c Query -Cc hm cn thit s dng trong truy vn -Cch s dng truy vn c iu kin Kin thc yu cu: - hc qua bi 1 v 2 -C kh nng vn dng mt s hm trong Microsoft Excel S tit ln lp: 10 Bng phn chia thi lng STTNI DUNGS TIT 1Nhng khi nim c bn v query1 2Cch thit k query v cc v d minh ha3 3Cc php ton trong query2 4Cc hm trong query2 5S dng truy vn vi iu kin 1 6Bi tp tho lun1 Trng tm bi ging: -Bit cch thit k mt query theo yu cu cn truy vn d liu.-Bit cch vn dng cc php ton, cc hm v iu kin vo cu lnh truy vn. 1.KHAI NIEM Query la cong cu khai thac va x ly d lieu cua Microsoft Access, no co theap ng cac nhu cau tra cu ve d lieu cua cac bang d lieu thuoc c s d lieu. DangQuerythng csdungnhieunhatlaSelectQuery(Bangvantinlachon).Bang loai Query nay, ta co the tham khao d lieu t nhieu bang d lieu khac nhau cua c s d lieu, cothe phan tch va hieu chnh d lieu ngay tren bang van tinhay s dung no lam c s cho motcong viec khac. Bang ket qua van tin c goi la Dyna Set (Dynamic Set). Mot Dyna set giong nh mot bang d lieu nhng khong phai la mot bang d lieu that s, no ch la cach tham khao (cach nhn ong- Dynamic View) oi vi d lieu c lay t nhieu bang khac nhau ma thoi. Bi ging Tin Hc Qun L - HLH2011 28 2.TAO MIV du 3.1: Tao query gom cac field : So bao danh , Ho Ten , Toan, ly , Hoa , Van , Tong im , LpBc 1: Tai ca so Database, chon Object la Queries,bamNew=>hienhopthoaiNew Query.ChonDesignView,Ok=>hoithoai Show Table. Bc 2: Xac nh ca cTable/Query tham gia van tin tai hop thoai Show Table. Cac nut chon: Table:NeuxacnhcacTablethamgiavan tin Queries : Neu xac nh cac Query tham gia van tin Both:NeuxacnhcocaTablevaQuerythamgiavan tin. Bc 3:Xac nh quan he gia ca c Table/Query neucac Table/Query cha co moi quan he Bc 4:Xac nh cac noi dung cua query Bc4.1: Xac nh cac Field co trong Queryoivicacfieldcosan(SoBD, Toan,): KeomousetaiFieldmuonchonvathavaovtr ch (cach nay co the giuptrong trng hp muon chen them 1 Field vao motv tr nao o) hoac nhap kep chuot tai filed muon chon. oi vi Field tnh toan th nhap trc tiep vao bieu thc. HoTen : Ho & & Ten Tong : Toan + Ly + Hoa + Van Bi ging Tin Hc Qun L - HLH2011 29 Bc 4.2: Xac nh ch tieu sap xep tai dong Sort Ascending: Sap theo th t tang dan Descending: Sap theo th t giam dan Not Sorted: Khong sap xep V du 3.2 : Sap xep theo th t tang dan cua Tong V du 3.3 :Sap xep tang dan theo Toan, va tang dan theo tong iem Chu y: Neu chon nhieu ch tieu sap xep th th t u tien t trai sang phai.Bc 4.3: Xac nh an hien noi dung cac Field tai dong Show. V du 3.4: Sap xep tang dan theo Lp va tangdan theo Tong iem

Bc 4.4: at ieu kien loc d lieu tai cacdong Criteria, OR... V du 3.5 : Loc cac hoc sinh thuoc lp 10A1 Bi ging Tin Hc Qun L - HLH2011 30 V du 3.6 :Loc cac hoc sinh co iem cac mon hoc eu >4 V du 3.7 : loc cac hoc sinh co t nhat 1 mon co iem di 5 -Cac hoc sinh thuoc Lp 10A2 va Tong > 32 -Cac hoc sinh co Tong>=20 va Tong < 30 -Cac hoc sinh thuoc Lp 10A1 hoac 10A2 -Cac hoc sinh co mon Toan > mon ly -Cac hoc sinh co ngay sinh sau nam 1973 -Cac hoc sinh co nam sinh la 1974 (*/*/1974) -Cac hoc sinh sinh trong thang 9 Bc 4.4 : Hien th mot so Record theo la chon Top Values. All: Hien tat ca cac Record cua Query 5: Hien 5 record au tien cua Query 25: Hien 25 record au tien cua Query100: Hien 100 record au tien cua Query 5%: Hien 5% so record au tien tren tong so record cua Query. 25%: Hien 25% so record au tien tren tong so record cua Query. V du 3.8 : tao query cha 5 hoc sinh co tong iem cao nhat -Sap xep theo th t giam dan cua Tong iem-Chon Top Values = 5 Bc 5 : Thay oi cac thuoc tnh cua FieldChon Field, Chon View Properties hoac bam vao nut Bc 6:Lu Query File - Save (hoac Ctrl + S) - at ten cho Query (Neu lu lan au tien). Chu y: Ten cua Query toi a 64 ky t va co the co khoang trong gia. Bc 7 : Xem ket qua cua mot Query Tai ca so Database : Chon Query can m, bam nut Open hoac nhap kep vao ten query Tai ca so Design: bam vao nut hoac Bi ging Tin Hc Qun L - HLH2011 31 3.Phep toan 3.1 Cac phep toan so hoc Ky hieuTenCu phapNoi dung +CongA+B -TrA-B *Nhan A*B /ChiaA/B ^Luy thaA^B \Phep chia nguyenA\BLay thng nguyen trong phep chia A cho B MODPhep ong dA MOD BLay so d trong phep chia A cho B 20 \ 8 = 2 20 Mod 8 =4 Chuy:Thtutientrencacpheptoanlaluytha,nhan,chia,phepchianguyen, cong tr. Ngoai ra co the s dung cac dau () e thay oi th t u tien. 3.2 Phep toan ghep chuoi Cu phap: & < Bieu thc chuoi 2> Cong dung:Ghep < Bieu thc chuoi 1> vao < Bieu thc chuoi 2>. Chu y: Neu thay phep toan & bang phep toan + th co the cho ket qua sai trong mot so trng hp. V du: HOTEN: HO & " " & TEN 3.3 Cac phep toan so sanh Ky hieuNoi dung =Bang >Ln hn =Ln hn hoac bang # 7/15/93 #and = and =0) : lam tron vi M so le SQR ( N ) can bac 2 cua n 4.2 Ham ve ngayYEAR(ngay ) nam MONTH ( ngay) :thang DAY ( ngay ) : ngay WEEKDAY(Ngay ) : th ( chunhat = 1) DatePart("q", Ngay ) : qu(q: quarter) Date() :ngay hien tai Now() : ngay gi hien tai 4.3 Ham ve chuoi Len (s) : chieu dai cua chuoi s Left ( s , n ) : n ky t ben trai cua s Right ( s , n ) : n ky t ben phai cua s Mid ( s , m , n ) : n ky t thuoc s tnh t v tr m Val ( Chuoi so ) : so LEN ( BIEN HOA) = 8 LEFT (BIEN HOA , 2) = BI RIGHT (BIEN HOA , 2 ) = OA MID (BIEN HOA ,2 , 3 ) =IEN 4.4 Ham iif I I F ( ieu kien , BT1 , BT2 ) VD. 3.10 : SoBD, Hoten, Toan, Ly, Hoa, Van, Tong, Ketqua, Lop Ket qua : au neu tong>=20, con lai la rt Ketqua : iif (Tong >=20 , au , Rt) Bi ging Tin Hc Qun L - HLH2011 35 VD.3.11 : SoBD, Hoten, Toan, Ly, Hoa, Van, TBinh, XepLoai, Lop Tbinh : (toan + ly + hoa + van)/4 XepLoai : iif ( Tbinh >= 8 , Gioi, iif ( Tbinh >=7 , Kha , iif(Tbinh >=5 , Trung bnh , Kem ))) VD3.12 : SoBD, Hoten, Toan, Ly, Hoa, Van, Tong, Ketqua, Lop Ket qua : au neu iem cua tat ca cac mon >=5, con lai la rt Ketqua : iif ( toan>=5 and ly>=5 and hoa>=5 and van>=5 , au,Rt) VD3.13 : SoBD, Hoten, Toan, Ly, Hoa, Van, Tong, Ketqua, Lop Ket qua : au neu Tong>=20 va co nhieu nhat la 2 mon di 5, con lai la rt KetQua : iif ( tong>=20 and ( iif( toan=7 + Kha: Neu tong iem >=28 va cac mon >=6 + Trung Bnh: Neu tong iem >=20 va cac mon >=4 + Trt: nhng HS con lai. Bi ging Tin Hc Qun L - HLH2011 37 2.Hay sap xep danh sach hoc sinh sau khi a xep loai nh query KETQUATHI theo th t tng lp hoc, trong cung lp th sap theo th t Xep loai, cung xep loai th sap theo tong iem giam dan. 3.Query: HSTBINH e len danh sach nhng HS at loai trung bnh trong ky thi. 4.Query: HS DIEM LIET e len danh sach nhng HS b iem liet trong ky thi (tc la co t nhat 1 mon co iem di 3). 5.Query: HS DIEM CAO e len danh sach ung 3 hoc sinh co tong iem cao nhat trong ky thi. 6.Query: XEPHOCLUC e xep loai hoc lc cho nhng HS aau trong ky thi bao gomho ten, lp hoc, iem trung bnh chung va xep loai hoc lc. Biet rang: -iem trung bnh thc = tong iem 4 mon/4 -iem u tien tnh nh sau: hoc sinh cac lp co ky t th 3 la A th c cong 0.3, cac lp co ky t th 3 la F th cong 0.5 va cac lp ky t th 3 la N th cong 0.15. -iem trung bnh chung = iem TB thc + iem u tien -Xep loai hoc lc HS c tnh nh sau: oHoc lc loai A: Neu iem trung bnh chung >8 vathi at loai kha tr len. oHoc lc loai B: Neu iem trung bnh chung > 6.5 oHoc lc loai C: nhng HS con lai. 5.3 Bi tp thc hnh Tao Query n gian 1.TaoQueryHOADON1chacacfiledMaHD,LoaiPhieu,NgayLapHD,TenSP,Donvitinh, Dongia,Soluong,ThanhTien.ViThanh Tien =Soluong*Dongiavanhdangtheocacyeu cau sau : Format la fixed, decimal places la 2 va caption la Thanh Tien. Cac ten field con lai eu co Caption la tieng viet. 2.Tao Query BAO CAO 1 cha cac field MaKH,NgayNV, TenCty, Thanhpho, TenSP, Donvitinh, Dongia.Soluong,Thanhtien,LoaiPhieu.ViThanhtienvanhdangtngtcau1.Sapxep theo th t tang dan cua maKH. 3.TaoQueryBAOCAO2chacacfieldMaNV,TenNV.TenSP,Donvitinh,Dongia,Soluong, ThanhTien. Vi TenNV la ho va ten cua nhan vien, Thanhtien va nh dang tng t cau 1. Sap xep theo th t tang dan cua MaNV, Neu MaNV trung nhau th sap theo th t giam dan cua so lng. Thc hien cac yeu cau sau1.Tao cac Query co cac field giong nh Query HOADON 1 nhng ch xuat hien cac record thoa yeu cau.Tao Query HOA DON 11 sao cho ch xuat hien cac record co MaHD = 00001. Tao Query HOA DON 12 sao cho ch xuat hien cac record co TenSP = ru. Tao Query HOA DON 13 sao cho ch xuat hien cac record co SoLuong > 20. Tao Query HOA DON 14 sao cho ch xuat hien cac record co MaHD la 00002 hoac 00003. Tao Query HOA DON 15 sao cho ch xuat hien cac record co TenSP la ru hoac nem. Tao Query HOA DON 16 sao cho ch xuat hien cac record co SoLuong > 20 hoac =10 va 15. TaoQueryHOADON19saochochxuathiencacrecordcoTenSPlaruhoacgiavvaSoLuong > 15. Tao Query HOA DON 20 sao cho ch xuat hien cac hoa nc lap trong nam 2003. Tao Query HOA DON 21 sao cho ch xuat hien cac hoa nc lap trong thang 9 nam 2003. Tao Query HOA DON 22 sao cho ch xuat hien cac hoa nc lap trong qu 1 nam 2003. 2.Tao cac query co cac field giong nh query BAO CAO 1 nhng ch xuat hien cac record thoa yeu cau. Query BAO CAO 11 sao cho ch xuat hien cac record co ThanhPho la Ha Noi. Query BAO CAO 12 sao cho ch xuat hien cac record co DonViTinh la Chai. Query BAO CAO 13 sao cho ch xuat hien cac record co MaKH la KH002. Query BAO CAO 14 sao cho ch xuat hien cac record co MaKH la KH004 hoac KH005. Query BAO CAO 15 sao cho ch xuat hien cac record co TenSP la Tao hoac Nem. Query BAO CAO 16 sao cho ch xuat hien ca c record co ky t au cua TenSP la C va SoLuong > 10. Query BAO CAO 17 sao cho ch xuat hien cac record co DonViTinh la Kg va SoLuong > 20. QueryBAOCAO18saochochxuathiencacrecordcoThanhPholaKhanhHoa,MaKHlaKH003 va SoLuong >= 15. Query BAO CAO 19 sao cho ch xuat hien cac record co TenSP la Tao hoac co SoLuong < 10. 3.Tao cac Query co cac field giong nhQuery BAOCAO 2 nhng ch xuat hien cac record thoa yeu cau. Query BAO CAO 21 sao cho ch xuat hien cac record co ho la Nguyn. Query BAO CAO 22 sao cho ch xuat hien cac record co Ngay NV trc ngay 01/04/2001 Query BAO CAO 23 sao cho ch xuat hien cac record co N = -1. Query BAO CAO 24 sao cho ch xuat hien cac record co MaNV la 1 hoac 4. Query BAO CAO 25 sao cho ch xuat hien cac record co ten la Ngoc hoac Nga. QueryBAOCAO26saochochxuathiencacrecordsauchotrongtencochNgocva NgayNV sau ngay 29/03/2000. Thc Hien Tnh Toan 1.Tao cac Query da vao query HOA DON 1 vi Thanh Tien c tnh nh sau :Query T- HOA DON 1 vi Thanh Tien = SoLuong * Dongia. Nhng neu SoLuong > 30 th giam 10% va ngc lai th khong giam. Query T-HOADON 2 vi Thanh Tien = Soluong * Dongia. Nhng neu MaHD = 00001 th giam 10%. MaHD = 00002 th giam 20% ngc lai th khong giam. QueryT-HOADON3viThanhTien=SoLuong*Dongia.NhngneuSoLuong=20000$ Add Table: Hoa Don, Chi tiet Hoa don, San pham FieldMaKH Tongtien: Dongia*Soluong TotalGroup bySum Show Criteria>=20000 1.4 Truyen tham so vao Query - Muc ch: Thay oi gia tr tnh toan hay chon la mau tin khi thi hanh truy van V du : Tao query chon nhng hoa n a lap trong thang 5 nam 1993 cua nhan vien co ten la Nga FieldMaHDHoTenNgayLapHD CriteriaNgaBetween #01/05/1993# and #31/05/1993# CachnayseratbattientrongtrnghpcanthayoitenNhanVienhoackhoangthigianlap hoa n, phai m query va tien hanh thay oi lai ieu kien. Viec s dung tham so se thuan li hn rat nhieu, cho phep chon ra cac hoa n cua mot nhan vien nao o, c lap trong mot khoang thi gian bat ky, va ten nhan vien, khoang thi gian nay se c nhap khi chay query. - Cach at tham so: Tai v tr co gia tr ca n thay oi, nhap ten tham so trong dau []. FieldMaHDHoTenNgayLapHD Criteria[Nhap ten]Between [Ngay dau] and [Ngay cuoi] Bi ging Tin Hc Qun L - HLH2011 43 Chu y:-Ten tham so khong c trung vi ten field-Khi thc hien query, ten tham so se hien thong bao trong hop thoaiEnter Parameter Value, va nhap gia tr can loc vao. Trong hop thoai nay khong c nhap bieu thc. - Ch nh kieu d lieu cho mot tham so. Khi gia tr nhap vao tham so khong phai la chuoi k t, ac biet la kieu Yes/No, phai ch nh kieu d lieu cua tham so bang cach: oChon Query > Parameters va nhap cac tham so can khai bao kieu d lieu Cot Parameter : nhap ten tham so can khai bao Cot Data type : chon kieu d lieu cho tham so 1.5 Bai tap 1.Thng k so lng hoc sinh moi lp. 2.So lng hoc sinh au cua moi lp, bit rng u khi khng c mn no di 5. 3.Danh sach hoc sinh co tong iem ln nhat mi lp. 4.Cho bit cac hoc sinh co tong iem cao nhat, tng iem thap nhat , trung bnh trong tng lp 5.Thng k tng s hc sinh, so hoc sinh n va nam cua tng lp. 6.So hoc sinh n co tong iem >32 cua tng lp. 7.oi vi moi lp hay cho biet so hoc sinh, so hoc sinh au, so hoc sinh rt, t le hoc sinh au, t te hoc sinh rt2.Thong ke bang Crosstab query Crosstab Query la mot loai Query dung e tong hpd lieu da tren gia tr cua t nhat la 2 field, trong o gia tr cua mot Field trong Table hoac Query nguon c dung lam tieu e dong, gia tr cua mot so Field (hoac mot bieu thc) c dung lam tieu e cac cot va giatr tong hp cua mot Field (hoac mot bieu thc) lam gia tr cua cac cot o trong Crosstab Query. Nh co Crosstab Query ma ta co the nhn d lieu di dang gon hn so vi Select Query. TrckhitmhiucchtoCrosstabquery,chngtahytmhiucutrcmtCrosstab, c minh ho bi hnh sau: Bi ging Tin Hc Qun L - HLH2011 44 Row heading Column heading Value - Row heading l tiu cc dng, c cha cc gi tr ca cc field no lm tiu ch thng k. Mi Crosstab phi c ti thiu 1 field lm Row heading; -Columnheadingltiuccct,cchaccgitrcamtfieldnolmtiuch thng k. Mi Crosstab ch c duy nht 01 field lm Column heading; - Value l vng d liu tng hp (l cc con s). Ch c duy nht mt field lm Value, tng ng vi n l mt php tng hp hoc: m, tnh tng, tnh trung bnh cng, max, min,.. V du 4.6: Gia s ban co query TONG HOP HOA DON nh sau: Ma HD, MaKH, Hoten, Ten SP, SoLuong, ThanhTien. Ban muon biet moi nhan vien ban moi san pham c bao nhieu tien? D lieu ket qua cua query nh sau: Tao Crosstab Query: Bc1:Tomtselectqueryviyccfieldclinquannphptnghp(khng tha v cng khng th thiu mt field no) Bc2:NhapbieutngQuerytrenQueryDesigntoobarroichonCrosstabQuery,taicaso Design View se hien them dong Crosstab di dong Total Bc 3: Chon cac loai field sau cho crosstab: -Motfieldlamchtieuphannhomtheodong:chonRowHeadingtaioCrosstabvachon Groupby tai o Total -Motfieldlamchtieuphannhomtheocot:chonColumnHeadingtaioCrosstabvachon Groupby tai o Total -Motfieldhaybieuthcchagiatrcantonghp:taioCrosstabchonValuevataioTotal chon kieu tong hp. Bi ging Tin Hc Qun L - HLH2011 45 Lu y: Cac Field dung at ieu kien la chon mau tin tham gia tong hp: tai o Total chon Where, khi o o Crosstab se la : not shown V du: Cho biet doanh so ban hang trong nam1993 cua moi nhan vien tren moi san pham la bao nhieu ? Bc 4: nhap bieu tng ( ! ) hoac Datasheet view tren Query Design toolbar e xem ket qua Bi ging Tin Hc Qun L - HLH2011 46 Bc 5: Ch nh tieu e cot:Trong bang hien ket qua (hnh tren), cac gia tr trong vung Column Heading c sap theo th t ch cai (Banh kem, Banh m, B, Ca hop, Gia v, Nem, Ru, Tao). Neu muon thay oi th t cac cot,chnhcotnaocanhienth,hoackhidungCrosstabQuerynaylamnguondlieucho mot SubForm, phai lam them thao tac nh sau: Chuyen query sang ca so Query Design NhapMousetai1otrencotcodong CrosstablaColumnHeading(Ten SP),nhapbieutngProperties, chonthuoctnhColumnHeadingva lietkecacgiatrxuathienvung Column Heading theo th t can chon. Chuy:Giacacgiatrlietkephai cach nhau bi dau ; V du:ColumnHeading:"Ru"; "Nem";"Banhkem";"Cahop"; "B"; "Gia v"; "Banh m"; "Tao" Bai tap 1.Tong so tien ban c cua tng nhan vien trong tng qui 2.Tong so lng ban c cua tng san pham trong tng nam.3.Tong so tien mua hang cua tng khach hang trong tng qu cua nam 1992 4.So lng hoc sinh cua tng lp trong tng ket qua.5.So lng hoc sinh cua tng lp co trong tng xep loai6.Cho biet moi khach hang tai TP HCM a mua mat hang Ru bao nhieu chai, tong so tien ma moi khach hang a chi emua mat hang na y la bao nhieu, so tien a mua trong tng nam (1992, 1993) la bao nhieu7.Cho biet so lieu thong ke trong moi lp trnh bay theo tng cot thong tin sau: "Tong so hoc sinh moi lp? Bi ging Tin Hc Qun L - HLH2011 47 "Tong so hoc sinh au ? "Tong so hoc sinh trt? 3. Cac loai Query khac: 3.1 Cac Query s dung ba ng Wizard: 3.1.1Find Duplicates Query Wizard: Chc nang: tm cac Record trung lap gia tr tren mot so cot nao o. V du 4.7 :liet ke cac hoa n c lap vao nhng ngay co t 2 hoa n tr len. Cach tao: Bc 1: Chon Object la Queries roi nhap New, cho n Find Duplicates Query Wizard, roi chon OK Bc 2: Chonbangdlieuchadlieutrunglapcantm(vduchonbangHOA DON), xong nhap Next Bc 3: Chonnhngcotchacacgiatrtrunglapcantm(vdu choncot NgayLapHD), xong nhap Next Bc 4:Chon cac cot hien bang ket qua (Ma HD, MaKH, ...) nhap Next. Bc 5:at ten cho Query ket qua, xong nhap Finish 3.1.2Find Unmatched Query Wizard Chc nang: tm cac Record trong mot bang (A) khong co trong bang khac (B) V du 4.8: liet ke cac khach hang cha at hoa n lan nao Cach tao: Bc 1: ChonObjectlaQueriesroinhapNew,chonFindUbmatchedQuery Wizard, roi chon OK Bc 2: Chon bang A (v du chon bang KHACH HANG), xong nhap Next Bc 3: Chon bang B (v du chon bang HOA DON), xong nhap Next Bc 4:Chon 2 cot quan he gia 2 bang va nhap nut , xong nhap Next (MaKH) Bc 5:Chon cac cot hien bang ket qua (Ma KH, Ten Cty, ...), nhap Next Bc 6: at ten cho Query ket qua, xong nhap Finish 3.2 Cac Query khong s du ng Wizard: 3.2.1Make Table Query: Chc nang:tao mot Table mi vi d lieu lay t cac Table hoac Query khac. Bi ging Tin Hc Qun L - HLH2011 48 V du 4.9: taoTableHOADONCUOINAM1993gomcaccotMaHD,MaKH, NgayLapHD,TongTienchacachoanthang11,12nam1993laytTableHOA DON Cach tao: Bc 1: Chon Object la Queries roi nhap New, chon Design View, roi chon OK Bc 2: Chon Table hoac Query cha d lieu nguo n (v du ban hay chon cac bang Hoa Don, Chi Tiet Hoa Don, San Pham) Bc 3: Nhapbieut ngQueryTypetrenQueryDesigntoolbarroichonMake-Table Query se hien hop thoaiTable Name: go ten table can tao (HOA DON CUOI NAM 1993) Chon Current Database (table ket qua at trong CSDL ang lam viec) hoac Another Database (table ket qua at trong CSDL khac va go ten file o File name). Bc 4:Liet ke cot, ieu kien chon cac Record a vao bang ch. V du ban hay a vao cac noi dung sau: FieldMaHDMaKHNgayLapHD TongTien: Dongia*Soluong Criteria>=#1/11/93# and =#1/10/93# and =#25/12/93# Bc 5:Nhap nut Rune thi hanh lenh se hien th hop thoai: Tra li Yes neu ban muon hoan thanh viec xoa record Tra li No neu khong muon xoa 4.Bi tp 4.1 Cc cu hi hiu bi Cu1:Gisccsdlchun(sbdlm trng kho). a ra thng tin tt c thisinh,sinhtnm1980n1984 chn cch no trong cc cch sau: A.TrongmcFieldnhp:ns:year([ngaysinh]);criteria nhp:between 1980and 1984 B.Trong mc Field nhp: [ngaysinh] ;mc criteria nhp:between 1980and 1984 C.Trong mc Field nhp: ns:year([ngaysinh]) ;mc criteria nhp:from 1980 to 1984 D.Trong mc Field nhp: ngaysinh; mc criteria nhp:>=1980and =15vkq=trtnu td=15,,trt) B.Trong mcupdate to ca ca kqnhp:iif(td>=15,,iif (td=15,,trt) D.Trong mccriteriaca kqnhp:iif(td>=15,,trt) Cu4:Gisccsdlchun(mmh:lmtrngkho). thngktheongythngs lung hng bn c,ta lm cch no trong cch truy vn crosstab sau: A.Trong mctotal ca tenhang :group by,soluong:sum ,ngayban:group by;trong mccrosstab ca tenhang: column heading soluong:value,ngay ban: row heading B.Trong mctotal ca tenhang :sum,soluong: group by ,ngayban:group by;trong mccrosstab catenhang:value,soluong: column heading,ngay ban: row heading C.Trong mctotal ca tenhang :group by,soluong:sum ,ngayban:group by;trong mccrosstab ca tenhang: row heading ;soluong:value,ngay ban: column heading D.Trong mctotal ca tenhang :group by,soluong:sum ,ngayban:group by;trong mccrosstab ca tenhang:valuesoluong: column heading ,ngay ban: row heading Cu 5:Gi s c csdl chun (sbd lm trng kho) mun a ra thng tintt c th sinh, sinh nm 1980 v kq: s dng cch no trong cc cch sau: Bi ging Tin Hc Qun L - HLH2011 52 A.trongmcfieldcangaysinhnhp:ns:year([ngaysinh]);criteria:1980;trongmcfield ca kq chn :kq; criteria nhp: B.trong mc field ca ngaysinh nhp:[ngaysinh];criteria nhp:year=1980;trong mc field ca kq chn :kq, criteria nhp: C.trong mc field ca ngaysinh nhp:ngaysinh ;criteria nhp:1980;trong mc field ca kq chn :kq ,criteria nhp: D.trong mc field ca ngaysinh nhp:[ngaysinh];criteria nhp:year=1980;trong mc field ca kq nhp:kq= Cu 6: Gi s c csdl chun (sbd lm trng kho; kq: c cp nht) mun to ra bng mi vi thi sinh s dng cch no trong cc cch sau: A.chn query/make table query/nhp tn bngmi, trong mc criteria ca trng kq nhp: B.chn query/make table query/ nhp tn bng mi ,Trong mc field ca trng kq nhp:kq: C.chn query/make table query/ nhp tnbngmi,trongmccriteria ca trng kq nhp: D.chn query/make table query/ nhp tn bng mi ,trong mc criteria ca trng kq nhp:kq= Cu 7: Gi s c csdl chun (mmh:lm trng kho) hy thng k theo tng thng m mt hng bn c A.TrongmcFieldcangaybannhp:thang:month([ngayban])crosstabchn:row heading TrongmcToTal cas lng chn sum ;crosstab chn value; Trongmccrosstabcammh chn :colum heading B.Trongmccriteriacangayban nhp:thang:month([ngayban]) crosstab chn : row heading TrongmcToTal cas lng chn sum ;crosstab chn value; Trongmccrosstabcammhchn :colum heading C.TrongmcFieldcangaybannhp:thang:month([ngayban])crosstabchn:columnheading Bi ging Tin Hc Qun L - HLH2011 53 Trongmc ToTal ca s lng chn sum ;crosstab chn row heading Trong mc crosstab ca mmh chn :value D.Trong mc Field ca ngayban nhp:month:([ngayban]) crosstab chn : row heading Trongmc ToTal ca s lng chn sum ;crosstab chn value; Trong mc crosstab ca mmh chn :colum heading Cu8:Giskhichymttruyvnhinrathng bo S dngtruy vn no trong cc truy vn sau: A.select query B.crosstab query C.make table query D.update query Cu9:Giskhichymttruyvnhinlnthng bo:(hnh nh), s dng truy vn no trong cc truy vn sau: A.delete query B.update query C.select query D.appen query E.tt c u sai Cu 10: Cch vit Like"[!0-9]a[a-c]" l th hin dng no ? a. Ky t u khng phi s, ky t th 2 l a, ky t th 3 l 1 trong hai ch ci [a,c] b. ky t u khng phi l s 0, ky t th 2 l a, ky t th 3 l 1 trong cc ch ci[a,b,c] c. ky t u khng phi l s 0, ky t th 2 l a, ky t th 3 l 1 trong hai ch ci [a,c] d. Ky t u khng phi s, ky t th 2 l a, ky t th 3 l 1 trong cc ch ci[a,b,c] 4.2Cc bi tp trn lp Da vo CSDL KQTHI v HOADON, cho bit 1.Tong so tien ban c cua tng nhan vien trong tng qui 2.Tong so lng ban c cua tng san pham trong tng nam.3.Tong so tien mua hang cua tng khach hang trong tng qu cua nam 1992 4.So lng hoc sinh cua tng lp trong tng ket qua.5.So lng hoc sinh cua tng lp co trong tng xep loai6.Cho biet moi khach hang tai TP HCM a mua mat hang Ru bao nhieu chai, tong so tien ma moi khach hang a chi emua mat hang nay la bao nhieu, so tien a mua trong tng nam (1992, 1993) la bao nhieuCho biet so lieu thong ke trong moi lp trnh bay theo tng cot thong tin sau: 7.Tong so hoc sinh moi lp? 8.Tong so hoc sinh au ? 9.Tong so hoc sinh trt? Bi ging Tin Hc Qun L - HLH2011 54 4.3 Bi tp thc hnh 4.3.1 Tong h p d lieu bang Query1.Cho biet moi khach hang at mua bao nhieu hoa n va tong so tien cua cac hoa n o2.Tong so tien cua cac hoa n trong tng thang. 3.Cho biet moi khach hang at mua bao nhieu hoa n mat hang ru vi so lng > 20 4. Cho biet cac hoa n vi tong so tien > 10000. 5.oi vi cac san pham. Haycho biet tong so lng, so lng ln nhat va so lng nho nhat a ban c. 6.oi vi moi san pham, hay cho biet so lng khach hang at mua, ngi khach au tien va ngi khach cuoi cung. 7.oi vi moi nhan vien, hay cho biet tong so tien ban c trong thang 1. 8.oi vi moi san pham, hay cho biet tong so lng ban c trong thang 1 nam 1992 4.3.2 Crosstab query 1.Cho biet moi nhan vien ban c nhng san pha m nao va vi so tien cua tng san pham labao nhieu. 2.Cho biet moi hoa n co nhng san pham nao va so lng bang bao nhieu. 3.Moi nhan vien lap c bao nhieu hoa n va cac hoa n o lap cho khach hang nao 4.Cho biet moi khach hang a mua nhng san pham nao va vi so lng bang bao nhieu. 5.Cho biet moi khach hang a at hoa n nao va ten cua nhan vien lap hoa n o. 6.Cho biet moi san pham tham gia trong bao nhieu hoa n va ten nhan vien lap hoa n o. 7.Cho biet moi hoa n co bao nhieu san pham va so lng tng san pham 8. Cho biet moi khach hang a at bao nhieu hoa n va san pham au tien cua tng hoa n. Bi ging Tin Hc Qun L - HLH2011 55 PH LC MOT SO HAM TRONG ACCESS 1. Cac ham x ly so: Abs (number)Tra ve tr tuyet oi cua number Fix(number) Tra ve phan nguyen cua number Int(number)Tra ve so nguyen ln nhat trong cac so nguyen nho hn hoac bang number Sqr(number) Tra ve can bac hai cua number 2. Cac ham x ly chuoi: Asc (string)Tra ve gia tr ma ASCII cua ky t au tien cua chuoi string Chr$(charcode)Tra ve ky t co ma ASCII la charcode, ket qua kieu String Format$(expression, fmt) nh dang thc cua expression theo dang thc fmt, ket qua la mot chuoi ky t kieu String Instr(start,string1,string2) Tmmotvtrautientrongchuoistring1taioxuat hien chuoi string2 Lcase$(string)oi nhng ky t cua string ra dang ch thng, ket qua kieu string Left$(string,n)Tra ve n ky t ben trai cua string, ket qua kieu string Len(string)Tra ve so ky t co trong string Ltrim$(string)Tra ve chuoi string a cat bo nhng ky t trong ben trai, ket qua kieu string Mid$(string,m,n)Travenkyttrongchuoistringbatautvtrm, ketqua kieu string Right$(string,n)Tra ve n ky t ben phai cua string, ket qua kieu string Rtrim$(string)Tra ve chuoi string a cat bo nhng ky t trong ben phai, ket qua kieu string Space$(number)Tra ve mot chuoi gom number ky t trong, ket qa kieu Variant StrComp(string1,string2)Travegiatrxacnhketquacuaphepsosanhhai chuoi string1 va string2 String$(n,char)Tra ve mot chuoi gom n ky t char, ket qua kieu string Bi ging Tin Hc Qun L - HLH2011 56 Ucase$(string)oinhngkytcuastringradangchhoa,ketquakieu string 3. Cac ham x ly ngay va thi gian: Date()Tra ve ket qua la ngay hien tai trong ong ho cua may, ket qua kieu Date/Time Date$()Tra ve ket qua la ngay hien tai trong ong ho cua may, ket qua kieu String Day(DateExpr)Tra ve gia tr xac nh ngay (t 1 en 31) cua DateExpr Hour(TimeExpr)Travegiatrxacnhgitrongngay(t0e n23)cua TimeExpr. Minute(TimeExpr)Travegiatrxacnhphuttronggi(t0en59)cua TimeExpr. Month(DateExpr)Tra ve gia tr xac nh ngay (t 1 en 12) cua DateExpr Now()Tra ve ket qua la ngay va gi hie n tai trong ong ho cua may, ket qua kieu Variant Second(TimeExpr) Travegiatrxacnhgiaytrong1phut(t0en59)cua TimeExpr TimeTra ve ket qua la gi hien ta i trong ong ho cua may, ket qua kieu Date/Time Time$()Tra ve ket qua la gi hien tai trong ong ho cua may, ket qua kieu String TimeValue(string)Tra ve ket qua la mot gia tr dang gi ng vi chuoi string Weekday(DataExpr)Tra ve ket qua la mot so t 1-7 bieu th ngay trong tuan cua DateExpr Year(DateExpr)Tra ve gia tr xac nh nam (t 100 en 9999) cua DateExpr 4. Cac ham oi kieu: CCur(Expr)Tra ve gia tr kieu Currency t bieu thc Expr CDbl(Expr)Tra ve mot gia tr kieu Double t bieu thc Expr Clnt(Expr)Tra ve mot gia tr kieu Integer t bieu thc Expr CLng(Expr)Tra ve mot gia tr kieu Long Integer t bieu thc Expr CSng(Expr)Tra ve mot gia tr kieu Single t bieu thc Expr CStr(Expr)Tra ve mot gia tr kieu String t bieu thc Expr Bi ging Tin Hc Qun L - HLH2011 57 CVar(Expr)Tra ve mot gia tr kieu Variant t bieu thc Expr Hex(number)Tra ve mot gia tr he thap luc phan ng vi so he thap phan number, ket qua kieu Variant Hex$(number)Tra ve mot gia tr he thap luc phan ng vi so he thap phan number, ket qua kieu String Oct(number) Tra ve mot gia tr hebat phan ng vi so he thap phan number, ket qua kieu Variant Oct$(number)Travemotgiatrhebatphanngvisohethapphan number, ket qua kieu String 5. Cac ham thong ke: Avg(Expr)TravegiatrtrungbnhcuabieuthcExprtrongnhngRecord thuoc mot tap hp trong Query, Form, Report Count(Expr) Tra ve so Record thuoc mot tap hp trong Query, Form, Report Max(Expr)Tra ve gia tr ln nhat cua bieu thc Expr trong nh ng Record thuoc mot tap hp trong Query, Form, Report Sum(Expr)Tra ve gia tr tong cua bieu thc Expr trong nhng Record thuoc tap hp trong Query, Form, Report Bi ging Tin Hc Qun L - HLH2011 58 BAI 5: THIET KE GIAO DIEN - FORM Mc ch yu cu:Sau khi hc xong bi ny sinh vin nm c: -L do cn s dng form trong Access -Cch thit k form giao din-Cc thao tc cn thit trong thit k giao din Kin thc yu cu: - hc qua bi 1, 2, 3, 4S tit ln lp: 5 Bng phn chia thi lng STTNI DUNGS TIT 1Cc dng form v cch to mt form n gin1 2Cc thao tc thit k, hiu chnh form1 3Cc thao tc nng cao: to sub form, tab control 1 4Thit k mt s i tng trn form: nt lnh, combo box 1 5Bi tp tho lun1 Trng tm bi ging: -Bit cch thit k giao din lm vic vi d liu. 1.Cac dang form n gian Columnar Tabular Bi ging Tin Hc Qun L - HLH2011 59 Datasheet 2.Tao form n gian B1 : Chn lp Forms, bm nut New B2 : Chn Form Wizard, OK B3 : Chn cac field xut hin trong form, bm Next Bi ging Tin Hc Qun L - HLH2011 60 B4 : Chn bo cuc cho Form (co the xem trc trong o Preview), Next B5 : Chn kieu dang (style) cho Form, bm Next B6 : nhp tieu e cho form. C th tu chn kch hot ngay form va to nu chn Open the form to view or enterinformationhocmformrachDesignviewsacutrcnuchn mc Modify the forms design, cuoi cung bm Finish 3.Tao form co cha subform Khi to mt form bng Wizard, nu trong bc 3 chung ta chn cac field thuc nhiubngcoquanhvi nhauthcothkt quthucslamtformco cha Sub form. Vd:toformcochacacfiled:Lop,GVCN,Hinh,SoBD,Ho,Ten,Nu, Ngaysinh th ta co thethu c form sau : Bi ging Tin Hc Qun L - HLH2011 61 Trong qua trnh tao form, khi xuat hien hoi thoai Neu chon Form with subform ta se c form sau : Neu chon Linked forms ta se c form sau : Bi ging Tin Hc Qun L - HLH2011 62 4.Thiet ke form 4.1 Ca so thiet keBc 1: th Form, nhn New, chn Design View. Mi trng thit k form xut hin: Bi ging Tin Hc Qun L - HLH2011 63 C 3 thnh phn quan trng lm vic: (1) Ca s Form ni s thit k v xy dng cc thng tin cn thit theo yu cu bi ton. Cu trc form gm 3 phn: Form Header - phn tiu u form; Form Footer - phn tiu cui; Detail - phn thn form. (2) Thanh cng c Toolbox ni cha nhng i tng, nhng cng c c th a ln form vi mc ch thit k giao din v iu khin d liu theo bi ton. V d: mun to nhp H tn trn form c th dng i tng Textbox, mun amt ch thch (nhn hin th) c th dng Label, mun to mt nt lnh c th dng CommandButton, (3) Ca s thuc tnh form (Form Properties): ni c th thit lp cc thuc tnh cho form cng nh cc i tng trn form Bc 2: Ca s Field List. CasFieldListcchadanhschccfieldtrnCSDLctrongngundliucaForm. N h tr vic a nhng field d liu ny ln form nhp v hin th d liu rt tt. Trong trnghpnytadnganhngfieldcnnhpdliutbng DSHS ln form. Nuchathycasnyxuthin,hythchinhinthnbng cchmthcnView|FieldListhocnhnntFieldListtrn thanh cng c chun. Bc 3: a nhng trng cn nhp d liu t ca s Field List ln Form ang thit k bng cch:DngchutkotngtrngmunthitklnformtcasFieldListthlnvtr hpltrnform(vibinyphikotonbcctrnglnform).Lcnycasthitk form c dng: MikhikomtfieldtFieldListlnform, Access s t ng to mt i tng gn kt ti field d liutngng,itngnycthlTextbox, Comboboxhayitngkhctuthucvokiud liucafieldtngng;vitngLabelikm nhm to nhn ch thch cho field d liu. Bi ging Tin Hc Qun L - HLH2011 64 4.2 Cac thao tac trong cua so thiet keChon oi tng e chon oi tng ta co the dung mot trong cac cach sau -Bam chuot vao oi tng -Bam chuot vao cay thc ngang hoac cay thc doc e chon nhieu oi tng -Keo chuot tren form Thay oi kch thc -Chon oi tng va keo chuot tai cac nut vuong quanh oi tnghoac bam phm shift va phm mui ten. -Thay oi kch thc e nhieu oi tng co kch thc bang nhauoChon cac oi tng oChon Format Size: Tallest cao nhat, Shortest thap nhat , Widest rong nhat , Narrowest hep nhat Di chuye n oi tng -Chon oi tng, di chuyen chuot vao trong oi tng va keo chuot hoac bam phm ten. -Canh le nhieu oi tng :oChon cac oi tngoChon Format Align : Left , Right, Top, Bottom nh dang -Chon oi tng -S dung cac nut lenh tren thanh cong cu Formatting : font , size , bold 4.3 oi tng 4.3.1 Label, Text box va Field - Label: tao nhan - Text box : tao o e nhap noi dung khi m form hoac o cong thc. - Field ( trong field list ) xut hin ni dung ca 1 filed. Nu field list khng xut hin ta c th chn View Field list V d : thit k form sau 4.3.2 Command Button (nut lenh) Bc 1:Bat sang bieu tng Control Wizard, chon bieu tng Command Button tren Toolbox Bc 2:nh v tr va kch thc cua oi tng Bi ging Tin Hc Qun L - HLH2011 65 Bc 3: Categories: Chon loai hanh ong cua nut lenh Action: chon hanh ong cu the cua nut lenh Bang sau liet ke cac loai hanh ong va mot so hanh ong c ban hay s dung: CategoriesActions Record Navigationloai hanh ong cho phep nhay con tro ti record nao o Go to First Record: Go to Last Record: Go to Next Record: Go to Previous Record: Nhay ti record au tien Nhay ti record cuoi cung Nhay ti record ke tiep Nhay ti record ngay trc Record Operationloai hanh ong x ly mau tin Add New record: Delete Rocord: Save record: Them 1 record mi Xoa record hien hanh Lu record hien hanh Form Operation s loai hanh ong x ly Form Edit Form Filter: Apply Form Filter: Close Form: Open Form: Sa cha ieu kien loc cac record hien th trong form Thc hien loc cac record hien th trong form ong Form hien hanh M mot form khac Report Operationsloai hanh ong x ly bao cao Preview Report: Print Report: In bao cao ra man hnh In bao cao ra may in Miscellaneous Prin Table: Run Macro: Run Query: In 1 table Thi hanh 1 Macro Thi hanh 1 Query Bc 4: Text: at tieu e cho nut lenh Picture: Chon hnh anh cho nut lenh Xong chon Next Bc 5:at ten cho nut lenh, Cuoi cung nhap Finish 4.3.3 Sub Form B1 : Chuyn Main form v ch Design B2 : Chn cng c Subform / Subreport, bm chut vo mainform Bi ging Tin Hc Qun L - HLH2011 66 B3 : Chn form, Table hay query l ngun cho sub form ( What data would you like to use for subform or subreport?) B4 : Chn field quan h gia Main Form v sub form ( Would you like to define while fields link your main form to this subform yourself, or choose frm the list below) B5 : chn tn cho subform ( what name would you like for your subform or sub report) 4.3.4 Combo Box Bc1:BatsangbieutngControlWizardtrenToolbox,nhapchonbieutngCombo Box (List box) Bc 2 : Chon font, font size, canh le ... cho oi tng bang cac bieu tng tren Fomatting toolbar Bc 3 : Keo tha mouse e xac nh v tr va kch thc cua oi tng Bc 4 : Xac nh che o hoat ong cua Combo Box: "I wantthe Combobox look uo theo value in atable or query":Chon muc nay neu nguon d lieu cua Combo box lay t Table hay Query "I will type in the value that I want" : Cho n muc nay neu muon t nh giatr cho cac cot trong Combo boxFind a record on my formbased onthevalue I selected in mycombo box": Chon mucnayneunguondlieucuaComboboxlaytnguondlieucuaFormangla m viec (muc nay ch co khi Form nay co nguon d lieu) Xong chon Next * Neu nguon d lieu cua Combo box lay t Table hay Query: Bc 5.a:Chon Table/Query lam nguon d lieu Combo boxBc 6.a:Chon cac field lam noi dung trnh bay trong combo box Bc 7.a:Chnhkchthcdong,cotva dichuyennhvchocaccottrnhbaytrong combo boxNeu chon tai muc Hide key column (recommended) th se khong trnhbay cot khoa chnh trongCombobox(ongthicunglaygiatrcotnaylamgiatrsdungcuaCombobox). Neu bo chon th se co them cot khoa chnh cua bang trnh bay trong Combo box. Bc 8.a:Chon cot ma gia tr c gan lam gia tr s dung cua oi tng Lu y: BcnaysekhongconeuchontaimucHidekeycolumn(recommended)bc trc Bc 9.a:xac nh che o s dung gia tr cua oi tng sau khi c chon. Bi ging Tin Hc Qun L - HLH2011 67 Remember the value for later use: Nh giatr cua oi tng e s dung sau Store that value in this field: Chon field e lu tr gia tr cua oi tng Bc 10.a: at tieu e cho oi tng Xong chon Finish * Neu nguon d lieu cua Combo box lay t nguon d lieu cua Form ang lam viec: Cac bc tng t nh trng hp tren (ch khong co bc nh 5.a ma thoi) * Neu t nh gia tr cho cac cot trong Combo box: Bc 5.b:Number of column: nh so cot trnh bay trong Combo box. Sau o nh gia tr cho cac cot trong Combo box tai cac cot Col1:, Col2:, ... Bc 6.b:Chon cot ma gia tr c gan cho oi tng Bc 7.b:Xac nh che o s dung gia tr cua oi tng sau khi c gan. Remember the value for later use: Nh gia tr cua oi tng e s dung sau Store that value in this field: Chon field e lu tr gia tr cua oi tng Bc 8.b: at tieu e cho oi tng Xong chon Finish e hoan thanh Chu y: "Gia tr c the hien len trong khung Combo box sau khi chon bao gi cung la gia tr cua cot au tien ben trai mac du gia tr c gan vao co the la gia tr cua cot khac " Neu ban muon lay gia tr cua 1 cot trong Combo box (List box) e bieu dien cho 1 oi tng khac th hay goi theo cong thc sau: [Ten Combo box]. Column (so th t cua cot) Qui c: Cot au tien co so th t = 0, cot th 2 co so th t = 1, cot th ba co so thc t = 2 ... Bi ging Tin Hc Qun L - HLH2011 68 4.3.5 Tab Control SdngTabControltrongformdngtrnhbyccthngtintrongformnhngtrang mn hnh khc nhau. V d Bi ging Tin Hc Qun L - HLH2011 69 CC BI TP THC HNH CHNG 5 Bi 1 S dung c s d lieu HOADON.MDB e thc hien cac yeu cau sau : CAU 1 : Tao Crosstab query TK1 cho biet so lng cac san pham ban c trong tng nam Cau 2 : tao Form theo doi san pham co dang sau : Yeu cau : Trong Form tren co 2 Sub form. Khi trong Main form chon mot san pham nao o thtrong Sub form 1 ( tren) se xuat hien tong so lng va tong so tien ban c cua san pham o, trong sub form 2 ( di) se xuat hien cac hoa n co chasan pham o. Nut ONG co cong dung ong Form.Bi ging Tin Hc Qun L - HLH2011 70 Bi 2 S dung c s d lieu HOADON.MDB e thc hien cac yeu cau sau : CAU 1 : Tao Crosstab query Khach Hang San Pham cho biet so tien moi khach hang a mua oi vi tng san pham.

CAU 2 : Tao Form Hoa Don co dang nh sau Yeu cau : Form co 2 Sub form : Sub form 1 cho biet so san pham va tong so tien, sub form 2 cho biet chi tiet hoa n c chon trong Main form. Bi ging Tin Hc Qun L - HLH2011 71 Bi 3 S dung c s d lieu HOADON.MDB e thc hien cac yeu cau sau : CAU 1 : : Tao Crosstab query Khach Hang San Pham cho biet so tien moi khach hang a mua oi vi tng san pham.

Cau 2 : tao Form theo doi san pham co dang sau : Yeu cau : Trong Form tren co 2 Sub form. Khi trong Main form chon mot khach hang nao o thtrong Sub form 1 ( tren) se xuat hien tong so tien va so tien trung bnh ma khach hang o a mua, trong sub form 2 ( di) se xuat hien cac hoa n ma khach hang a mua. Bi ging Tin Hc Qun L - HLH2011 72 BI 4 S dung c s d lieu KQTHI.MDB e thc hien cac yeu cau sau : CAU 1 : Tao query KET QUA THIco cac vung sau: SOBD, HOTEN, NGAY SINH, LOP, TOAN, VAN, NN, DTB, XEP LOAI Trong o : DTB la diem trung bnh cua 3 mon TOAN, VAN , NN XEP LOAI c xep theo tieu chuan sau : Len thang : neu DTB>=5 va khong co mon nao di 3. Thi lai : con lai CAU 2 :Tao Crosstab query TK1 cho so lng hoc sinh trong tng lp cua tng xep loai.

Cau 3 : tao Form Lop co dang sau :Bi ging Tin Hc Qun L - HLH2011 73 BI 5 S dung c s d lieu THI.MDB e thc hien cac yeu cau sau : CAU 1 : Tao query KET QUA THIco cac vung sau: SOBD, HOTEN, PHAI, NGAY SINH, LOP, TOAN, VAN, NN, DTB, XEP LOAI Cho biet : - DTB la diem trung bnh cua 3 mon TOAN, VAN , NN trong o Toan he so 3, Van he so 2 va NN he so 1. - XEP LOAI c xep theo tieu chuan sau : au: neukhong co mon nao di 5. Rt : con lai CAU 2 :Tao Crosstab query TK1 cho so lng hoc sinh trong tng lp cua tng xep loai.

Cau 3 : tao Form Lop co dang sau : Bi ging Tin Hc Qun L - HLH2011 74 BI 6 S dung c s d lieu KQTHI.MDB e thc hiec cac nhiem v u sau 1.Tao query co dang sau : LOPiem cao nhatGioiKemKhaTrung Bnh 10A133.5351319 10A234.5412013 10A333.5471417 10A434732927 10A537912318 Ghi chu-Field iem cao nhat : cho biet tong iem cao nhat cua tng lp. -Tong iem la tong iem cua cac mon hoc. -Field Gioi, Kem, Kha va Trung Bnh cho biet so lng hoc sinh co xep loai la Gioi, Kem, Khava Trung Bnhcua tng lp. -Hoc sinh co xep loai laGioi neu tong iem ln hn hoac bang 32, la Kha neu tong iem ln hn hoac bang 28, la Trung Bnh neu tong iem ln hn hoac bang 20 va la Kem neu tong iem nho hn 5. 2.Thiet ke Form sau Lu y :Hoc sinh co ket qua la au neu tong iem ln hn hoac bang 20 va khong co mon nao di 4. Bi ging Tin Hc Qun L - HLH2011 75 BI 7 S dung c s d lieu KQTHI.MDB e tao form sau Ghi chu-iem trung bnh: cho biet iem trung bnh cua tng lp. -Tong iem la tong iem cua cac mon hoc. -Field Gioi, Kem, Kha va Trung Bnh cho biet so lng hoc sinh co xep loai la Gioi, Kem, Khava Trung Bnhcua tng lp. -Hoc sinh co xep loai laGioi neu tong iem ln hn hoac bang 32, la Kha neu tong iem ln hn hoac bang 28, la Trung Bnh neu tong iem ln hn hoac bang 20 va la Kem neu tong iem nho hn 5. -Hoc sinh co ket qua la au neu tong iem ln hn hoac bang 20 , khong co mon nao di 4 va co toi a 2 mon di 5. Bi ging Tin Hc Qun L - HLH2011 76 BI 8 S dung c s d lieu HOADON.MDB e thc hiec cac nhiem vu sau 1.Tao query cho biet tong so tien ban c cua tng san pham va so tien ban c cua tng san pham ban c trong tng nam ( co dang nh sau) 2.Thiet ke form sau : Yeu cau : -Thanh tien : so lng * n gia. Neu so lng ln hn 100 th giam 3%, neu so lngln hn 10 th giam 2%, con lai th khong giam. Bi ging Tin Hc Qun L - HLH2011 77 BI 9 S dung c s d lieu HOADON.MDB e thc hiec cac nhiem vu sau 1.Tao query cho biet tong so tien ban c cua tng nhan vien va so tien ban c cua tng nhan vien oi vi tng san pham ( co dang nh sau) 2.Thiet form sau : Yeu cau - Thanh tien = so lng * n gia . Neu san pham la Gia v th giam 5%, neu san pham la Nem th giam 2%, con lai th khong giam. Bi ging Tin Hc Qun L - HLH2011 78 BI 10 S dung c s d lieu HOADON.MDB e thc hiec cac nhiem vu sau 1.Tao query cho biet tong so tien cua tng nhan vien a ban va tong so tien cua tng nhan vien a ban trong tng thang.2.Thiet ke form sau : Lu y : Thanh tien : so lng * n gia. Neu MaKH la ASC th giam 10%, neu MaKH la ASECO th giam 8%, con lai th khong giam. Bi ging Tin Hc Qun L - HLH2011 79 BI 11 Cau 1 : Tao mau Form : CHI TIET SAN PHAM e xem cac thong tin ve tng San Pham va chi tiet cac hoa n a lap cho san pham o hnh 1 Hnh 2 a.Mo taForm tren co cha Tab control. Khi nhap nut San Pham se hien th cac thong tin cua tng san pham nh hnh 1. Khi nhap nut Chi Tiet San Pham se xuat hien cac thong tin ve cac hoa n a lap cho san pham ang xem man hnh 1 nh hnh 2. Bi ging Tin Hc Qun L - HLH2011 80 BI 12Cau 1 : Tao mau Form : KHACH HANG e xem cac thong tin ve tng khach hang va cac hoa n ma khach hang a mua. Hnh 1 Hnh 2 b.Mo taForm tren co cha Tab control. Khi nhap nut KHACH HANGse hien th cac thong tin cua tng khach hang nh hnh 1. Khi nhap nut CAC HOA N xuat hien cac thong tin ve cachoa n ma khach hang o a mua.