Chapter 5 More on Access 2016

Learning Objectives

After studying this chapter, you will be able to:

 Know about primary key

 Know about relationship between tables
 Know about query in Access
 Learn about form
 Know about report
 Print a report

Let’s Connect

Label the components of Access 2016 screen.

Introduction
The database contains a large amount of data in the form of
multiple tables. Sometimes, we need to retrieve the information
from these tables or we need to add, delete and update the tables.
In this chapter, we will learn how to create a relationship between
tables, query, creating form and report.
Primary Key
If a field or a combination of fields, is designated as primary key, than that redundant data
cannot be entered in that field ,i.e. primary key uniquely identify each record in a table. The
value in primary key field is different for different records.
The primary key ensures that there is no data duplication inside the table.
MUST KNOW
To assign multiple keys as primary key, hold down the Ctrl key and click on the row selector
for each field.

Setting Relationships between Tables

A relationship is a link that associates a field in one table with a field
in the other table. It matches a field with the same name in both the
tables. For example, matching a primary key from one table and a
foreign key in the other table.
MUST KNOW
Foreign key is the column that points the primary key of another table.

To create a relationship, follow the given steps:

1. Open or create the tables between which relationship is to be
created. For example, consider following two tables:

Here, Table1 had the primary key as Roll No.

 Here, Table 2 had the foreign key Roll No which had the same
set of data as that of data in the Roll No field in Table1.
2. Click on the Database Tools tab.

3. Click on the option in the Relationships group. The Show Table dialog
box appears.
4.

5. Click on the 1st table and click on Add button.

6. Click on the 2nd table and click on the Add button. The tables will appear in the
Relationships window.
7. After adding the tables click on the Close button.

8. Drag the primary key from the main table (Table1) to the child table (Table2)
and drop it over the same field. The Edit Relationships dialog box appears.
9. Click on the Create button. A linking line will appear between the tables that
displays the relationship between tables.
 Queries
Queries are made on tables and the result of a query is also
displayed in the form of a table. Queries provide an efficient
way to retrieve specific information from the database. They
are like simple questions. Access allows you to create four
types of queries:
• Select Query
It is used to retrieve the records from the database according to the
specified criteria. It displays the searched records in the Datasheet
View.
 Update Query
It is used to update the database.
 Crosstab Query
A crosstab query calculates sum, average or other aggregate
functions, and then groups the results by two sets of values —
one down the side of the datasheet and the other across the
top.
• Append Query
An append query selects records from one or more data
sources and copies the selected records to an existing table.
Creating a Select Query
There are the following two methods by using that we can create
queries in the Access.
• Query Wizard Method • Query Design Method
Creating a Select Query Using Query Wizard Method
To create a query using the query wizard method, follow the given
steps:
1. Open the database from which you want to retrieve the data.
2. Click on the Create tab.

3. Click on the Query Wizard option in the Queries group. The

New Query dialog box appears.

4. Click on the Simple Query Wizard option.

5. Click on the OK button. The Simple Query Wizard dialog box
appears.
6. Select the table name from which you want to retrieve fields. For
Example: select the Table1 in the Tables Queries drop-down
box.

7. Select the field in the Available Fields box and click on the
button to move the selected field in the Selected Fields box. You
can also move all the fields of the selected table in the Selected
Fields box by clicking on the button.
8. Click on the Next button. The Simple Query Wizard dialog box appears.
 10. Click on the Detail option.
11. Click on the Next button. Another Simple Query Wizard dialog box appears

12. Specify the Title of the query.

13. Click on the Finish button. The query window appears in the datasheet view that
will display the records according to your specified criteria.
Creating a Query Using Query Design Method
To create a query using query design method, follow the given
steps:
1. Open the database from which you want to retrieve the data.
2. Click on the Create tab.

3. Click on the Query Design option in the Queries group.

The Show Table dialog box appears.
4. Choose the table on which you want to run a query or select a saved query
to run on the tables. For selecting both tables — Table1 and Table2. After
selecting the table.
5. Click on the Add button. The Query Design View appears. It consists of two
panes — Top and Bottom.
Top Pane of Query Design View
The top pane displays the table(s) selected for the query.
Bottom Pane of Query Design View
The bottom pane displays the design grid on which you can add
fields to the query. The Design Grid consists of the following
elements:
• Field: A row that displays the selected fields from the table.
• Table: This row shows the names of the tables from which fields
are selected.
• Sort: This row is used to filter the data either in the ascending
order or descending order. This is optional.
• Show: This row displays the check marks. The fields that contain
the check marks, display the information when the query is
being run.
• Criteria: This row holds the conditions on the basis of which the
records will be filtered in the query output.
6. Double-click on the field (which you want to see in the query table) available
in the table in the top pane. The field will be inserted in the bottom pane. Do
same to add more fields.
7. Set the search criteria on field(s) by clicking on the cell in the Criteria row.

8. Click on the Run( ) button in the Results group to execute the query. The
records will appear in the query window according to your specified criteria.

