Introduction to MS Access

Overview

Time: 45 min

Objectives

Introduction to MS Access application

Manipulating data using MS Access

Microsoft Access is a database management tool which is simple to use. It provides an easy, user friendly user interface to create, edit and manage databases.

In a relational database, data is generally stored in the form of tables. Each table here represents an entity set while the columns of the table represent the entity attributes. Each row in the table represents a single entity inside the entity set.

Note: Every UIC student should have free access to Microsoft office product. Once you open MS Access in your device if prompted to sign in, use your UIC email address and password to sign in.

CREATING DATABASE AND TABLES:

Open MS-Access
Select Black database option
Provide a name for the database (e.g. Sample_Database) and then click Create

access_1

Once done the first table is automatically generated
Now select Create from the menu bar and select Table twice to create two additional tables (entity sets)

access_2

Now rename the 3 tables as: Customer, Item, Purchase. To rename a table, select the table at the top and not on the left pane and then press Ctrl + S

access_3

To create columns (attributes) for a table, select the particular table on top and switch to design view by clicking on View (or) Down Arrow on View → Design View

access_4

In Design View enter the column names (or) attributes for each table as shown in the image below and then save the table before moving to the next table

access_5

DATA TYPES – FIELD PROPERTIES & FORMATTING

MS-ACCESS provided a list of pre-defined data types rom which we need to assign a single data type for each attribute

access_6

Set the Data Type for each attribute by selecting it from the corresponding drop-down box

access_7

The Field Properties box can be located at the bottom of the page when a particular Field or its Data Type is selected in the Design View

access_8

Enter Field Size as 30 for First Name and Last Name and 100 for Address in Customer table

access_9

For Phone Number in Customer table click on Input Mask first and then click on three dots at the end of property

access_10

Now Select Phone Number option and click Next.
In the next dialog box, replace the 999 with 000 (9 → optional to enter ; 0 → mandatory to enter) and click Next
In the final dialog box, choose the preferred format to display the number and click Finish

access_11

To set the primary key, select the attribute in Design View and click on the particular attribute and click the primary key icon. To make multiple attributes as primary key, select all the attributes by clicking on the grey box to its left while holding down Ctrl key and click the primary key icon

Set the primary keys as below:

Customer - ID

Item - ID

Purchase - Customer ID, Item ID, Date

access_12

Now Enter a few sample data in all the three tables

RELATIONSHIPS

Select Database Tools → Relationships to open the relationships window

access_13

Now drag all three tables into the relationship window

access_14

Now click on Customer.ID column and drag & drop it on Purchase.Customer ID column. Check the “Enforce Referential Integrity” checkbox and click Create

access_15

Referential Integrity – Helps prevent actions which might affect the relationship. For e.g. Changing only the data type of ID column in Customer table to Text but not the Customer ID column in Purchase table.