CBSE Notes for Class 10 Foundation of Information Technology – Microsoft Access

CBSE Notes for Class 10 Foundation of Information Technology – Microsoft Access

Foundation of IT

Microsoft Access is the relational database management system found in the Microsoft Office suite. It allows users to create interactive database, where they can manage data such as inventory, budgets, incoming and outgoing assets, payrolls etc. Once you have prepared the database, you can add, modify or delete the data into the database, table or even in records. MS-Access offers wizards to help users to create Tables, Queries, Forms and Reports, along with a set of predefined table definitions for tracking Assets, Customers, Sales Orders, Invoices and much more. It gives you more efficient way of handling data than a spreadsheet.

e.g. consider a database for a library, which will provide access to resource across a wide spectrum of topic and subject areas. It provides information such as title, author, source, publisher, date of publication etc.

STARTING MICROSOFT ACCESS

To start Microsoft Access, you need to perform the steps which are as follows:
Step 1 – Click the Start button.
Step 2 – Click All Programs option.
Step 3 – Select Microsoft Office —> Microsoft Office Access 2007 option from the menu.

The Getting Started with Microsoft Office Access page of database wizard will appear. Using this wizard, you can create a new blank database, open an existing database and also can view Featured Online Templates.

Creating a Database
MS-Access provides two ways to create a database, which are as follows:

1. Creating a Blank Database
To create a new blank database, you need to perform the steps which are as follows:
Step 1 – Start Microsoft Access 2007, then Getting Started with Microsoft Office Access page will appear.

Step 2 – Click on Blank Database icon, the Blank Database pane will appear at the right hand side as shown in the below figure:

Create a Microsoft Office Access database that does not contain any existing data or objects.
Step 3 – Type a name for the database in the File Name: box. If you do not give a file name extension, Access automatically adds the default extension .accdb.
To change the location of the file from the default, click on Browse () icon for changing the location.
Step 4 – Click Create button.
Access creates the database with an empty table named Table 1, which will open in Datasheet View.
The cursor is placed in the first empty cell in the Add New Field column as shown in following figure:

2. Creating a Database using Templates
Template is a complete tracking application with predefined tables, forms, reports, queries, macros and relationships. Each template creates a complete end-to-end solution that you can use either with no modification or customize to suit your business needs. Steps to create a database using templates are as follows:

Step 1 – Start Microsoft Access 2007, then Getting Started with Microsoft Office Access page will appear.
Step 2 – Several featured templates are displayed in the middle of the page, click the template that you want to use. You can also download additional templates from the Office Online Website.
Step 3 – Type a name for your database in the File Name: box.
Step 4 – Click Create (or Download, for an Office Online template).

Download a Template from Office Online
If you can not find a template that fits your needs on the Getting Started with Microsoft Office Access page then, follow these steps:

Step 1 – Click Templates under More on Office Online on the Getting Started with Microsoft Office Access page.
Step 2 – Use the Office Online navigation and search tools for finding the templates that you want to use in your database. After that, the template is designed to open a data entry form so that, you can start entering data immediately.
MS-Access 2007 includes a collection of database templates, which are as follows:

• Assets It is used to create an assets database to keep track of assets, including asset details and owners.
• Contacts It is used to create a contacts database to manage information about people such as customers, partners etc.
• Issues It is used to create an issues database to manage a set of issues or problems. You can assign priority and follow the progress of issues from start to finish.
• Events It is used to create an events database for tracking, upcoming meetings, deadlines and other important events.
• Marketing projects It is used to create a marketing projects database to track time-sensitive deliverable and vendor status for projects.
• Projects It is used to create a projects tracking database to track multiple projects and assign tasks to different people.
• Sales pipeline It is used to create a sales pipeline database to track the progress of prospective sales within a small group of sales professionals.
• Tasks It is used to create a tasks database to track a group of work items that you or your team need to complete.
• Faculty It is used to create a faculty database to keep track of information about faculty members.
• Students is used to create a students database to keep information about students and their details.

Opening an Existing Database
In MS-Access, you can open an existing database in two ways, i.e. by using the Getting Started with Microsoft Office Access or by using the Microsoft Office button ().

