6.

34

5MS-AccaS
(7) The query results willbe displayed in the query's Datasheet view, which
looks lile
a table.

g EMP Query3
ENAME SALARY
AMAN 12000
RAMAN 50000
SUSHIL 30000

Fig.6.44 (Query's Result)

6.12 REPORTS
Reports offer a way to view, format, and summarize the information in your Microsoft
Access database. For example, you can create a simple report of phone numbers for all your
Contacts, or asummary report on the total sales across different regions and time periods.
Reports give you the ability to present components of your database in an easy-to-read,
printable format.
A report can be created based on either tables or queries. Reports provide a pathway
from a huge amount of data set in tables or queries to a nice and brief summary of data.
They can include headers and footers in which headings of subtotals and totals can be
specified, serving as an invoice, order, presentation, or mailing labels.
Like other objects in Access, a report can be created in the Design View or by the Report
Wizard with two types of basic report types; columnar and tabular. The columnar report
orients field names to the left and their values to the right as in a columnar form, In contrast,
the tabular report looks similar to the format of tables, listing all the field names on top and
their values underneath of field names. Needless to say, the orientation of a report is
dependent on the taste and needs of a report designer.
6.12.1 Creating Reports
Tocreate a database report in Access 2010, following steps should be considered:
Step 1: Choose a record source
The record source of a report can be a table or a named query. The record source must
contain all of the rows and columns of data you want display on the report.
If the data is from an existing table or query, select the table or query in the Navigation
Pane, and then continue to Step 2.
S-Access
6.35
If the record source does not yet exist, do
one of the following:
Continue to Step 2 and use the Blank Report tool.
OR
Create the tableor query that contains the required data. Select
thequery or table in
the Navigation Pane, and then continue to Step 2.
Step 2: Choose a report tool
The report tools are located on the Create tab of the ribbon, in the Reports group. The
following table describes the options:
Tool Description
Report Creates a simple, tabular report containing all of the fields in the record source you
selected in the Navigation Pane.
Report Opens a blank report in Design view, to which you can add the required fields and
Design controls.

Blank Opens a blank report in Layout view, and displays the Field List from where you can
:Report add fields to the report
Report Displays a multiple-step wizard that lets you specity fields, grouping/sorting levels,
Wizard and layoutoptions.
Labels Displays a wizard that lets you select standard or custom label sizes, as well as
which fields you want to display, and how you want them sorted.
Step 3: Create the report
(1) Click the button for the tool you want to use. If a wizard appears, follow the steps in
the wizard and click Finish on the last page. Access displays the report in Layout
VIew.

(2) Format the report to achieve the looks that you want:
Resize fields and labels by selecting them and then dragging the edges until they
are the size you want.
Move afield by selecting it (and its label, if present), and then dragging it to the
new location.
IKight-click a field and use the commands on the shortcut menu to merge or split
cells, delete or select fields, and perform other formatting tasks.
Example:
() Open the table or query you want to use in your report.
6.36

EMP
ENAME JOB SALARY
9S-A
ID
h01 AMAN CLERK ADDRESS
12000 DELHI
MANAGER
102 RAMAN 50000 MUMBAI
LECTURER
103 SUSHIL 30000 NEW YORK
Flg. 6.45 (Table)

(2) Select the Create tab onthe Ribbon, and locate the Reports group. Click the
command. Repot
Report Wizard
Labels
Report Report Blank
Design Report
Reports

Fig. 6.46 (Report Command)

(3) Access will create a new report based on your object. It is likely that some of vor
data will be located on the other side of the page break. To fix this, resize vour
fields. Simply select afield, then click and drag its edge until the field is the desired
size. Repeat with additional fields until all of your fields fit.
EMP

EMP 22 November 2018

6.01.30 PM
I) ENAME
JOB
SALARY ADDRESS
201 AMAN
CLERK
12000|| DELAI
102 RAMAN
MANAGER
103 SUSHIL
50000| MUMBAI
LECTURER 30000|| NEW YORK

