?Microsoft Access ? ?????

1
?Microsoft Access ??????
?????? (Table)
2
??????????

• ???????
• ????
• ?????
• ?????
• ?????

3
?????

• ????
• ????????????
• ????????????????????
• ???????????
• ?????????
• ????
• ?????
• ?????????
• ????
• ????

4
??????(Field Validation Rule)

• Use the Validation Rule property for a field to
  define a simple check on the values that you
  allow in a field.
• Access won't allow you to enter a field value
  that violates this rule.
• When the rule is violated, a dialog window will
  appear to show the Validation Text(????).

5
Field Validation Rule

• In general, a field validation expression
  consists of
• an operator and a comparison value.
• Ex. gt 100
• The operator can be omitted, if its an "equals"
  ().
• Ex. 100 means 100
• You can specify multiple comparisons separated
  by
• the Boolean operators OR and AND.
• Ex. (1). gt 100 OR lt0 OR 50
• (2). 200 OR (gt0 AND lt100)
• Enclose text string values in s.
• Ex. January, Bill Gates
• Enclose date constants in s.
• Ex. 01/15/95

6
TABLE 5-4. Comparison symbols that can be used
in validation rules Operator Meaning lt
Less than lt Less than or equal to gt
Greater than gt Greater than or equal to
Equal to ltgt Not equal to IN
Equal to" any member in a list BETWEEN AND
A range of values comparison value must be
two values LIKE A text or memo field to
match a pattern string
7
IN

• IN (Mon, Tue, Wed)
• IN (1,3,5,7)

BETWEEN AND

• Between 1 And 12
• Between 1/1/97 and 12/31/97
• Between 120000 AM And 120000 PM

8
LIKE Pattern?? ?????
? ????????(SQL _) ????????(SQL
) ?????(0-9)? charlist ?
charlist??????? !charlist ?? charlist
???????
Like A-Z Like ??00 Like
Like !0-9BMQ Like
????? Like !., Like Smith Like
A-Z-A-Z-A-Z-
9
???????

• ????????????(???)????????
• Ex. ???????????
• ??????????????/?????
• ?????? FieldName
• Ex. field1field2100

10
IIF ??
IIf(expr, truepart, falsepart) ?? ?? expr ?????
?????? true?? ?? expr ? True,????????? false?? ??
expr ? False,?????????
Ex IIf(IsNull(num1),Not(IsNull(num2)),T
rue) IIf (PayBylt3, True,
Left(CCNumber,1)CStr(PayBy))
??PayBylt3 ?? True?, ???? Left(CCNumber,1)CSt
r(PayBy)
See Page 5-35
11
???????
12
??????(Input Mask)

• ????????????????,??????????????
• ???????????/??????????
• ??
• (1). (_ _/_ _/_ _) (10/31/97)
• (2). ( _ _) _ _ _-_ _ _ _ [phone]
• (3). AN_ _ __at__ _ ANxyz_at_12

13
??????

• ???? ???? ?????
• ???????????????,??????(????)
• ????
• 0 ??????????????
• 1 ????????
• ?????????????????

Ex \(999\)000\-00000
()-
14
????????
0 ?? (0 ? 9,????,???? ?? - ???)? 9 ?????
(????,?????????)? ????? (??????????????????, ?
?????????????????????). L ?? (A ? Z,????)? ? ??
(A ? Z,??????)? A ????? (????)? a ?????
(??????)? ?????????? (????)? C ??????????
(??????)?
15
????????
. ??????? , ?????? - /
??????????? lt ???????????? gt
???????????? ! ???????????,?????????
??????????????????? \ ?????????????? ? ???
???? ???? ?? ?????????,???? () ????
16
Access??? ????
???? ???? ????? ???? _at_0 ? A?
?? _at_9 ? A? ?? _at_L ? A? ?? _at_D ? ??
??? _at_d ? ?? ???
17
???? ???? \(000\) 000\-0000 \(999\)
999\-9999 \(000\) AAA\-AAAA 999 gtL????L?00
0L0 gtL0L 0L0 00000\-9999 gtLlt??????????????
SSN 000\-00\-0000 gtLL00000\-0000
[phone]
[phone] ( ) 555-0248
(206) 555-TELE
-20 2000
GREENGR339M3 MAY R 452B7
T2F 8M4
98115- 98115-3007
Maria Brendan
SSN 555-55-5555
DB51392-0493
18
?????

• ?????
• ????
• ???
• ???
• ???

19
????

• ???/?????? ? ??
• ??????
• ???????
• ????????
• ????????
• ????(Join)
• ????(Inner Join)
• ????(Outer Join)??????(Left Join)?
• ??????(Right Join)

20
1 ???? 7 ??
[phone] ??? 2 ???? 12
?? [phone] ??? 3 ???
18 ?? [phone] ???
?
1
21
Relationship 1 ? ?
22
???????
23
(No Transcript)
24
????(Join)
Inner Join
Outer Join
Left Join Right Join
25
???? ???? ???
???? ???? ????
FISSA
?????
FAMIA
10184
26
(No Transcript)
27
Inner Join
Left Join
Outer Join
Right Join
28
????

• ??????
• -??? -????
• ?????

29
Access?????

• A table can have up to 255 fields.
• A table can have up to 32 indexes.
• A multiple field index can have up to 10 columns.
  The sum of the lengths of the columns cannot
  exceed 255 bytes.
• A row, excluding memo fields and OLE objects, can
  be no longer than approximately 2 kilobytes.
• A memo/OLE field can store up to 1 GB.
• An Access database cannot be larger than 1 GB.