Forms
You must have filled various kinds of forms to fill the details.
Access also allows us to enter the data in the database by using
Forms. When you create a form in Access, an organised view of
the fields of one or more tables appears by using that you can
easily add, update and delete the records.
Access provides following three views for forms:
• Form View
This helps us to type or edit the data.
• Layout View
This view is used to change the look of the form. You can add
elements, like Date or Time, Logo, etc. in this view.
• Design View
This view is used to customize the design of your form. You can
modify the form design in this view.
Creating a Form
To create a form, follow the given steps:
1. Double-click on the table name in the Navigation Pane to
open it in the Datasheet View. For Example: we are clicking on
Table1.

2. Click on the Create tab.

3. Click on the Form button in the Forms group. The Form View
appears.
5. You can easily enter new records and can also navigate through the records by
using Record Navigation Bar.
 6. To save the work, press Ctrl+S keys. The Save As dialog box appears.

7. Type the name of the form.

8. Click on the OK button. The form will be saved with the given name.

Reports
Reports are used to organise and summarise the large amount of
the data for viewing and printing. They provide easy access to the
information stored in the database.

Creating a Report
To create a report, follow the given steps:
1. Select the table for which you want to create the report.
2. Click on the Create tab.
 2. Click on the Report option. A report opens up in the Layout
View.

3. Click on the Report View( ) button as visible on the status bar,

if you want to enter or edit the data. A cursor appears to edit
the data.
4. Press Ctrl+S keys to save the report. The Save As dialog box
appears.

5. Type the name of the report and click on the OK button. The
report will be saved with the given name.

Printing of Report
You may print the report that you get, as long as the printer is installed in the computer
system.
To print the report, follow the given steps:
1. Open the report which you want to be print.
2. Click on the Print Preview( ) button visible on the status bar.
The Print Preview tab appears.

3. Click on the Print( ) button in the Print group. The Print dialog
box appears.
 4. Do the required changes.
5. Click on the OK button. The particular report will get printed.
Summary
 Primary key uniquely identifies each record in a table.
 A relationship is a link that associates a field in one table with
a field in the other table. It matches a field with the same
name in both the tables.
 Queries are made on tables and the result of a query is also
displayed in the form of a table. Queries provide an efficient
way to retrieve specific information from the database.
 Access also allows us to enter the data in the database by
using Forms. When you create a form in Access, an organised
view of the fields of one or more tables appears by using that
you can easily add, update and delete the records.
 Reports are used to organise and summarise the large amount
of the data for viewing and printing. They provide easy access
to the information stored in the database.
Test Yourself
A. Multiple choice questions.
1. Which of the following uniquely identifies each record in a
table?
a. Primary key b. Form c. Report
2. Which tab is used to create relationship between two
tables?
a. Create tab b. Database Tools tab c. None of
these
 3. Which type of query is used to retrieve the records from the
database according to the specified criteria?
a. Select Query b. Update Query c.
Crosstab Query
4. In the bottom pane of the Query Design view, which row
displays the checkmarks?
a. Field b. Sort c. Show
B. Fill in the blanks.
Queries Reports Update Relationship
1. A ____________________ is a link that associates a field in one
table with a field in the other table.
2. The ___________________ query is used to update the
database.
3. _______________ provide an efficient way to retrieve specific
information from the database.
4. ________________are used to organise and summarise the
large amount of the data for viewing and printing.
C. State True or False.
1. You cannot print the report.
2. Primary key uniquely identifies each record in a table.
3. An append query selects records from one or more data
sources and copies the selected records to an existing table.
4. You cannot enter the records by using the form.
D. Answer the following questions.
1. Write the steps to create a report in Access.
2. What are the steps to create a form?
3. Explain the bottom pane of the Query Design View in
Access.
4. Explain the steps to create relationship between two tables.
E. Life Skills
1. Pinky had forgotten the steps to print the report in Access.
Tell her the steps to perform the same.
2. Joydeep wants to know about primary key. Answer to his
query.
Activity Session
Fun in Searching
Find and encircle 12 meaningful terms extracted from this chapter.
See across and downwards.
The words are: RELATIONSHIP, RUN, QUERY, DATABASE, REPORT,
UPDATE, SORT, TABLE, FIELD, FOREIGN, SHOW and APPEND.
F D F O R E I G N
Q I A E
U E T P S U
R E L A T I O N S H I P
U R D B A R O O D
N Y A B T R W A
S L T T
E E A P P E N D E

Fun in Exploring
Explore the use of form in Access 2016. Make a note in the
notebook.
Fun In the Lab
Create the following database:

Perform the following tasks:

 Enter at least 10 records in the database.
 Save the table.
 Create a form of the table created.
 Enter 5 more records into the table.
 Save the form.
  Create a report and print it.

Art Integrated Project

On the topic “Stop Using Plastic”, make a form that contains various
fields. Do this in an A4 size sheet.
Teacher’s Notes
 Demonstrate the steps to create relationship between tables,
query, form and report to the students in detail.
 Practically explain the detailed steps to perform each topic
covered in the chapter.