1. By using Getting Started with Microsoft Office Access
This method-is used to open a recently used database quickly. The steps to open an existing database by using Getting Started with Microsoft Office Access are as follows:
Step 1 – Start Microsoft Access 2007.
Step 2 – Now, see the Open Recent Database pane on the right hand side of the page, which provides a link, i.e. More… and a list of recent used databases as shown in given figure:

Step 3 – If the given list contains that database which you want to open, then click the name of that database.
Otherwise, click on More… then, the Open dialog box will appear as shown below:

In this dialog box, enter the database name in the File name: box and then, click Open button.

2. By using Microsoft Office Button
This method is used when we are already working with a database and want to go on another database. The steps to open an existing database by using Microsoft Office button are as follows:

Step 1 – Click the Microsoft Office button () and then, click Open button.
Step 2 – Open dialog box will appear.
Step 3 – In this dialog box, click the drive or folder that contains the database which you want to open.
Step 4 – When you find the database, select it and then, click Open button.

MS-ACCESS RIBBON

The Office Ribbon is the primary replacement for menus and toolbars. It provides the main command interface in Microsoft Office 2007. When you open a database, the Ribbon appears at the top of the main Microsoft Office 2007 window.

The Ribbon contains a series of command tabs. In MS-Access 2007, the main command tabs are as follows:

• Home
• Create
• External Data
• Database Tools
• Datasheet

The following table shows a representative sampling of the tabs and the commands available on each tab:

Command Tab	Common Things You Can Do
Home	Select a different view from the Views group. Copy, cut and paste from the Clipboard group. Set the current font characteristics and alignment from the Font group. From Rich Text group apply formatting to a memo field. Work with Records (New, Save, Delete, Totals, Spelling, More). Sort & Filter the records. Find the records.
Create	Create a new blank table. Create a new table using a Table Templates. Create a list on a SharePoint Lists. Create a new form based on the active table or query. Create a new PivotChart. Create a new report based on the active table or query. Create a new query, macro, module or class module.
External Data	Import and Export data. Collect data via E-mail. Work with online SharePoint Lists. Saved imports and exports.
Database Tools	Launch the Visual Basic editor and run a macro. Create and view table relationships. Show/Hide object dependencies or the property sheet. Run the Database Documenter or analyse performance and table. Move data to Microsoft SQL Server or an Access (Tables only) database. Run the Linked Table Manager. Encrypt database with Password. Manage Access Add-ins.
Datasheet	Provides a datasheet, pivottable, pivotchart and design view. Manages fields and columns of the table. Set the data type and formatting. Create table relationships and object dependencies.

CHECK POINT

1. What are the steps to start MS-Access 2007?
2. An Access database file save with  extension.
3. Event database template is used for which purpose?
4. Name the ways through which database can be created.
5. What are the tasks that can be performed by database tools?

ELEMENTS OF DATABASE (MS-ACCESS)

A database stores information in an organised way that makes it easy to get information in an effective way. In MS-Access, you can create various elements for database operations. The Create command tab on the top of the window screen provides access to these elements in the database. These elements are as follows:

• Tables
• Queries
• Forms
• Reports
• Macros
• Modules

You can hide or view the objects in the navigation pane, if required.

The tool (⇓) double down arrow is used to view the objects, whereas the tool (⇑) double up arrow is used to hide the objects.
In this chapter, we will discuss only tables and queries.

Table
In MS-Access 2007, tables are used to store the data. It represents data in tabular format, i.e. in rows (records) and columns (fields). The table structure depends on the type of information that you want the table should contain.
The structure of a table includes table components which are as follows:

• Field Name
• Field Type or Data Type
• Field Length or Field Size
• Field Properties
• Primary key

Field Name
It is a label provided for a field that specifies the type of information contained in a particular field. Two fields should not have the same field name (i.e. field name should be unique).
e.g. a field name CONTACT_NO, would contain the mobile number or landline number of the student.

Field Type (Data Type)
It specifies the type of data that is stored in the field. A field can store various types of data such as textual data and numerical data or combination of both.
In MS-Access, data types can be categorized into the following types:

