Class 10 - Information Technology
Chapter 4 - Microsoft Access
Top Block 1
Very Short Answer Type Questions
Question :1. What is the default extension of a MS-Access database?
Answer :
The default extension of a MS-Access database is .accdb.
Question : 2. How NUMBER and DATE/TIME type fields are different in Access? Give any one difference.
Answer :
NUMBER data type contains numeric values, while DATE/TIME type field contains date and time values.
Question : 3. Give five names of database templates used in MS-Access 2007.
Answer :
Assets, Contacts, Events, Faculty and Issues.
Question : 4. What happens when you enter numbers in a Text data type field? CBSE 2008
Answer :
Numbers are accepted as a text values.
Question : 5. Name the data type that should be used to store Student’s Admission numbers. [Examples of Admission number; S100, S101, S102] CBSE 2008
Answer :
Data type should be Text.
Question : 6. Give the names of five data types used in MS-Access.
Answer :
Text, Memo, Date/Time, Yes/No and Currency.
Question : 7. What happens when text is entered in a Number type field? CBSE 2005
Answer :
When we enter text in a Number field and press Enter or press Tab key, MS-Access displays a message that “The value you entered does not match the Number data type in this column.”
Question : 8. Is it necessary to define a field type for a field in MS-Access?
Answer :
Yes, it is necessary to define a type of a field while entering data into a field.
Question : 9. Write one example of data for which Memo data type is used. CBSE 2008
Answer :
To store resume of an employee in Employee table is an example of Memo data type.
Question : 10. Which property should be set, if it is required that ‘Delhi’ is automatically entered in the city field when a new record is created? CBSE 2008
Answer :
Default Value property should be set.
Question : 11. Which field property sets limits on the data that is entered? CBSE 2006
Answer :
Fixed Field Length or Field Size property sets the limits on the data entered.
Question : 12. For which type of field, “Default Value” property is not applicable? CBSE 2007
Answer :
Attachment, AutoNumber, OLE Object.
Question : 13. What is the use of AutoNumber type field used in MS-Access?
or
Define the AutoNumber data type in MS- Access. CBSE 2006
Answer :
AutoNumber data type starts with a number for the first record and it automatically increases the number by a set of amount for each record.
Question : 14. Which field property specifies whether the field can be left empty when entering data into a record? CBSE 2005
Answer :
Required property
Question : 15. What are two areas of the Design View of the table? CBSE 2006
Answer :
Field description and Field properties.
Question : 16. What happens, if you do not enter a caption for a field?
Answer :
In this case, Field Name is used as a label.
Question : 17. If the data type of a field is set to Text, what will be its number range?
Answer :
Its range will be 0 to 255.
Question : 18. What are the views in which fields can be deleted in MS-Access? CBSE 2006
Answer :
Datasheet View and Design View.
Question : 19. What is Fixed Field Length?
Answer :
It refers to a type of field length in which the number of characters you enter will be fixed.
Question : 20. What are the commands contained by Create tab?
Answer :
The Create tab has various commands such as Table Design, Table Templates, Pivotchart, Form, Report, Macro and Query Wizard.
Short Answer Type Questions
Question : 1. How will you create a blank database in MS-Access 2007?
Answer :
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.
To change the location of the file from the default, click on Browse (Sir ) 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:
Mddle block 1
Question : 2. Explain field length with its types.
Answer :
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:
Question : 3. What are Datasheet View and Design View?
Answer :
In MS-Access 2007, two types of views are available to create a table as follows:
1. 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).
2. 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.
Question : 4. Write down the steps to insert a new row into the table.
or
How can you add a record in the table in Datasheet View?
Answer :
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.
Question : 5. How can you delete an Access table from a database?
Answer :
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.
Question : 6. When Memo data type is preferred over Text type for a field? CBSE 2008
Answer :
When the length of the field is more than 255 characters. Text data type is not capable to store the project description because its length can not be more than 255 characters so, Memo data type is preferred over Text data type.
Question : 7. Distinguish between Text and Memo data types.
or
How are field types Text and Memo different from each other? Explain with the help of an example.
Answer :
Distinguishing between Text and Memo are as follows:
| Text | Memo |
|---|---|
| It is used for relatively short entries. | It is used for long text paragraphs. |
| It can store upto 255 characters only. | It can store upto 65536 characters. |
| It uses field size property to control the number of characters. | It does not use field size property. |
| e.g. Emp_name | e.g. Emp_description |
Question : 8. When is AutoNumber data type preferred over Number data type? CBSE 2008
Answer :
When you want to increment the number automatically as you add or delete the records. AutoNumber data type is preferred. Also, if the table does not have a primary key, then AutoNumber uniquely identifies the record.
Question : 9. A text field is initially 40 characters long and one record has an entry in this field in MS-Access database. The field value contains 28 characters, including spaces. Now, you have reduced the length of the field to 20 characters. What will happen to the field value?
Answer :
Since, the new field length is 20 characters long. So, this field will contain only left 20 characters including spaces and the remaining data will be discarded. Thus, the data from the right side of the field will be lost.
Question : 10. Write one example of data field for which you would set the Required property to Yes? CBSE 2008
Answer :
In a table, when we declare a field as a primary key, then the field’s Required property must be set to Yes because in a primary key field, we need to enter data always.
Question : 11. What is the purpose of Default Value field property? CBSE 2007, 05
Answer :
If there is a situation when you want to enter same value for all records. Then, to avoid typing the same thing many times, you can set as a Default Value property.
Question : 12. What do you mean by the Entry Required field information?
Answer :
Entry Required field decides whether entering data in the field is necessary or not. So, if Entry Required is set to Yes, then that field must have some value in it. By default, Entry Required is set to Yes.
Question : 13. What is a primary key? How do we set a primary key?
or
Define primary key. How do you set it in MS-Access? CBSE 2006
Answer :
Primary key is the key which is used to uniquely identify the records into the table. To set the primary key you have to follow steps given below:
Step 1 Select the field that you want to set as a primary key.
Step 2 Click Primary Key button from the Design tab.
or
Right click on the selected field and select Primary Key.
Question : 14. Give any one difference between freezing a field and hiding a field. CBSE 2003, 02
Answer :
When you freeze a field, then no matter how far you scroll down in a datasheet, you will always see the frozen field. But, when you hide a field, then it means that field is not displayed on the screen.
Long Answer Type Questions
Question : 1. What is database template? Give the description of five templates, used in Microsoft Access 2007.
Answer :
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 customise to suit your business needs.
MS-Access 2007 includes a collection of database templates, which are as follows:
1. Assets It is used to create an assets database to keep track of assets, including asset details and owners.
2. Contacts It is used to create a contacts database to manage information about people such as customers, partners etc.
3. 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.
4. Events It is used to create an events database for tracking, upcoming meetings, deadlines and other important events.
5. Marketing projects It is used to create a marketing projects database to track time-sensitive deliverable and vendor status for projects.
6. Projects It is used to create a projects tracking database to track multiple projects and assign tasks to different people.
Question : 2. Explain MS-Access Ribbon.
Answer :
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
| 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 Pivot Chart. • 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, pivot table, pivot chart and design view. • Manages fields and columns of the table. • Set the data type and formatting. • Create table relationships and object dependencies. |
Question : 3. Write one example of each field, for which you would use
1. Text data type
2. Memo data type
Answer :
1. Text data type It allows to store text or combination of text and numbers as well as numbers that don’t require calculations such as phone number. This data type allows maximum 255 characters to store.
e.g. if Employee is a table and Emp_No, Name and Description are fields, then name will be a Text field. Because, name is a character entry field.
2. Memo data type It allows long blocks of text that uses text formatting, e.g. in the Employee table, the field Description will be of Memo data type, because the length of description of employee may be large.
Question : 4. Distinguish between Number and AutoNumber data type field. Give example of each.
Answer :
Distinguishing between Number and AutoNumber data
| Number | Auto Number |
|---|---|
| It holds numeric values which are used for calculations. | It allows to store numbers that are automatically generated for each record. |
| The field length of this data type is 1,2, 4, 8 or 16 bytes. | The field length of this data type is 4 bytes. |
| It does not support field property New values. | It supports the field property New values. |
| e.g. in a table Employee, Salary is a Number type field. | e.g. in a table Bank, the Account No is a AutoNumber field as, for every new customer, this field will automatically increase and will provide a new number. |
Question : 5. Which field properties are used in Office Access 2007? Give the purpose of each field property.
Answer :
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:
| 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, Date Time, Currency, Auto Number, Yes/No, Hyperlink. |
| Input Mask | It sets up the field to receive data in a particular format. | Text, Number, Date Time, 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 Rule | A condition that must be meet before the data is accepted into the database. | 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, Date/Time, 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, Date/Time, Currency, Auto Number, Yes/No, Hyperlink. |
| Smart Tags | Attaches smart tag to the field. | Text, Memo, Number, Date/Time, Currency, Auto mber, Hyperlink. |
| Text Align | Specifies the alignment (i.e. general, left, center etc.) of text within a control. | Text, Memo, Number, Date/Time, Currency AutoNumber, Yes/No, OLE Object, Hyperlink. |
Application Oriented Questions
Question : 1. Pronita is a programmer in an institution and is asked to handle the records containing personal information of the teachers teaching in the institution.
Use the information to give the answer of the following questions (1) to (3).
1. Suggest five held names, which you think must be present in the table Teacherlnfo and why?
2. Give the data type of the suggested fields.
3. Which held is most likely to be the key field and why?
Answer :
Suggested five fields are as follows:
1. TCode —→ To store the code of the teachers.
TName —→ To store the name of the teachers.
TSubject —→ To store the name of subjects for which the teacher is placed in the institution.
Address —→ To store the address of the teachers.
Salary —→ To store the monthly income of the teachers.
2. TCode —→ Text
TName —→ Text
TSubject —→ Text
Address —→ Text
Salary —→ Number
3. TCode is most likely to be the key field because it has a unique value for each of the records.
Question : 2. The following table named Summer Camp shows part of the information kept on children attending the summer camp in Shimla.
| ChildID | FirstName | LastName | DOB | Gender | Group |
|---|---|---|---|---|---|
| 109 | Vedansh | Gupta | 30/06/10 | M | 2A |
| 214 | Mack | Tyagi | 09/12/08 | M | 1B |
| 115 | Aditi | Thakur | 13/04/09 | F | 1A |
| 108 | Vikrant | Chauhan | 02/02/09 | M | 2A |
| 141 | Swati | Saini | 06/05/10 | F | 2B |
| 233 | Vishakha | Tyagi | 01/08/10 | F | 3A |
| 274 | Madhur | Gupta | 06/03/10 | M | 3B |
Answer the following questions:
1. State the number of fields in’the above table.
2. State the number of records in the above table.
3. Give one reason why Summer Camp would add a record.
4. Give one reason why Summer Camp would edit a record.
5. Give one reason why Summer Camp would delete a record.
6. Give any two data types used in the table.
Answer :
1. Number of fields-6
2. Number of records-7
3. If a new child joins the Summer Camp, then Summer Camp would add a record in the table.
4. If a child is shifted from one group to another, then Summer Camp would edit a record in the table.
5. If any child leaves the Summer Camp, then Summer Camp would delete a record of that child from the table.
6. Text and Date/Time.
Question : 3. Consider the following database:
| ProductCode | ProductName | DateOfSale | QtySold | CustomerName | Amount |
|---|---|---|---|---|---|
| P001 | Pencil | 05/10/11 | 5 | Himanshu | 25 |
| P002 | Eraser | 01/01/12 | 4 | Ali | 8 |
| P003 | Sharpner | 09/12/11 | 6 | Deepak | 12 |
| P004 | Whitener | 25/04/11 | 2 | Ankit | 30 |
| P005 | Glue Pen | 20/07/12 | 3 | Ruchi | 30 |
1. Write the name of the field that contains numeric data.
2. Identify the primary key field in the database.
3. Identify the field type of the DateofSale field.
4. Identify the names of the fields that contain textual data.
5. The given table contains how many fields and records?
6. Which property should you use, if you want to make sure that the QtySold entered is less than 10?
Answer :
1. Qty Sold and Amount fields
2. ProductCode field
3. Date/Time data type
4. ProductCode, ProductName and CustomerName fields
5. 6 fields and 5 records
6. Validation Rule
Question : 4. Look at the given table called ‘Item’ and answer the questions.
| ItemSold | ItemName | ItemStock | ItemDOP | QuantityPurchased |
1. Suggest the data types of all the fields.
2. Which two fields can act as the primary key from the above table?
Answer :
1. ItemID —→ Text
ItemName —→ Text
ItemStock —→ Number
ItemDOP —→ Date
Question : uantityPurchased —→ Number
2. ItemID and ItemName can act as primary key.
Question : 5. Damini is a programmer in an institute and is asked to handle the records containing information of students. Suggest any 5 fields name and their data type of students database. CBSE 2013
Answer :
| FieldName | FieldType |
|---|---|
| RollNo | Number |
| Name | Text |
| Class | Text |
| Section | Text |
| Gender | Text |
Question : 6. Answer the following questions based on the database given below:
| Product Table | |||||||
|---|---|---|---|---|---|---|---|
| Prod_Id | Prod_Name | Category_Name | Supplier_No | Unit_In_Stock | Unit_On_Order | Unit_Cost | Record |
| 1 | Inkpen | 1000 | 1 | 1000 | 500 | 2 | 100 |
| 2 | Ballpen | 1001 | 2 | 1030 | 400 | 4 | 200 |
| 3 | Pencil | 1002 | 3 | 1035 | 300 | 6 | 300 |
| 4 | Scale | 1003 | 4 | 1040 | 200 | 8 | 400 |
| 5 | Eraser | 1004 | 5 | 1045 | 100 | 10 | 500 |
| 6 | Sharpner | 1005 | 4 | 1047 | 600 | 12 | 600 |
| 7 | Poster | 1006 | 3 | 1050 | 700 | 14 | 700 |
| 8 | Crayons | 1007 | 2 | 1055 | 800 | 16 | 800 |
1. Identify the primary key and justify your answer.
2. Suggest suitable data type for Unit_Cost and justify your answer.
3. If the database is sorted in ascending order of UnitCost. What is the product name of last record?
4. How will you ensure that Supplier_No must not be empty?
5. To add the details of new product, what should the user add: record or field? Write the steps for the same.
6. How will you ensure that ‘1’ must appear in Supplier_No, if no value is entered?
7. How many records and fields are there in the above table?
8. It is needed to add photograph of the product in the database. Which is suitable data type for the same?
Answer :
1. The primary key of the given table Product will be Prod _Id because no two products have same Product_Id. So, it will be uniquely identify for each record into the table.
2. The suitable data type for the field Unit_Cost is Number because this field in the table contains value in numbers only.
3. Crayons
4. By setting the Required property to Yes.
5. To add the details of new product the user should add a record. The steps are as follows:
• Step 1 Open the database.
• Step 2 Open the table in which you want to add a record in a Datasheet View.
• Step 3 If you open the table, a new blank record will automatically appear at the end of the table.
• Step 4 Type the data into the fields.
• Step 5 Save the table.
By setting the property Default Value which is to be set as 1.
Records = 8, Fields = 8
The data type OLE Object is suitable for adding the photograph of the product into the table.
Multiple Choice Questions
Question : 1. Microsoft Access is an example of
(a) Computer
(b) DBMS
(c) CPU
(d) Device
Answer :
(b) MS-Access is the DataBase Management System (DBMS) found in the Microsoft Office suite.
Question : 2. Which of the following is not a part of a table structure in MS-Access?
(a) Field Name
(b) Field Type
(c) Primary Key
(d) Number of Records
Answer :
(d) From the given options Field Name, Field Type Primary Key are the part of a table.
Question : 3. In a MS-Access database, which component is not present?
(a) Formula Bar
(b) Tables
(c) Queries
(d) Forms
Answer :
(a) MS-Access contains various components, i.e. Tables, Queries, Forms, Reports, Macros and Modules.
Question : 4. Which of the following is not a data type?
(a) Picture/Graphic
(b) Date/Time
(c) Text
(d) Number
Answer :
(a) From the given options Date/Time, Text and Number are the data types.
Question : 5. What data type should you choose for a zipcode field in a table?
(a) Text
(b) Number
(c) Memo
(d) All of these
Answer :
(b) Number data type should be chosen for a zipcode field in a table.
Question : 6. For what, Memo data type is used?
(a) To add table
(b) To store objects created in other programs
(c) For long text entries
(d) For short text entries
Answer :
(c) As we know, Memo provides character upto 65536 so, it is used for long text entries.
Question : 7. The default data type for a field is
(a) Number
(b) AutoNumber
(c) Currency
(d) Text
Answer :
(d) The default data type for a field is Text.
Question : 8. Single and double are the types of
(a) Integer data type
(b) Decimal numbers
(c) Text data
(d) None of these
Answer :
(a) Integer data type provides single and double data types.
Question : 9. You create a table in MS-Access. You decided to create two fields RollNo and DateofBirth, what will be the data type for DateofBirth column?
(a) Number
(b) Text
(c) Yes/No
(d) Date/Time
Answer :
(d) Date/Time will be the data type because it allows to store date and time format.
Question : 10. Which data type helps you to handle input that is in boolean format?
(a) OLE Object
(b) Attachment
(c) Yes/No
(d) None of these
Answer :
(c) Yes/No will allow boolean value.
Question : 11. Which property is used to add a label to the field to assist the user?
(a) Default value
(b) Caption
(c) Format
(d) Indexed
Answer :
(b) Caption is used to add a label to the field for assisting to the user.
Question : 12. Which field property helps in setting data validation in a table?
(a) Caption
(b) Default Value
(c) Decimal Places
(d) Validation Rule
Answer :
(d) Validation Rule property specifies a condition that must be met before the data is accepted into the database.
Question : 13. What is the purpose of the description column in table Design View?
(a) To describe the data that should be entered in each field
(b) To define the data type applied to each field within the table
(c) To enter lookup data, the field should be refer to the table
(d) None of the above
Answer :
(a) A Design View of table provides three columns as Field Name, Data Type and Description. The description column provides the facility to write description about Field Name.
Question : 14. An integer takes a storage size of
(a) 2 bytes
(b) 1 byte
(c) 4 bytes
(d) 8 bytes
Answer :
(a) An integer takes a storage size of 2 bytes.
Question : 15. When you define a field for a table, which of the following parameters do Access always consider optional?
(a) Field Name
(b) Data Type
(c) Field Size
(d) Description
Answer :
(d) Description field of table is optional, as it depends on database designer that he/she wants to describe field or not.
Question : 16. A command tab which allows to create the table in an interactive manner is called
(a) Home
(b) Table
(c) Form
(d) Create
Answer :
(d) MS-Access provides a command tab named Create which allows to create the table in an interactive manner.
Fill in the Blanks
Question : 1. Microsoft Office Access 2007 includes a set of database ………… which are designed for specific business needs.
Answer :
templates
Question : 2. In a table, columns are called ………. and rows are called ………… .
Answer :
fields, records
Question : 3. The different objects supported by MS-Access are tables, queries, ………… and reports.
Answer :
forms
Question : 4. The ………….. field property makes data entry compulsory, so that the field cannot be left blank.
Answer :
Required
Question : 5. To store object like image, you need to create a field in a table, having field type as …………. .
Answer :
OLE Object
Question : 6. …………. is a field which is used as a primary key and numbers each record sequentially. CBSE 2001
Answer :
AutoNumber
Question : 7. For storing lengthy information in a field ………….. data type is used.
Answer :
Memo
Question : 8. In a Text type data field, we can enter a maximum of ………….. characters. CBSE 2011
Answer :
255
True or False
Question : 1. When you open a database, the Ribbon appears on the left side of the MS-Access 2007 window.
Answer :
False Because ribbon appears on the top of the window.
Question : 2. A Text data type cannot allow number entry.
Answer :
False As, it can include both text as well as numbers.
Question : 3. Memo data type allows you to store character type values in a table. CBSE 2011
Answer :
True Memo data type allows upto 65536 characters.
Question : 4. Memo data type allows you to store only 255 characters in the table.
Answer :
False Memo data type is used when character size is more than 255.
Question : 5. The Datasheet View displays the data in a table in tabular format. CBSE 2011
Answer :
True A datasheet is a simple view of data arranged in rows and columns.
Question : 6. Design View allows users to enter data in the table.
Answer :
False Because Design View is used to edit the table design only.
Question : 7. A query is used to retrieve data from the database based on one or more criteria. This database object is not supported by MS-Access 2007.
Answer :
False Because MS-Access 2007 supports the database object query.
