MS-Access Database Management System Class 10 Computer Science 

MS-Access Database Management System

Introduction to Database Management System

Every organization has to maintain data concerning their activities, business, transaction, employees, etc. For example, your school maintains data regarding a student's general information, academic performance, punctuality, and discipline. These data need to be kept in such a way that they can be readily available and can be presented easily in desired formats. The data also needs to be updated and appended regularly. Actually, these data are manually maintained in various files in most cases.

As you can notice in the below Table - Telephone directory, it has information distributed under the headings, such as S. No. Name, Address, Telephone Number. Furthermore, the entire information is alphabetically organized for easy retrieval.

Table - Telephone Directory

S. No.	Name	Address	Telephone No.
1.	Vishal Singh	Lumbini, Nepal	042-236457
2.	Manoj Sahani	Sundi, Nepal	071-673847
3.	Santosh Devkota	Butwal, Nepal	011-774729

Well-organized information as above is a database. In other words, a collection of systematically organized inter-related data is called a database. 

Data and Information

Raw form of any facts, figures, or entities are known as data. Data alone does not give any meaning. For example, Aaradhya, 1000, account, balance, etc. are raw data individually does not give any meaning.

The processed form of data that gives meaning is known as information. When the data becomes information it gives meaningful results. For example, Aaradhya has a 1000 balance in his bank account. Here Aaradhya, 1000, account, balance all have their significant meaning. Thus, we can say that information is the organized collection of inter-related data.

Database

Database is a collection of data related to a particular subject or purpose. It is an organized collection of data in a systematic manner. A database organizes the data in an easily accessible manner. The database is used to store, organize and retrieve data. Database can be computerized and non-computerize (manual). A database may have a single table or multiple tables. The data in a database are organized in rows and columns. Some examples of databases are the Telephone directory, mark ledger, attendance register, dictionary, etc.

 

Database Management System (DBMS)

Database Management System

Database management system (DBMS) is a computerized system that stores data processes them and provides information in an organized form. DBMS is a system or software that allows us to create, organize, modify, and manage databases and retrieve information. DBMS basically deals with the creation of a database, its management, and the retrieval process. Creating, modifying, updating, appending, organizing, sorting, removing, and retrieving are the major tasks performed easily, efficiently, and accurately using a database management system. Some of the popular DBMS software are MS-Access, Oracle, MS-SQL Server, MySQL, PostgreSQL etc.

These DBMS are capable to handle records in multiple tables on the basis of a key field. It allows a user to view or retrieve records from the multiple linked tables continuously at a time.

Features of DBMS

The following are the features of a DBMS:

l   Large the volume of data can be stored and updated easily.

l   Provides data integrity and security.

l   Easy in data administration or data management.

l   Provides the data sharing facility.

l   Reduces data redundancy (duplication of data).

l   Provides concurrent access recovers the data from the crashes.

l    Supports centralized control

 

 

Advantages of DBMS

Some of the major advantages of DBMS are listed below:

1. Simple to create and maintain a huge amount of data.
2. Better Data Transferring
3. Better Data Security
4. Better data integration
5. Minimized Data Inconsistency
6. Faster data Access
7. Better decision-making
8. Increased end-user productivity 

 

Creating Database Using Microsoft Access

An Introduction to Microsoft Access

MS-Access is DBMS software developed by Microsoft Corporation. This software is distributed along with the Microsoft Office application package. Microsoft Access 2019 is the latest version available in the market.

The Access database consists of several different components. Each component is called an object. Access consists of seven objects. The various objects of MS-Access are Tables, Queries, Forms, Reports, Pages, Macros, and Modules. All objects of a database are stored in a single file, and the extension of a database file is .accdb.

Main features of MS-Access

a)             Creates sophisticated databases quickly.

b)            Analyze and modify your data easily with queries.

c)             Creates elaborated reports from your data.

d)            Makes customized data entry forms.

e)             Presents your data dynamically on the WWW (World Wide Web).

Objects of MS-Access

Tables

All databases must have at least one table. Tables are the primary building block of the database. All data are stored and managed in a table. Every table in a database focuses on one specific subject. Table stores a large volume of data into rows and columns.

Queries

A query is simply the question that we ask about the data stored in the table. Query is also used to perform various actions on the data. For example, "Show the names of students who have scored greater than or equal to 80 marks in science." "Update the salary of employees by 10%."