Data Types	Description
Text	Allows to store text or combination of text and numbers, as well as numbers that don’t require calculations such as phone numbers. Also, it is a default data type.
Memo	Allows to store long blocks of text that use text formatting.
Number	Holds numeric values which are used for calculations. It includes various types such as Byte, Integer, Long Integer, (Single, Double) Replication ID and Decimal.
Date Time	Allows to store date and time value for the year 100 to 9999.
Currency	Allows to store monetary values that can be used in calculations. Accurate upto 15 digits on L.H.S. and 4 digits on R.H.S. of decimal point.
AutoNumber	Allows to store numbers that are automatically generated for each record. It increases the number automatically when you add records.
Yes/No	Allows boolean value. (i.e. one of two possible values)
OLE Object	OLE is an acronym for Object Linking Embedding. It can store objects such as a video clip, a picture, Word document or any other binary data.
Hyperlink	Allows to store hyperlinks such as E-mail addresses.
Attachment	Allows to store files such as digital photos. Multiple files can be attached per record.
Lookup Wizard…	Lets you type a list of options, which can be chosen from a drop down list.

Field Length (Field Size)
It refers to the maximum number of characters that a field can contain. Each character requires one byte for its storage.
Field length is of two types which are as follows:

1. Fixed length field It is a type of field length in which the number of characters you enter in a field is fixed. These are present in Format option in Data Type Formatting group (in Datasheet tab) such as Currency, Euro, Percent etc.
2. Variable length field In this type of field length, the number of characters is not fixed. Actually, the number of characters of the data entered in the field decide the field length.

The field length or field size of each data type are as follows:

Data Type	Field length or Field size	Data Type	Field length or Field size
Text	0-255 characters	Memo	0-65,536 characters
Number	1,2, 4, 8 or 16 bytes	DateTime	8 bytes
Currency	8 bytes	AutoNumber	4 or 16 bytes
Yes/No	1 bit (Oor 1)	OLE Object	Upto 2 GB
Hyperlink	Each part contains upto 2048 characters	Lookup Wizard…	4 bytes

Field Properties
After you create a field and set its data types, you can also set additional field properties. Setting field properties can give the database extra flexibility and functionality.
Some important properties of field are as follows:

Primary Key
It is used to uniquely identify the records in a table, e.g. the primary key of a table EMPLOYEE, which stores the data about employees of an organisation, can be EMPID because two employees can not have the same EMPID.

Rule A condition that must be meet before the data is accepted into the database.Text, Memo, Number, DateTime, Currency, Yes/No, Hyperlink.

Field Property Description		Supported by
Field Size	It limits the number of characters that can be typed into a field.	AutoNumber, Text, Number.
Format	It customises the way that text or numbers are displayed and printed.	Text, Memo, Number, DateTime, Currency, Auto Number, Yes/No, Hyperlink.
Input Mask	It sets up the field to receive data in a particular format.	Text, Number, DateTime, Currency.
Caption	It adds a label to the field to assist the user.	All data types.
Default Value	Sets a value to appear in the field before you enter any data. It is used to avoid the type.	Text, Memo, Number, Date/ Time, Currency, Yes/No, Hyperlink.
Validation Text	This appears if a validation rule is not satisfied.	Same as validation rule.
Required	Makes data entry compulsory, so that field can not be left blank.	Text, Memo, Number, DateTime, Currency, OLE Object, Hyperlink, Attachment.
Indexed	To speed up queries, sorting and grouping operations or to prevent users from entering duplicate values.	Text, Memo, Numbers, DateTime, Currency, Auto Number, Yes/No, Hyperlink.
Smart Tags	Attaches smart tag to the field.	Text, Memo, Number, DateTime, Currency, AutoNumber, Hyperlink.
Text Align	Specifies the alignment (i.e. general, left, center etc.) of text within a control.	Text, Memo, Number, DateTime, Currency AutoNumber, Yes/No, OLE Object, Hyperlink.

Set Field as a Primary Key
Steps to assign field as a primary key are as follows:
Step 1 – Select the field that you want to set a primary key.
Step 2 – Click the Primary Key button from Tools group (under the Design tab), or Right click and select Primary Key.
Step 3 – A Key icon ( ) indicates which field contains the primary key.

Remove a Primary Key
Steps to remove- a primary key are as follows:
Step 1 – Click the field that has been assigned as a primary key.
Step 2 – Click the Primary Key button from Tools group (under Design tab), or Right click and select Primary Key.

