Thuc Hanh CSDL

  • Published on
    02-Jul-2015

  • View
    231

  • Download
    10

Embed Size (px)

DESCRIPTION

NI DUNG PHN THC HNH Bi thc hnh 1: Thit k CSDL trong MS Access Ni dung: To CSDL trong MS Access To Table Khai bo kha chnh, kha ngoi, min gi tr Nhp d liu vo cc bng Bi thc hnh 2: Truy xut d liu Ni dung: To select query n gin To select query c to filed mi To select query c thng k theo group Bi thc hnh 3: Thit k report Ni dung: To report bng wizard Thit k report trong design view To Main/subreport Bi thc hnh 4: Thit

Transcript

<p>NI DUNG PHN THC HNH Bi thc hnh 1: Thit k CSDL trong MS Access Ni dung: To CSDL trong MS Access To Table Khai bo kha chnh, kha ngoi, min gi tr Nhp d liu vo cc bng Bi thc hnh 2: Truy xut d liu Ni dung: To select query n gin To select query c to filed mi To select query c thng k theo group Bi thc hnh 3: Thit k report Ni dung: To report bng wizard Thit k report trong design view To Main/subreport Bi thc hnh 4: Thit k form Ni dung: To Form cp nht d liu trn mt table To Form nhp liu cho nhiu table To Form tm kim thng tin. Bi thc hnh 5: To menu, toolbar Ni dung: To menu To toolbar Gn toolbar vo form hay report</p> <p>NI DUNG PHN THC HNH Bi thc hnh 1: Thit k CSDL trong MS Access Ni dung: To CSDL trong MS Access To cc Table Khai bo kha chnh, kha ngoi, min gi tr Nhp d liu vo cc bng</p> <p>CSDL QUN L BN HNGTable: KHACHHANG Name MaKH TenCty DiaChi ThanhPho DienThoai Table: LOAISANPHAM Name Type MaLoai Number TenLoai Text MoTa Memo Hnh OLE Object Table: NHANVIEN Name Type MaNV Number Ho Text Ten Text Nu Yes/No NgaySinh Date/Time NoiSinh Text DiaChi Text DienThoaiNha Text ChucVu Text NgayNV Date/Time DTDiDong Text Hinh OLE Object GhiChu Memo MaNVQL Number Table: SANPHAM Name Type MaSP AutoNumbe r TenSP Text DonViTinh Text DonGia Currency SoTon Number MaLoai Number Table: HOADON Name MaHD MaKH MaNV NgayLapHD NgayNhanHang</p> <p>Type Text Text Text Text Text Size Long 15 -</p> <p>Size 10 50 60 20 10</p> <p>Din gii M khch Tn cng ty a ch Thnh ph in thoi</p> <p>Din gii M loi sn phm Tn loi M t Hnh loi sn phm</p> <p>Size Din gii Long M nhn vin 30 H 7 Tn L phi n Ngy sinh 15 Ni sinh 60 a ch 10 in thoi nh 20 Chc v Ngy nhn vic 50 in thoi di ng Hnh Ghi ch Long M cn b qun l trc tip Size 20 20 Din gii M sn phm-Kha chnh</p> <p>Tn sn phm n v tnh n gi Double S lng tn kho Long M loi sn phm Size 10 Long Din gii M ha n-Kha chnh M khch M nhn vin Ngy lp ha n Ngy khch nhn hng</p> <p>Type AutoNumber Text Number Date/Time Date/Time</p> <p>Table: CHITIETHD Name Type MaHD Number MaSP Number SoLuong Number Dongia Currency</p> <p>Size Long Long Byte</p> <p>Din gii M ha n M sn phm S lng n gi bn</p> <p>Kha chnh</p> <p>Bi thc hnh 2: Truy xut d liu Ni dung: To select query n gin To select query c to filed mi To select query c thng k theo group 1- Hy lit k cc chi tit ha n gm cc thng tin: MaHD, MaKH, MaNV, MaSP,</p> <p>SoLuong, DonGia, ThanhTien: SoLuong*DonGia. Sp th t theo MaHD. Lu vi tn "Qry_Tien CTHD".2- To Query "Qry_ThanhToan" c cc Field sau:MaHD, TenSP, DonGia, SoLuong, TienSP, SoNgay, TienTraTruoc, ConLai. Sp th t tng dn theo MaHD. Trong : SoNgay: l s ngy t ngy lp ha n n ngy nhn hng. TienTraTruoc c tnh nh sau: nu SoNgay &lt; 10 th tin tr trc l 20% ca TinSP, nu 10 SoNgay &lt; 20 th tin tr trc l 30% ca TienSP, v t 20 tr ln tin phi tr trc l 40% ca TienSP. ConLai: l s tin cn li phi tr nhng phi gim i 5% 3- To Query "Qry_TienQuyDoi" ( Tin quy i ) xem bng tnh tin cho tng sn phm theo tng thi im gm cc thng tin sau: MaHD, ngy lp, tn sn phm, n gi, s lng, thnh tin VND, thnh tin USD. Bit rng: t gi 1 USD trong nm 2000 l 13500VND, 6 thng u nm 2001 l 14500 v 6 thng cui nm 2001 n nay l 15000. Bit rng n gi trong bng sn phm c tnh l VND. 4- Lit k danh sch cc nhn vin n gm cc thng tin:M nhn vin, h v tn, Ngy nhn vic. Sp th t theo ngy nhn vic. Lu query vi tn Qry_Nhan Vien Nu 5- Lit k danh sch ha n mua mt hng Ru trong nm 2001 gm cc thng tin: M ha n, M khch, Tn cng ty, Ngy lp ha n. Sp th t theo tng khch hng. 6- Lit k danh sch cc ha n khng c ngy nhn hng gm cc thng tin: MaHD, M khch, Tn cng ty, Ngy lp ha n. Sp th t theo tng khch hng v ngy lp. Lu vi Hoa Don Chua Nhan Hang. 7- Lit k danh sch cc khch hng c ha n trong nm 2000. Vi cc thng tin: M khch, TnCty. Yu cu khng c hai record trng nhau. Lu vi tn Qry_Khach mua 2000 8- To Query tnh tin cho mi ha n, gm cc thng tin: MaHD, MaKH, MaNV, Ngy Lp, Ngy nhnhng, m s lng chi tit ha n (So CT), Tin ha n (TienHD). Lu vi tn "Qry_TienHoaon" 9- Cho bit mi Khch hng t mua nhng sn phm no, tng s lng v tng s tin mua cho nhng loi sn phm ca khch. Gm cc thng tin: MaKH, MaSP, Tng s lng (TongSoLuong), Tng tin (TongTien). Lu vi tn Qry_San Pham Khach Mua. 10- Cho bit s lng v doanh thu ca tng sn phm bn trong mi nm, gm cc ct:Nm, MaSP, TenSP, TongSoLuong, TongTien. Lu vi tn Doanh Thu San Pham Moi Nam. 11- To Query cho bit thng tin ca nhng khch hng khng c ha n trong nm 2001. Vi cc thng tin: M khch, TnCty, a ch, S in thai. Lu vi tn Qry_Khach khong mua hang 2001</p> <p>12- Cho bit tng s nhn vin, s nam v s n trong cng ty. Gm cc ct: Si So, So Nam, So Nu 13- To query tnh tin cho cc ha n, gm cc thng tin: MaHD, MaKH, MaNV, ngy lp, ngy nhn hng, s dngchi tit trong mi ha n (SoCT), Tin ha n (TienHD). Lu vi tn Tin Ha n.</p> <p>14- Cho bit doanh thu tng mt hng trong nm 2001, sp theo th t gim ca s tin thu. Gm cc ct:MaSP,TenSP, Hinh, n gi, Tng lng hng bn (TongSL), Tng tin bn sn phm (TongTien). Lu vi tn DsSP Nam 2001 15- Cho bit doanh thu tng mt hng mi thng trong nm 2001, sp theo th t gim ca s tin thu trong tng thng. Gm cc ct: MaSP, TenSP, Thang, s lng bn(TongSL), TienBan. Lu vi tn San Pham Ban Moi Thang Nam 2001</p> <p>16- To Crosstab Query cho bit s lng tng sn phm bn trong tng nm gm cc ct: MaSP,TnSP, TngDoanh s, Cc nm.</p> <p>17- Thit k Crosstab Query cho bit doanh thu ca mi nhn vin trong tng nm. gm cc ct: MaNV, HoTenNV,Tng Doanh s, cc nm. 18- Cho bit doanh s bn cc sn phm theo cc thng lp ha n trong mt nm no gm cc ct: MaSP, Tn SP, Tng Doanh s, Tng s lng, Thng 1, Thng 2, Thng 3,.. .. Trong , Nm cn tng hp c nhp vo khi thi hnh Query. 19- Cho bit doanh s bn hng ca tng nhn vin theo tng loi sn phm da trn cc ha n bn trong nm 2000, gm cc ct: MaNV,H tn nhn vin, Tng Doanh s, Tin bn tng loi sn phm. MaNV H Tn NV Tng DS Bnh Kem C Hp ... Ru 0001 Nguyn Vn A 7000000 1200000 700000 ... 1500000 ... .... .... .... ... ... ..... 20- Cho bit doanh s bn hng ca mi nhn vin trong nm 2002 theo tng qy gm cc ct: MaNV,H tn, Tng Doanh s, Quy 1, Quy 2,... Lu tn Qry Doanh So Nhan Vien Tung Quy2002 21- Cho bit doanh s bn hng ca mi nhn vin trn tng mt hng theo tng qy trong nm 2002 gm cc ct: MaNV,MaSP,TenSP, Tng Doanh s, Quy 1, Quy 2,., Quy 4. Lu vi tn Qry DSo Nhan Vien ban SP Tung Quy2002</p> <p>Bi thc hnh 3: Thit k report Ni dung: To report bng wizard Thit k report trong design view To Main/Subreport1- Dng AutoReport:Tabular to bo co "Danh Mc Sn Phm" nh sau:</p> <p>To report bng wizard</p> <p>2- Dng AutoReport:Columnar to bo co "rptTheNhanVien" nh sau:</p> <p>3- S dng AutoReport Columnar vi ngun d liu t bng Khach hang to bo co rptDanhMucKhachHang trn kh giy A4 nh hnh mu.</p> <p>4- To report "San Pham Khach Mua Moi Nam" in danh sch cc sn phm khch mua, phn theo tng nm. Cui mi nm v mi khch hng c dng tnh tng tin mua.</p> <p>Hng dn: D liu trn bo co gm thng tin v Khch hng trong table Khach Hang lin kt mtnhiu vi cc sn phm khch mua mi nm thng qua M khch. Do , cn to query tng hp s lng , tin mua mi sn phm trong tng nm. To query San Pham Khach Mua Moi Nam bng Simple Query Wizard: Chn cc fields trong: San Pham, query Lien Ket CTHD. Tnh tng ct soLuong v Thanh tien. Sau , sa li tn cc Fields nh sau:Field MaKH Nam: Year(NgayLapHD) TenSP DonVi Tinh TongSL: SoLuong Tienmua: ThanhTien Table San Pham San Pham Total Group by Group by Group by Group by Sum Sum</p> <p>To Report bng Report Wizard, click Ok HT1: Chn cc Fields trong: + Table Khach Hang: MaKh, TenCty, Diachi</p> <p>+ Query San Pham Khach Mua Moi Nam: chn ht cc fields tr field MaKH HT2: Chn kiu hin th d liu theo table Khach Hang (by Khach Hang), click nt Next HT3: Chn Nam lm chun phn nhm cc sn phm khch mua trong nm. Click nt Next. Do you want to add grouping levels? Click nt Summary Options, chn tnh tng Sum ct TienMua cc sn phm khch mua trong tng nm. Click OK, v click Next. 5- To Report Doanh Thu San Pham Moi Nam dng Groups/Totals lit k cc s lng, tin bn tng sn phm trong tng nm. C tnh tng s lng v tng tin bn trong mi Nm v ca tt c cc Nm.</p> <p>Hng dn: D liu trn bo co gm: Nm, tn sn phm, n v tnh, s lng v tin bn sn phm trong nm. To query Doanh Thu San Pham Moi Nam FieldNam: Year(NgayLapHD) MASP TenSP TongSL: SoLuong TienBan: SoLuong*DonGia Table San Pham TotalGroup by Group By Group By Sum Sum</p> <p>To Report bng Report Wizard, vi bng ngun l query Doanh Thu San Pham Moi Nam. HT1: Chn cc Field: Nam, TenSP, DonViTinh, Soluong, TienBan. V click nt Next HT2: Chn field Nam phn nhm cc mu tin sn phm, click nt Next HT3: Click nt Summary Options, chn tnh tng Sum mi nm cho ct SoLuong v TienBan. Click OK, v click Next.</p> <p>6- To report Doanh Thu Moi Nam lit k tin bn mi nm.</p> <p>Hng dn: D liu trn bo co gm c: Nm v tin bn sn phm trong nm. Nn to query Doanh Thu San Pham Moi Nam lm ngun cho bo co. To Report bng Report Wizard, vi bng ngun l query Doanh Thu San Pham Moi Nam. HT1: Chn cc Field: Nam, TienBan. V click nt Next HT2: Chn field Nam phn nhm cc mu tin sn phm, click nt Next HT3: Click nt Summary Options, chn tnh tng Sum mi nm cho ct TienBan v Summary Only ch hin dng tng cui nm khng hin chi tit tng sn phm trong nm nh cu 3. Click OK, v click Next.</p> <p>1- To report Doanh Thu SP Mt Qy bo co s lng v tin bn mi thng ca tng sn phm trong mt qy.Trong , Qy v Nm c nhp khi m Report. Mu bo co sau y hin th thng tin ca qu 1 nm 2002 ( chn hnh ty ) DOANH THU SN PHM QU 1 NM 2002 ISTT Sn Phm Tng s Tng Tin Ngy lp bo co: 15/04/2002 Thng Th Nht Thng Th Hai Thng Th Ba S Tin S Tin S Tin lng lng lng</p> <p>Thit k report trong design view</p> <p>1 2</p> <p>Bnh kem</p> <p>Bnh m</p> <p>131 ...</p> <p>$ 1310 ...</p> <p>55 ...</p> <p>$ 550 ...</p> <p>0 ...</p> <p>$0 ...</p> <p>76 ...</p> <p>$ 760 ...</p> <p>Cng c Qu : Xxx xxx xxx xxx Xxx xxx xxx xxx Hng dn: To Query ngun cho Report: Add: Hoa Don, Chi Tiet HD, San Pham Field TenSP Tongso: Tongtien: TienThang1:iif(Month( SLThang1: Soluong Soluong* NgayLHD) Mod 3 = iif(Month(NgayLHD) Dongia 1 ,Soluong*Dongia,0) Mod 3 = 1, Soluong, 0) Table SPham Total G. By Sum Sum Sum Sum Criteria ..... .. Nam: Year(NgayLapHD) Quy:Datepart(q,NgayLapHD) Hoa Don Where Where [Nhap Nam] [Nhap Quy]</p> <p>Lp cng thc tng t cho thng th 2 v thng th 3 trong qy: TienThang2:iif(Month(NgayLapHD) Mod 3 = 2 , Soluong*Dongia, 0) TienThang3:iif(Month(NgayLapHD) Mod 3 = 0 , Soluong*Dongia, 0) Mn hnh thit k Report nh sau:</p> <p>2-</p> <p>Trong , cc cng thc dng Cng c qu nh sau : Ct Tng SL =Sum([TongSL]) Ct Tng tin =Sum([TongTien]) Cc ct cn li han ton tng t. - Chn i tng Image v chn file hnh bt k Thit K Report dng Group/Total:To report Doanh S SP Cc Nm in s lng, tin bn tng sn phm theo tng qy trong nm.</p> <p>DOANH THU NM:</p> <p>2000</p> <p>Sn phm bn trong Qu 1 STTTN SPS LNGTIN BN1Ru288$ 66,384.00 .......Doanh thu Qy 1:$ xxxxxxSn phm bn trong Qu 2 STTTN SPS LNGTIN BN.......Doanh thu Qy 2:$ xxxxxxSn phm bn trong Qu 3 STTTN SPS LNGTIN BN.......Doanh thu Qy 3:$ xxxxxxSn phm bn trong Qu 4 STTTN SPS LNGTIN BN.......Doanh thu Qy 4:$ xxxxxx Doanh thu c nm 2000 : $ xxxxxxxx DOANH THU NM: 2001</p> <p>Hng dn: To Query ngun cho Report ( nu cha ). Add: Hoa Don, Chi Tiet HD, San Pham Field Nam:Year(NgayLapHD) Quy: DatePart(q,NgayLapHD) TenSP TongSL:Soluong TongTienBan: Soluong*Dongia Table SPham Total Group by G.by G. By Sum Sum</p> <p>Dng Query lm ngun d liu, tao Report vi mn hnh thit k nh trn.</p> <p>3- Thit K Report dng Main/Subreport : to report Ha n dng Main/SubReport theo mu sau:</p> <p>Hng dn: To report cha cc chi tit ha n cho Subreport trong report Hoa Don: - To Query cho Subreport: Add: Chi tiet HD, San Pham Field MaHD TenSP Dongia Soluong TienSP:Soluong*Dongia Table CTHD San Pham San Pham CTHD - To Report dng Tabular vi bng ngun l query trn. To thm iu khin tnh tng cho field TienSP t trong vng Report Footer. To Main Report Hoa Don - To Query cho Main Report: Add: Hoa Don, Khach Hang, Nhan Vien Field MaHD TenCty Hoten:Ho &amp; &amp; Ten NgayLapHD Ngaynhanhang Table Hoa Don Khach .. Nhan Vien Hoa Don Hoa Don Thit k Main Report nh sau:</p> <p>-</p> <p>Nhng SubReport vo MainReport v t thuc tnh lin kt nh sau : Link Child Field : MaHD Link Master Field : MaHD</p> <p>Bi thc hnh 4: Thit k form Ni dung: To Form nhp d liu cho 1 table To form nhp liu cho nhiu thable To form tm kim thng tin</p> <p>-</p> <p>To Form nhp d liu cho 1 table S dng Form Wizard</p> <p>1- S dng AutoForm:Columnar to form frmDanhMucSanPham cho php cp nht (thm, sa, xa) d liu trong Table Frm_San Pham</p> <p>2- S dng AutoForm:Columnar to form frmDanhSachNhanVien cho php cp nht (thm, sa, xa)thng tin nhn vin trong Table Nhan Vien</p> <p>3- S dng AutoForm:Tabular to form frmDanhMucKhachHang cho php cp nht d liu trong table Khach Hang</p> <p>- S dng cc cng c thit k form1- T table Nhan Vien hy to Form frmNhanVien dng cp nht h s nhn vin</p> <p>Hng dn: Chn cc nt lnh: Chn Control Wizard (Nu cha), ln lt chn Command Button + Xa H S : Chn Record Operations, Chn Delete Record + Tm H S: Chn Record Navigation, Chn Find Record + In H S: Chn Record Operations, Chn Print Record + ng: Chn Form Operations, Chn Close Form. 2- To Form Danh Muc San Pham cho php xem, cp nht sn phm.</p> <p>Yu cu: Khi click nt Thm th hin Form trng cho nhp mu tin mi. Khi click nt Lu th lu mu tin vo table Khi click nt Khng lu th xa b nhng g va nhp cho mu tin. Khi Click nt Xa th xa mu tin hin hnh. Khi click nt Trc | Sau th chuyn sang mu tin k. Khi click nt ng th ng Form. Hng dn: Vng Detail: Chn cc nt lnh: Chn Control Wizard (Nu cha), ln lt chn Command Button + Lu : Chn Record Operations, Chn Save Record + Phc Hi: Chn Record Operations, Chn Undo Record + Trc: Chn Record Navigation, Chn Go to Previous Record + Sau: Chn Record Navigation, Chn Go to Next Record + Thm: Chn Record Operations, Chn Add New Record + Xa: Chn Record Operations, Chn Delete Record + ng: Chn Form Operations, Chn Close Form.</p> <p>1- To form Cap Nhat Hoa Don dng xem, sa v thm ha n mi.</p> <p>To form nhp liu cho nhiu table - Main/Subform</p> <p>Yu cu:</p> <p>Combo M Khch cha MaKH, TenCty ca Table Khach Hang dng cung cp MaKh cho field field MaKH trong table Hoa Don Combo M Nhn Vin cha MaNV, HoTen ca Table Nhan Vien dng cung cp MaNV cho field MaNV trong table Hoa Don. Text box Tn Cty v H Tn NV truy xut d liu t 2 combo tng ng i tng SubForm cha Form Cp Nht CTHD Text Box Tin phi tr cha tng tin cc dng chi tit ca ha n hin hnh. Khi click nt Trc (Sau) hin record k trc (sau) record hin hnh. Click nt U (CUI) hin record u tin (cui cng). Click nt THM th chuyn n mu tin mi Click nt LU th lu mu tin mi vo i. Click nt PHC HI th phc hi d liu trc cho Form. Click nt XA th xa ha n hin hnh Khi click nt TT Khch hng (TT Nhn Vin) th m Form v hin mu tin c m s tng ng vi m hin hnh trong Combo. Click nt In HA N th m Report HOA DON ch Print Preview. Hng dn: Chn Design view, bng ngun l Hoa Don. Vng Form Header : Chn Label lm tiu Form v Text box MAHD t Name l MaHD Vng Detail: - Chn Combo M Khch: ly d liu t bng Khach Hang, chn 2 Field: MaKH, TenCTy. D liu rng buc l MaKH. Lu d liu vo Field MaKH ca Table Hoa Don. t thuc tnh Name l cboMaKH. - Chn Text Box Tn Cty: =cboMakh.Column(1) - Chn Combo M Nhn Vin: ly d liu t bng Nhan Vien, chn 3 Field: MaNV, Ho, Ten. D liu rng buc l MaNV. Lu d liu vo Field MaNV ca Table Hoa Don. t thuc tnh Name l cboMaNV. - Chn Text Box H Tn NV: =cboManv.Column(1) &amp; &amp; cboManv.Column(2) - Chn SubForm|SubReport, khai bo thuc tnh: + Source Object: Tong Hop CTHD + Link child Fields: MaHD + Link Master Fields: MaHD - Lu Form vi tn Cap N...</p>