Ms-access

KEMENTERIAN PELAJARAN MALAYSIA

Self Access Learning Module

ICT Literacy for Secondary School Programm e Database

ICTL For Secondary School - Database Module

PUSAT PERKEMBANAGN KURIKULUM KEMENTERIAN PELAJARAN MALAYSIA

2

ICTL for Secondary School - Microsoft Access Module

MODUL I

INTRODUCTION TO MICROSOFT ACCESS 2003

Curriculum Development Centre Ministry of Education

1

ICTL for Secondary School - Microsoft Access Module

Name of Module:

Introduction to Microsoft Access 2003

Learning Outcomes:

Students should be able to: a. b. c. d.

state the usage of application list example which application of database is used state the function of the features in database create a new database

a. b. c. d. e.

Students known the meaning and usage of database Start Microsoft Access 2003 Create, Name and Save database file Open existing database Exit database application

Knowledge and Skills:

Module Summary: At the end of the module, students should be able to open, name, save file and close database. Activity Summary:

Activities in this module involved: a. b. c. d.

Starting Microsoft Access 2003 Naming and saving file database Opening existing database Exercises

Meaning of database

A database is the computer equivalent of an organized list of information. Typically, this information has a common subject or purpose, such as the list of employees, phonebook guide or a classmates address book. Microsoft Access 2003 (Access 2003) is a database system management (database programs). It can store, manage, manipulate, analyze, retrieve, display or publish information from database.

2

Activity 1: Starting Microsoft Access 2003 There are two methods to start / launch your Mircosoft Access 2003. Method 1: 1. Double click on the Mircrosoft Office Access 2003 shortcut on the Windows desktop (see below). Shortcut icon MS Access 2003

Method 2: 1. Click-on the Start button, then click-on Programs, then click-on Microsoft Access 2003 (Figure 1)

Figure 1

Figure 2

2.The following Access 2003 Getting Started Task Pane will appear on the right side of your Access 2003 screen. (refer figure 2) 3. In the Open area of the Access 2003 Getting Started Task Pane, click the left mouse button on Create a new file. Activity 2: To Name and Save Database File 1. The New File menu screen on the top will appear when you click the left mouse button on Create a new file. 2. Click–on Blank database. (Figure 3) Click here

ICTL for Secondary School - Microsoft Access Module

Figure 3

A File New Database menu screen, similar to the one below, will be on your screen. We’ll have to do several “things” to set-up this screen to save your database. ( Figure 4 )

Step 1

Step 2

Step 3

Figure 4

3. Click-on the small down arrow on the right. Choose My Documents to save by double clicking on the folder. Your selection should now appear in the Save in : area. (Step 1) 4. Next click-in the area to the right of File Name. Type-in the word students profile as shown at the bottom of the above image. (Step 2) 5. Now click-on the Create button. (Step 3) The following Students Profile: Database menu screen will appear. ( Figure 5 )

4

ICTL for Secondary School - Microsoft Access Module

Figure 5

6. Click File and click Exit to end this session and to close Ms Access 2003. Activity 3: Open Existing Access Database There are two methods to open existing database. Method 1: 1. Repeat Activity 1 to open Ms Access 2003. Once you have created a database, you will see your database in the Open portion of the Task Pane. 2. Click students profile. (refer Figure 6) Click students profile

Method 2:

Figure 6

5

ICTL for Secondary School - Microsoft Access Module

1. Click File in the Menu Bar, then click Open. (refer figure 7)

Figure 7

2. When the Open menu screen appears, click the small down arrow to the right of the Look in area and select My Documents.

Select My Documents

Click students profile

3. Then click the name of your database (e.g. students profile.mdb) and then click Open. Note: You will notice in the Students Profile: Database menu screen, in the left border consist of objects bar: Tables, Queries, Forms, Report, Pages, Macros and Modules. Access window title bar

Database toolbar

Database window menu bar

Database window toolbar bar

6

ICTL for Secondary School - Microsoft Access Module

Database window title bar

Database window

