Lesson 02:

1. Lesson 02: Creating Database

2. At the end of this lesson, students should be able to: • Save database file • Create table by using wizard • Differentiate field, record and table • Define primary key • Insert data in the table • Save table • Find record from table

3. Field 1 (numbers) Field 1 (name) Field 1 (address) Field 1 (icno) Creating Table For example, students profiles table as shown below. (Figure 1) record 2 Figure 1

4. Steps Creating Table • Open MS Access 2003 software • Click File, click Open, find students profiles database file then click Openbutton. (refer Figure 2) Figure 2

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

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

7. At Field Namecolumn, type in numbers and at Data typecolumn, change value Text to Number. (refer Figure 6) Click this button to view the list of data type, then click number Figure 6

8. There are 10 types of data as stated in the table below:

11. There are 10 types of data as stated in the table below:

13. 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”. Place the cursor at address Figure 7 Change Field Size value to 100 Figure 8

14. Click numbers at field name column, click at Edit menu bar, then click • Primary Key or icon Primary Key . Shown • at Figure 9. Primary key icon close icon Figure 9

15. Click Close icon as shown at figure 8 to • close the creating table session. Then • dialog box in figure 9 displayed. Figure 10 • Click Yes. Dialog box Save As in figure 10 • displayed Key in table name here Figure 11

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

17. Inserting Data in the Table • Click at Students Profile table then click open at database window toolbar. • (Figure 13) Figure 13

18. Datasheet view Students Profile : Table as shown in Figure 14 displayed Figure 14 • Key in data at the students profile datasheet

19. 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. Figure 15

20. Insert all data in the table by repeating the same process above. As a result, students will get the table below Figure 16 • Click icon save to saving file. Students Profile table now contained five records • Click icon close to close the inserting data session

21. Add Records • Open Microsoft Access 2003 • Click Open an Existing Database, then click file students profile and click OK. • In the database window, double click students profile in the Tables pane to open the table in Datasheet view, as shown in figure 17.

22. 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 17). Icon New record Figure 17

23. Add five more record as below. (refer table 1) Table 1 • Save record. • Close students profile : Table.

24. Add Fields Method 1: Add field after the last field. • Click Design button at database toolbar windows Students Profile: Database, to display all the fields. • Click field below icno (refer Figure 18)

25. Type in email inside the Field Name and select text as the Data Type. Figure 18 • Save the record

26. Add Fields Method 1: Add field after the last field. • Click Design button at database toolbar windows Students Profile: Database, to display all the fields. • Click field below icno (refer Figure 19)

27. Figure 19 • Save the record

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

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

30. Put cursor in between field name and field address. • Type in sex inside the blank Field Name and select text as the Data Type.(refer Figure 20) • Save students profile. • Click to close students profile : Table.

31. Edit Record. 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)

32. Table 2

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

34. Finding Record From Table • Click field name as shown in Figure 21. Icon Find Figure 21

35. Click icon Find (refer Figure 21) and dialog box Find and Replace will be display as below (Figure 22). Figure 22

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

37. 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.