• 沒有找到結果。

使用 MS Access 設計數據庫,學習SQL

N/A
N/A
Protected

Academic year: 2022

Share "使用 MS Access 設計數據庫,學習SQL"

Copied!
27
0
0

加載中.... (立即查看全文)

全文

(1)

使用 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 温華恩先生

電腦科科主任 宣道會鄭榮之中學

(2)

1. Overview

(3)

▪ 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)

4

Overall Teaching strategy

Tables Queries Forms Reports

(5)

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

(6)

Considerations

Cater the learner’s diversity?

Compatible to SBA?

Adapt to all or most of

the new curriculum?

(7)

2. Characteristics - Tables

(8)

A. Create Tables

Design view

Datasheet view

SQL view

Validation Rules Default values Combo Box Mask

(9)

B. Relationships

Visualization, Drag & Drop SQL view

(10)

C. ER-Diagram

(11)

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

(12)

D. Data Entry

(13)

3. Characteristics - Queries

(14)

A. Create Queries

Datasheet view

Design view

SQL view

Reference:

https://www.fmsinc.com/MicrosoftAcces s/query/crosstab-report/index.html

(15)

B. Query types

(16)

C. Query types – Crosstab

Reference:

http://www.iaccessworld.com/how-to-create-a-crosstab-query-use-group-by-and-sum/

(17)

3. Characteristics - Forms

(18)

Teaching strategy

Simple Form

Master /detail

form

Form with interactive

functions

Form with VBA codes

(19)

A. Simple Form

(20)

B. Master/detail Form

With relationship with other tables (FK)

(21)

C. Form with interactive functions

Print button

(22)

D. Form with VBA functions

Proper Case

MNAME = StrConv(MNAME, vbProperCase)

(23)

4. Characteristics - Reports

(24)

A. Create report

(25)

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

(26)

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

(27)

THANKS!

Contact Us

27

https://www.hkace.org.hk/

why@hkace.org.hk

https://www.facebook.com/hkace.org

https://instagram.com/hkace.socialmedia

參考文獻

相關文件

(三)使用 Visual Studio 之 C# 程式語言(.Net framework 架構)、Visual Studio Code 之 JavaScript 程式語言(JavaScript framework 架構) ,搭配 MS

Besides adopting effective strategies, such as flexible grouping, differentiating learning tasks and promoting self-access learning, to cater for learners’ diverse needs, due

Design learning activities and projects that require students to evaluate, extract, organise and synthesise information and ideas from different sources, and create new ideas

[r]

Direct Access Attack 直接訪問攻擊. 詳細的攻擊手段描述請閱附件一 SQL

(A)SQL 指令是關聯式資料庫的基本規格(B)只有 SQLServer 2000 支援 SQL 指令(C)SQL 指令 複雜難寫,適合程式進階者使用(D)是由 Oracle

private void Page_Load(object sender, System.EventArgs e) {. string dataSource

zSELECT 欄位名稱1, 欄位名稱2, … FROM 資料表名稱 WHERE 條件式 ORDER BY 欄 位名稱 (字串需以單引號 '