Objects bar

Groups bar

There are 4 important objects as stated in the table below. Objects Bar

Table

Form

Query

Report

Function A collection of records that identify a category of data, such as Customers, Orders, or Inventory. Data inside a table is arrange by column name field and row named Record.  Field – A single characteristic or attribute of a person, place, object, event, or idea.  Record – A set of related field values. Use to enter new information, to edit or remove existing information or to locate information. Used to locate information, to view, change or analyze it in various ways. A select query retrieves data from one or more tables and displays the result in datasheet. A select query can be used to group record and calculate sums, counts, averages and other types of totals. Typically used to summarize, organize and analyze information in order to express a particular point of view to specific audience.

4. Click File and Exit to stop this session. At the end of this activities, you had learned how to: i. ii. iii.

Create a database Close a database Open an existing database

In the next module you will learn how to design table and insert data. Exercises

7

ICTL for Secondary School - Microsoft Access Module

Question 1 Give two examples of database that you know. Question 2 Create a database named Teachers Profile. Save this file in My Document directory.

MODULE 2

STUDENTS PROFILE

8

ICTL for Secondary School - Microsoft Access Module

Curriculum Development Center Ministry Of Education Malaysia

9

ICTL for Secondary School - Microsoft Access Module

Name of Module:

Students Profile

Learning Outcomes:

Student should be able to create a table, define primary key and insert data.

Knowledge and Skills: 1. Students known how to differentiate field, record and table 2. Students known all types of data 3. Creating table. 4. Define primary key 5. Inserting the data in the table. Module summary: At the end of this module, table below will be created by students.

Activities summary: This module involved a few activities such as; 1. Creating tables. 2. Inserting the data in the table. 3. Exercise.

10

Information and Communication Technology Literacy For Secondary– Data Base Module

Activity 1: Creating Table Next you will be creating the fields that make up a database. This is similar to creating a blank personnel form (on paper) that will be "filled-in" for each employee (Name, Address, Phone Number, etc – are called fields in a database). This "forms" are called records in a database. There will be a record, or form, for each employee. All the forms, together, make up a Table (database). For example, students profiles table as shown below. (Figure 1)

record 2

Field 1 (numbers)

Field 2 (name)

Field 3 (address)

Field 4 (icno)

Figure 1

The table above content four records. Each records have four fields which are numbers field, students name, address and ic number. At the end of this module, students will creating a database which is consist a table name “students profile”.

11

Information and Communication Technology Literacy For Secondary– Data Base Module

Steps Creating Table 1. Open MS Access 2003 software (Refer module 1, if needed). 2. Click File, click Open, find students profiles database file then click Open button. (refer Figure 2)

Figure 2

3. Database window students profile: Database will appeared as shown below (Figure 3) then click New button at database windows toolbar. The dialog box in Figure 4 will appear.

Figure 3

12

ICTL for Secondary School - Microsoft Access Module

4. Click Design View , then click OK to get Datasheet view as shown at Figure 5.

Design View

Figure 4

Figure 5

5. At Field Name column, type in numbers and at Data type column, change value Text to Number. (refer Figure 6)

Click this button to view the list of data type, then click number

Figure 6

13

ICTL for Secondary School - Microsoft Access Module

There are 10 types of data as stated in the table below; Types Data Text

Memo

Number

Function You may type in any alphabetical/numerical data that you desire-up to a maximum of 255 characters. As indicated, this is a text field, so you can't do mathematical calculations. Examples of Text data are: names, addresses, stock numbers, room numbers, zip codes, etc This field is for lots of text. You can have up to 32,000 characters. This field is for numbers where you want to add, subtract, multiply, divide, average, and do numerical calculations. This field can be a very large size, so when we get to Field Properties, we'll talk about "sizing" this field so it doesn't take up too much "space" in storage.

Date/Time

Dates and Times. You may format this later, as you may desire.

Currency

Dollar ($). You may format this later, as you may desire. For example Ringgit Malaysia (RM)