Page1of!

Flg. 6.47 (Database

6.12.2 Preview and Print Report)
To Preview a Reports
report:
(1) Right-click the report in the Navigation Pane and click Print Preview. Youcanuse
the commands on the Print
Print the report Preview tab to do any of the following:
LS-AccasS 6.37

Adjust page size or layout

Zoom in or out, or vieW multiple pages at a timne
Refresh the data on the report
Export the report to another file format.
(2) Click Close Print Preview.
To Print a report:
(1) To print a report without previewing it, Right-click the report in the Navigation
Pane and click Print. The report is sent to your default printer.
(2) If you select the report in the Navigation Pane and select Print from the File tab,
you can select additional printing options such as number of pages and copies and
specify a printer.
 6.37

Adjust page size or layout

Zoom in or out, or view multiple pages at a time
Refresh the dataon the report
Export the report to another file format.
Preview.
2) Click Close Print
To Print a report:
1) To print a report without previewing it, Right-click the report in the Navigation
Pane and click Print. The report is sent to your default printer.
the File tab,
2) If you select the report in the Navigation Pane and select Print from
you can select additional printing options such as number of pages and copies and
specify a printer.
6.13 SORTING
Sorting is one method that helps you find information quickly and easily in a database.
Records can be sorted in ascending order (A-Z), and descending order (Z-A). You can sort by zip
code, last name, first name, company, contact type etc.
You can sort by one field or by many fields, but the fields must be adjacernt. If they are
from left
not, then youcan temporarily rearrange them. Also, MS-ACCESS alwayssorts data
is
to right. This means that if you sort by more than one field, it will sort by whichever field
extreme left and move right.

6.13.1Sorting by One Field (Quick Sort)

Tosort one by one column or field, do the following:
Place your cursor anywhere in the column on which you want the sort performed.
ENAME JOB SALARY ADDRESS
101 AMAN CLERK 12000 DELHI

102 RAMAN MANAGER 50000 MUMBAI

30000 NEW YORK
103 SUSHIL LECTURER

Click the Home tab on the Ribbon, and locate the Sort & Filter group.
Sort the field by selecting the Ascending or Descending command. Select
Ascending to sort text A to Z or to sort numbers from smallest to largest. Select
Descending to sort text Zto A or to sort numbers from largest to smallest.
 6.38

Ascending Selection
MS-Acs
Descending| Advanced
Filter
A Remove Sort Y Toggle Fiter
Sort & Filter

now be
After selecting the Ascending command, the table will sorted by the
field as follows: selected
ENAME JOB SALARY ADDRESS
ID
101 AMAN CLERK L200C/DELHI
103 SUSHIL LECTURER 30000 NEW YORK
102 RAMAN MANAGER 50000 MUMBAI

Note: When you close a table, sorted records return to their previous order unless vou ea
the sort.

6.13.2 Sorting by Multiple Fields

To sort across multiplefields, do the following:
Select multiple columns by clicking and dragging across the column headings of the
columns you want to sort.
ID ENAME ADDRESS
101 AMAN CLERK 12000 DELHI
102 RAMAN MANAGER 50000 MUMBAI
103 SUSHIL LECTURER 30000 NEW YORK

Click the Home tab on the Ribbon, and locate the Sort & Filter group.
Select
Sort the field by selecting the Ascending or Descending Command. Select
Ascending to sort text Ato Z or to sort numbers from smallest to largest.
Descending to sort text Z to A or to sort numbers from largest to Smaue

2Ascending Selection r
Filter Descending|Advanced
Remove Sort Y Toggle Filter
Sort & Filter
secondary sors
Access sorts by the leftmost column first:
(where there were Access then performs theremaining
duplicate values in the column to the left) on
selected columns to the right.
 6.39