Creating a Table in MS-Access
In MS-Access 2007, two types of views are available to create a table as follows:
Datasheet View It provides a visual way to create a table. It is a simple view which arranges the data in rows and columns and allows to edit the data, but not allows to change the format of the database, other than minor changes (such as insert or delete columns).
Design View It allows you to create or change the table. You can set or change every available properties for each field and can open existing tables in Design View, add, remove or change fields.

1. To Create a Table using Datasheet View
Steps to create a table using the Datasheet View are as follows:
Step 1 – Open the database.
Step 2 – Go to Create tab in the Ribbon and select the Table command.
Step 3 – After clicking at the Table command, a Datasheet tab will appear as shown below:

Step 4 – In the above figure, the field ID is automatically generated that is of datatype AutoNumber. Due to which, its values are automatically generated for each record and field ID uniquely identifies the record in a table.

Steps to add a new field are as follows:
Step 1 – Double click the Add New Field column label.
Or
Right click the Add New Field column label.
A menu will appear, click on Rename Column option.
Step 2 – Type the Field name.
Step 3 – Press Enter.
Step 4 – Type the next field name. Continue, until you have created all of the fields in your table.

Steps to explicitly assign a data type to a field areas are as follows:
Step 1 – Click the Field label for the field which you want to assign a data type.
Step 2 – Activate the Datasheet tab.
Step 3 – Click drop down arrow next to the Data Type field from Data Type & Formatting group and then, choose a data type.

Note :
By default data type consists as a Text.

Make Changes in a Datasheet View
Some of the changes made in a table from a Datasheet View window are as follows:

Insert a Field
Steps to add a field into a table are as follows:
Step 1 – Right click on the table and click Datasheet View.
Step 2 – To insert a new column, click the Insert in Datasheet tab or Right click the header (Field Name) of the field that follows the new field.
Step 3 – Click at Insert Column from the menu.

Delete a Field
Steps to delete a field from a table are as follows:
Step 1 – To delete the column, click Delete in Datasheet tab.
Or
Right click the field name of the field, you want to delete.
Step 2 – Click at Delete Column from the menu.
Step 3 – Now, MS-Access will display a prompt message to confirm that you want to delete the field(s) or not?
Step 4 – Click on Yes button to permanently delete the field(s).

Rename a Field
Steps to rename a field into a table are as follows:
Step 1 – Just double click the Field Name to highlight it.
Or
Right click the Field Name that you want to change and click at Rename Column from menu.
Or
Select on Datasheet tab and click on Rename option from Fields & Column group.
Step 2 – Click the Field Name if you want to edit or just start typing to replace the name completely.

Editing Data Into a Table
Steps to edit data into a table are as follows:
Step 1 – Open table in a Datasheet View.
Step 2 – Select the cell containing the data that you want to edit.
Step 3 – Type the new value for the selected cell and save the table.

Hide a Column
You can temporarily hide a column in a Datasheet View such that you can view other columns in a large datasheet.
Steps to hide a column into a table are as follows:
Step 1 – Right click at the Field Name of the field that you want to hide.
Step 2 – Click Hide Columns from the menu.

Freeze Columns
Freezing a field means that no matter how far you scroll in a datasheet, you will always see the frozen field on the left side pane.
Steps for freezing column(s) in a table are as follows:
Step 1 – Right click at the Field Name of the field that you want to freeze.
Step 2 – Click Freeze Columns from the menu.

Unfreeze All Columns
Steps to unfreeze all columns in a table are as follows:
Step 1 – Right click at the Field Name of any field.
Step 2 – Click Unfreeze All Columns from the menu.

2. To Create a Table using Design View
Steps to create a table using the Design View are as follows:
Step 1 – Open the database.
Step 2 – Go to Create tab in the Ribbon and select the Table Design command.
Step 3 – After clicking at the Table Design command, a Design tab and a field entry area at middle with field property area will appear at bottom as shown in the following figure:

Note :
The field entry area is used for entering field’s name, data type and description. This description is optional and the field properties pane is used for entering more details for each field, i.e. field size, validation rule etc. The table needs to be opened in Design View to access the field properties.

