Microsoft Access

1
Microsoft Access

• ?.?. ???????? ??????????

2
Microsoft Access

• DBMS ( DataBase Management System )
• ???????????????????
• Field
• Record
• Table / File
• Relational Database

3
?????????? Microsoft Access

• ??????? Double Click ??? Icon ?? Desktop
  ???????Taskbar Start gt Programs gt Microsoft
  Access
• Title bar
• Menu bar
• Toolbar ???? Database toolbar
• Dialog box ????????

4
?????????? Microsoft Access (???)

• Dialog box ???????? - Blank Access database -
  Access database wizards , pages , and projects
  - Open an existing database
• ???????? Blank Access database ?????????? folder
  ?????????? file ???????????????????

5
??????? Dialog box database ???????? file ?? 6 tab

• Tables
• Queries
• Forms
• Reports

• Pages
• Macros
• Modules

6
Dialog box database

• Tables ??????????????????????????????? ?????
  table ?????? key ????????????????
• Queries ???????????????????? tables
  ???????????????????
• Forms ??????????????????????? ?????????
  ?????????????????? ?? ????? ???????? table
• Reports ?????????????? ???????????????? printer

7
Dialog box database(???)

• Pages ???????? web page ?????? internet
• Macros ?????????????????????? ????????????????????
  ????????????
• Modules ??????????????????????????
  ??????????????????????????????
• ??????? Icons ????? Large Icons Small Icons
  List Details

8
???????? table

• Create table in Design view
• Create table by using wizard
• Create table by entering data
• ???????????? Table ?????????? ???? Table
  ???????? Design ???? Table ???????? Datasheet
  ???????? tool Open ????????? Copy Delete
  ??????????? Table ???

9
????????? Table

• Design View ?????????????????? table
  ????????Field NameData TypeField Size
  ?????????????????? ???? ?? ????? Field Name ???
• Datasheet View ??? key ?????????? ??????
  ???????????? ?? ??????????????

10
???????? table (???)

• ???????? table ???????? Design view
• Field Name ?????????????? 64 ???????????????????
• Data Type ???????????????????????????????field
• Description ?????????????? field
• - Field Properties ???????????????????? field
  ???? Field Size ???????????????????????? field
  Format ????????????????????????????? field
  ?????? Datasheet view ??? Table

11
Data Type

• Text ???????????? ?????? ???????? keyboard
  ????????? ???????????????? ????????????? 255 ???
  ??????????????????????????
• Memo ?????? Text ??? vary ??????????
• Date/Time ???????????????????????
  ?????????????????????????? ??????????????????
• Yes/No ??????????????? 2 ?????????? Yes / No
  True / False On / Off 1 / 0

12
Data Type (???1)

• Number ????????????????????????
• Byte ????????? 0 ??? 255 ?????? 1 byte
• Integer ????????? 32,768 ??? 32,767 ?????? 2
  bytes
• Long integer ????????? /- ?????? 2,000 ????
  2,147,483,648??? 2,147,483,647??????4 bytes
• Single ???????????????????? /- 3.4 e 38 ??????
  4 bytes
• Double ??????????????????????? /- 1.797 e 308
  ?????? 8 bytes

13
Data Type (???2)

• Currency ?????????????????????????
  ??????????????????????????? Number
  ????????????????????????????????????????????
• AutoNumber ??????????????????????? Access
  ??????????????????????????????? record ??????????

14
Data Type (???3)

• OLE Object ???? object ???? ?????????? link
  ????????Access????file wordfile??? file ?????
• Hyperlink ???????????????????? link
  ?????????????????? ???????????????? Access ????
  program ???? ???????????????????????? ??????
  network ???? internet
• Lookup Wizard ????? Lookup Wizard ????????? list
  box ???????????????????????? ?????????? key
  ????????????

15
Primary Key

• ???? field ?? field ??????? Table
• Unique ??????????? field ????????????????????
  record
• ?????????????? field ???? ????????????? ( null )
  ??????

16
??????????????????? Tables

• One to one ??????????????????? ????? record ??
  Table ????? ??????????????????? ????? record
  ????? Table ?????
• One to many ??????????????????? ????? record ??
  Table ????? ??????????????????? ???? record ?????
  Table ?????

17
Datasheet View ??? Table

• ??????????????????????????????? Tool ???? Sort
  Ascending Sort Descending
• ???????????????????????????????????
• - Filter By Selection ?????? cursor??????????????
  ????????????????????? tool ??? ????????? record
  ??????????????????????? ????????? tool Remove
  Filter

18
Datasheet View ???Table(???1)