Forms

Forms are the graphical interface used to enter data into the tables or multiple linked tables. Forms are also used to display the information in a specific manner, as well as it permits a user to add, modify and delete data in a table. Primarily, fields in the table or query are made available to place in the forms that we create.

Reports

Reports are the presentation of information in the desired format. Reports are generally created for the printing purpose of any desired information. With Access, we can create reports of any table or query. For example, the report card of your final examination.

Data Types in MS-Access

Data Type	Description	Maximum Size	Default Field Size
Short Text	Used for text or combinations of text and numbers, as well as numbers those don't require calculations, such as phone numbers.	Up to 255 Characters	255
Long Text	Lengthy text or combinations of text and numbers.	Up to 64,000 Characters	-
Number	Used for data to be included in mathematical calculations.	1,2,4 or 8 Bytes	Long Integer
-Byte	Stores numbers from 0 to 255 without decimals.	1 byte
-Integer	Stores numbers from -32768 to +32768 without decimals.	2 bytes
-Long Integer	Stores numbers from -2147483648 to +2147483647 without decimals.	4 bytes
-Single	Single precision floating-point numbers from -3.4 x 10-38 to 3.4 x 1038.	4 bytes, 7 decimal places
-Double	Double precision floating point numbers from -1.797 x 10-308 to 1.797 x 10308.	8 bytes, 15 decimal places
Date/Time	Used for dates and time data.	8 Bytes	-
Currency	Used for currency values.	8 Bytes	-
Auto Number	Used for unique sequential (incrementing by 1).	4 Bytes	-
Yes/No	Used for data that can be only one of two possible values, such as Yes/No.	1 Bit	-
OLE Object	Used for OLE objects (such as MSWord documents, MS-Excel spreadsheets, pictures, sounds).	Up to about 2 GB	-
Hyperlink	Used for hyperlinks.	Up to 2048 Characters	-
Attachments	To attach any supported type of file	Up to about 2 GB	-
Lookup Wizard	Used to create a field that allows you to choose a value from another table	Dependent on the data type of the lookup field	-

 

Starting MS-Access

Follow the below steps to run MS-Access:

 

     Step 1:        Press Windows Key and R at the same time. Run window appears.

     Step 2:        Type ‘msaccess’ as shown in the figure above.

     Step 3:       Finally click on OK button.

Creating a new database file

When MS-Access is started, it gives the user a choice of either creating a new database or opening an existing database. Follow the below steps to create a new database file:

Step 1: Click on the Blank desktop database.

 

A blank desktop database dialog box will appear.

Step 2:        Type the File Name.

Step 3:        Choose the required folder where you want to save.

Step 4:        Click on Create button.

Creating Table in MS-Access

Follow the below steps to create a table:

          

     Step 1:       From Create tab, click on Table Design button of Tables group.

     Step 2:        Add the required Field Name and Data Type.

 

     Step 3:       Right click on the Table1 tab in the above figure.

     Step 4:       Type Table name. Click on OK.

Note: A field name can be up 40 characters long.

Adding Fields in a table

Follow the below steps to add a field in an existing table:

Step 1:        Open the database and open the table.

Step 2:       Right click on the field above which you want to add a new field.

 

Step 3:      Click on Insert Rows. A new blank field is inserted above the selected field.

Step 4:       Type Field Name and Data Type.

Deleting Fields in a table 

Follow the below steps to add a field in an existing table:

Step 1:        Open the database and open the table.

Step 2:        Right click on the field which you want to delete.

Step 3:        Click on Delete Rows.

Defining Data types

The data type determines the kind of values that you can store in the field. After you enter the valid field name, it allows a user to select the data type for the field. You can use the Data Type property to specify the type of data stored in a table field such as text for the name field, a number for the class field, Date and time for DOB field, etc. Each field can store data consisting of only a single data type. MS-Access supports the following data types:

Field Description

You can enter the description of each field in the Description column. It helps you to remember the use and purpose of a particular field. This is an optional part in a database.

It is displayed in the status bar when you select this field on a form.

 

Primary Key

Primary Key is a special field or group of fields in the table that uniquely identifies each record from the database. To distinguish one record from another, the table must contain a unique field named as the primary key. The primary key does not accept duplicate values for a field and it does not allow a user to leave the field blank or null. The primary key is an identifier such as a student ID, a Product code, Exam roll no. etc. Hence, a primary key is unique to each record.