Steps to add a new field are as follows:
Step 1 – Click on the first cell in the Field Name column and type the field name.
Step 2 – Press Enter. The neighboring cell in the Data Type column is selected. To select the data type, click the dropdown arrow to the right of the Data Type field and select an alternative data type.
Step 3 – Press Enter,. A cell in the Description column will be selected. Enter a description, if required.
Step 4 – Press Enter and repeat the above process for other fields.

Steps to set the field properties are as follows:
Step 1 – Click once on a Field Name for which you want to set the field property.
Step 2 – The Field Properties will appear at the bottom of the screen as shown in the following figure:
Step 3 – Click the General tab.

Step 4 – Choose the property and click on the dropdown list next to it.
Step 5 – The application of a property depends on its type. You may need to type a value, click a button and choose from a list of options or click a button and use a wizard to develop the property.

Make Changes in a Design View
The Design View of a table can be modified by the following changes:

Insert a Field
Steps to insert a field in a table are as follows:
Step 1 – Select the field, before which you want to insert a new field.
Step 2 – To insert a new field, click at the Insert Rows in the Design tab.
Or
Right click at the selected field and click at the Insert Rows option from the context menu.
Step 3 – A new field is added to the table design thus, you can enter field name, data type and description.

Delete a Field
Steps to delete a field from a table are as follows:
Step 1 – Select the field that you want to delete.
Step 2 – Click at the Delete Rows command in the Design tab.
Or
Right click at the selected field and choose the option Delete Rows from context menu.
Step 3 – Now, MS-Access will display a prompt message to confirm that you want to delete the field(s) or not.
Step 4 – Click on Yes button to permanently delete the field(s).

Rename a Field
You can change a field name by placing the cursor on the field, double click on it and type the new name.

Naming and Saving of a Table
Steps to save a table are as follows:
Step 1 – To save the table, click the Save button at the top of the screen or click the Office button and select Save. The Save As dialog box will appear as shown in following figure:

Step 2 – Type the name that you want to give to your table in Table Name: text box.
Step 3 – Click OK.
Step 4 – The table will be listed in the Tables window on the left hand side.

Note:
To save changes, you can also use keyboard shortcut Ctrl + S key.

Entering/Removing Record into/from a Table
Once a table has been created, the field and its properties are defined, you can start to enter the records. This is done in a Datasheet View. If you create table in Design View, you need to switch to the Datasheet View to enter records.

Insert a Record
Steps to insert a record in a Datasheet View are as follows:
Step 1 – When you create a table, a new blank record automatically appears in the second row of the table
Or
If you enter data in the last record, a new blank record will automatically appear at the end of the table.
Step 2 – Type data into the fields.
Step 3 – When you have finished adding records in the datasheet, save it and close it.

Delete an Existing Record
Steps to delete an existing record are as follows:
Step 1 – Select the row which you want to delete.
Step 2 – Right click on the row and select the Delete Record from context menu.
Step 3 – Now, MS-Access will display a prompt message to confirm that you want to delete the record(s) or not.

Step 4 – Click on Yes button to permanently delete the record(s).

Opening an Existing Table
Existing table can be opened by two ways which are as follows:

1. In Datasheet View To open a table in a Datasheet View, follow these steps:
Step 1 – Find a table in the All Access Objects list (the left hand window).
Step 2 – Right click on a table and select Datasheet View. Datasheet View represents the data in a table.
Or
Select Home tab and click the View dropdown arrow and select the Datasheet View.

2. In Design View To open a table in a Design View, follow these steps:

Step 1 – Find a table in the All Access Objects list (the left hand window).
Step 2 – Right click on the table and select Design View. Design View represents the structure of the table.
Or
Select the Home tab and click the View dropdown arrow and select the Design View^T.

Renaming and Deleting a Table
Steps to rename a table are as follows:
Step 1 – Find a table in the All Access Objects list (the left hand window).
Step 2 – Right click on a table and select Rename.
Note:
If you are working on this table, then it will display a prompt message that you can’t rename the database object, ‘Tablet while its open. So firstly close this table.
Step 3 – Now, type a new name for the table.
Step 4 – Press Enter from the keyboard.

