TRANSCRIPT
GV: Mai Lin Khng
Hng dn thc hnh Access
HNG DN THC HNH ACCESSI. C s d liuL mt tp hp cc d liu c lin quan vi nhau cha thng tin v mt t chc no (nh mt trng i hc, mt ngn hng, mt cng ty, mt nh my, …), c lu tr trn cc thit b nh th cp (nh bng t, a t, ) p ng nhu cu khai thc thng tin ca nhiu ngi s dng vi nhiu mc ch khc nhau. Access l g? Access l mt h qun tr CSDL nm trong b Microsoft Office Access c dng ? Xy dng chn gi cc phn mm qun l quy m va v nh nh: Qun l Hc vin, sinh vin, nhn s, vt t
II.
Bc 3: Hp thoi File New Database xut hin thc hin hng dn nh hnh bn di
Trang 1
GV: Mai Lin Khng
Hng dn thc hnh Access
2. Chn ni lu CSDL
1. Nhp tn CSDL cn to
3. Nhn Create to
Ca s CSDL QLHV sau khi c to
Trang 2
GV: Mai Lin Khng
Hng dn thc hnh Access
2. Chn kiu d liu 1. Nhp tn Field
3. Nhp ch thch cho Field nu cn
4. Xc nh cc thuc tnh cho Field
Lp li th t t 1 n 4 nh trong hnh cho cc Field tip theo Bc 3: Xc nh kha chnh(Primary key) cho Table Bc 4: Lu Table Bc 5: Thc hin li cc bc 1 n bc 4 to cc Table cn li
a. Cc kiu d liu ca Field(Data type) Kiu Text: Kiu k t c di 255 k t V d: Field Name MAHV HOLOT DIACHI V d: Field Name GHICHU DIENGIAI NOIDUNG Data type Text Text Text Data type Memo Memo Memo
Kiu Memo: Kiu k t dng vn bn c di 65,535 k t
Trang 3
GV: Mai Lin Khng Kiu Number: Kiu s (d liu dng s) V d: Field Name SOLUONG DONGIA HOCPHI Data type Number Number Number Data type Date/Time Date/Time Date/Time
Hng dn thc hnh Access
Kiu Date/TimeV d: Field Name NGAYSINH NGAYNHAP NGAYXUAT
Kiu Yes/No: Kiu lun l ch nhn 1trong 2 gi tr (true/false, Yes/no, 1/0) V d: Field Name PHAI LOAIVATTU GIADINH Data type Yes/No Yes/No Yes/No Data type Currency Currency Currency
Kiu Currency: Kiu tin tV d: Field Name THANHTIEN HOCPHI DONGIA
Kiu Autonumber: Gi tr t ng, t ng tng ln 1 khi thm mu tin mi Kiu Ole/Object: Kiu lu tr hnh nh Kiu Hyperlink: Kiu dng lin website (vd: chúng tôi )
Trang 4
GV: Mai Lin Khng
Hng dn thc hnh Access
b. Cc thuc tnh ca Field Field size : rng ca Field dng s(number) hoc k t (text, memo).
Vi kiu Text: Xt Field MAHV C Filed size: 6
khi nhp liu, ti ct MAHV ch nhn gi tr i k t thng thnh HOA, 10 v Validation Text: Nhp s ln hn 10
V d 8: Hm IIF kt hp lm Left hay Right Cho bit nhng nhn vin thuc phng ban no gm Manv, Ho va ten, PhongBan. Bit Tn phng nh sau: nu 2 k t bn tri ca Manv l HC th Hnh Chnh, KT l K Ton, TH l Tin Hc
TenPhong:IIF(Left([Manv],2)=”HC”,”HnhChnh”,IIF(Left([Manv],2)=”KT”,”K Ton”,”Tin Hoc”))
Trang 14
GV: Mai Lin Khng
Hng dn thc hnh Access
Access Access Word Word
Hv01 Hv02 Hv01 Hv02
450000 450000 300000 300000
Mamh
Hocphi
Access Word
900000 600000
Total Query
VD: Tnh tng thnh tin cho tng khch hng mua hng gm Makh, Tenkh, TongThanh Tien
V d 10: Tnh tng hc ph cho mn hc Access.
Trang 15
GV: Mai Lin Khng
Hng dn thc hnh Access
2.2 Crosstab query:y l loi query dng tng hp mt khi lng ln d liu theo dng bng hai chiu. Sohd HD1 HD2 HD3 HD4 HD5 HD6 Khch Hng Cty A Cty A Cty A Cty A Cty B Cty C Sanpham Tivi May Lanh May Lanh Tivi Tivi May Lanh Soluong 20 10 15 20 20 15 Crosstab
Khch Hng Cty A Cty B Cty C
Tivi May Lanh 40 25 20 15
o Chn Row heading cho Field lm tiu dng. Trong Crosstab query ch c duy nht mt Filed lm tiu ct, cn tiu dng c th c nhiu.
V d 11 (Crosstab query): Xem s lng t hng ca tng khch hng theo tng snphm, trong tn sn phm th hin theo ct.
Trang 16
GV: Mai Lin Khng
Hng dn thc hnh Access
V d 12 (Cosstab Query): Trnh by bng thng k tng s lng mua cc mt hng theo tng nh cung cp (Tn nh cung cp th hin theo ct)
2.3 Parameter query (Query tham s) : Trong trng hp ta thc hin mt cuquery nhiu ln vi nhiu tiu ch khc nhau ta nn s dng quey tham s. V d 13:Query nhn mt tham s: Lit k nhng hc vin theo ni sinh, cho php ngi s dng nhp vo ni sinh no th ch hin th nhng hc vin c ni sinh va nhp, gm Mahv, Hovaten, Phai, Ngaysinh, Noisinh.
V d 14: Query nhn hai tham s: Lit k theo s lng mua cc sn phm ca tng khch hng, ch lit k s lng trong mt khong do ngi dng nhp vo.
Trang 17
GV: Mai Lin Khng
Hng dn thc hnh Access
2.4 Action Query: L loi query dng cp nht d liu mt cch ng lot cho chúng tôi mt bng mi, ni d liu t cc bng thnh mt bng duy nht.
2.4.1 Update query: Dng cp nht d liu cho tableCc bc to tng t select query thm bc sau: vo menu Query chn update query ta c thm dng mi Update to V d 15 Tng hc ph ln cho tt c cc mn hc 10000 Nhp cng thc ti dng Update to ca ct HOCPHI l: [HOCPHI]+10000 Nhn nt Run ! trn thanh cng c
–
Chn Yes tng hc ph
Lu : xem kt qu ca cu query Bn sang Table MONHOC kim tra. V d 16 (Update query): Cng 1.5 im cho tt c cc hc vin c ni sinh vng tu
Run ! V d 17: Tng n gi ln 15% cho mt hng vi KATE
Run !
Trang 18
GV: Mai Lin Khng
Hng dn thc hnh Access
Sang th Table kim tra kt qu (ta c thm 1 Table mi l KETQUADAU) V d 19: Tng t v d 18 to bng c tn KETQUAROT cha nhng hc vin c kt qu rt V d 20: To bng BAOCAO gm cc ct tn hng, tn nh cung cp, thnh tin
OK Run !
Trang 19
GV: Mai Lin Khng
Hng dn thc hnh Access
2.4.3 Append query: l loi query dng ni d liu t nhiu bng thnh mt bngduy nht Cc bc to: u tin to Select Query nh thng thng(ly bng ngun, ly Field): Vo Menu Query Append Query Hp thoi Append hin ra Ti Table Name Chn Table ch cn ni OK Run V d 21: Ni bng KETQUADAU (v d 18) vo bng KETQUAROT (v d 19)
Vo menu query Append query OK Run ! 2.4.4 Delete query: Dng xa d liu tha mn iu kin a ra Cc bc to tng t Select Query nhng thm bc sau: Vo Menu Query Delete Query Ta c thm dng mi: Delete chn Filed cha mu tin cn xo nhp iu kin ti dng Criteria Run ! V d 22: Xa nhng hc vin c im di 7 trong bng KETQUADAU
Run ! Yes
Trang 20
GV: Mai Lin Khng 2.5 Query kt hp cu lnh SQL
Hng dn thc hnh Access
V d 23: Cho bit danh sch nhng khch hng cha t hng, gm makh, ten kh, dia chi
V d 24: Cho bit nhng mt hng no cha c cung cp (khng c mt trong bng HOADON)
V d 25: Cho bit danh sch nhng hc vin c trong bng hc vin nhng khng c trong bng ng k
Trang 21
GV: Mai Lin Khng
Hng dn thc hnh Access
IV.
Form- Form l cng c cho php to giao din chng trnh gia ngi s dng v chng trnh khi tng tc. – Form l ni hin th, nhp, hiu chnh d liu. – Ngun d liu to Form c th l Table hoc Query
1) Cc dng Form a. Dng Columnar
b. Dng Tabular
c. Dng Datasheet
Trang 22
GV: Mai Lin Khng
Hng dn thc hnh Access
d. Dng Main-sub
1. Chn Table hay Query lm ngun d liu cho Form
2.
2. Chn cc Field th hin trn Form bng cch a cc Field t vng Available Fields sang Selected Fields
3. Nhn Next sang hp thoi k tip
Trang 23
GV: Mai Lin Khng
Hng dn thc hnh AccessHp thoi Form Wizard th 2
1. Chn kiu trnh by cho Form
Hp thoi Form Wizard th 3
1. Chn hnh nn cho Form
Trang 24
GV: Mai Lin Khng
Hng dn thc hnh AccessHp thoi Form Wizard th 4
1.Nhp tiu cho Form
Form khch hng sau khi c to
Trang 25
GV: Mai Lin KhngHp thoi Form Wizard th 4
Hng dn thc hnh Access