MS Access - Overview

Microsoft Access is a Database Management System (DBMS) from Microsoft Microsoft Access is just
one part of Microsofts overall data management product strategy.
 It stores data in its own format based on the Access Jet Database Engine.
 Like relational databases, Microsoft Access also allows you to link related information easily. For
example, customer and order data. It can also import or link directly to data stored in other
applications and databases.
 Access can also understand and use a wide variety of other data formats, including many other
database file structures.
 You can export data to and import data from word processing files, spreadsheets, or database files
directly.
Microsoft Access stores information which is called a database. To use MS Access, you will need to
follow these four steps −
 Database Creation − Create your Microsoft Access database and specify what kind of data you
will be storing.
 Data Input − After your database is created, the data of every business day can be entered into
the Access database.
 Query − This is a fancy term to basically describe the process of retrieving information from the
database.
 Report (optional) − Information from the database is organized in a nice presentation that can be
printed in an Access Report.

MS Access - Objects

MS Access uses objects" to help the user list and organize information, as well as prepare specially
designed reports. When you create a database, Access offers you Tables, Queries, Forms, Reports,
Macros, and Modules. Databases in Access are composed of many objects but the following are the major
objects −
 Tables
 Queries
 Forms
 Reports
Together, these objects allow you to enter, store, analyze, and compile your data. Here is a summary of
the major objects in an Access database;
Table
Table is an object that is used to define and store data. When you create a new table, Access asks you to
define fields which is also known as column headings.
 Each field must have a unique name, and data type.
 Tables contain fields or columns that store different kinds of data, such as a name or an address,
and records or rows that collect all the information about a particular instance of the subject, such
as all the information about a customer or employee etc.
 You can define a primary key, one or more fields that have a unique value for each record, and
one or more indexes on each table to help retrieve your data more quickly.
Query
An object that provides a custom view of data from one or more tables. Queries are a way of searching for
and compiling data from one or more tables.
 Running a query is like asking a detailed question of your database.
 When you build a query in Access, you are defining specific search conditions to find exactly the
data you want.
 In Access, you can use the graphical query by example facility or you can write Structured Query
Language (SQL) statements to create your queries.
 You can define queries to Select, Update, Insert, or Delete data.
 You can also define queries that create new tables from data in one or more existing tables.
Form
Form is an object in a desktop database designed primarily for data input or display or for control of
application execution. You use forms to customize the presentation of data that your application extracts
from queries or tables.
 Forms are used for entering, modifying, and viewing records.
 The reason forms are used so often is that they are an easy way to guide people toward entering
data correctly.
 When you enter information into a form in Access, the data goes exactly where the database
designer wants it to go in one or more related tables.
Report
Report is an object in desktop databases designed for formatting, calculating, printing, and summarizing
selected data.
 You can view a report on your screen before you print it.
 If forms are for input purposes, then reports are for output.
  Anything you plan to print deserves a report, whether it is a list of names and addresses, a
financial summary for a period, or a set of mailing labels.
 Reports are useful because they allow you to present components of your database in an easy-to-
read format.
 You can even customize a report's appearance to make it visually appealing.
 Access offers you the ability to create a report from any table or query.

MS Access - Create Database

To create a database from a template, we first need to open MS Access and you will see the following
screen in which different Access database templates are displayed.

To view the all the possible databases, you can scroll down or you can also use the search box.
Let us enter project in the search box and press Enter. You will see the database templates related to
project management.
Select the first template. You will see more information related to this template.

After selecting a template related to your requirements, enter a name in the File name field and you can
also specify another location for your file if you want.
Now, press the Create option. Access will download that database template and open a new blank
database as shown in the following screenshot.

Now, click the Navigation pane on the left side and you will see all the other objects that come with this
database.
Click the Projects Navigation and select the Object Type in the menu.

You will now see all the objects types tables, queries, etc.
Create Blank Database
Sometimes database requirements can be so specific that using and modifying the existing templates
requires more work than just creating a database from scratch. In such case, we make use of blank
database.
Step 1 − Let us now start by opening MS Access.
Step 2 − Select Blank desktop database. Enter the name and click the Create button.

Step 3 − Access will create a new blank database and will open up the table which is also completely
blank.
 MS Access - Data Types
Every field in a table has properties and these properties define the field's characteristics and behavior.
The most important property for a field is its data type. A field's data type determines what kind of data it
can store. MS Access supports different types of data, each with a specific purpose.
 The data type determines the kind of the values that users can store in any given field.
 Each field can store data consisting of only a single data type.
Here are some of the most common data types you will find used in a typical Microsoft Access database.

Type of Data Description Size