• Filter By Form - ???? form ?????????????????
  click drop down list ????????????????? field
  ???????????? field ????? field ?????????? and -
  ????????? tool ???? Apply Filter - ?????????
  record ????????????????? - ?????? ?????? tool
  ???? Remove Filter

19
Datasheet View???Table(???2)

• Find ?????????????? - Find What key
  ?????????????????????- Look In ????? field
  ?????????????????? field ????? field ??? cursor
  ???? ???????????????? Table - Match Any Part
  of Field Whole Field Start of Field
• Replace ?????????????????????????????????????
  Find ????? Replace With key ????????????????????
  ??

20
????????? Query

• Select Query ????????????????????Table
  ???????????????????
• Update Query ????????????????? ?? Table
  ??????????
• Delete Query ????????????? Table
• Append Query ????????????????????? Table

21
????????? Query (???)

• Make-table Query ???????? Table ????
• Cross tab Query ????????????????????? ?????
  ????????? ????????????????????????? 2 ????? ????
  ????????????????????????????????????????
• Parameter Query ???? Select Query
  ???????????????????? keyboard ????parameter
  ??????????????????? Table

22
????????? Query

• Design View ??????????????????????????????????????
  ?? tables ???????? field Criteria
  ????????????????(sort) show
• Datasheet View ???????????????????????????
• SQL View ?????????????? SQL ??????????????????????
  ??????????????????????????? Design View
  ?????????????????????????????? SQL

23
Criteria

• ???????? , -, , /, \ ????????? , ??????? ,
  MOD ????????????????? , ( )
• ?????????????? gt, lt, , gt , lt , ltgt , NOT,
  AND, OR
• ???????????????? ???? MSACCESS

24
Criteria (???)

• ???????????? IS NULL , IS NOT NULL
• ??????????????????????? LIKE ?????? BETWEEN
  ??????1 AND ??????2 IN (??????1, ??????2,,
  ??????n)
• ??????????? field ????table.????field

25
???????

• 1. ???????????????????????????? c
• 2. ?????????????????????? 1 ??????????
• 3. ???????????????????????????????? 200
• 4. ?????????????????????????? 200 ??? 400
• 5. ?????????????????????????????200???400

26
??????? (??? 1)

• 6. ????????????????????? 200,420,500
• 7. ???????????????????????? 200,420,500
• 8. ?????????????????????? 1 ???? ??? 2 ??????????
• 9. ??????????????????? ? ?????????? ???
  ????????????????????? 200

27
??????? (??? 2)

• 10. ????????????????????????? ????????????????????
  ?? ??????????? ???? ????????? key ?????????? 1
  output ?????????? ????????????? ??????
  ?????? 15 ???? ???? 5

28
??????? (??? 3)

• 11. ?????????????????????? ???????????????????????
  ???? output ?????????? ???????????
  ?????????? 1 20 ?????????? 2 3 .
  . . .

29
???????? Form

• ????? tool ???? New ????? ??????????????
• - Design View
• - Form Wizard
• - AutoForm Columnar
• - AutoForm Tabular
• - AutoForm Datasheet
• ???????! ????????? Table / Query
  ???????????????Form ???? OK

30
????????? Form

• Design View ????????????????????????????????????
  table/query ???????? Field name ?? Field list
  ??????????????????AutoFormat
• Form View ??????????????? table / query
  ???????????? ?? Design View ??????
  ?????????????????? ?? ??????????????
• Datasheet View ??????????????? table / query
  ???????????? Datasheet View ?? table ??????
  ?????????????????? ?? ??????????????

31
??????? Form

• Formatting toolbar ???? Font Font Size B I
  U Align Left Center Align Right Fill
  Color Font Color Border Color Border Width
  Special Effect ???? Raised(???), Sunken(????),
  Shadowed(?????)
• Toolbox toolbar ???? Label Text box

32
???????? Report

• ????? tool ???? New ????? ??????????????
• - Design View
• - Report Wizard
• - AutoReport Columnar
• - AutoReport Tabular
• ???????! ????????? Table / Query
  ???????????????Report ???? OK

33
????????? Report

• Design View ?????????????????????????? table
  /query ????????????? printer ???????? Field name
  ?? Field list ????????????? AutoFormat
• Print Preview ????????????? ???????????? ??
  Design View

34
??????????? ?? Report

• Report Header
• Page Header
• Detail
• Page Footer
• Report Footer

35
??????? Report

• Formatting toolbar ???? Font Font Size B I
  U Align Left Center Align Right Fill
  Color Font Color Border Color Border Width
  Special Effect ???? Raised(???), Sunken(????),
  Shadowed(?????)
• Toolbox toolbar ???? Label Text box

36
????????????????????? Report

• Now()
• "Page "Page" of "Pages
• Sum(Expr1)