AutoNumber Yes/No OLE Object

Hyperlink

Lookup Wizard

This field is an "automatic" counter that assigns a number each time you put data into a new field. This is a "True/False" or "Yes/No, 0/1” type of field. This means "Object Link Embedding" which indicates you can insert a graphic, picture, sound, etc. Pretty neat to put a photograph in a personnel record or a picture of an inventory item in the stock record (advanced stuff). A hyperlink is a pointer from one object to another. The destination is frequently another Web page, but it can also be a picture, an e-mail address, a file (such as a multimedia file or Microsoft Office document), or a program. The hyperlink itself can be displayed as text or as a picture. This wizard creates a lookup column, which displays a list of values you can choose from.

14

ICTL for Secondary School - Microsoft Access Module

7.

Insert data in the Field Name, Data Types for each fields as displays (Figure 7). Students can move to the next column by press “Tab key”.

1. Place the cursor at address 2. Change Field Size value to 100

Figure 7

Note: Place the cursor at the address inside the Field Name, then change Field Size value from 50 to 100. 8. Click numbers at field name column, click at Edit menu bar, then click Primary Key or icon Primary Key

close icon . Shown at figure 8.

PRIMARY KEY means the value in the primary key fields is need to uniquely identify each record; that’s no record can have the same value in this field.

Figure 8

15

Primary key icon

ICTL for Secondary School - Microsoft Access Module

Figure 9

9. Click Close icon as shown at figure 8 to close the creating table session. Then dialog box in figure 10 displayed.

Figure 10

10. Click Yes. Dialog box Save As in figure 11 displayed.

Key in table name here

Figure 11

16

ICTL for Secondary School - Microsoft Access Module

11. Type students profile at Table Name and then click OK at dialog box as shown in figure 11. Now you had finished creating one table named “students profile:database” as shown below. (Figure 12)

students profile table created

Figure 12

Activity 2: Inserting data in the table The students profile table that created before, not contains any data. Steps below can be used to key in the data. 1. Click at Students Profile table then click open at database window toolbar. (Figure 13)

17

ICTL for Secondary School - Microsoft Access Module

Figure 13

2. Datasheet view Students Profile : Table as shown in figure 14 displayed.

Figure 14

3. Key in data at the students profile datasheet. At the first row, type number ‘1’ at numbers column. Then, Type name Abdul Rahman b. Idris at name column. Next, type address and ic number at address and icno column.

18

ICTL for Secondary School - Microsoft Access Module

Figure 15

4. Insert all data in the table by repeating the same process above. As a result, students will get the table below.

Figure 16

5. Click icon save records.

to saving file. Students Profile table now contained five

6. Click icon close

to close the inserting data session.

7. Exit the Microsoft Access 2003 Software. Exercises 1. Build one of your friends profile table. Named the table “Co-curriculum Data”. That table must have fields as stated below: • •

Numbers Student Name

19

ICTL for Secondary School - Microsoft Access Module

• • • •

Class Club Sports Uniforms Unit

2. Insert 10 record in that table.

MODULE 3 USING RECORD IN DATABASE

20

ICTL for Secondary School - Microsoft Access Module

Curriculum Development Center Ministry Of Education Malaysia

Name of Module:

Using Record In Database

Learning Outcomes:

Students should be able to use and manipulate records in database.

Knowledge and Skills: a. b. c. d. e.

Students should be able to use record in database. add records add field edit records find records from Table

Module Summary: At the end of this module, table below will be created by students.

21

ICTL for Secondary School - Microsoft Access Module

Activity Summary: Activities in this module involve: 1. add records 2. add field 3. edit records 4. find records from Table

Activity 1: Add Records 1. Open Microsoft Access 2003 2. Click Open an Existing Database, then click file students profile and click OK. 3. In the database window, double click students profile in the Tables pane to open the table in Datasheet view, as shown in figure 1. 4. To add new record, click on icon New Record that can be seen at database toolbar or at the bottom of datasheet window. (refer figure 1).

Icon New

Figure 1 22

