Microsoft Access
♦ A database is a collection of data organised in a manner that allows access, retrieval, and use of that data.
♦ A Database Management System (DBMS) allows users to create a database; add, change, and delete data in the database; retrieve data from the database; and create forms and reports using the data in the database. Microsoft Access is a DBMS.
Access Screen
Fig. 1
Create a New Database
♦ Before you do any works using Access, the first thing you have to do is to create a new database file or open an existing database file.
♦ To create a new database file
i. Click the Blank Database (空白資料庫)
ii. Choose the path and type the filename, e.g. student.
♦ After you create a database file, a database window will be displayed as show in fig. 2.
fig. 2
♦ The Database window is a special window that allows you to access easily and rapidly a variety of objects such as table (資料表), query (查詢), form (表單) and report (報表).
Table
♦ A database consists of a collection of tables. To create a table, you should first create the table structure, i.e. define the fields of each record, and then add records to the table.
♦ The definition of the fields include stating the following details of each field, i. field name – must be unique.
ii. data type – type of the data that the field will contain, e.g. Text, Number, Date/Time, etc.
iii. description – optional, for detail description.
♦ To create a new table
i. Click New (新增) button on the right of the window.
ii. Choose Design View (設計檢視)
♦ A window will be displayed as shown in fig. 3.
fig. 3
♦ Then you can define each field in the upper pane. Note that after you have chosen the field data type, you can define the more specific properties of the field in the lower pane. These properties include
i. Field size(欄位大小欄位大小欄位大小) It limits the largest number of characters stored in the field. 欄位大小
ii. Format(格式格式格式格式). The format of data displayed. It does not affect the actual value. Check out the different choices for Number and Date/Time fields.
iii. Input Mask(輸入遮罩輸入遮罩輸入遮罩輸入遮罩). It pre-sets delimiters, e.g. ‘/’, ‘(‘, ‘)’, ‘-‘, etc; and controls the input values using special control characters, e.g. 0, 9, L, A, etc. E.g. for a field of size 4, the input mask L(000) controls the input value so that the first character must be a letter and the rest must be number with the parenthesis pre-set. Then “P(123)” is a valid input.
iv. Caption(標題標題標題標題). It is used to label the field instead of the field name.
v. Default Value(預設值預設值預設值預設值). This value will be entered automatically when a new record is added.
vi. Validation Rule(驗證規則驗證規則驗證規則驗證規則). It set up rules such “>1000”, “Is Null” to validate the input data.
vii. Validation Text(驗證文字驗證文字驗證文字驗證文字). It will be displayed if the above Validation Rule is violated.
♦ After you have define the table structure,
♦ To start enter data, click the datasheet button on the top left. The table will be displayed in datasheet view (資料工作表檢視) as shown in fig. 5, in which table is represented as collection of rows and columns called a datasheet(資料工作表).
fig. 4
fig. 5
♦ Note that in datasheet view,
i. you can press the tab key to complete the entry for a field (pressing the tab key at the last field in the last record will automatically add a new record),
ii. the asterisk “*” sign indicates the end-of-file mark.
iii. Records are automatically saved after each data entry.
Form
♦ In addition to datasheet view, you can use Form view so that you can view a single record at a time.
♦ Advantages of using Forms.
i. If there are many fields and it is not possible to display all of them on a single row.
ii. The data entry interface can be customised so that it becomes more user-friendly.
♦ There are 3 basic views for each database table: design view, datasheet view and browse view.
♦ The fastest way to create a form,
ii. choose the Autoform (自動產生表單) option under the New Object button on the top right.
fig. 6
♦ The data table will be then viewed in the Form view as follows.
fig. 7
♦ Click the save button and type the form name to save the form design then.
♦ After you have created the Form, you can open the form for data input by double-clicking the form icon under the Form tab.
♦ If you want to change the design of the Form, click the Design view button. The form is then displayed in Design view with Toolbox toolbar to help you to change the Form design as shown in fig. 7.
Report
♦ You can create a customised report as shown in fig. 8 using the Report functions of Microsoft Access.
♦ Note that a report usually contains 3 parts:
i. Page header. It appears at the top of each page containing a custom title.
ii. Detail lines. They are the lines printed for each record containing only those fields you specify.
iii. Page footer. It appears at the bottom of each page containing the summary information.
fig. 8
Student Information
Student ID Name Telephone
A(123) Peter Pan 23423423 B(456) Mary Chan 23232323
M
Total: 23 students
fig. 9
♦ To create a report
i. select the table from which the data come in the Table tab.
ii. select AutoReport (自動產生報表)option under the New Object button, Or select Report (報表) option and then select Report Wizard to customize the report design.
♦ You can modify a report layout by clicking the design view after you choose the report in the Report tab.
♦ After you have created the report you can preview by double-clicking the report icon in the Report tab.
♦ After you have created the report you can print by the following steps, i. Click the report icon in the Report tab.
Page header
Detail lines
Page footer
Query
♦ One of the most powerful tools in a DBMS is queries, which allow you to ask questions on a database and find out answers or information from the database.
♦ For example,
i. Finding the Chinese marks of all girls in class S5F.
ii. Finding the names of the students who have overdue book from library and the titles of the books.
♦ Steps to create a new query,
i. Select a table from the table tab.
ii. Click the query button under the New Object button arrow.
♦ A Select Query (選取查詢) window will be then displayed in which you create a query.
fig. 10
♦ General Steps for Creating a Query
i. Select the table(s) for which you are creating a query in the database.
ii. If more than one tables are selected, set join between tables.
iii. Select Field(s) from the Table(s).
♦ To add a table to a query
i. Right-click any open area in the upper pane.
ii. Choose Show table… (顯示資料表…).
iii. Choose the table that you want to add and click Add.
♦ Normally the join will automatically set after you add a new table. If it does not, drag the field from one table onto the related field on another table.
♦ To select fields, double-click the fields that you want to select on the upper pane.
♦ To set filter criteria, type them the criteria (準則) row. Note that all criteria on the same row have an
‘AND’ relation, criteria on different rows have an ‘OR’ relation.
♦ To set sort key, choose the sorting order on the sort (排序) row to set a field to be the sort key. The leftmost sort key will be the primary one, and so on.
♦ To view the query result, i.e. run the query, just click on the run button, the one with the ‘!’ sign.
♦ You should save the query if you want to use the same query in the future. A query file is actually a text file written in a Structured Query Language (SQL). To view SQL codes, choose SQL view under the View button arrow on the top left or View menu.
♦ You can also create a form or a report from the query result as follows, i. Select the query in the query tab.
ii. Click the corresponding button under the New Object button arrow.