Importance of Primary Key

l   To identify each record of a table uniquely.

l   To reduce and control duplication of the record in a table.

l   To set the relationship between tables.

Setting the Primary Key

Follow the below steps to create a primary key field:

Step 1: Open the table in Design View

Step 2: Click on the field in which you want to apply the primary key.

Step 3: Click on Design Tab

Step 4: Click on the Primary Key button from the Tools group.  A key icon will be displayed on the left side of the primary key

Foreign Key

A foreign key is a field in a child table that refers to the primary key of a master table. It is required for setting relationships between tables. A foreign key allows us to store duplicate data but does not allows us to leave the field blank.

Field Properties Pane

The field properties pane displays a list of properties associated with each field data type. To control the contents of a field we can set the field size, format, validation rule, etc.

The general properties of fields are given below:

Field Properties of Number

Field Properties of  Short Text

 

1. Field Size

You can use the Field Size property to set the maximum size for data stored in the field that is set to the Text or Number data type.

For Text data, the amount of data that you can store can be determined by the number of characters you wish to store on it. The maximum is 255 characters.

For numeric data, the field size can be set depending upon the type and range of value you wish to store. The below table explains about the numeric data range and storage.

 

Field Size Setting	Range of Values	Decimal Places	Storage Size (in Bytes)
Byte	0 to 255	None	1
Integer	-32768 to 32767	None	2
Long Integer	2147483646 to 2147483647	None	4
Single	-3.4 x 10-38 to 3.4 x 1038	7	4
Double	-1.797 x 10-308 to 1.797 x 10308	15	8

 

2. Caption

Caption field property is the alternative name given for any field. This helps to make the field name more explanatory. The maximum size for this is 2048 characters. It is also a label for a field when used on a form. If the caption is not entered, the field name is used for a label for a field on a form.

3. Default Value

Default Value field property is one that is displayed automatically for the field when you add a new record to the table.

4. Format

Format field property allows you to display data in a format different from the way it is actually stored in a table. It is also known as a display layout for a field. Depending on the type of data, the option in the Format menu will differ.

The format setting for text and memo fields are listed below:

Symbol	Effects	Format setting	Entered As	Displays
<	Converts all characters to lowercase	<	NEPAL	nepal
>	Converts all characters to uppercase	>	nepal	NEPAL
@	Indicates a character or a space is required	@@@-@@-@@@	12345678	123-45-678
$	Right aligns the text in Column	$	NEPAL	NEPAL
&	Indicates text character is optional	&&-&&-&&	789087	78-90-87

 

You can format Number and Currency data with predefined formats or can create your own format using special formatting symbols. The predefined formats for Number and Currency field data types are as below:

Setting	Effects	Entered As	Displays
General Number	Displays number as entered. Default for number fields	123.456	123.456
Currency	Displays number with currency symbol and thousands separators. Default for currency fields	1235.789	$1,235.79
Euro	Displays number with Euro currency symbol and thousands separator	1234.789	€1,234.79
Fixed	Displays at least one digit	132.789	132.79
Standard	Displays thousand separator	2456.789	2,456.79
Percent	Displays values multiplied by 100 with the added percent sign	2	200.00%
Scientific	Uses Standard scientific notation with exponents	123	1.23E+02

The custom Number and Currency formats also use special characters to indicate how you want the values to appear. The custom Number and Currency formatting characters are given as below:

Symbol	Effects
. (period)	Indicates the decimal point that separates the parts of a number.
, (comma)	Used as the thousands separator
0	A digit that displays a digit if one is there, or if none are there, displays zero
#	A digit placeholder that displays a digit if one is there, or if none are there, closes up the adjoining digits
$	Displays a dollar sign
%	Value is multiplied by 100 and a perfect sign is added
E- or e- or E+ or e+	Used to display numbers in scientific notation

The following are examples of using format string with number fields.

Format String	Entered	Displays
$#, ##0.00	4532.57	$4,532.57
$#, ##0.00	0	$0.00
##.00%	1.235	123.50%
##.00%	0	.00

5. Input Mask