ICTL for Secondary School - Microsoft Access Module

5. Add five more record as below. (refer table 1) name Alias b Mohamad Mohd Shahid b Mohd Som Noorul-Aini bt Ambak Noor Asiah bt Zakaria Fasitah bt. Mansor

Save record.

address No. 5, Jalan Desa Kayang 3, Desa kayang, 01000 Kangar. No. 39, Taman Tapah, Jalan Pahang 35000 Tapah, Perak. No 15, Jalan Limau Kasturi, 4/4A 40000 Shah Alam, Selangor. Lot 2646, Kg Simpang Tiga, Bt 6 3/4, 53100 Gombak, Selangor No. 36, Jln. Melati, Tmn. Mewah, 31000 Batu Gajah, Perak

icno 950918-02-5533 950912-05-5321 950212-11-5678 950402-04-5566 950606-03-5432

Table 1

6. Close students profile : Table. Activity 2: Add Fields. Method 1: Add field after the last field. 1. Click Design button at database toolbar windows Students Profile: Database, to display all the fields. 2. Click field below icno (refer Figure 2) 3. Type in email inside the Field Name and select text as the Data Type. Figure 2

4. Save the record. Method 2: Add new field in between the two fields 1. Click Design button at database toolbar windows Students Profile: Database, to display all the fields. 2. Click the address field ( the row will be highlight -refer Figure 3 )

23

ICTL for Secondary School - Microsoft Access Module

Figure 3

3. Click Insert at menu bar, then click rows. A new row will be added. (refer Figure 4) Figure 4

4. Put cursor in between field name and field address. 5. Type in sex inside the blank Field Name and select text as the Data Type. (refer Figure 5) Figure 5

6. Save students profile.

24

ICTL for Secondary School - Microsoft Access Module

7. Click

to close students profile : Table.

Activity 3: Edit Record. 1. Open Students Profile table. 2. To edit record, click inside the selected field. (sex and email) 3. Insert in all the record for sex field and email field. (refer Table 2) numbers 1 2 3 4 5 6 7 8 9 10

name Abdul Rahman b. Idris Johana bt. Muhammad Faridah bt Abdullah Rosnani bt. Sembok Hassan b. Wahid Alias b Mohamad Mohd Shahid b Mohd Som Noorul-Aini bt Ambak Noor Asiah bt Zakaria Fasitah bt. Mansor

sex a email d [email protected] male female [email protected] Female [email protected] Female [email protected] Male [email protected] Male [email protected] Male [email protected] Female [email protected] Female [email protected] female [email protected]

Table 2

4. Repeat step 2 above to add in 5 postcodes (see bold figure) inside the first five record. (Refer Table 3). Numbers 1 2 3 4 5

name Abdul Rahman b. Idris Johana bt. Muhammad Faridah bt Abdullah Rosnani bt. Sembok Hassan b. Wahid Choong Fook Keong

sex male femal e Femal e Femal e Male

address 2304, Rumah Rumah Tasek Gelugor, 13300 Pulau No 24,Pinang Jln 1, Taman Seri Sinar, 50600 Kulal Lumpur No 39, Jln Pinang Merah 11, 81000 Bandar Tengg, Johor No 1005, Bukit Datu, 21200 Kuala Terengganu, Terengganu No 101, Taman Rapat Jaya, 41000 Kelang, Selangor

Table 3

25

ICTL for Secondary School - Microsoft Access Module

5. Save students profile table.

Activity 4: Finding Record From Table 1. Click field name. Figure 6 Icon Find

2. Click icon Find (refer Figure 6) and dialog box Find and Replace will be display as below (Figure 7).

3. Type in Noor* inside box Find What to find all the name starting with Noor. 4. Click Find Next button. Name Noorul-Aini bt Ambak will be highlighted at name field (refer figure 8).

26 Figure 7

ICTL for Secondary School - Microsoft Access Module

Figure 8

