UNIT - V MS Access:

Orientation to Microsoft Access - Create a Simple Access Database - Working with Table
Data - Modify Table Data - Sort and Filter Records - Querying a Database - Create Basic
Queries - Sort and Filter Data in a Query - Perform Calculations in a Query - Create Basic
Access Forms - Work with Data on Access Forms - Create a Report - Add Controls to a
Report - Format Reports.

1. What is Microsoft Access? What are the advantages and disadvantages of access?

Microsoft Access is a Database Management System offered by Microsoft. It uses the Microsoft
Jet Database Engine and comes as a part of the Microsoft Office suite of application.

Microsoft Access offers the functionality of a database and the programming capabilities to create
easy to navigate screens (forms). It helps you analyze large amounts of information, and manage
data efficiently.

Advantages of MS Access

 Access offers a fully functional, relational database management system in minutes.

 Easy to import data from multiple sources into Access
 You can easily customize Access according to personal and company needs
 Microsoft Access online works well with many of the development languages that work on
Windows OS
 MS-Access allows you to link to data in its existing location and use it for viewing, updating,
querying, and reporting.
 Allows you to create tables, queries, forms, and reports, and connect with the help of Macros

Disadvantages of MS Access

 Microsoft Access database is useful for small-to-medium business sectors. However, it is not
useful for large-sized organizations
 Lacks robustness compared to dbms systems like MS SQL Server or Oracle
 All the information from your database is saved into one file. This can slow down reports,
queries, and forms.
2. What the objects of MS – Access?

Table

 A Table is an object which stores data in Row & Column format to store data.
 A Table is usually related to other tables in the database file.
 Each column must have Unique name
 We can also define Primary Key in a table.

Query

 Queries answer a question by selecting and sorting and filtering data based on search
criteria.
 Queries show a selection of data based on criteria (limitations) you provide.
 Queries can pull from one or more related Tables and other Queries.
 Types of Query can be SELECT, INSERT, UPDATE, DELETE

Form

 A form is a database object that you can use to create a user inferface for a database.
 The user can create an expression which uses data from one or more fields

Report

 A report is an object in desktop databases primarily used for formatting, calculating, printing,
and summarizing selected data.
 You can even customize the report's look and feel.

3. Explain briefly about Data types in Ms – access?

some of the most common data types used in a Microsoft Access database.

Number : Numeric data used in mathematical calculations.

Short Text : Text or combinations of text and numbers, including numbers that do not require
calculating (e.g. phone numbers).

Long Text : Lengthy text or combinations of text and numbers.

Date/Time : Date and time values for the years 100 through 9999.

Currency : Currency values and numeric data used in mathematical calculations involving data with
one to four decimal places.
AutoNumber : A unique sequential (incremented by 1) number or random number assigned by
Microsoft Access whenever a new record is added to a table.

Yes/No : Yes and No values and fields that contain only one of two values (Yes/No, True/False, or
On/Off).

OLE objects : OLE objects can store pictures, audio, video, or other BLOBs (Binary Large Objects)

4. How to Start Microsoft Access?

There are two ways to Start MS Access.

1. From Windows, 'Start' button.

2. From Desktop, Right Click> 'New' option.

Option 1) From Windows, Start button.

1. Click on the 'Windows' icon. You will find the list of installed programs.
2. Check and click on Access Icon.
3. MS Access Application window will appear
4. Press 'Esc'

Option 2) From Desktop, 'New' option.

1. Right Click from Desktop and Click 'New'

2. Click on 'Microsoft Access Database Option'
3. Below MS Access Application window will appear
4. Press 'Esc'

5. How to Create a Database in Ms - Access?

Database store the data in a well-organized manner for easy access and retrieval.
There are two ways to create Database in SQL Access:

1. Create Database from Template

2. Create a Blank Database

Create a Blank Database

Step 1) With MS Access application open, Click on File > New

Step 2) Click on 'Blank Database.'

Step 3) File name box will appear with the default file name.

Step 4) Enter the new name.

Step 5) Click on 'Create.'

6. How to Create Table using Design view in Ms – Access?

There are two ways to create Database in MS Access

1. Create a Table from Design View

2. Create a Table from Datasheet View

Create Table – Design View