Input mask field property specifies the pattern of data that you wish to enter into the specific field. It is simply the control over the data entry. This field property does a couple of tasks in a field. They are:

l   It forces you to enter field values in a specified pattern.

l   It can fill in constant characters like dash (-), parenthesis ( ), and slash (/) and optionally store these characters as part of the field value as you do not have to enter these characters at the time of data entry.

6. Validation Rule

It is used to limit the values that can be entered into a field. Data validation in various cases are explained in the below table.

Expression	Example	Description
Validation expression for numbers
<	<100	Entered value must be less than 100.
>	>50	Entered value must be greater than 50.
<>	<>0	Entered value must be not equal to 0.
<=	<=100	Entered value must be less than or equal to 100.
>=	>=50	Entered value must be less than or equal to 50.
=	=10	Entered value must be equal to 10.
Between .... and ...	Between 0 and 100	Entered value must be within the range of 0 to 100.
Validation expression for dates
	< # 1/30/2012 #	Entered date must be before January 30, 2012.
	> # 1/30/2010 5:30 PM #	Entered date must be after January 30, 2010 and after 5:30 PM.
	<= #1/30/2012 #	Entered date must be before or on January 30, 2012.
	>= # 1/30/2010	Entered date must be after or on January 30, 2010.
	> Date()	Entered date must be after the current date.
	< Date()	Entered date must be before the current date.
	> Now()	Entered date must be today after the current time or any other day in future.
	< Now()	Entered date must be today before the current time or any other day in past.
Validation expression for texts
Like	Like "B*"	Here * represents zero or more characters. It checks that the value starts with B (or b), followed by zero or more characters.
	Like "*up"	It checks whether text ends with characters "up".
	Like "?????UP"	Here? represents one character. It accepts 7 characters and ends with UP.
	Like "####CSX	Here # also represents one character but it is number. It accepts 7 characters and ends with CSX and preceded by five numbers.
	Like "[BO]????"	It accepts 5 characters and first character is either B or O.
	Like "[A-Z] [A- Z] [A-Z] [A-Z]	It accepts four characters (Only letters). [A-Z] represents characters ranges from A to Z.
Validation expression with Boolean operators
AND	>=10 AND <=50	Entered value must be within the range of 10 to 50
OR	>500 OR <100	Entered value must be either greater than 500 or less than 100.

7. Validation Text

Validation Text is the error message that appears if the data entered is invalid according to the specified validation rule.

Validation Rule for section field "A" or "B"

Validation Text Message Box

8. Required

You can use the required property to specify whether a value is required in a field or not. So, Yes and No are the options for the required property. If this property is set to Yes for a field, the field must receive value during data entry. If this property is set to No for a field, the field can be left blank.

9. Indexed

You can use the Indexed property to set an index on a field. It speeds up the searching and sorting of records based on a field. For example, if you search for a specific student name in a SName field, you can create an index for this field to speed up the search for the specific name. By default, the indexed property is set as No. It also uses the other two settings.

Table - Indexed property settings

Settings	Description
No	No indexing
Yes (Duplicates OK)	The index allows duplicates.
Yes (No Duplicates)	The index does not allow duplicates.

Entering and Editing Data

Table Datasheet and its Formatting

Table datasheet is simply the display of records in a row and column format. Using the datasheet

view, you can add, modify, search or delete records. There are mainly two views of Table.

a)            Design View – Related with table structure. You can add, edit or delete fields and its properties.

b)            Datasheet View – Related with records. You can add, modify, search or delete records.

Switching to Datasheet View:

Follow the below steps to switch from Design View to Datasheet View:

Switching to Datasheet View 

Step 1:        Click on the Design Tab.

Step 2:         Click on View drop-down button from Tools

group.

Step 3:        Click on Datasheet View.

Adding Records in a Datasheet

After the table is created through the design view option, you open it in the datasheet to enter the records. Follow the below steps to add records in the datasheet:

Step 1:	After you, open the datasheet mouse cursor will be placed in the first field of the first record. Type the required data in the field.
Step 2:	Press Tab or Arrow to move to the next field. Or, simply place the mouse cursor in the desired field.
Step 3:	While entering the data in a field, next row for the new record will be

automatically displayed. For example, if you are entering the record in the first row, the second row automatically appears. Repeat the above steps to enter the data in fields till required.

Step 4:       After finishing the adding records in the datasheet, you can close the