5. Click Find Next button to find similar name with Noor. Name Noor Asiah bt Zakaria will be highlighted. 6. You can repeat step 3 to find other record. 7. Click File and Close to end this session. Exercise Module 3 1. Based on your exercise in module 2 (“Curriculum Data”). a. Add 3 new records b. Add 2 new fields c. Find records

27

ICTL for Secondary School - Microsoft Access Module

MODULE 4

CREATE FORM

Curriculum Development Center Ministry Of Education Malaysia

28

ICTL for Secondary School - Microsoft Access Module

Name of Module:

Create Form

Learning Outcomes:

Students should be able to create form and add record

Knowledge and Skills:

Students should be able to : a. Create form b. Add records

Module Summary: At the end of this module, a form will be create by students.

Activity Summary: Activities in this module involve: a. Create form b. Add record

29

ICTL for Secondary School - Microsoft Access Module

Activity 1: Create Form 1. Open Microsoft Access 2003 2. Click Open an Existing Database, then click file students profile and click OK. 3.In the database window, click Form and double click Create Form by using wizard.

1.Click Forms 2.Click Create Form by using wizard

4.Click double arrow to select Available Fields

1.Click here

30

ICTL for Secondary School - Microsoft Access Module

5. The selected Fields will be remove from left to right.Then click Next

1.Click Next

6. Click Next

1.Click Next

31

ICTL for Secondary School - Microsoft Access Module

7. Click Standard for style.Then click Next

1.Click Standard 2.Clik Next

8. Click Finish

1.Click Finish

32

ICTL for Secondary School - Microsoft Access Module

9. The form will display as below

Activity 2: Add Fields. 1. To add new record, click on icon New Record that can be seen at database toolbar or at the bottom of datasheet window.

1.New Record

33

ICTL for Secondary School - Microsoft Access Module

2. Add one more record as below. Numbers

Name

sex

address

11

Wan Mohd Sidqi Bin Wan Abd.Ghaffar

Male

No.9 Jln.Banggol 15300 Kota Bharu Kelantan

icno 95061703-6339

3. The form will display as below

4. Click File and Close to end this session.

Exercise Module 4 1.

Based on your exercise in module 4 (“Create Form”). a. Create new form. b. Add 3 new record.

34

Email [email protected]

ICTL for Secondary School - Microsoft Access Module

MODULE 5

CREATE REPORT

Curriculum Development Center Ministry Of Education Malaysia

Name of Module:

Create Report

Learning Outcomes:

Students should be able to create report and print record

Knowledge and Skills: Students should be able to : a. Create report b. Print report

35

ICTL for Secondary School - Microsoft Access Module

Module Summary: At the end of this module, form below will be create by students.

Activity Summary: Activities in this module involve: a. Create report b. Print report

36

ICTL for Secondary School - Microsoft Access Module

Activity 1: Create Report 1. Open Microsoft Access 2003 2. Click Open an Existing Database, then click file students profile and click OK. 3. In the database window, click Report and click create report by using wizard.

1.Click Reports 2.Click Create Report by using wizard

4. Click double arrow to select Available Fields

1.Click here

37

ICTL for Secondary School - Microsoft Access Module

5. The selected Fields will be remove from left to right.Then click Next

1.Click Next

6. Click Next

1.Click Next

38

ICTL for Secondary School - Microsoft Access Module

7. Click Next

1.Click Next

8. At the Layout click Tabular.Select Landscape at the Orientation. Then click Next.

1.Click Tabular 2.Click Landscape 3.Click Next

39

ICTL for Secondary School - Microsoft Access Module

9.Select the Casual style.Then click Next.

1.Click Next

10.Click Finish

1.Click Finish

40

ICTL for Secondary School - Microsoft Access Module

11. A dialog box report will display as below

Activity 2: Print Report. 1. To print the report,click File and print

41

ICTL for Secondary School - Microsoft Access Module

1.Click File 2.Click Print

2. A dialog box print will display as below. Then click OK.

1.Click OK

42

ICTL for Secondary School - Microsoft Access Module

