SEHH2240 Database Systems Tutorial 2

Tutorial 2

Microsoft Access 2016

Create Database
Learning Outcomes
• Creating Database File
• Adding/Deleting records of the database
• Execute SQL

Access
Access is a DBMS that functions in the Windows environment and allows you to create and
process data in a database. For example, an Access database called Staff may look like the
following,

Fields

ID Name Department Salary

92354789 Alice Chan IT 32000
Records 96258971 Bob Li CS 24000
95874125 Cathy Man ENG 31500

Access uses similar terminology as in most database system,

Field Each unit of information is called a field. In the example above, there is one field for
ID, one field for Name, one field for Department and one field for Salary. In this
case, the database table (or relation) contains 4 fields for each data item.
Record All the information about a single item in a database table is called a record. For
example, in the example above, each row is a record.
Table A collection of similar records that are used together is called a table or relation. In
the example above, the 3 records all belong to the same table, called the Staff table.

Access allows you to create a database and its tables, design a data entry form, and generate reports
or graphs for the data. A key is a field that enables you to identify or search for a record in a table.
For example, your student ID is a key in the student record table.

Page 1 of 13
SEHH2240 Database Systems Tutorial 2

Creating a Database

To create a database file,

• Click the Access icon and you will find the screen similar to the one as above.
• Choose Blank database among the available templates as shown.
• In the pop-up window, type in the new database file name (extension ending .accdb normally)
in the File Name box.
• Press the folder button there to store the database file in the place you like in your computer
(e.g. Desktop).
• Press the Create button.
A database window will be created.

Opening a Database
• Choose Open Other Files in the screen previously shown.
• Select the appropriate drive or browse the appropriate folder for the file you wish to open.
• Select the desired database file and its file name will appear in the File name box.
• Press the Open button.

Creating Tables
A database may consist of many tables. You can create your own table which is specially designed
for your database.

Page 2 of 13
SEHH2240 Database Systems Tutorial 2

Create table under All Access Objects

On the left of the screen shown previously,
• In the All Access Objects frame, click Table1 once under Tables.
(Note: if Table1 is not found, go to the Create Tab at the top, and click Table to create a new one.
More details can be found in the Create table in Create tab section)

• Click Home-View group, and then choose Design View as shown below.

• Enter appropriate Table Name in the blue highlighted area below.

• Click OK button.

• At the Primary Key field, enter appropriate Field Name, Data Type, and Description.

Primary Key field

• Enter appropriate Field Name, Data Type, and Description for each other field, if any.
• Click Save button in Quick Access Toolbar to save it.

Page 3 of 13
SEHH2240 Database Systems Tutorial 2

Quick Access Toolbar

Save button

Create table in Create tab

• Click Create tab, then click Table Design in Tables group.
Create tab
Table Design

• Enter appropriate Filed Name, Data Type, and Description for each field to store, if any.
• Put Primary Key to an appropriate field.
• Click Save button in Quick Access Toolbar to save it.
• Enter appropriate Table Name.
• Click OK button.

A Table Design window is displayed which allows you to modify or add fields in your new table.
Click Save button in Quick Access Toolbar to save your work. When finished, you may close it.

Modifying the Existing Table Using Design View

Make sure you can see All Access Objects in the Navigation Pane, select (highlight) the name of
the table you want to modify and click the mouse’s right button then choose Design View. The
Table Design window will be displayed for you to make changes.

(Note: DO NOT double-click on the name of the table. Double-clicking will display the
Datasheet View (for data entry), see Adding Records Using Datasheet View section.)

Page 4 of 13
SEHH2240 Database Systems Tutorial 2

e.g. Highlight Table 1

Right-click, choose
Design View

Modifying a Field
• In the Table Design window, you can modify your fields. Each field has a name and a data
type, with optional description.

Data Types available in Access