datasheet simply by clicking on the close button.

Adding records in a datasheet 

 

Modify Records in a Datasheet

You can open the existing table and can modify the data in the datasheet. Follow the below steps to modify records in the datasheet:

     Step 1:        Open the table in Datasheet View.

     Step 2:        Put the mouse cursor in the desired cell.

     Step 3:        Type a new value or edit the existing value of the desired cell.

Delete Records from a Datasheet

Delete records from the datasheet

You can remove the unwanted records of the datasheet. Follow the below steps to delete records in datasheet:

Step 1: Open the table in a Datasheet View.

   Step 2: Right-click on the square box left to the record which you want to delete. A pop-up menu will appear.

Step 3: Click on the Delete Record option. A message box will appear.

Step 4: Click on Yes.

Hiding Columns or fields

Hiding the columns or fields

You can temporarily hide a column(s) or field(s) so that you can view more columns in a larger datasheet. Follow the below steps to hide fields:

Step 1:  Right-click on the column heading (field name) which you want to hide. A pop-up menu will appear.

Step 2: Click on the Hide Fields option.

Unhide the hidden fields

Unhiding the hidden fields

Follow the below steps to unhide the hidden columns:

Step 1:        Open the table in Datasheet View.

Step 2:        Right-Click on any Column heading. A pop-up                           menu will appear.

Step 3:        Click on Unhide Fields option. An Unhide Columns dialog box will appear with the list of fields.

Step 4:        Click on the check box of required filed to unhide.

Step 5:        Click on Close.

Freeze Columns

Freeze columns

You can freeze one or more columns on a datasheet so that they become the leftmost columns and are visible at all times no matter wherever you scroll. Follow the below steps to freeze column(s):

Step 1: Open the table in Datasheet View.

Step 2: Right-Click on the column heading which you want to freeze. A pop-up menu will appear.

Step 3: Click on the Freeze Fields option.

Unfreeze Columns

You can unfreeze columns on a datasheet. Follow the below steps to unfreeze column(s):

     Step 1:        Right-Click on the column heading. A pop-up menu will appear.

     Step 2:        Click on Unfreeze All Fields option.

Adjusting Column Width

In the datasheet, we can adjust the column width as required to fit the data in the field. In some cases, the field might contain the longer data that might not be easily visible and in other case field might contain the short data that taking unnecessary longer width. So, to improve from this inefficient view of the datasheet we have to adjust the column width.

This can be done in the following ways.

Method 1

By dragging the field sizing line from the right-most corner of the field header.

Method 2

By double-clicking on the field sizing line.                                                            

Method 3

By setting the fixed width in a column width dialog box.

Step 1:        Right-Click on the header of a column.

Step 2:        Click on the Field Width option. A Column Width dialog box will appear.

Step 3:        Type the required width value.

Step 4:        Click on OK.

Adjusting Row Height

In the datasheet, we can also adjust the height of the row as required to fit the records in the row. This can be done in the below-mentioned two ways.

Method 1

By dragging the row sizing line from the bottom-most corner of the row.

Method 2

By setting the row height in a Row Height dialog box.

Step 1:     Right-Click on the square box left to the required row. A pop-up menu will appear.

Step 2:        Select Row Height option. A Row Height dialog box

will appear.

Step 3:        Type the required row height.

Step 4:        Click on OK.

Sorting Records

The process of arranging all the records in a table either ascending or descending order based on-field or fields is known as sorting. It is the arrangement of records on the basis of a field or a group of fields. MS- Access automatically sorts records by the value in the primary key field.

Sorting Record

l   Text types of data are sorted in alphabetical order (i.e. A to Z or Z to A).

l   Date and Time data are sorted from oldest to newest or newest to oldest.

l   Number/Currency types of data are sorted in smallest to largest or largest to smallest.

Follow the below steps to sort data of Short Text field:

Step 1:        Open the table in Datasheet View.

Step 2:      Right-Click on the column heading of a field that you want to sort. A pop-up menu will appear.

Step 3:        Click on Sort A to Z option. 

The data will be sorted in alphabetical order.

 

       

Before shorting

After shorting

                                         

Field with Memo, Hyperlink and OLE Objects data types cannot be sorted. After the sorting process, you need to save the table to keep changes.

Filtering Records