Step 1) First Click Create tab. Then from Tables group, click Table.

Step 2) Table Dialog box appears. For each Field enter Filed Name, Data Type and Description.

Steps 3) To Add Course_ID as Primary Key, select it and Click on 'Primary Key.' Course_Id will be
Preceded by KEY ICON as shown below:

Steps 4) Press 'Ctrl+S.' Enter the Table Name and Click OK

7. How to modify table data in access?

Once you have created a table, you can modify it later by adding, deleting, and modifying its
fields.
Display a table in Design View
1. Open the table in Datasheet View. Click the Home tab on the Ribbon and click the View button in
the Views group.
2 The table appears in Design View. Here you can add, delete, or modify the table’s structure and
fields.
Add/modify a field in Design View
1. In Design View, enter or edit a field name in the Field Name column, click in the Data Type
column for that row
2. click the Data Type list arrow and select a data type for the field. Repeat as desired.
Change a field’s data type
A field’s data type determines the type of information that can be stored in a field. For example, you
cannot enter text into a number field.
1. Display the table in Design View.
2. Click the field’s Data Type box, click the list arrow, and select a data type from the list.
8. How to sort and filter records in Ms – access?
Records can be sorted either alphabetically, numerically, or chronologically (by date).
Additionally, you can sort information in ascending (A to Z) or descending (Z to A) order.
Sort records
1. Open the table you want to sort and click anywhere in the column (field) you want to use to sort
by.
2. Click the Home tab on the Ribbon and click either the Ascending or Descending button in the
Sort & Filter group.
Clear a sort
1. Click the Home tab on the Ribbon and click the Clear All Sorts button in the Sort & Filter group.
2. The sorted data returns to its original order.
Filtering records
Sometimes you may want to see only certain records in your table. By filtering a table, you display
only the records that meet your criteria and hide the records that do not.
There are several filter methods:
 Common Filters
 Filter by Selection
 Filter by Form
  Advanced Filter/Sort
Apply a common filter
1. Click anywhere in the column you want to filter by, click the Home tab on the Ribbon and click the
Filter button in the Sort & Filter group.
2. A contextual menu appears with two ways to filter:
 Use the check box list. This list contains all the values in that column. Uncheck a box and
click OK to filter out a particular value.
 Point to [Data type] Filters, then point to an option—such as Equals or Does Not Equal—
on the submenu that appears. Enter a filter criterion in the Custom Filter dialog box.
3. Select a filter option.The data is filtered.
9. How to create a query in Ms- access?
Create a query in Design View
1. Click the Create tab on the Ribbon and click the Query Design button in the Other group.
2. Select the table you want to add to the query and click Add.
3. Repeat Step 2 as necessary for additional tables or queries, and click Close.
 The Query window appears in Design View. Noticethat the window is split.
 The top half contains a box that displays all the fields in the table you added to the query.
 The bottom half of the screen contains a design grid, which is where you will add the fields
you want to appear in your query.
4. In the field list, double-click each field you want to include in the query.
5. In the design grid, enter any desired search criteria
6. Click the Save button on the Quick Access Toolbar, enter a name for the query in the Save As
dialog box and click OK for the field in the Criteria box.
7. Click the Design tab under Query Tools on the Ribbon and click the Run button in the Results
group.
10. How to sort a query in Ms- Access?
Tables normally display records in the order they were entered. you can create a simple
query that sorts the table information and presents it in an ordered, easy-to-read display. you can
create a simple query that sorts the table information and presents it in an ordered, easy-to-read
display. You can sort records alphabetically, numerically, or chronologically (by date) in ascending
(A to Z) or descending (Z to A) order.
1. Display the query in Design view.
2. Add the field you want to use to sort the query to the design grid, along with any other fields you
want to appear in the query results.
3. Click the Sort list arrow for the first field you want to use to sort the query, then select a sort
order.
4. Save and run the query.
11. Explain how to perform calculations in a query?
1. Display the query in Design view.
2. Click the Field row of a blank column in the design grid.
3. Enter the field name for the field that will display the results of the calculation, followed by a :
(colon).
4. Enter the expression you want Access to calculate, using the proper syntax
For example, the expression total: [sub1]+[sub2]+[sub3]
5. Click the Design tab under Query Tools on the Ribbon and click the Run button in the Results
group.
6. Click the Office Button, point to Save As, and select Save Object As. Enter a query name and
click OK.

