CBSE Notes for Class 8 Computer in Action – Advanced Features of Access 2013
In the previous chapter, we learnt that Access is a RDBMS software. Tables, Forms, Queries and Reports are the important objects that can be created in an Access database. In real life scenarios, hundreds of records are stored in the database tables. Access objects such as Queries, Forms and Reports provide a convenient and easy way to enter, search, analyse and display the large amounts of data stored in the database tables. Queries let us quickly extract data from one or more tables based on the given conditions. Forms and Reports let us enter or display data in a user friendly manner. Let us learn more about these objects.
QUERY
A query is used to retrieve selective data based on specific criteria from one or more tables. For example, you may design a query to display names of all students who have secured more than 90 percent or a query to find out details of customers who made a purchase in the past one month. Let us learn to create simple queries in Access.
Creating a Query
Just like tables, there is a Query Design view in which queries are created and a Query Datasheet view in which results of the query are displayed. Let us create a query based on table Student to filter the details of students whose percentage is more than 90.
To create a Query, follow the given steps.
Step 1: Click on the Create tab (Fig. 3.1).
Step 2: Click on the Query Design option in the Queries group. The Show Table dialog box appears (Fig. 3.2).
Step 3: Choose the table on which you want to base your query and click on the Add button. After adding the table, click on the Close button to close the Show Table dialog box. The selected table appears in a small window in the upper section of the Query Design View. The lower section of the Query Design View displays the Design Grid (Fig. 3.3). In this example, click and add the Doctor table.
Step 4: Add the fields to be used in the query in the Design Grid by following any one of the following.
- Double-click on the field name in the small table window in the upper section of the Query Design View.
OR - Drag and drop fields from the small table window in the upper section to the Field column in the Design Grid.
In this example, add the D_Id, Name, Hospital, Fee and Specialisation fields to the Design Grid.
Step 5: Type the search condition in the Criteria row of the field on the basis of which you want to choose or filter the records (Fig. 3.4).
In this example, type the condition >600 in the Criteria row of the Fee field. .
Step 6: Clear the Show checkbox for fields that should not appear in the Query Result.
Step 7: If you want the Query results to be sorted, then select Ascending or Descending order from the Sort box under that field. In this example, select Ascending order in the Sort box under the Fee field.
Step 8: Click on the Save option in the Quick Access Toolbar or the Save option on the File tab to save the Query. You will notice that the Query object gets added to the Navigation Pane (Fig. 3.5).
Step 9: Click on the Run option in the Results group on the Design tab to execute the query. The query results appear in the Datasheet View, which looks like a table. You will notice that the query displays the details of all doctors with fee more than ?600 arranged in ascending order of the Fee field.
Creating a Query with Multiple Conditions
Let us learn to create queries with more than one condition.
- Specifying the condition in the Criteria row for more than one field will result in a query that will select only those records that meet all the conditions.
Consider the following queries based on the table Doctor (Fig. 3.1).
Example 1:
The following query shows the records for which the Fee<600 and the Hospital is Heal Well Hospital.
Example 2:
The following query shows the records for which the Fee>=600 and the Hospital is Heal Well Hospital and the Specialisation is Nephrologist.
- Specifying the first condition in the Criteria row and other conditions in the Or row and the rows beneath it results in a query that meets one or more of the specified conditions.
Example 1:
The following query shows the records for which the Fee<600 or the Hospital is Heal Well Hospital.
Example 2:
The following query shows the records for which the Fee>=600 or the Hospital is Heal Well Hospital or the Specialisation is Nephrologist.
FORM
A form provides an interface that allows users to enter, change, and view the data in a database tables. Forms are made up of elements such as textboxes, labels and so on. These elements are known as controls.
You can work on a form in three views (Fig. 3.11).
- Form View :
: You can enter, edit, and view data in Form view. - Layout View:
:You can view the data and make changes in the form such as change the size of various controls. - Design View:
: Design view displays only the structure of your form. In this view, you cannot see the underlying data, but you can perform some tasks in Design view that you cannot perform in Layout view such as view the Header and Footer.
Creating a Form
Follow these steps to create a form.
Step 1: Click on the table in the Navigation Pane on which the form should be based (Fig. 3.12).
Step 2: Click on the Create tab.
Step 3: Click on the Form option in the Forms group. A form is created and it opens in the Layout View. You can change the view to the Form view and enter or display data in the table. Three new tabs—Design, Arrange and Format appear in the Ribbon.
Step 4: Click on the Save option in the Quick Access Toolbar or the Save option on the File tab to save the form. You will notice that the Form object gets added to the Navigation Pane (Fig. 3.13).
Formatting a Form
You can change the appearance of the form using the various options on the Design and Format tabs. Let us use some of these options.
Using the Design tab
You can add a logo and title to your report by making use of Logo and Title option in the Header/Footer group on the Design tab (Fig. 3.14 and Fig. 3.15).
Using the Format tab
You can format the font (size, colour, alignment), add a background image, change the colour of shapes and so on. using the options on the Format tab (Fig. 3.16 and Fig. 3.17).
REPORT
Reports are used to present data from tables or queries in a format that can be printed. Printouts of report cards, invoices are examples of reports. You cannot make changes to the data in a report. You can work on a report in four views.
- Report View
: You can view data in this view but cannot make any design related changes. - Print Preview
: This view displays the report as it will appear after printing. - Design View
:Design view displays only the structure of your report. In this view you cannot see the underlying data, but you can perform some tasks in Design view that you cannot perform in Layout view such as view the Header and Footer sections. - Layout View
: You can view the data and make changes in the report such as change the size of various controls.
Creating a Report
Follow these steps to create a report.
Step 1: Click on the table or the query for which you want to create a report (Fig. 3.18).
Step 2: Click on the Create tab.
Step 3: Click on the Report option in the Reports group.
Step 4: The report is created and opens in the Layout view. Four new tabs—Design, Arrange, Format and Page Setup appear in the Ribbon
Step 5:
Click on the Save option in the Quick Access Toolbar or the Save option on the File tab to save the report. You will notice that the Report object gets added to the Navigation Pane.
Formatting a Report
Just like a form, you can format a report using the options on the Design and Format tab. Figure 3.20 shows a report that has been formatted using the options discussed under Formatting a Form section.
Printing Reports
You can print reports using the Print option under the File tab or by using the Print Preview view of a Report. This view shows how the report will appear on a page. It also lets us choose the page orientation, page setup and so on.
Step 1: Click the down arrow under View option in the Views group on the Home tab or the Design tab.
Step 2: Click on the Print Preview option. The report is displayed in the manner in which it would appear on a page.
Step 3: Choose appropriate values for Page Size, Margin and Orientation from the Ribbon.
Step 4: Click on the Print command to print the report.