From a large number of records, you need to find a record or a group of records from the table which satisfies search criteria as you specify. The process of viewing the required records of a table on the basis of specified criteria is known as filtering. In MS-Access you can search a record by using the Find command. But Find command does not allow you to specify the search criteria for searching more than one record at a time. Filter command and query help us to find more than one record according to search criteria.  

Querying Database

 1. Query

Query is the question asked for the database. Information stored in any database becomes meaningful only when you will be able to retrieve the desired information. So, using the various types of queries you can retrieve the information that you desire with some specific purpose. So, a query is an object of a database that is used to view, retrieve, change and analyze records from a table or multiple linked tables based on specified conditions.

Using a query, you can answer very specific questions about your data that would be difficult to answer by looking at table data directly. You can use queries to filter your data, to perform calculations with your data, and summarize your data.

There are two main types of Query used in Access.

Select Query: This type of query serve data from the table as per the user’s request

without making any change in the underlying data.

Action Query: This type of query changes data in the source table. The examples of Action Query are Update Query, Delete Query and Append Query.

a)      Select Query

A select query is simply used to select and display the relevant data from the database. You can select all or part of data from a single or multiple tables or existing queries and display in the datasheet. You can also use a select query to group records and calculate sums, counts, averages, minimum, maximum, and other types of totals.

Creating a Select Query

Follow the below steps to create a select query:

Step 1:        Open the database file created in MS-Access.

Step 2:        Click on Create tab.

Step 3:        Click on the Query Design button from the Queries group. QBE (Query By Example) grid will appear. Also, a Show Table dialog box will appear with the list of tables and queries created in the database.

Step 4:        Select the required table based on which you are creating the query. Click on Add.

Step 5:        Click on the Close button.

QBE Grid

The QBE grid appears with two sections. The upper section shows the list of fields in the selected table and the lower section shows the grid with rows and columns where we define the query.

The first row in the grid shows the fields that we have selected from the table.

The second row shows the table from which the field is selected.

The third row is ‘Sort’. We can select ‘Ascending’, ‘Descending’ or ‘Not sorted’ for sort. If we set the sorting option (ascending or descending) for more than one field then the left-most field for which sorting is enabled becomes the primary sort key and other sorting enabled fields subsequently become the secondary sort keys.

Show row has a check box for each field. The fields that are checked at this row are displayed and those not checked are not displayed.

The fifth row is ‘Criteria’. Here, we can provide criteria or conditions to select the records from the table.

The last row is ‘or’. Here, we provide the criteria which are tested with OR logical operator with the above condition or criteria mentioned in the row ‘Criteria’.

Now, do the following tasks in the QBE grid:

Step 1:        First you need to bring the required field of the table in the grid. We can drag

or double-click each field to bring them in the grid. So, bring the required field of the selected table from the upper section of QBE.

Step 2:       Choose the fields and select either Ascending or Descending from the Sort row, if you want                     to display the result in sorted order of any field.

Step 3:      Remove the tick (√) mark from the checkbox of the Show row, if you don’t

want to show field(s).

Step 4:       Set the criteria in the Criteria row. In this example, “10” is written in the Class

field and “A” is written in the Section field. It means only the records of Class 10A are selected in the query.

 

Running the Query

After making a query, you need to run. Follow the below steps to run a query:

 Step 1: Click on the Design tab under Query Tools.

 Step 2: Click on the Run button from the Results group. A new datasheet will be displayed based on the criteria and other settings we provided in the query.

Result of the above query

Details Table- Data source for the query

Results

 

Calculation in Select Query

As mentioned earlier, a select query can be used for mathematical calculation and display the result in a new field. Let’s explore the below example to understand how to perform this.

Step 1:        Create a below table in MS-Access and named as “Marks”

Datasheet View- Table Marks

Design View- Table Marks

 

Step 2:	To create a query, click on Create tab and click on the Query Design button from the Queries group.
Step 3:	Select the table “Marks” from the Show Table dialog box. Click on Add and then Close.
Step 4:	Bring all the fields of “Marks” in the QBE grid.
Step 5:	In the QBE grid, type a new field name “Total” next to the field “Computer” and use the below expression to calculate Total marks.
	Total: [English] + [Math] + [Computer]

 

     Step 6:          Run the query. You will see the new column with calculated total marks.

 

     b)     Action Query

