使用 MS Access 設計數據庫,學習SQL 指令及製作簡單表單及報告
Using MS Access to design database, learning SQL commands and create forms and reports
Mr. Wen Hua Yan ICT Panel Head
Christian Alliance Cheng Wing Gee College 温華恩先生
電腦科科主任 宣道會鄭榮之中學
1. Overview
▪ Visualize ERD, SQL,…
▪ does not require a great deal of prerequisite learning
▪ provides form and report templates
▪ Expression Builder create
complex reports and queries using codes / without codes
3
Why using MS Access?
4
Overall Teaching strategy
Tables Queries Forms Reports
Curriculum updates (Elective Option A)
5
Relational Databases Concepts, 6,
16%
SQL, 18, 47%
Database Design Methodolog
y, 14, 37%
NEW
Introduction to Databases,
8, 11%
Relational Databases,
28, 37%
Introduction to Database Design Methodology, 22, 29%
Database Applications, Development and
Society, 17, 23%
OLD
Considerations
Cater the learner’s diversity?
Compatible to SBA?
Adapt to all or most of
the new curriculum?
2. Characteristics - Tables
A. Create Tables
Design view
Datasheet view
SQL view
Validation Rules Default values Combo Box Mask
B. Relationships
Visualization, Drag & Drop SQL view
C. ER-Diagram
D. Data Entry
From different sources
Using INSERT SQL command
Access files:
https://drive.google.com/file/d/1YhwHP1fqV_1yVN9lOIyD3quzz3ysiHl3/view?usp=sharing https://drive.google.com/file/d/1fcxSlZddjstnUTT-iG9FUeI2jwgYgHWu/view?usp=sharing
D. Data Entry
3. Characteristics - Queries
A. Create Queries
Datasheet view
Design view
SQL view
Reference:
https://www.fmsinc.com/MicrosoftAcces s/query/crosstab-report/index.html
B. Query types
C. Query types – Crosstab
Reference:
http://www.iaccessworld.com/how-to-create-a-crosstab-query-use-group-by-and-sum/
3. Characteristics - Forms
Teaching strategy
Simple Form
Master /detail
form
Form with interactive
functions
Form with VBA codes
A. Simple Form
B. Master/detail Form
With relationship with other tables (FK)
C. Form with interactive functions
Print button
D. Form with VBA functions
Proper Case
MNAME = StrConv(MNAME, vbProperCase)
4. Characteristics - Reports
A. Create report
25
Further study
Access video training
https://support.microsoft.com/en-us/office/access-video-training-a5ffb1ef- 4cc4-4d79-a862-e2dda6ef38e6
Microsoft Access 2010, 2013, 2016 and 2019 Tutorial
http://holowczak.com/microsoft-access-2007-and-2010-tutorial/
Good Database Project
http://holowczak.com/?s=hair+salon
Upcoming Event
Teacher Workshop (JUL 2022) 1. Introduction to internet and web
elements
2. Create a simple web application 3. Introduction to Database
4. Using SQL query in PHP to add and retrieve data
26
THANKS!
Contact Us
27
https://www.hkace.org.hk/
why@hkace.org.hk
https://www.facebook.com/hkace.org
https://instagram.com/hkace.socialmedia