Steps to delete a table are as follows:
Step 1 – Find a table in the All Access Objects list.
Step 2 – Right click on a table and select Delete.
Step 3 – Now, MS-Access will display the prompt message to confirm that you want to delete the table or not.
Step 4 – Click on Yes button to delete a table with its contents.

Navigation in a Table
It is required to move or to navigate any record for the purpose of checking it from time to time.
You can navigate to any record in the table using the Record Navigation buttons provided in MS-Access.
The Record Navigation buttons are located at the bottom of the table when it is opened in a Datasheet View, as shown in the following figure:

The following table shows the task of each navigation button:

Query
Queries are the basis of power in a database. It is a way to get specific information from the database. They give us the ability to ask questions, record the questions for later and to take actions on the answers.

To Create Query using a Design View
MS-Office Access 2007 provides you functionality to create a query by using a Design View.
Steps to create a query through a Design View are as follows:
Step 1 – Open the Create tab from the Ribbon and select the Query Design command from Other group.
Step 2 – The Show Table dialog box will appear as shown in the following figure:

Step 3 – Select a table in the Tables tab and click the Add button to add a table.
Step 4 – The selected table is being displayed as shown in the following figure. You can click the Close button to close the Show Table dialog box.

Step 5 – Edit the Field row and other rows.
Step 6 –  Save the query by using shortcut Ctrl+ S key. The Save As dialog box will appear and then, click OK button.

Step 7 – Select the Design tab, click Run from Results group .
Step 8 – The query will display the output in a tabular form.

CHECK POINT

1. Which type of terms are specified by field name?
2. Memo allows to store…………… of text.
3. OLE can store objects such as a video clip, picture etc. Thus, OLE stands for…………….
4. What are the steps to remove a primary key?
5. When user click on Query Design command tab, which dialog box will be appear on the screen?

• Wizard is a computer utility designed for describing a step-by-step U-approach in making software easier for the user to achieve a specific objective such as choosing an option and clicking button.
• Currency data type provides various types such as General Number, Currency, Euro, Fixed, Standard, Percent, Scientific.
• You should generally use the smallest possible length setting because smaller data sizes can be processed faster and requires less memory and disk space.
• In all the fractional value data types, you can set the field to accept a number up to several decimal places by using Decimal Places property.
• For numeric data, the field size depends on the field setting, which includes byte, integer, long integer, single, double etc.
• To switch between views, right click on the name at the top of the table and select the view that is required.
• Integer data type takes 2 bytes to store a value. It has two types-single and double.
• MS-Access also provides the SQL view for look up, edit or delete the queries. For this, right click on the Query tab and select SQL View.

SHORTCUT KEYS FOR MS-ACCESS

Working with Database Object

Ctrl + N	Create a new database
Ctrl + O	Open an existing database
Alt + N	Create a new database object
Alt + O	Open a database object
Ctrl + S	Save a database object
Ctrl + P	Print the current or selected object
Ctrl + C	Copy the selected object
Ctrl + X	Cut the selected object
Ctrl + V	Paste the selected object
Delete	Delete an object

Working with Tables

Ctrl + Plus sign(+)	Add a new record
Ctrl + Semicolon(;)	Insert the current date
Ctrl + Shift + Colon(:)	Insert the current time
Ctrl + Alt + Spacebar	Insert the default value for a field
Ctrl + Apostrophe(‘)	Insert the value from the same field
	in the previous record
Ctrl + A	Select all records
Ctrl + Minus sign (-)	Delete the current record
Esc	Undo changes made to the current field

Common Tasks

Ctrl + B	Bold letters
Ctrl + I	Italic letters
Ctrl + U	Underline letters
Ctrl + F	Find text
Ctrl + H	Replace Text
F5	Refresh
F2	Rename
Ctrl + Y	Redo Last Action
Ctrl’ + Z	Undo Last Action
Ctrl + W	Close the active Window
F1	Open Microsoft Access help
F7	Check spelling

Navigation In a Table

Shift + Tab	Previous Field
Page Down	Next Screen
Page Up	Previous Screen
Ctrl + T	First Record
Ctrl + 4-	Last Record
i	Next Record
T	Previous Record

Design View

Alt + Enter	Display a property sheet in a Design View
Alt + V + P	Open pivotchart In a Design View