An action query is a query that makes changes to or removes many records in just one operation. Here, we discuss only two types of action query. Below are the four different types of action queries.

     i)       Update Query

An update query makes entire changes to a record or group of records in one or more tables. For example, you can increase the salary of all staff by 10% from a single operation in the staff database. With an update query, you can change data in existing tables.

Let’s explore the below example to understand how to perform this.

Step 1:       Create a below table in MS-Access and named as “Staff”

Design View-Table Staff

Datasheet View-Table staff

Step 2:        To create a query, click on Create tab and click on Query Design button

from Queries group.

Step 3:	Select the table “Staff” from the Show Table dialog box. Click on Add and then Close.
Step 4:	From the Design tab, select Update type from Query Type group.

 

Step 5:	As we are going to update the value of Salary field only, bring the field “Salary”  of “Staff” in the QBE grid by double-clicking on it.
Step 6:	Type the below expression in the “Update To” row:
	[Salary]*1.1
Step 7:	Run the query.
Step 8:	A message box will appear for your confirmation. Click on Yes. And see

the below updated result in the table “Staff”.

Before Delete- Table Staff

 

After update- Table Staff

ii) Delete Query

A delete query deletes a record or group of records from one or more tables. For example, you can delete the records of those students who have got marks less than 40 in each subject in the marks database.

Let’s explore the below example to understand how to perform this.

Step 1:	Open the database file that has the table “Marks”
Step 2:	To create a query, click on Create tab and click on Query Design button from Queries group.
Step 3:	Select the table “Marks” from the Show Table dialog box. Click on Add and then Close.
Step 4:	From the Design tab, select Delete type from Query Type group.

 

     Step 5:  You need to give criteria to select records in order to erase. In this example,

we are erasing the records of those students who have got marks less than 40 in each subject. So, we have to apply conditions in all the fields of subjects. Bring all the subject fields in the QBE grid by double-clicking on them.

     Step 6:   Provide conditions in the Criteria row as in below figure.

 

Note: Here, the condition <40 for each subject is given in the different lines of criteria to use OR operator. If you write the condition in the same line of Criteria row AND operator will be applied.

     Step 7:       A message box will appear for your confirmation. Click on Yes. And open

the table to check whether the records are deleted or not.

Before Delete-Table Marks

After Delete-Table Marks

 

 iii) Inserting records into table from queries

Using a query, you can insert a record into the desired table. Let’s explore the below example to learn how to insert new records using SQL statements.

SQL (Structured Query Language) is a language used to communicate with databases. We can use several SQL statements to do different tasks with your database. Here, the INSERT statement is used to insert the record into a table.

Follow the below steps to insert a new record using INSERT SQL statement:

Step 1: Let’s assume you have already created the below table “Marks”. If not, create this table first                     where you are going to insert a new record using query.

Step 2:	Click Create tab and click Query Design from Queries Group.
Step 3:	Close the Show Table window.
Step 4:	From the Design tab, click on the SQL View button in the Results group. A SQL View window will appear.
Step 5:	Type the below INSERT statement in the SQL View window as below

figure.

insert into Marks values (6,'Rabin Shahi',78,54,32) ;

In the above INSERT statement,

Marks                                 –  Name of the table

6,'Rabin Shahi',78,54,32  –    Values for 5 fields (Text field value is enclosed within

                                                single quote 'Rabin Shahi')

Step 6:        Run the query. A message will appear for your confirmation.

Step 7:        Click Yes and open your table to see new records.

 

In the above table, one new record is added.

Wildcard Characters and Operators in Query

Wildcards/ Operators	Example	Description
?	?????UP	It represents a single character. ?????UP shows the records from the specified field that has first any five character before UP.
*/	S* / Like S*	Represents the number of characters. S* retrieves the record from the field that starts from the character S.
#		Represents any single digit, 0-9.
>	>100	Shows the record where the specified field is greater than 100.
<	<200	Shows the record where the specified field is less than 200.
>=	>=100	Shows the record where the specified field is greater than or equal to 100.
<=	<=200	Shows the record where the specified field is less than or equal to 200.
<>	<>50	Shows the record where the specified field is not equal to 50.
Between.... And	Between #02/04/10# And #04/07/12#	Shows the record between 02/04/10 to 04/07/12 date.
In	In("Ram", "Hari")	Shows the record that has the field value as Ram or Hari.
AND	>60 And <=80	Shows the record that has specific value from 61 to 80
OR	"Bus" OR "Car"	Shows the record that has specific value Bus or car.
Is Null		Shows the record that has no value.
IS not Null		Shows the record that has no null (blank) value.

