????????????? ?? Microsoft Access

1
????????????? ?? Microsoft Access
2
????????? (Query)

• ?????????????? (Object) ????????????????????????
  ????? ???????????????? ???? ?????????????????????
  ????, ?????????????, ?????????????????????????????
  ???? ??????????????????????

3
??????????? Query

• 1. ???????????????????????????????????
• 2. ????????????????????????????
• 3. ?????????????????????????????????????
• 4. ?????????????????? ??????????????????
• 5. ???????????????????????????????????????????

4
????????? Query

• 1. Select Query ????????????????????????????????
  ??????????? ???????????????????
  ???????????????????????????? Query ???????????
• 2. Crosstab-Query ???????????????????????????????
  ????????
• 3. Parameter Query ???????????????????????????????
  Query ???? ??????????????????????????????????????
  ????
• 4. Action Query ?????????????????????????????????

5
Query ?????????? Action Query

• 4.1 Make Table Query ?????????????????????????????
  ?????????????? Query
• 4.2 Append Query ?????????????????????????????????
  Query ??????????????????????????????????????????
• 4.3 Update Query ?????????????????????????????????
  ???????????
• 4.4 Delete Query ??????????????????
  ???????????????????

6
????????? Query

• 1. Design View ?????????????????????????? Query
  ???????????????????????????????
• 2. Datasheet View ????????????????
  ????????????????????????????????
• 3. ?????? SQL ??????????????? Design View
  ????????????????????? SQL (Structured Query
  Language)

7
???????? Query

• 1. Query Wizard ?????????????????? Access
  ??????????????
• 2. Query Design ??????????????????????
  ???????????? Query Wizard ????????????????????????
  ?????

8
???????? Query ???? Wizard

• ?????????????? Wizard ?????????? Query
  ?????????????????????

1. ????????? Query
2. Click mouse
3. ??????????????? New Query
Wizard 4 ????????? ??????????
9
???????? Query ???? Simple Query Wizard

• ????????????????????? TblProducts
  ?????????????????????????
• ???????????????????????????????????????????
  ???????????????????????????? ?????????????????????
  ????

????? TblProducts ProductID (PK) CategoryID Produ
ctName UnitPrice VATFlag UnitInStock
(482010)/3
26
???????????????? ??????????? 3
10
1. Click mouse
2.1 ????? Sample Query Wizard
2.2 Click mouse
3.1 ?????????? TblProducts
3.2 ?????????? UnitPrice
3.3 Click mouse
11
4.1 ????? Summary
4.2 Click mouse
4.3 ????? Avg ??????????? UnitPrice
4.4 ??????????????????????
4.5 Click mouse
12
5.1 ????????????????????????
4.6 Click mouse
52 Click mouse
6. ??????????????????????? ???????????????????????
??????????????????????????????????
13

• ?????????? Sample Query Wizard ???????????????????
  ???????? ????? ??????

N
M
1
N
????? TblCustomers CustomerID(PK) CustomerName Cu
stomerAddress ZipCode Telephone HomePageName
????? TblCategories CategoryID(PK) CategorylName
CategoryDescription CategoryPicture
????? TblProducts ProductID (PK) CategoryID Produ
ctName UnitPrice VATFlag UnitInStock
???????? ????????????????????? ????? ?????????
?????????????? ????????????????????????????
??????????? ????????????????????????? ???????????
??????????????????????? ????????????????????
????????????? ????????????????????????????
14
1. Click mouse
2.1 ????? Sample Query Wizard
2.2 Click mouse
3.1 ?????????? CategoryName, ProductID
??? CustomerName ???????? TblCategories, TblProduc
t ??? TblCustomer ????????
3.2 Click mouse
15
4.1 .???????? Detail ?????????????????????????????
??????????
5.1 ???????????????????
4.2 Click mouse
5.2 Click mouse
6. ??????????????????????? ???????????????????????
CategorName, ProductID ??? CustomerName
??? ????? TblCategories, TblProduct ???
TblCustomer ????????????????
16

• ????????????????? Crosstab Query Wizard ??????

??????????????????????????????????? ??????????????
Crosstab Query ????????? ?????????????????????????
???????
17
1.Click mouse
3.1 ?????????????????????????????????
2.1 ????? Crosstab Query Wizard
2.2 Click mouse
3.2 Click mouse
18
4.1 ?????????? CustomerName ??????????
5.1 ?????????? CategoryName ??????????????
4.2 Click mouse
5.2 Click mouse
19
6.2 ????? Count
6.1 ????? ProductID
7.1 ????????????????????
6.4 Click mouse
6.3 Click mouse ???? ????????????????????? ???????
??????? ????????????????????
8. ??????????????????????? ??????????????? ???????
?????????????????????????????????? ???????????
7.2 Click mouse
20

• ????????????????? Find Duplicates Query Wizard
• ?????????????????????????? ?????????????????????
  ????????????????????????????????????????????
  ??????

????????????????? CustomerID ????????
21

• ???????? ??????????? TblOrder ????????????????????
  ?? ??????

????? TblOrders OrderID (PK) CutomerID OrderDate
ShippedDate
????????????????????????????????????????? ????????
????????????????????????????????? ??????????????
1. Click mouse
2.1 ????? Find Duplicates Query Wizard
2.2 Click mouse
22
3.1 ????????????????????? TblOrders
4.1 ??????????????????? ??? ????????????????? Cust
omerID
3.2 Click mouse
4.2 Click mouse
23
5.1 ?????????????????????????????
TblOrderID OrderDate ??? ShippedDate
6.1 ????????????????????
5.2 Click mouse
6.2 Click mouse
7. ??????????????????????? ?????????????? ????????
????????????????????????????? 1 ?????
24

• ????????????????? Find Unmatched Query Wizard
• ??????????????????????????????? 2 ?????
  (?????????) ???????????????????????????????????
  ?????????????????????????????????????????????
  ???????????????????????????????
  ???????????????????????????????
• ?????????????????????????????????????????

????????????
??????????
1
N
????? TblCustomers CustomerID (PK) CustomerName C
ustomerAddress ZipCode Telephone
????? TblOrders OrderID CustomerID OrderDate Ship
pedDate
?????????????????????????? ???????????????????????
25
1. Click mouse
2.1 ????? Find Unmatched Query Wizard
2.2 Click mouse
3.1 ????????????????????? TblCustomers
3.2 Click mouse
26
4.1 ??????????????????? TblOrders
4.2 Click mouse
5.1 ?????????? CustomerID ?????????????????? ?????
??????????????????????????????????????????? ??????
?????????????
5.2 Click mouse
27
6.1 ????????????????????????????
6.2 Click mouse
7.2 Click mouse
7.1 ?????????????????????
8. ????????????????????? ?????????????????????????
???????????????????????????????????