12. Explain different types of queries in MS- Access?

MS- Access provides different types of queries.

Select Query:
The most basic and common type of query, select queries find and display the data you
want from one or more tables or queries.
Parameter Query Prompts the user for specific information every time the query is run.
Crosstab Query Summarizes data in a table format that makes it easy to read and compare
information.
Make Table Query Creates a new table from all or part of the data in one or more tables. Useful for
backing up and
exporting information.
Append Query Appends or adds selected records from one table to another table. Useful for
importing information into a table.
Delete Query Deletes selected records from one or more tables.
Update Query Updates selected information in a table..
Union Query Combines fields from two or more tables or queries into one field and is written
directly in SQL.
Pass-Through Query Allows you to work directly with tables on an ODBC database server, instead
of having the Access database on your local computer process the data.
Data Definition Query Does not retrieve data (unlike other types of queries). Can be used instead
of the Access graphical interface to create tables, constraints, indexes, and relationships.
13. What is a form? Write the steps how can create a form using form wizard?
Form : A form is an object of access. It is used to read the data from the table or tables, in
the same way it is also used to write the data into the table or tables. It provides tools to make the
form with GUI feature.
To create a form using wizard, follow these steps:
 Click on create tab on the ribbon.
 Click on more forms from forms group.
 Select from wizard from more forms.
 A form wizard appears on the screen.
 Click the tables/ queries drop down menu to select the table or query on which the form will
be based.
 Select fields you want to include in the form. Click on a field you want to add and then click
the > button.
 The field moves from the “available fields” to the “ selected fields” list.
 If you want to add all the fields from a table or query at once click >> button.
 Click on next and Select the layout for the form
 Click on next and select the style
 Enter the title for the form
 Click on FINISH
14. What is a Report? Write the steps how can create a report using report wizard?
We can use the report wizard to create a professionally designed report that summarizes
data from your database. The wizard asks you detailed questions about the sources fields, layouts
and format you want then creates a report based on your answers.
To create a report with wizard, follow these steps:
 Click on create tab from the ribbon.
 Click Repot Wizard from the Reports group
 Click the tables/ queries drop down menu to select the table or query on which to base the
report.
 Select fields you want to include in the form. Click on a field you want to add and then click
the > button.
 The field moves from the “available fields” to the “ selected fields” list.
 If you want to add all the fields from a table or query at once click >> button.
 Click next to continue
 To sort the records in your record click the drop down list
 Check the field you want to use to sort the records.
 Choose ascending or descending sort.
  Click next to continue
 Click the layout that you want to use for your report
 Click either portrait or landscape page orientation.
 Click next to continue.
 Select the style that you want to use for your report. A preview of the style you selected
appears in the left pane.
 Click next to continue.
 Type the name of the report in the space provided.
 Click FINISH to create your report
15. How to add controls to a report?
Label : Creates a static text label that is the same for every record, such as a heading. Most
controls already have a text label attached.
Text Box : Creates a text box that displays information from a table and query. You can also use
text boxes to simply enter text.
Option Button: Creates an option button (or radio button) that allows the user to make a single
selection from two or more choices. Option Buttons are normally used with the Option Group
control. Normally used in forms, not reports
Check Box :Creates a box that is checked or unchecked. Use to enter data from a Yes/No field.
Normally used in forms, not reports.
Combo Box :Creates a drop-down box that lets the user enter text or select an item from a list of
options. Normally used in forms, not reports.
List Box :Creates a box that lets the user select an item from a list of options. Normally used in
forms, not reports.
Command Button :Creates a button that runs a macro or Visual Basic function.
Image : Displays a picture or graphic file that you specify.
Line : Enables you to draw a line.
Rectangle : Enables you to draw a rectangle.
Insert Chart : Inserts a Chart.
Insert Hyperlink : Inserts a link to a Web page or file.
Title : Adds a title label to the report’s header.
Insert Page Number : Adds page numbers to the report’s Header or Footer section.
Date and Time : Adds the date and time that the report was opened.