Creating and Using Forms

Forms

Form is one of the MS-Access database objects that is primarily used to create an interface for entering data in a table or multiple linked tables. Forms are basically GUI (Graphical Users Interface) using which users interact with the MS-Access database. Form also displays complete records one at a time, so you can view and modify records using the form.

Creating form by using the wizard

The wizard enables you to select the fields from a table or multiple linked tables, layout, and user interface for the form. Follow the below steps to create forms using the wizard option:

Step 1:        Open the Access database file.

Step 2:        Click on Create tab.

Step 3:        Click on the Form Wizard button from the Forms group. Form Wizard dialog box will appear.

Step 4:        Choose the required table from the Tables/Queries                         drop-down list.

 

Step 5:     A list of fields of the selected table will be displayed. Now, you have to select the fields that you want to keep in the form from the Available Fields box. To select fields as per the requirement, click on the single " > " arrow button to select a single field or you can also select all the available fields at once by clicking on a double arrow ">>" button. After selecting fields click on the Next button.

     Step 6:      Select the desired layout. In this example, Columnar is chosen. Click Next.

     Step 7:       Type the title of the form. In this example, Marks_Forms is typed.

     Step 8:        Click on the Finish button.

Now, the form for the Marks table is created as below:

 

Entering Data Using a Form

After the form is created, you can enter the data simply typing in the text box. MS-Access form also contains the various control options while entering the data. These control options are, Text box, Combo box, List box, Check box, Label, Command button, Tab control, Image, hyperlink etc. Steps to add records in form datasheet are as follows:

l   After you open form datasheet, mouse cursor will be placed in the first field of first record. Type the required data in the field.

l   Press Tab or enter to move to the next field. Or, simply place the mouse cursor in the desired field.

l   When you finish entering all the data for the first record, MS-Access automatically prompts for entering the second record and so on.

 

Creating and Printing Reports

Report

Report is one of the MS-Access database objects ued to present information in an effective and organized format that is ready for printing. Using the report, you can display the information the way you want to view it. A report's record source refers to the fields in the underlying tables and queries. A report need not contain all the fields from each of the tables or queries that it is based on. Creating a report is very similar to creating a form.

Creating Report by using Wizard

The wizard enables you to create a report in the desired format. The Report Wizard provides you with more flexibility such as you 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 your report.

Follow the below steps to create a report using Wizard:

Step 1: Open the Access database file.

Step 2: Click on Create tab.

Step 3: Click on Report Wizard button from Reports group. Report Wizard dialog box will appear.

Step 4: Choose the required table from the Tables/Queries drop-down list.

 

Step 5: A list of fields of the selected table will be displayed. Now, you have to select

the fields that you want to keep in the form from the Available Fields box. To select fields as per the requirement, click on the single arrow ">" button to select a single field or you can also select all the available fields at once by clicking on the double ">>" button. After selecting fields click on the Next button.

Step 6: On the next wizard screen, you can further group records in the report by a particular field. To group by a field, click the field and then click the arrow button. You can select several grouping levels in the order you want them.

Then click Next to move on.

 

Step 7: The wizard then asks whether you would like to sort the records in the report as in the below figure If you want to sort the records by a particular field or fields, open the top drop-down list and select a field by which to sort. Click Next to move on.

Step 8: On the next wizard, select the required Layout and Page Orientation. Click Next.

Step 9: Type the title of the report. In this example, Marks_Report is typed.

Step 10: Click on the Finish button.

Now, the Report for the Marks table is created as below:

 

Viewing and Printing Reports in Print Preview

When you create a report with Report Wizard option, the report appears in Print Preview (as shown in the above figure). From there, you can print the report if it is ok or go to Report Design view to make changes. In the Print Preview mode, you can zoom in and out on the report using the Zoom tool (click once to zoom in and click again to zoom out). Using the appropriate button on the Print Preview toolbar, you can also display the report as one page, two pages, or multiple pages.

 MS-Access Database Management System Important Question Answer