E-Content of
IT TOOLS AND BUSINESS SYSTEM
Chapter : 7.6 Manipulation of Data
Topic : 7.6.1 Query
Query
User can use a query to view a subset of data or to answer questions about user
data.
For example, if user wants to view a list of student names and email address, but
user do not want to see addresses and other data, user can create a query that
displays the student's first name, last name and email address only.
Open Tables or Queries in Query Design View
To open tables or queries in Query Design View
FIG 7.27: Open tables or queries in Query Design View
Page | 30
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Activate the Create tab.
Click the Query Design button in the Queries group. The Show Table dialog
box appears.
Activate the Tables tab if user wants to base their query on tables, activate
the Queries tab if user want base their query on queries or activate the Both
tab if user want to base their query on both tables and queries.
Click to choose the table or query on which user want to base their query.
Click Add. The table appears in the window.
Click to choose the next table or query on which user want to base their
query.
Continue clicking tables or queries until user have all the tables and
queries their plan to use.
Click Close. MS-Access changes to Query Design view.
Display All Records and All Fields
In Query Design view, each table has an option that allows user to display all of
the fields and all of the records in a table.
This option appears on the field line on the drop-down menu as the table name
followed by a period and an asterisk (tablename.*).
To display all records and all fields
Page | 31
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.28: Display all records and all fields
Open a table or query in Query Design view.
Click the down-arrow in the first field on the Field row and then select
the tablename.* option. The table name appears on the table line.
Click the Run button. MS-Access retrieves all of the fields and records for the
table and displays them in Datasheet view.
Page | 32
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.29: Display all records and all fields
Sort a Query
While creating a query, user can sort the rows that they retrieve in ascending or
descending order by choosing the option of their need on the Sort row in Query
Design view.
Follow the steps to perform a sort
Page | 33
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.30: Sort a Field
Open a table or query in Query Design view.
Choose the field names to retrieve them.
Under the field click the down-arrow and then choose Ascending or
Descending.
Click the Run button. MS-Access retrieves the selected columns and displays
the rows in the order that user specified.
Page | 34
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.31: Result after running a query
Page | 35
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Save a Query
User can save and return a saved query at any time.
To save a query
FIG 7.32: Save a query
Click the Save button on the Quick Access toolbar. Access saves the query
unless it saving for first time.
Type the name of the query in Save As dialog box.
Click OK. MS-Access saves the query. User can now MS-Access the query
by using the Navigation pane.
Page | 36
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Chapter : 7.6 Manipulation of Data
Topic : 7.6.2 Data Entry Forms
Data Entry Forms
MS-Access forms are much like paper forms. User can use them to enter, edit, or
display data.
They are based on tables. When using a form, user can choose the format, the
arrangement, and which fields to display.
Using the Form Button
MS-Access can automatically create several types of forms.
For example, when user click the Form button on the Create tab, MS-Access
places all fields in the selected table on a form.
If the table has a one-to-many relationship with other table or query, MS-Access
creates a stacked form (the records are displayed in a column) for the primary
table and a datasheet for the related table.
If there are several tables with a one-to-many relationship, MS-Access does not
create the datasheet.
Follow the steps to create a form
Page | 37
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.33: Create a Form
Open the Navigation pane.
Click the table or query on which user want to base their form.
Activate the Create tab.
Click Form in the Forms group. MS-Access creates a form.
Page | 38
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.34: Department Form tab
User can use the Navigation bars to move through the records on a form.
FIG 7.35: Navigation bar in the Form
1 Go to First Record
2 Go to Previous Record
3 The Current Record
4 Go to Next Record
5 Go to Last Record
6 Create a New (Blank) Record
To save a form
Click the Save button on the Quick Access toolbar. MS-Access saves the
form unless user are saving for the first time. If user saving for the first time,
the Save As dialog box appears.
Type the name user want to give the form.
Click OK. MS-Access saves the form. User can now access the form by using
the Navigation pane.
Modify a Form
After user creates a form, it opens in Layout view, where user can modify it.
To change the size of a field
Click a side of the field and drag to change the width of the field.
Click the top or bottom of a field and drag to change the height of a field.
To move a datasheet
Click the datasheet to select it.
Page | 39
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Click and drag the four-sided arrow in the upper-right corner to move the
datasheet.
To resize a datasheet
Click the datasheet to select it.
Click a side of the datasheet and drag to change the width.
Click the top or bottom of the datasheet and drag to change the height.
To apply an AutoFormat
Activate the Format tab.
Click Shape fill button and choose any color for background color.
User can also insert background image from gallery.
To change the font color, style, size click Select all button then choose the
appropriate changes.
Then navigate to Forms view to see the full structure of the form
To change a Form title
While user create a form, by default, MS-Access uses the form name as the
title. User can change the title.
Activate the Format tab.
Click the Title button.
Type the new title.
To add the date and time
User can easily add the date and time in their form.
Activate the Format tab.
Click the Date and Time button. The Date and Time dialog box appears.
Select the date and time to appear on the Form.
Page | 40
E-Content of
IT TOOLS AND BUSINESS SYSTEM
To change Fonts and Formats
Page | 41
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Chapter : 7.6 Manipulation of Data
Topic : 7.6.3 Reports
Reports
Reports organize and summarize data for viewing online or for printing.
A detail report displays all of the selected records.
Creating Reports
User can include summary data such as totals, counts, and percentages in a
detail report.
A summary report does not list the selected records but instead summarizes the
data and presents totals, counts, percentages, or other summary data only.
MS-Access has several report generation tools that user can use to create both
detail and summary reports quickly.
Use of Report Button
The Report button creates a simple report that lists the records in the selected
table or query in a column format.
To use the Report button
FIG 7.36: Report button
Open the Navigation pane.
Click the table or query on which user want to base their report.
Activate the Create tab.
Page | 42
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Click the Report button in the Reports group. MS-Access creates report and
displays report in Layout view. User can modify the report.
Sections of Report
FIG 7.38: Sections of a Report
Page | 43
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Use the Report Wizard
User can also use the Report Wizard to create a report.
The Report Wizard provides with more flexibility than by using the Report button.
User can choose the tables and fields, group the data, sort the data, summarize
the data, choose a layout and orientation, apply a style, and title their report.
To create a report by using the Report Wizard
Open the Report Wizard
FIG 7.39: Report Wizard Button
Activate the Create tab.
Click Report Wizard in the Reports group. The Report Wizard appears.
Select tables, queries and fields
Page | 44
E-Content of
IT TOOLS AND BUSINESS SYSTEM
When using the Report Wizard, user can use fields from multiple tables
and/or queries if the tables/queries have a relationship.
FIG 7.40: Select table, queries and fields
Click the down-arrow next to the Table/Queries field and then click the
table from which user want to select fields.
Click a field and then click the single-right arrow to select a single field,
click the double-right arrows to select all fields, click a field and then click
the single-left arrow to deselect a single field, or click the double-left arrow
to deselect all fields.
Click Next. The Report Wizard moves to the next page.
Page | 45
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Group
User can group data by using Report Wizard.
Grouping puts all of the values in a field into a group based on the field’s
value.
FIG 7.41 : Groups
Click to select the field which user want to group their data. User may not
see this page of the wizard if they are selecting data from a single table.
Click Next. The Report Wizard moves to the next page.
Page | 46
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.42: Groups in Report Wizard
Click a field user want to group by.
Click the right-arrow to select a field; click a field and then click the left
arrow to deselect a field. Use the up and down arrows to change the order
of the groupings.
Click Next. The Report Wizard moves to the next page.
Page | 47
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Sort and summarize
User can create up to four levels of sort by using the Report Wizard.
FIG 7.43: Sort and Summarize
Click the down-arrow and then select the field user want to sort by.
Click the button to choose ascending or descending order. Clicking the
button toggles between Ascending and Descending. User can sort up to
four levels.
Click the Summary Options button. The Summary Options window
appears.
Page | 48
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.44: Steps to sort and summarize
Click to select the summary data user want.
Click to select whether user want detail and summary data or if user want
summary data only.
Click if user want to calculate the percent to the total for sums.
Click OK. The Summary Options window closes.
Click Next. The Report Wizard moves to the next page.
Layout and orientation
User can choose the layout and orientation of their report.
The layout determines where the each fields appears on the page.
MS-Access provides three options for layout to choose
Stepped
Block
Outline
Orientation determines whether MS-Access creates the report in portrait or
landscape.
Page | 49
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.45: Layout and orientation
Click to select a layout.
Click to select a page orientation.
Choose the Adjust The Field Width So All Fields Fit On A Page option if
user want all fields to fit on a single page.
Click Next. The Report Wizard moves to the next page.
Page | 50
E-Content of
IT TOOLS AND BUSINESS SYSTEM
Style
MS-Access supplies predesigned styles that format titles, labels, and more.
When user choose a style, the left side of the window displays a preview.
FIG 7.46: To select style
Click to select a style.
Click Next. The Report Wizard moves to the next page.
Create a title
On the final page of the Report Wizard, user can title their report. The title
appears at the top of the report and on the Navigation pane.
Page | 51
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.47: Create a title
Type the title user want to give the report.
Click Finish. MS-Access creates, saves, and opens their report in Layout
view.
Page | 52
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.48: Example Report
Modify a Report
After creation of a report, user can modify it by adding groups or sorting, adding
fields, change labels and can perform many other tasks.
User can view a report in Report view, Layout view, design view and Print
Preview.
Change to Layout view
Open the report.
Activate the Home tab.
Click the View button. A menu appears.
Click Layout View. MS-Access changes to Layout view.
Page | 53
E-Content of
IT TOOLS AND BUSINESS SYSTEM
To change the Size of a Field or Label
FIG 7.49: Change the size of a field or label
Click the field or label. A border appears around it.
Click a side of the border and drag outward to increase the width. Click a side
of the border and drag inward to decrease the width.
Add a Group or Sort
Use Group & Sort button on the Design tab to create a group or sort.
To Group or Sort
FIG 7.50: Add a group or sort
Page | 54
E-Content of
IT TOOLS AND BUSINESS SYSTEM
FIG 7.51: Add a Sort
Click Add A Group and then select the field by which user want to group.
MS-Access groups and sorts the field.
FIG 7.52: Add a group
Click Add A Sort and then select the field on which user want to sort. MS-
Access sorts the field.
Page | 55
E-Content of
IT TOOLS AND BUSINESS SYSTEM
To add a field
FIG 7.53: Add Existing Fields
Open the report in Layout view.
Activate the Design tab.
Click the Add Existing Fields button in the Tools group. The Field List pane
appears.
Click Show All Tables if the field that user want to add does not appear.
FIG 7.54: Drag a field into report
Click the field user want to add and drag it onto their report.
A thick line appears on the report. MS-Access places the field before the line.
If user want the field to appear in the detail area, be sure to drag it to the
detail area.
To delete a field
Open the report in Layout view.
Click the field user want to delete.
Press the Delete key. MS-Access deletes the field.
Page | 56
E-Content of
IT TOOLS AND BUSINESS SYSTEM
To move a column
Open the report in Layout view.
Click the column label.
Drag the column to the new location. MS-Access moves the column to the
new location.
To change a title
Open the report in Layout view.
Double-click in the Title field.
Click and drag to select the current title.
Type a new title.
Click anywhere outside the Title field. MS-Access changes the title.
To change a field label
Open the report in Layout view.
Double-click the field label.
Click and drag to select the label name.
Type the new label name.
Click anywhere outside the label. MS-Access changed the field label..
To add page numbers
Open the report in Layout view.
Activate the Design tab.
Click the Insert Page Number button. The Page Numbers dialog box appears.
Click a radio button to choose a format.
Click a radio button to choose a position.
Click the down-arrow in the Alignment field and then choose an alignment.
Deselect Show Number On First Page if user do not want the page number to
appear on the first page.
Click OK. MS-Access places the page number in their report.
Page | 57