After selecting the Descending command, the table will now be sorted by the
selected field as follows:
ENAME 3O8 ARY ADDRESS
102 RAMAN MANAGER 50000 MUMBAI
103 SUSHIL LECTURER 30000 NEW YORK
101 AMAN CLERK 12000 DELHI

6.13.3 Removing a Sort

remove it to
After asort is performed, you will be given the option to save the sort or to
the sort.
allow the records toreturn to the way they were ordered before you applied
To Remove the Sort:
tab.
Click the Remove Sort command under Sort & Filter group in the Home
AAscending yselection
Descending Advanced
Filter
ORemove SortY Toggle Fitter
Sort & Fiter
Fig. 6.48 (Removing a Sort)

6.13.4 Saving a Sort

will prompt you to save the
When you attempt to close a table after a sort, Access
changes to the table design.
ToSave a Sort:
Exit the table.
save changes to the
Click the Yesbutton in response to the question;Do you want to
table?
X
Microsoft Access

table EMP'?
Do you want to save hanges to the design of

Yes No Cancel

table again, the records will still be

te Sort order is saved. When you open the
sorted.

ToCancel a Sort:
Exit the table.
6.40

Click the No button in response to the question; Do you want to save

MS-cCSs
table?
changes to the
The change is not saved; the table remains in its original design.
6.14 FILTERING RECORDS
At times, you might want to view only those records that matcha specific
filter is a technique that lets you view and work with asubset of data. Applying a filter to an criterion. A

Access table, form, or query temporarily hides records that don't meet your search criteria
6.14.1 Applying a Filter
To Filter By Selection:
Click anywhere in the field that you want to filter the records in the table.
ID ENAME JOB SALARY ADDRESS
101 AMAN CLERK 12000 DELHI
102 RAMAN MANAGER 50000 MUMBAI
103 SUSHIL LECTURER 30000 NEW YORK

A drop-down menu with a checklist will appear. Only checked items will be
included inthe filtered results. Use the following options to determine which items
will be included in your filter:
Select and deselect items one at a time by clicking their check boxes.
Click Select All to include every item in the filter. Clicking Select All a second
time will deselect all itemns.
in the
selected
Click Blank to set the filter to find only the records with no data
field.
ID ENAMEOB SALARY ADDRESS Click
101 AMAN CLERK Sort A to Z
102 RAMAN MANAGER | Sort Zto A
103 SUSHIL LECTURER
Clear flter from OB
Text Fiters

(Select A)
(Blanks)
CLERK
V LECTURER
VMANAGER

OK Cancel
 6.41

Click OK. The filter will be applied. The result would be look like:
ID ENAME JOB SALARY ADDRESS
102 RAMAN MANAGER 50000 MUMBAI
103 SUSHIL LECTURER 30000 NEW YORK

Filter
6.14.2 Removing a
Filter:
To Remove a
Cick the Toggle Filter command under Sort &Filter group in the Home tab.
4Ascending Selection
Descending Advanced
Filter
Remove Sort Toggle Filter
Sort & Filter

The records revert to their ordering before the filter was applied.

JOB SALARY ADDRESS

ID ENAME
CLERK 12000 DELHI
101 AMAN
102 RAMAN MANAGER 50000 MUMBAI

LECTURER 30000 NEW YORK

103 SUSHIL

button again.
Optional, if youwish to reapply the filter, click the Toggle Filter
6.14.3 Saving a Filter
applied to the table
MS-Access defaults to displaying all records ina table. Filters are not
attempt to close
initially. Filtering table records actually change the table design. When you
the table desigin.
a table after a filter, Access will prompt you to save the changes to
To Save a Filter:
Exit the table.
question; Do you want to save changes to the
lck the Yes button in response tothe
C

table?
Microsoft Access

to the design of table EMP'?

Do you want to save dhanges

Yes No Cancel

The filter order is saved.

42

To Cancel a Filter:
MS-Acces
Exit the table.

Click the No button in response to the question; Do you want to save changes to the
table?

The change is not saved; the table remains in its original design.

Questions