Text or combinations of text and numbers,

Short Text including numbers that do not require Up to 255 characters.
calculating (e.g. phone numbers).

Lengthy text or combinations of text and

Long Text Up to 63, 999 characters.
numbers.

Numeric data used in mathematical

Number 1, 2, 4, or 8 bytes (16 bytes if set to Rep
calculations.

Date and time values for the years 100

Date/Time 8 bytes
through 9999.

Currency values and numeric data used in

Currency mathematical calculations involving data 8 bytes
with one to four decimal places.

A unique sequential (incremented by 1)

number or random number assigned by
AutoNumber 4 bytes (16 bytes if set to Replication ID
Microsoft Access whenever a new record is
added to a table.

Yes and No values and fields that contain

Yes/No only one of two values (Yes/No, True/False, 1 bit.
or On/Off).

When you create a database, you store your data in tables. Because other database objects depend so
heavily on tables, you should always start your design of a database by creating all of its tables and then
creating any other object. Before you create tables, carefully consider your requirements and determine
all the tables that you need.
Let us try and create the first table that will store the basic contact information concerning the employees
as shown in the following table −

Field Name Data Type

EmployeelD AutoNumber

FirstName Short Text

LastName Short Text

Address1 Short Text

Address2 Short Text

City Short Text

State Short Text

Zip Short Text

Phone Short Text

Phone Type Short Text

Let us now have short text as the data type for all these fields and open a blank database in Access.
This is where we left things off. We created the database and then Access automatically opened up this
table-one-datasheet view for a table.
Let us now go to the Field tab and you will see that it is also automatically created. The ID which is an
AutoNumber field acts as our unique identifier and is the primary key for this table.
The ID field has already been created and we now want to rename it to suit our conditions. This is an
Employee table and this will be the unique identifier for our employees.

Click on the Name & Caption option in the Ribbon and you will see the following dialog box.

Change the name of this field to EmployeeID to make it more specific to this table. Enter the other
optional information if you want and click Ok.
We now have our employee ID field with the caption Employee ID. This is automatically set to auto
number so we don't really need to change the data type.
Let us now add some more fields by clicking on click to add.
Choose Short Text as the field. When you choose short text, Access will then highlight that field name
automatically and all you have to do is type the field name.

Type FirstName as the field name. Similarly, add all the required fields as shown in the following
screenshot.

Once all the fields are added, click the Save icon.
You will now see the Save As dialog box, where you can enter a table name for the table.

Enter the name of your table in the Table Name field. Here the tbl prefix stands for table. Let us click Ok
and you will see your table in the navigation pane.

Table Design View

As we have already created one table using Datasheet View. We will now create another table using
the Table Design View. We will be creating the following fields in this table. These tables will store
some of the information for various book projects.

Field Name Data Type

Project ID AutoNumber
 ProjectName Short Text

ManagingEditor Short Text

Author Short Text

PStatus Short Text

Contracts Attachment

ProjectStart Date/Time

ProjectEnd Date/Time

Budget Currency

ProjectNotes Long Text

Let us now go to the Create tab.

In the tables group, click on Table and you can see this looks completely different from the Datasheet
View. In this view, you can see the field name and data type side by side.
We now need to make ProjectID a primary key for this table, so let us select ProjectID and click
on Primary Key option in the ribbon.
You can now see a little key icon that will show up next to that field. This shows that the field is part of
the tables primary key.
Let us save this table and give this table a name.

Click Ok and you can now see what this table looks like in the Datasheet View.

Let us click the datasheet view button on the top left corner of the ribbon.
If you ever want to make changes to this table or any specific field, you don't always have to go back to
the Design View to change it. You can also change it from the Datasheet View. Let us update the PStatus
field as shown in the following screenshot.

Click Ok and you will see the changes.

 MS Access - Adding Data

An Access database is not a file in the same sense as a Microsoft Office Word document or a Microsoft
Office PowerPoint are. Instead, an Access database is a collection of objects like tables, forms, reports,
queries etc. that must work together for a database to function properly. We have now created two tables
with all of the fields and field properties necessary in our database. To view, change, insert, or delete data
in a table within Access, you can use the tables Datasheet View.
 A datasheet is a simple way to look at your data in rows and columns without any special
formatting.
 Whenever you create a new web table, Access automatically creates two views that you can start
using immediately for data entry.
 A table open in Datasheet View resembles an Excel worksheet, and you can type or paste data
into one or more fields.
 You do not need to explicitly save your data. Access commits your changes to the table when you
move the cursor to a new field in the same row, or when you move the cursor to another row.
 By default, the fields in an Access database are set to accept a specific type of data, such as text
or numbers. You must enter the type of data that the field is set to accept. If you don't, Access
displays an error message −
Let us add some data into your tables by opening the Access database we have created.

Select the Views → Datasheet View option in the ribbon and add some data as shown in the following
screenshot.

Similarly, add some data in the second table as well as shown in the following screenshot.
You can now see that inserting a new data and updating the existing data is very simple in Datasheet
View as working in spreadsheet. But if you want to delete any data you need to select the entire row first
as shown in the following screenshot.

Now press the delete button. This will display the confirmation message.
Click Yes and you will see that the selected record is deleted now.

MS Access - Query Data

A query is a request for data results, and for action on data. You can use a query to answer a simple
question, to perform calculations, to combine data from different tables, or even to add, change, or delete
table data.
 As tables grow in size they can have hundreds of thousands of records, which makes it impossible
for the user to pick out specific records from that table.
 With a query you can apply a filter to the table's data, so that you only get the information that
you want.
 Queries that you use to retrieve data from a table or to make calculations are called select queries.
  Queries that add, change, or delete data are called action queries.
 You can also use a query to supply data for a form or report.
 In a well-designed database, the data that you want to present by using a form or report is often
located in several different tables.
 The tricky part of queries is that you must understand how to construct one before you can
actually use them.
Create Select Query
If you want to review data from only certain fields in a table, or review data from multiple tables
simultaneously or maybe just see the databased on certain criteria, you can use the Select query. Let us
now look into a simple example in which we will create a simple query which will retrieve information
from tblEmployees table. Open the database and click on the Create tab.

Click Query Design.

In the Tables tab, on the Show Table dialog, double-click the tblEmployees table and then Close the
dialog box.
In the tblEmployees table, double-click all those fields which you want to see as result of the query. Add
these fields to the query design grid as shown in the following screenshot.

Now click Run on the Design tab, then click Run.

The query runs, and displays only data in those field which is specified in the query.
 MS Access - Query Criteria
Query criteria helps you to retrieve specific items from an Access database. If an item matches with all
the criteria you enter, it appears in the query results. When you want to limit the results of a query based
on the values in a field, you use query criteria.
 A query criterion is an expression that Access compares to query field values to determine
whether to include the record that contains each value.
 Some criteria are simple, and use basic operators and constants. Others are complex, and use
functions, special operators, and include field references.
 To add some criteria to a query, you must open the query in the Design View.
 You then identify the fields for which you want to specify criteria.
Example
Lets look at a simple example in which we will use criteria in a query. First open your Access database
and then go to the Create tab and click on Query Design.

In the Tables tab on Show Table dialog, double-click on the tblEmployees table and then close the dialog
box.
Let us now add some field to the query grid such as EmployeeID, FirstName, LastName, JobTitle and
Email as shown in the following screenshot.

Let us now run your query and you will see only these fields as query result.
If you want to see only those whose JobTitle are Marketing Coordinator then you will need to add the
criteria for that. Lets go to the Query Design again and in Criteria row of JobTitle enter Marketing
Coordinator.

Let us now run your query again and you will see that only Job title of Marketing Coordinators are
retrieved.
If you want to add criteria for multiple fields, just add the criteria in multiple fields. Let us say we want to
retrieve data only for Marketing Coordinator and Accounting Assistant; we can specify the OR row
operator as shown in the following screenshot −

Let us now run your query again and you will see the following results.
If you need to use the functionality of the AND operator, then you have to specify the other condition in
the Criteria row. Let us say we want to retrieve all Accounting Assistants but only those Marketing
Coordinator titles with Pollard as last name.

Let us now run your query again and you will see the following results.
 MS Access - Action Queries
In MS Access and other DBMS systems, queries can do a lot more than just displaying data, but they can
actually perform various actions on the data in your database.
 Action queries are queries that can add, change, or delete multiple records at one time.
 The added benefit is that you can preview the query results in Access before you run it.
 Microsoft Access provides 4 different types of Action Queries −
o Append

o Update

o Delete

o Make-table

 An action query cannot be undone. You should consider making a backup of any tables that you
will update by using an update query.
Create an Append Query
You can use an Append Query to retrieve data from one or more tables and add that data to another table.
Let us create a new table in which we will add data from the tblEmployees table. This will be temporary
table for demo purpose.
Let us call it TempEmployees and this contains the fields as shown in the following screenshot.
In the Tables tab, on the Show Table dialog box, double-click on the tblEmployees table and then close
the dialog box. Double-click on the field you want to be displayed.
Let us run your query to display the data first.

Now let us go back to Query design and select the Append button.
In the Query Type, select the Append option button. This will display the following dialog box.

Select the table name from the drop-down list and click Ok.
In the Query grid, you can see that in the Append To row all the field are selected by default
except Address1. This because that Address1 field is not available in the TempEmployee table. So, we
need to select the field from the drop-down list.

Let us look into the Address field.

Let us now run your query and you will see the following confirmation message.

Click Yes to confirm your action.

When you open the TempEmployee table, you will see all the data is added from the tblEmployees to the
TempEmployee table.

MS Access - Create Queries

Create an Update Query
You can use an Update Query to change the data in your tables, and you can use an update query to enter
criteria to specify which rows should be updated. An update query provides you an opportunity to review
the updated data before you perform the update. Let us go to the Create tab again and click Query Design.
In the Tables tab, on the Show Table dialog box, double-click on the tblEmployees table and then close
the dialog box.
On the Design tab, in the Query Type group, click Update and double-click on the field in which you
want to update the value. Let us say we want to update the FirstName of Rex to Max.

In the Update row of the Design grid, enter the updated value and in Criteria row add the original value
which you want to be updated and run the query. This will display the confirmation message.

Click Yes and go to Datasheet View and you will see the first record FirstName is updated to Max now.
Create a Delete Query
You can use a delete query to delete data from your tables, and you can use a delete query to enter criteria
to specify which rows should be deleted. A Delete Query provides you an opportunity to review the rows
that will be deleted before you perform the deletion. Let us go to the Create tab again and click Query
Design.
In the Tables tab on the Show Table dialog box, double-click the tblEmployees table and then close the
dialog box.
On the Design tab, in the Query Type group, click Delete and double-click on the EmployeeID.

In the Criteria row of the Design Grid, type 11. Here we want to delete an employee whose EmployeeID
is 11.

Let us now run the query. This query will display the confirmation message.
Click Yes and go to your Datasheet View and you will see that the specified employee record is deleted
now.

Create a Make Table Query

You can use a make-table query to create a new table from data that is stored in other tables. Let us go to
the Create tab again and click Query Design.
In the Tables tab, on the Show Table dialog box, double-click the tblEmployees table and then close the
dialog box.
Select all those fields which you want to copy to another table.

In the Query Type, select the Make Table option button.

You will see the following dialog box. Enter the name of the new table you want to create and click OK.

Now run your query.

You will now see the following message.

Click Yes and you will see a new table created in the navigation pane.
 MS Access - Relating Data
Let us now look into the following table which contains data, but the problem is that this data is quite
redundant which increases the chances of typo and inconsistent phrasing during data entry.

CustID Name Address Cookie Quantity Price

1 Ethel Smith 12 Main St, Arlington, VA 22201 S Chocolate Chip 5 $2.00

2 Tom Wilber 1234 Oak Dr., Pekin, IL 61555 Choc Chip 3 $2.00

3 Ethil Smithy 12 Main St., Arlington, VA 22201 Chocolate Chip 5 $2.00

To solve this problem, we need to restructure our data and break it down into multiple tables to eliminate
some of those redundancy as shown in the following three tables.
Here, we have one table for Customers, the 2nd one is for Orders and the 3rd one is for Cookies.
The problem here is that just by splitting the data in multiple tables will not help to tell how data from one
table relates to data in another table. To connect data in multiple tables, we have to add foreign keys to
the Orders table.
Defining Relationships
A relationship works by matching data in key columns usually columns with the same name in both the
tables. In most cases, the relationship matches the primary key from one table, which provides a unique
identifier for each row, with an entry in the foreign key in the other table. There are three types of
relationships between tables. The type of relationship that is created depends on how the related columns
are defined.
Let us now look into the three types of relationships −
One-to-Many Relationships
A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in
table A can have many matching rows in table B, but a row in table B can have only one matching row in
table A.
For example, the Customers and Orders tables have a one-to-many relationship: each customer can place
many orders, but each order comes from only one customer.
Many-to-Many Relationships
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice
versa.
You create such a relationship by defining a third table, called a junction table, whose primary key
consists of the foreign keys from both table A and table B.
For example, the Customers table and the Cookies table have a many-to-many relationship that is defined
by a one-to-many relationship from each of these tables to the Orders table.
One-to-One Relationships
In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and
vice versa. A one-to-one relationship is created if both the related columns are primary keys or have
unique constraints.
This type of relationship is not common because most information related in this way would be all in one
table. You might use a one-to-one relationship to −
 Divide a table into many columns.
 Isolate part of a table for security reasons.
 Store data that is short-lived and could be easily deleted by simply deleting the table.
 Store information that applies only to a subset of the main table.

MS Access - Create Relationships

Why Create Table Relationships?

MS Access uses table relationships to join tables when you need to use them in a database object. There
are several reasons why you should create table relationships before you create other database objects,
such as forms, queries, macros, and reports.
 To work with records from more than one table, you often must create a query that joins the
tables.
 The query works by matching the values in the primary key field of the first table with a foreign
key field in the second table.
 When you design a form or report, MS Access uses the information it gathers from the table
relationships you have already defined to present you with informed choices and to prepopulate
property settings with appropriate default values.
 When you design a database, you divide your information into tables, each of which has a
primary key and then add foreign keys to related tables that reference those primary keys.
 These foreign key-primary key pairings form the basis for table relationships and multi-table
queries.
Let us now add another table into your database and name it tblHRData using Table Design as shown in
the following screenshot.
Click on the Save icon as in the above screenshot.

Enter tblHRData as table name and click Ok.

tblHRData is now created with data in it.
 MS Access - One-To-One Relationship

Let us now understand One-to-One Relationship in MS Access. This relationship is used to relate one
record from one table to one and only one record in another table.
Let us now go to the Database Tools tab.

Click on the Relationships option.

Select tblEmployees and tblHRData and then click on the Add button to add them to our view and then
close the Show Table dialog box.
To create a relationship between these two tables, use the mouse, and click and hold
the EmployeeID field from tblEmployees and drag and drop that field on the field we want to relate by
hovering the mouse right over EmployeeID from tblHRData. When you release your mouse button,
Access will then open the following window −

The above window relates EmployeeID of tblEmployees to EmployeeID of tblHRData. Let us now click
on the Create button and now these two tables are related.
The relationship is now saved automatically and there's no real need to click on the Save button. Now that
we have the most basic of relationships created, let us now go to the table side to see what has happened
with this relationship.
Let us open the tblEmployees table.
Here, on the left-hand side of each and every record, you will see a little plus sign by default. When you
create a relationship, Access will automatically add a sub-datasheet to that table.
Let us click on the plus sign and you will see the information that is related to this record is on
the tblHRData table.

Click on the Save icon and open tblHRData and you will see that the data we have entered is already
here.
 MS Access - One-To-Many Relationship

The vast majority of your relationships will more than likely be this one to many relationships where one
record from a table has the potential to be related to many records in another table.
The process to create one-to-many relationship is exactly the same as for creating a one-to-one
relationship.
Let us first clear the layout by clicking on the Clear Layout option on the Design tab.

We will first add another table tblTasks as shown in the following screenshot.
Click on the Save icon and enter tblTasks as the table name and go to the Relationship view.

Click on the Show Table option.

Add tblProjects and tblTasks and close the Show Table dialog box.
We can run through the same process once again to relate these tables. Click and hold ProjectID from
tblProjects and drag that all the way over to the ProjectID from tblTasks. Further, a relationships window
pops up when you release the mouse.

Click the Create button. We now have a very simple relationship created.
 MS Access - Many-To-Many Relationship

In this chapter, let us understand Many-to-Many Relationship. To represent a many-tomany relationship,

you must create a third table, often called a junction table, that breaks down the many-to-many
relationship into two one-to-many relationships. To do so, we also need to add a junction table. Let us
first add another table tblAuthers.
Let us now create a many-to-many relationship. We have more than one author working on more than
one project and vice versa. As you know, we have an Author field in tblProjects so, we have created a
table for it. We do not need this field any more.
Select the Author field and press the delete button and you will see the following message.

Click Yes. We will now have to create a junction table. This junction table have two foreign keys in it as
shown in the following screenshot.

These foreign key fields will be the primary keys from the two tables that were linked
together tblAuthers and tblProjects.
To create a composite key in Access, select both these fields and from the table tools design tab, you can
click directly on that primary key and that will mark not one but both of these fields.
The combination of these two fields is the tables unique identifier. Let us now save this table
as tblAuthorJunction.
The last step in bringing the many-to-many relationships together is to go back to that relationships
view and create those relationships by clicking on Show Table.
Select the above three highlighted tables and click on the Add button and then close this dialog box.
Click and drag the AuthorID field from tblAuthors and place it on top of
the tblAuthorJunction table AuthorID.
The relationship youre creating is the one that Access will consider as a one-to-many relationship. We
will also enforce referential integrity. Let us now turn on Cascade Update and click on the Create button
as in the above screenshot.

Let us now hold the ProjectID, drag and drop it right on top of ProjectID from tblAuthorJunction.
We will Enforce Referential Integrity and Cascade Update Related Fields.

The following are the many-to-many relationships.