Exercise Module 5 1. Based on your exercise in module 5 (“Create Report”). c. Create a new report d. Print the report

MODULE 6

CREATE QUERY

43

ICTL for Secondary School - Microsoft Access Module

Curriculum Development Center Ministry Of Education Malaysia

Name of Module:

Create Query

Learning Outcomes:

Students should be able to create query

Knowledge and Skills:

Students should be able to :

a. Create query b. Save query Module Summary: At the end of this module, the query below will be create by students.

44

ICTL for Secondary School - Microsoft Access Module

Activity Summary: Activities in this module involve: a. Create query b. Save query

Activity 1: Create Query. 1. Open Microsoft Access 2003 2. Click Open an Existing Database, then click file students profile and click OK. 3. In the database window, click Queries and click create query by using wizard.

45

ICTL for Secondary School - Microsoft Access Module

1.Click Queries 2.Click Create query by using wizard

4. Click double arrow to select Available Fields

1.Click here

5. The selected Fields will be remove from left to right.Then click Next

46

ICTL for Secondary School - Microsoft Access Module

1.Click Next

6. Click Finish

1.Click Finish

47

ICTL for Secondary School - Microsoft Access Module

7. A dialog box will display as below.Click icon design view.

1.Click icon view

8. A dialog box will display as below.

48

ICTL for Secondary School - Microsoft Access Module

9. At the row Criteria, type-in male and press Enter 10. Click icon Run

1.Type-in male

2.Icon Run

11. A dialog box for male students will be display

49

ICTL for Secondary School - Microsoft Access Module

Activity 2: Save Query. 1. Click File and Save As

2. Save file as : male students profile Query

50

ICTL for Secondary School - Microsoft Access Module

3. The file will be save as below

4. Click File and Close to end this session.

Exercise Module 6 1. Based on your exercise in module 6 (“Create Query”). a.Create new report for female students b.Save the file as “ female students “

51

ICTL for Secondary School - Microsoft Access Module

MODULE 7 GETTING EXTERNAL DATA

Curriculum Development Center Ministry Of Education Malaysia

52

ICTL for Secondary School - Microsoft Access Module

Name of Module:

Getting External Data

Learning Outcomes:

Students should be able to get external data

Knowledge and Skills:

Students should be able to : a.Create file b.Import spreadsheet file

Module Summary: At the end of this module, the students will be able to get data and create a table as shown below

Activity Summary: Activities in this module involve: a.Import file b.Create table

53

ICTL for Secondary School - Microsoft Access Module

Activity 1: Import Spreadsheet Files 1. Open Microsoft Access 2003 2. Create new database file 3.Click file 4.Click Get External Data 5.Click Import

1.Click File 2.Get External Data 3.Import

6.At the Files of type,click pull down and select Microsoft Excel 7.Click any spreadsheet files as shown in example below ( Test 2007 ) 8.Click Import

1.File “Test 2007 “ 2.Import

54

ICTL for Secondary School - Microsoft Access Module

9.Click Next

1.Next

10.Put a tick on the box at the First Row Contain Column Headings 11.Click Next

1.Next

55

ICTL for Secondary School - Microsoft Access Module

12.Click OK

13.Click In a New Table 14.Click Next

1.Click In a New Table 2.Click Next

56

ICTL for Secondary School - Microsoft Access Module

15.Click Next

1.Next

16.Tic the

k at

Choose my own primary key and select No 17.Click Next

1.Choose my own primary key 2.Select No. 3.Next

57

ICTL for Secondary School - Microsoft Access Module

16.Click Finish

1.Finish

16.Now you had finished creating one table named “ Register “ 17.Double click at Register

1.Click Register

58

ICTL for Secondary School - Microsoft Access Module

18.The next window will be appear 19.Click File and close

1.Click File and Close

20.Click close to end this session

1.Click close

Exercise Module 7 1. Based on your exercise in module 7 (“Getting External Data”). a.Get any data from spreadsheet file b.Save the file

59

ICTL for Secondary School - Microsoft Access Module

60