When designing a database, you should decide the tables and fields to be used as well as the data
type for each field. Access has a number of data types, from which you can choose, as described
below,
Data Type Description Example
Short Text To store letters, numbers, and special Introduction to Access
characters (*&^%$#@!); can be up to 255
characters longs.
Long Text To store a textual data type up to about 1 Any kind of textual
gigabyte (GB), but controls to display a message that is often quite
long text are limited to the first 64,000 long can be stored here.
characters. Use this data type for notes,
comments, or long fields (greater than 255
characters)
Number Contains different field sizes. The
following enlisted a few popularly used
ones.

Byte: To store small numbers with value

from 0 to 255 (no fraction). E.g. 5

Integer: To store integers from -32768 to

32767. E.g. 2018

Long Integer: To store integers from -

2147483648 to 2147483647. E.g. 1234567

Single: To store a number with 7

significant digits. E.g. 25.32

Double: To store a number with 15

significant digits. E.g. 3.141592653589
Date/Time To store date and/or time values (different 6/19/2007 10:21:23 AM
formats).
Currency To store monetary values, up to 15 digits $5.00
Page 5 of 13
SEHH2240 Database Systems Tutorial 2

on the left side of the decimal point and 4

digits to the right side; displayed in forms
and reports in the currency format.
AutoNumber A number automatically incremented (or 321
randomly generated) by Access whenever a
new record is added to a table; it is most
often used as a primary key, maintained
within Access and cannot be changed by
user.
Yes/No A data type when one of two possible Yes
answers is needed, such as yes/no,
true/false or On/Off. There are some
restrictions imposed on this data type, so
use it carefully.

Additional settings for each field can be made at the bottom, Field Properties. Access allows you
to define the following various properties for a field:

Field Property Description

Field Size Sets the maximum length for data entered in Text and Number fields.

Format Determines how Access displays and prints data, such as whether to
display Date fields in words or just numbers.
Decimal Places Determines the number of decimal places displayed and printed in
Number and Currency fields.
Input Mask Determines how a field looks when the user is entering data. For
example, one can create an input mask for a field for entering dates
that automatically display two slashes ( / / ) or a field for entering
phone numbers that displays dash ( - ).
Caption Supplies a label for Access to use in forms and reports, instead of the
field name.
Default Value Assigns a value that Access inserts into the field in each new record
you add to the table. You can change the value during data entry.
Validation Rule Checks the data entered in the field against set criteria to prevent entry
of invalid data.
Validation Text Defines the contents of the message that appears when a user enters
data that does not match the criteria specified in the Validation Rule
property.
Required Indicates that some data must be entered in the field before the record
can be saved (i.e., the value in the field cannot be Null). By default,
Access fields are not required.
Allow Zero Determines whether a zero-length string is a valid entry. To enter a
Length zero-length string, type “”. By default, zero-length strings are not
valid entries.
Indexed Creates an index on the field to speed up search of the field.

Field names can be in any string, but it is a good design to have spaces left out in the fields for
readability.
• The default data type is Short Text. Use the drop-down box to select other types. Select the
appropriate field size for a text type in Field Properties.

Page 6 of 13
SEHH2240 Database Systems Tutorial 2

• Define a field to be a primary key by clicking that field and then hitting the primary key
button at the Tools group in the Design tab. There can only be one primary key per table.

Primary key Design tab

• When you have done, click Save button in the Quick Access Toolbar to save it.
• Make sure that there is a primary key before a table can be saved.

Moving / Copying a Field

• Highlight the field you want to move/copy (click on the left most box of the field).
• Click the mouse’s right button, select Cut or Copy from the menu.
• Click on the row you want to paste your field.
• Click the mouse’s right button, select Paste button.

Inserting a Field
• Position the cursor to the field where you want to insert another field.
• Click the mouse’s right button, select Insert Rows button.
• Add the relevant information.

Deleting a Field
• Highlight the field you want to delete (click on the leftmost box of the field).
• Click the mouse’s right button, select Delete Rows button.

Adding Records Using Datasheet View

Adding Records
• At the All Access Objects in the Navigation Pane on the left, select (highlight) the name of the
table you want to add records.
• Double-click the mouse’s left button.
• The Datasheet View window is displayed with the caption names of the fields as the column
headings. Each row represents a single record.
• If there are no records in the table, then you can just enter the data field by field.
• You can use <Tab> or <Enter> to move to the next field or record.
• AutoNumber field cannot be edited. Access will assign numbers automatically. Don’t worry
even if the numbers are not consecutively listed after the insertion and/ or deletion of records.
Page 7 of 13
SEHH2240 Database Systems Tutorial 2

Resizing a Column of a Datasheet

• Position the cursor on the right boundary of the column heading with which you want to adjust
its width. The cursor will change to a short vertical line called the I-beam. Then you can either:
• Drag the column boundary to left/ right to resize the column.
• Double-click while the cursor is changed into the I-beam. This will cause Access to adjust
the width of the column automatically to fit the data.

Saving and Closing the Datasheet

• Click Save button in Quick Access Toolbar.
• Select Close button for the table you want to close.

Deleting Records Using Datasheet View

• At the All Access Objects in the Navigation Pane, select (highlight) the name of the table you
want to add records.
• Double-click the mouse’s left button.
• The Datasheet View window is displayed with the caption names of the fields as the column
headings. Each row represents a single record.
• Highlight the field you want to delete (click on the left most box of the field).
• Click the mouse’s right button, select Cut or Delete Record from the menu.
Caution: There is no way to undelete records. Make sure that you are deleting the right records and
answer Yes on the alert message box.

Updating Records Using Datasheet View

• At the All Access Objects in the Navigation Pane, select (highlight) the name of the table you
want to add records.
• Double-click the mouse’s left button.
• The Datasheet View window is displayed with the caption names of the fields as the column
headings. Each row represents a single record.
• Position the cursor to the field of the record to be changed and click.
• The cursor will change from an arrow to an I-beam.
• You may then update the cell.

Caution: You can only undo the change in one previous step. Make sure the changes you make are
correct. There is no alert box this time.

Page 8 of 13
SEHH2240 Database Systems Tutorial 2

Sorting Records Using Datasheet View

Records are always inserted at the end of a table. You can sort the records according to
any field, either in ascending order or in descending order.
• Open the table you want to sort in Datasheet View.
• Click on the field by which you want to sort.
• Click the Ascending or Descending button at the Sort & Filter group in the Home
tab.
Ascending button
Home tab

• The records are then sorted according to the selected field.

Modifying an Existing Table Using Datasheet View

• At the All Access Objects in the Navigation Pane, select (highlight) the name of the table you
want to add records.
• Double-click the mouse’s left button.
• The Datasheet View window is displayed with the caption names of the fields as the column
headings. Each row represents a single record.
• Highlight the field you want to insert field (click on the top most box of the field).
• Click the mouse’s right button, select Insert Field from the menu. A field will be added to the
left.
• Highlight the field you want to rename (click on the top most box of the field).
• Click the mouse’s right button, select Rename Field from the menu. Type the new field
name.
• Highlight the field you want to delete (click on the top most box of the field).
• Click the mouse’s right button, select Delete Field from the menu.

Caution: You cannot undo the deletion of a column/field and the change of name. You need to
answer Yes on the alert message box to confirm a deletion.

Page 9 of 13
SEHH2240 Database Systems Tutorial 2

Exercise One
1. Create a new database called Lab2_yourname.accdb. If your name is Chan Tai Man, then save
the new database as Lab2_ChanTaiMan.accdb.

2. Enter the following fields and descriptions to the table using Table Design in Create tab:
Name Type Description Field Size
StaffID AutoNumber Staff ID number Long Integer
FirstName Short Text First name of Staff 50
LastName Short Text Last name of Staff 50
Married Yes/No Married Y/N Yes/No (format)
Date of Birth Date/Time Birthday (dd-mmm-yy) Medium date (format)
Address1 Short Text Address line 1 35
Address2 Short Text Address line 2 35
District Short Text District 15
Department Short Text Staff department 2
Salary Currency Monthly salary Standard

3. Save the table as Staff and close the Table Design window.

4. Open the Staff table in the Design View. Insert the following field before Married:
Name Type Field Size Description
Gender Short Text 1 Gender F/M

5. Save the table as Staff and close the Table Design window.

6. Open the Datasheet for the table Staff and insert the following records:
Name Record 1 Record 2 Record 3 Record 4
StaffID
FirstName Ka Wah Mei Lee Chun Kit Charles
LastName Chan Wong Chan Li
Gender M F M M
Married Y N Y N
Birthday 03-Sep-89 12-Dec-92 02-Jan-88 03-Mar-98
Address1 6A, Blk 2 Rm 711, Blk 21 8/f, Tai Wah Bldg 7B, Blk 4
Address2 23, Wing On St Wah Fu Estate 11, Water Road 44, Ming Tak St
District Mong Kok Aberdeen Sheung Wan Shatin
Salary 22000 45000 33000 11000
Department IT EN MT IT

7. Close the Datasheet View, save the records and close everything.

Page 10 of 13
SEHH2240 Database Systems Tutorial 2

How to Edit and Execute a SQL Statement

A query can be set up to question about data stored in a database, e.g., to return the records for
salary over 30000. You may do this in Excel by sorting on the salary column, but if you need to
return information from multiple tables, you need the database querying function involving
multiple tables. In Access, you have to specify the conditions and restrictions imposed on data to
limit the information presented. These are called criteria.
• In the database window, choose the Create tab, Query Design.

Query Design
Create

• If you select the Design View, you will see the Show Table window. You can add the
appropriate tables you want as you have done in the Relationships window. The links between
the tables are shown automatically as well (information when you define the relationships).
• You will see the Query Design window at the bottom and you can add the fields and table
names in the window, or else you can drag the fields from the tables directly to the cells in the
bottom window.
• Highlight a table in the Show Table dialog box, then click Add in the Show Table dialog box

• Then, click Close in the Show Table.

• Specify any criteria and sorting order you want.
• When you are done, select File tab, Save to save the query under a certain name.
• Double-click the query to return the result of the query.
• Or in Design View click File tab, Run.

Run

Note: You can enter one statement at a time. Delete the old statement before entering a new one.
Page 11 of 13
SEHH2240 Database Systems Tutorial 2

• To enter the SQL in SQL view, right-click on the Query tab

Mouse Right-click

• Choose SQL view

• Then, you can enter a statement in the SQL view. Note that only one SQL statement can be
executed at the SQL view and the SQL statement must end with a semi-colon (;).

• After entering a SQL statement in the SQL view, click Run.

Page 12 of 13
SEHH2240 Database Systems Tutorial 2

Exercise Two – Making changes to a database table

1. Open the database in Exercise 1 Lab2_yourname.accdb.
2. Create a new query on the Staff table. The query should show StaffID, FirstName, LastName
and sort the Staff in ascending order of first names. Save the query as “Staff - sorted by first
names”.
3. Run the query to see the results.
4. Run the SQL statement below to modify table Staff by adding field HKID.
Alter table Staff add column HKID char(60)

5. Now run the query “Staff - sorted by first names” (the query in step 2) again, do you notice any
difference. What database feature is reflected in the last few steps? Hint: adding a field is a
structural change.
6. To enter a StaffID value like 02802828A (as in HK PolyU), we need to make some changes.
Modify table Staff so that the field StaffID is changed from having data type AutoNumber to
Text.
7. Now run the query “Staff - sorted by first names” (the query in step 2) again, do you notice any
difference. What database feature is reflected in the last few steps? Hint: we have just changed
the data characteristic of a field in the step before this.
8. Close all tables and forms.

Page 13 of 13