Office Automation Tools

RAYALASEEMA UNIVERSITY: KURNOOL

Fourth Semester BCom Computer Applications

DSC 3D : Office Automation Tools

Unit-I: MS-Excel: features of Ms-Excel, Parts of MS-Excel window, entering and editing data in
worksheet, number formatting in excel, different cell references, how to enter and edit formula in
excel, auto fill and custom fill, printing options.

Unit-II: Formatting options: Different formatting options, change row height, formulae and
functions, Functions: Meaning and advantages of functions, different types of functions available
in Excel.

Unit-III: Charts: Different types of charts, Parts of chart, chart creation using wizard, chart
operations, data maps, graphs, data sorting, filtering. Excel sub totals, scenarios, what-if analysis
Macro: Meaning and advantages of Macros, creation, editing and deletion of macros - Creating a
macro, how to run, how to delete a macro.

Unit-IV: MS Access: Creating a Simple Database and Tables: Features of Ms-Access, Creating
a Database, Parts of Access. Tables: table creation using design view, table wizard, data sheet
view, import table, link table. Forms: The Form Wizard, design view, columnar, tabular, data
sheet, chart wizard.

Unit- V: Finding, Sorting and Displaying Data: Queries and Dynasts, Creating and using select
queries, Returning to the Query Design, Multi-level sorts, Finding incomplete matches, showing
All records after a Query, saving queries - Crosstab Queries. Printing Reports: Form and Database
Printing. Relational Databases: Flat Versus Relational, Types of Relationships, Viewing
Relationships, Defining and Redefining Relationships, Creating and Deleting Relationships.

Reference Books:

[Link] Mansfield, Working in Microsoft Office, Tata McGraw Hill(2008)

[Link], Woody Leonhard, Using Microsoft Office 2007, Pearson Education(2007)
3. Sanjay Saxsena, Microsoft Office, [Link] Office, BPB Publications

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 1

 Office Automation Tools

Unit – I - MS EXCEL
1. What is a Spread sheet and write the advantages, applications of spread sheet
? (or)
What is Ms Excel and explain the advantages, applications of Ms Excel ?
Ms Excel is one of the important tool of Ms Office. Ms Excel file is called as
work book , Each work book contain up to spread sheets. Each spread sheet contain
16,384 columns and 1,048,576 rows . The information can be organized in rows and
column called as a cells. Ms Excel work book will have the extension name “.xlsx”
Advantages (or) Features of Electric spread sheet (or) Ms Excel
1. It is used for calculations.
2. We can create, edit, print workbook .
3. Ms Excel supports three types of references. Such as relative, absolute and mixed .
4. It supports many built in formulas called functions.
5. It supports fourteen types of graphs.
6. It supports different type of formats and styles
7. It supports the validating the data by applying the validation rules.
8. Spread sheet allows the user to sort the data either in ascending order or
descending order.
9. it support to create pivot table.
10. it supports what if analysis tools .
11. It support copying, moving, deleting data from the cells.
12. Used to present the data as tabular format and print it.
13. used to maintain the small database.
14. used to protect the sheet.
15. supports automatically filling of data (numbers, dates, week day names etc).
Application of spread sheet or ms excel
1. Accounts Department 2. Income tax Department 3. Financial analysis 4. Sales
department 5. Marketing Department etc.

Q:- Explain about the parts of Ms-Excel windows?

Title bar:- shows the application software name and workbook name.
It has 3 buttons.
a) Minimize button :- minimizes the application windows.
b) Maximize / Restore button :- displays the application window in full screen or in
resizable window.
c) Close button :- closes the database application.
Quick Access Tool bar:- contains save undo (cancels the recently done work), redo
(repeat the work which was undone by undo) and save (to store) buttons.
Tab bar:- displays all available menus.
Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 2
 Office Automation Tools

Ribbon bar:- displays the selected menus shortcut buttons and allows user to fast
accessing.
Formula bar :- Shows the cell name / address and formula of selected cell.
Horizontal scroll bar:- used to move the spread sheet as a left / right position.
Vertical scroll bar:- used to move the spread sheet up / down position.
Status bar:- indicates the status of the work book and has Zoom slider to adjusting
zoom size.
Sheet tab:- Display the all available sheets and allows us to create new sheet.

Q. Explain about creating Work Book in Excel?

When the Ms excel soft ware is invoked then new work sheet will be created
automatically . If you already working with work sheet for creating a new work
sheet follow the below procedure .
Step 1 : Click on Office button .
Step 2 : Select and click on new button (or) Press Ctrl + N buttons from keyboard .
Q: Explain about Saving Worksheet? If you want to save a newly create work
sheet then follow the below method ?
Step 1 : Click on office button .
Step 2 : Select and click on save button or press Ctrl + S .
Step 3 : Specify the saving location (example LOCAL DISK E) .
Step 4 : Enter the work sheet file name and click on save button .
If you are working with stored document then use save option from office button or
Press Ctrl+S to save the modification in stored files.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 3

 Office Automation Tools

Q:- Write about to open stored workbook in Excel?

If the Ms Excel is already opened then follow the bellow procedure to open an
existing excel file.
Step 1 : Click on Office button .
Step 2 : Select and click on open option or press Ctrl + O .
Step 3 : Specify the look in location (D: )
Step 4 : Select the Ms Excel file by clicking mouse on that file and click open button
.Note : After opening a Ms Excel file , If you want to save sheet .you press Ctrl + S.

Q. Write the procedure for entering and editing text ?

Entering the text :
Step 1 : Move the cell pointer in to particular cell position where you want to enter
the text using arrow keys.
Step 2 : Type the text and press enter key .
Editing the text :
step 1 : Move the cell pointer in to the cell which is containing the text to edit .
Step 2 : Press F2 button and perform the changes . Press enter key or double click on
cell and perform the changes press enter key .

Q. Write the procedure for selecting cells ?

We are selecting the cell for copying, moving and filling the data in the cells .
Eg : If you want to select the cells from A1 to A10 then follow the below method .
Step 1 : Move the cell pointer to A1 cell .
Step 2 : Press shift and down arrow key up to selecting A10 cell .

Q:- Explain about the number format in Excel ?

In excel we can change the entered data in various formats.
HomeNumber :- contains the following number formats.

Number format drop down list box – used to change in various format.
Accounting number format – used to change the numbers as accounts.
Percentage style – changes into % style.
Comma style – changes into , style.
Increase decimal – increases the decimal part of a numbers.
Decrease decimal – decreases the decimal part of a numbers.

HomeNumberNumber format drop down listmore number format:-

In format cell dialogue box, number table contains the following number formats.
General – no formats
Number – number style.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 4

 Office Automation Tools

Currency – currency style.

Accounting – accounting style.
Date – date format.
Time – time format.
Percentage – percentage style.
Fraction – fraction style.
Scientific – scientific number style.
Text – text style.
Special – zip, pin code style.
Custom – user required style.
Q:- Explain about Cell references in Excel or Referencing cells?
In spread sheet, data is organized as a cells. Each cell is identified as a column
name and row number called as a cell reference / cell address. Excel supports 3 types
of cell references. They are
1. Relative reference :
In relative reference ,if any formulae will be copied from one cell to another cell
,then cell reference will be change according to the location of copied formulae .
Ex:- =Sum (A1:A10)
2. Absolute reference :
Absolute reference is followed by $ sign in front of the column name and row
number in a formula . If you copy a formula which is containing absolute reference
in to another cell, then cell reference cannot
be changed with in formula . eg : =Sum ($A$1: $A$10)
3. Mixed reference :
Combination of relative and absolute reference are called mixed reference . i.e. in
cell address, which is followed by the $ sign will not be changed and which is not
followed by $ sign will be changed when the formula copied to another location with
in a sheet
eg : =Sum ($A1 : $A10)
If we are copying the above formula in to another cell then column name will
not be changed and row number will be changed based on the location of copied
formula in a sheet .
Q. Explain about how to create the formulae in work sheet ?
write the procedure for editing formula ?
Formulae are widely used for simple and advanced computing .They provide the
power to analysis data extensively .
Spread sheet also provides an important feature of auto recalculation every
time a change is made in the cells. Any change made in one cell causes the ripple
effect in all the dependent cells. There
are basically two kinds of recalculations .

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 5

 Office Automation Tools

1. Automatic : This is the default mode in which the change in the value of the cell
causes the recalculation of the whole work sheet automatically .
2. Manual : The calculation of the work sheet is done only on pressing F9 Key .
Entering Formula :-
Rules for using formulas :
1. A formula always start with an equal sign .
2. A formula may consist of operators, cell references, range names, values and
functions .
3. When a formula is entered in the cell then the result will be display in the entered
cell and formula will be display in the formula bar .
Procedure :-
1. Move the cell pointer where you want to enter the formula.
2. Type the formual.
3. Press enter key.
Order or precedence of operators :
Operator Meaning Example
: Range A1:A10
Space Blank space
- Negation =-10
% Percentage =20%
^ Exponential =2^3
* Multiplication =10*5
/ Division =10/2
+ Addition =10+3
- Subtraction =10-3
& Concatenations =”Work” & “Book”
> Greater than =A1>A10
< Less than =A1<A10
>= Greater than or equal to
=A1>=20
<= Less than or equal to =A1<=20
<> Not equal to =A1<>20
= Equal to =A1=50

Editing formula :
Step 1 : Activate the cell which is containing the formulas .
Step 2 : Press f2 key and perform the changes in that cell (or) type the changes in the
formula bar .

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 6

 Office Automation Tools

Step 3 : Another method for performing changes in the formula is by double click on
the cell which is containing the formula and make the changes , press enter key .

Q: Explain about the auto fill and custom fill of data in Excel? or
Write about automatically Generation of numbers, Dates, day name and
month names?
Excel supports to generate the numbers and dates and name of the and month
names.
1. Auto fill :- In excel we can automatically generate serial numbers, dates, month
names, day names.
Generating numbers:-
--> Move the cell pointer to the cell position from where you want to generate
numbers.
 type the starting number and activate the cell.
 move the mouse pointer to the right side down position corner until pointer
change as the + symbol.
press ctrl button from key board and click the left button of the mouse and drag the
down or right position.
Generating dates/day names / month names
 Move the cell pointer to the cell position from where you want to generate date /
day names / month names.
 type the starting date / day names / month name and activate the cell.
 move the mouse pointer to the right side down position corner of cell pointer until
the pointer change as the + symbol.
 Click the left button of the mouse and drag the down or right position.
2. Custom fill :-
Use a custom list to sort or fill in a user-defined order. Excel provides day-of-
the-week and month-of-the year built-in lists, but you can also create your own
custom list.
Procedure :-
1. Microsoft Office Button > Excel Options > Popular >Top options
for working with Excel > Edit Custom Lists.
2. In the Custom Lists box, click NEW LIST, and then type
the entries in the List entries box, beginning with the first entry.
Press the Enter key after each entry.
3. When the list is complete, click Add.
4. Click OK twice.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 7

 Office Automation Tools

Q:- Explain about printing options in Excel?

Print option from office buttons contains the following options.
Print :- used to print the selected worksheet, with selected no. of copies.
Quick print :- prints entire worksheet with default printing options.
Print preview :- used to preview the worksheet before taking print out.
OfficePrintPrint:-
Print option from print of office button or Ctrl +P is used to print the spread
sheet . When the print command is executed, then excel will display the print
dialogue box on the screen. In this dialogue box, user specify required options and
click on ok button.

Name :- Select the printer.

Print Range :- Specify the all pages / pages from to.
Print What :- Specify the selection / active sheets / entire work book to print.
Copies :- Specify the number of copies to print each page.
Collage :- Define the order of number of copies to be print.
Properties :- Define the printer option to be print.

Unit – II – Formatting options

Q:- Explain the method of formatting work sheet in Ms Excel? Or Different
formatting options?
Ms Excel provides different types of formats to apply on work sheet. In MS
Excel 2007, font tab and cell tab’s format options allows us to change the formats of
a cells.
Home menu contains the following Group buttons to apply the format :-
Font Group frame :- These buttons are used to change the font type, size, style,
borders, font colour and back ground colours.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 8

 Office Automation Tools

Alignment group frame :- These buttons are used to align contents of a selected
cells. It contains the left, right, center, top, middle, bottom, merge and center
buttons.
Number Group frame :- These buttons are used to change number format of a
selected cell data(General, %, date, comma, currency, decimal formats).
Style Group frame :- These buttons are used to change the format of selected cell
data as a table format. It contains the conditional format, format table, cell styles.
Cell group frame :-
Row Height:
Used to increase or decrease the row height.
Auto fit row height:
Adjust the selected rows height according to the contents of selected cells.
Column Width:
Used to increase or decrease the width of column .
Auto fit column width:
It adjust the selected column width based on the information contained in cells
Default Width:
It shows the columns default width(8.43).
Visibility – hide & unhide
Hide Rows: Used to hide the selected rows.
Hide columns:
Used to hide the selected columns.
Hide sheet:
It hides the activated sheet or selected sheet.
Unhide rows:
Used to Unhide the hidden rows.
Unhide columns:
Used to display the hidden columns.
Un hide Sheet:
Used to un hide the hidden sheet.
Organize sheet :-
Rename:
Used to change the sheet name.
Move or copy sheet :-
It is used to move or copy the selected sheets.
Tab color:
Used to set the color to the sheet tab.
Protection:-
Protection sheet:-
Used to protect the sheet from the modifications.
Lock cell:
Used to lock the cell.
Format Cells:-
It is used to change the following properties of the selected cell data.
Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 9
 Office Automation Tools

A)number format B)Alignment

c) font formats d) Borders of cells
e) fill options f)protection settings.
Editing Group Frame :- These buttons are used to calculate auto sum, to fill the
data as up, down, right, left, series, to clear the data, to sort and filter the data and to
find the data.

Q:- Explain about the changing column width and row height?
Cell size options from format button of cell group frame of home menu allows
us to change the row height / column width.
Procedure to change the row height :-
select the rows homecells formats  cell size row height  enter height
click ok
Autofit row height is used to set row height of a selected rows based on
contents of a rows.
Procedure to change the column width :-
select the column homecells formats  cell size  column width  enter
width click ok
Autofit column width is used to set column width of a selected columns based
on contents of a columns.
Functions
Q:- What is a function? Explain about the advantage of functions?
A function is a predefined formula that performs calculations using specific
values in a particular order. Excel includes many common functions that can be used
to quickly find the sum, average, count, maximum value, and minimum value for
a range of cells.
Advantages :-
1. Easy to solve complex problems.
2. No need to remember syntax of functions.
3. Automatically update the values when we are changing the values of cells those
are used with in a functions.
4. Returns the accurate results.
Function contains :-
 Start a function with an equal sign (=).
 Enter the function name.
 Include information about a cell or range of cells to be analyzed.
 Enter arguments about what to do with the selected range of cells.
Types of Functions :-
Ms Excel supports various categories of functions. They are
1. Mathematical functions.
2. Statistical functions.
3. Text functions.
4. Engineering functions.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 10

 Office Automation Tools

5. Date & Time functions.

6. Logical functions.

Q:- List the different types of functions in Ms Excel?

The readymade formulae are called as functions. The user has to provide the
cell references or addresses only. These are called arguments of the function that are
given between a pair of parenthesis(( ) ).
The function perform the operation on the given values and return the result
where the function was entered.
Type of functions:
Ms Excel supports various categories of functions. They are
1. Mathematical functions.
2. Statistical functions.
3. Text functions.
4. Engineering functions.
5. Date & Time functions.
6. Logical functions.
Mathematical Functions :-
These functions are used perform the mathematical operations on values.
1). ABS(number).
Returns the absolute value of a number.
=ABS(-75) - Output - 75
2)EVEN (Number):Round the positive number up and negative number down to the
nearest even integer.
Ex:- =EVEN(3) - Output - 4
3) FACT (Number): Return the factorial of a number.
Ex:- =FACT(5) -- Output -- 120
4) odd:- Round the positive number up and negative number down to the nearest odd
integer.
Ex:- =ODD(4) -- Output -- 5
5)PRODUCT:- Multiplies all the given numbers.
Ex:- =PRODUCT(3,4,5) -- Output -- 60
6) SUM:- add all the numbers in range of cells .
Ex :- =SUM(A1:A5)
Statistical functions :-
These functions are used to perform the statistical operations on numbers.
1)AVERAGE (Number 1, Number 2, …..): Returns the average (arithmetic mean)
of its arguments .
ex:- =AVERAGE(60,50,40) -- output -- 50
2)COUNT (Value 1, Value 2, ..): Counts the number of the columns numbers and
numbers within the list of arguments.
Ex: =COUNT(a1:a10)
Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 11
 Office Automation Tools

3) MEDIAN (number 1, number 2, …): Returns the median or the number in the
middle of the set of given numbers.
Ex:- =MEDIAN(20,30,40,50,60,70)
4) MODE(number1,number2,…):
Returns the most repeated number from the list of given values.
Ex:- =MODE(20,30,20,30,50,50,20) -- Output -- 20
5)MAX(number1,number2,…): Returns largest value from the given set of values.
Ex:- =MAX(20,50,80,10) -- Output -- 80
Text functions:-
These functions are used to perform the various operations on text.
1) LEN (text): Returns the number of characters in the text string.
Ex:- =LEN(“SRP SCHOOL”) -- Output -- 10
2) LEFT (text, num): Returns the specified number of characters from the start of a
text string.
Ex:- =LEFT(“srp school”,3) -- Output -- srp
3) RIGHT (text,num): Returns the specified number of characters from the end of
a text string.
Ex:- =RIGHT(“SRP SCHOOL”,6) -- Output -- school
4) CONCATENATE (text 1, text 2, ..): Join several text strings into one text string.
Ex:- =CONCATENATE(“srp”,” school”) -- Output - Srp school
5) UPPER (text): Converts a text string to all uppercase letters.
Ex:- =upper(“srp school”) -- Output -SRP SCHOOL
6) LOWER (text): Converts a text string to all lowercase letters.
Ex:- =LOWER(“SRP SCHOOL”) - Output - srp school
Date and time Functions:-
These functions are used to perform the various operations on date and time.
1. DATE (year, month, day): Returns the number that represents the date in
Microsoft Excel date time code.
Ex:- =DATE(2014,11,18) -- OUTPUT -- 41961
2. DAY (Serial number): Returns the day of month, number from 1 to 31.
EX:- =DAY(3266) -- Output -- 9
[Link] (Serial number): Returns the month , a number from 1 (January) to 12
(December).
Ex:- =MONTH(32500) -- OUTPUT -- 12
4. NOW ( ): Returns the current date and time formatted as a date and time.
Ex:- =NOW()
[Link] (): Returns the current date formatted as a date.
Ex:- =TODAY()
6. YEAR (Serial number): Returns the year of a date, an enter in the range 1900-
9999.
Ex:- =YEAR(3250) -- Output -1908

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 12

 Office Automation Tools

Logical Functions:-
These functions are used to check the multiple conditions.
1) AND(logical1,logical2, …): Checks whether all arguments are TRUE, and
returns TRUE, If all arguments are TRUE.
Ex:- =AND(5>2,10>6) -- TRUE, =AND(5>2,20>30) -- FALSE
2. OR(Logical1,logical2, …): Check whether any of the arguments are TRUE and
Return TRUE or it returns FALSE if all arguments are FALSE.
EX:- =OR(5>2,10>20,40>50) – TRUE, =OR(20>30,50>60,50<20) - FALSE
3. NOT (Logical): Changes FALSE to TRUE, or TRUE to FALSE.
EX:- =NOT(5>2) – FALSE, =NOT(5>10) - TRUE
4. IF (Logical text, Logical If true, Value if False): Check whether a condition is
met and returns one value if TRUE and another value if FALSE.
EX:- =IF(5>2,”5 IS BIG”,”NOT BIG”) -- OUTPUT -- 5 IS BIG

Unit – III Charts

Q. What is a chart / graph? Explain the various type of chart /graphs in Excel?
Chart is a graphic component of a numerical data. The data which is entered as a
table form can be viewed in the graphical form as a charts which makes the figure of
data effective, interesting easy to understand and easy to analysis and compare data.
Chart can be of two types.
1. Embedded charts: These charts are included in the work sheet and can be moved,
copied and resized as any typical object. its advantage is that it can be display along
with the data and many charts can be inserted.
2. Chart sheet: Separate chart sheets are inserted when a chart is created. This type
of chart size will not be modified. Ms Excel offers 11 different main major chart
types, each of which has at least one sub type.
Types of charts:
1. Column chart 2. Line chart 3. Pie chart [Link] chart 5. Area Chart 6. XY chart
7. Stock chart 8. Surface chart 9. Doughnut chart 10. Bubble chart 11. Radar
Chart.
Procedure to create graphs :-
Enter the data in table form.
Select the data.
Insert tab  charts group frame select the chart main type and subtype click on
Ok button.
If we want to move the chart then we have to click on move chart location
option, specify the location to where the chart to be moved (New sheet / Object in
sheet).

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 13

 Office Automation Tools

Q:- Explain about the parts of chart?

The typical chart (or graph) in Excel 2010 comprises several distinct parts,
including the chart area, data series, axes, legend, plot area, gridlines, data markers,
and more. The following list summarizes the parts of a typical Excel chart, some of
which appear in the illustration.

 Chart area: Everything inside the chart window, including all parts of the
chart (labels, axes, data markers, tick marks, and other elements listed here).
 Data marker: A symbol on the chart that represents a single value in the
worksheet. A data marker (or data point) may be a bar in a bar chart, a pie in a
pie chart, or a line on a line chart. Data markers with the same shape or pattern
represent a single data series in the chart.
 Data series: A group of related values, such as all the values in a single row in
the chart. A chart can have just one data series (shown in a single bar or line),
but it usually has several.
 Axis: A line that serves as a major reference for plotting data in a chart. In
two-dimensional charts there are two axes — the x-axis (horizontal/category)
and the y-axis (vertical/value). In most two-dimensional charts (except bar
charts), Excel plots categories (labels) along the x-axis and values (numbers)
along the y-axis. Bar charts reverse the scheme, plotting values along the x-
axis. Pie charts have no axes. Three-dimensional charts have an x-axis, a y-
axis, and a z-axis. The x- and y-axes delineate the horizontal surface of the

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 14

 Office Automation Tools

chart. The z-axis is the vertical axis, showing the depth of the third dimension
in the chart.
 Tick mark: A small line intersecting an axis. A tick mark indicates a category,
scale, or chart data series. A tick mark can have a label attached.
 Plot area: The area where Excel plots your data, including the axes and all
markers that represent data points.
 Gridlines: Optional lines extending from the tick marks across the plot area,
thus making it easier to view the data values represented by the tick marks.
 Chart text: A label or title that you add to the chart. Attached text is a title or
label linked to an axis such as the Chart Title, Vertical Axis Title, and
Horizontal Axis Title that you can’t move independently of the
chart. Unattached text is text that you add with the Text Box command button
on the Insert tab of the Ribbon.
 Legend: A key that identifies patterns, colors, or symbols associated with the
markers of a chart data series. The legend shows the data series name
corresponding to each data marker (such as the name of the blue columns in a
column chart).
Q:- Explain about the chart operations? Or
Explain about the change the inserted chart design, layout and format
options in excel?
Three menus are displayed in menu bar when the inserted chart is selected.
They are
1. Design:- Design menu display the tools which are used to change the chart type, to
add/remove the data, switch the chart as row/column wise, chart layouts, chart styles
and to change the chart location.
2. Layout:- This menu display the tools which are used to change the chart labels,
Titles, legends, background area settings, Axes, analysis and properties.
3. Format:- these tools are used to change the formats of a charts.

Q:- Explain about the chart creation using wizard?

We can create charts using chart dialogue box. This chart dialogue box
will open by using the following navigation.
Insert chart group frame click on create chart button  select the main type
and sub type click ok button.
Specifying data :-
1. Select chart. 2. Design  data frame group select data  specify the data
range.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 15

 Office Automation Tools

Q:- Explain about the data maps in Excel?

we can use a Map Chart to compare values and show categories across
geographical regions. Use it when you have geographical regions in our data, like
countries/regions, states, counties or postal codes.
Create a Map chart
1. Type the data as a geographical wise
2. Select the data.
3. Click Insert > Charts > Recommended Charts, or Insert > Charts > Maps,
then select the map chart.

Q . Explain about the sorting of data?

MS Excel allows us to sort the cell data. By using custom sort option
from sort & filter button of home menu we can sort the data. Follow the below
procedure for sorting.
Step 1: Select the data which you want to sort.
Step 2: Go to Home menu and select and click on custom sort option from sort &
filter button.
Step 3: Specify the sort columns and Smallest to largest / largest to smallest and
click on ok button.

Q:- Explain about the filter in Excel?

Filter option allows the user to display the rows only which are matched the
given condition in filter option. This option allows easy and quick view of required
data based on the condition to find, Copy or delete.
Procedure:-
1. Type the data in tabular form.
2. Place the cell pointer at the heading row of a data table.
3. Data tabSort& filterfilter.

Q:- Explain about subtotals in Excel?

Total several rows of related data together by automatically inserting subtotal
and totals for the selected cells.
Procedure:-
1. Type the data as a tabular form.
2. sort the data based on required column.
3. select the data.
4. Data taboutlinesubtotal.
5. specify the at each change column, use function and subtotal column and click on
Ok.
Group and ungroup buttons are used to set the Grouping of data as a row /
columns wise or to remove the inserted grouping as a row/ Column wise.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 16

 Office Automation Tools

Q:- Explain about the Data Analysis or What if analysis?

What-If Analysis is the process of changing the values in cells to see how
those changes will affect the outcome of formulas on the worksheet. Three kinds of
What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables.
Scenarios and Data tables take sets of input values and determine possible results.
1. Scenario
Scenario manager helps us to save the different values in a selected cells and
the user can show the required stored set of values in a selected cells.
Procedure:-
1. Select the cells in which cells you want to store values.
2. DataData ToolsWhat if analysis Scenario Manager.
3. Click on add button in Scenario manager dialogue box.
4. Enter the scenario name, type the values and click on ok button.
5. If you want to add more scenario then follow the step 3 and step 4.
6. click show button to display the stores values. in scenario manager dialogue box,
delete button Used to delete the scenario.
Goal-seek:-
Goal seek option allows the user to achieve the target value in target cell by
changing the value of a specified cell.
Procedure:
1. Type the values in cells and formula in a target cell of a worksheet.
2. Data toolsWhat if analysisGoal seek.
3. Specify the set cell, to value & by changing the cell fields and click on ok button.
Data Table:-
This feature allows the user to perform the calculations automatically by
defining the input row and input column on different set of data.
Procedure:-
1. Type the data as a tabular form.
2. insert the formula in which input row and column cells are used at first cell of
tabular form of data.
3. click on data-table button from What-if analysis from Data Tools frame of Data
tab.
4. Specify the Row input cell and column input cell.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 17

 Office Automation Tools

Macro
Q:- Explain about the Macros? Or Advantages of Macros ?
Creating Macro, Running Macro, Deleting Macro, Editing Macro?
A macros is a series of excel commands grouped together as a single
command to make every day task easier we can assign a macro to a tool bar a menu
or a short cut key.

Advantages of Macro :-
1. They reduce the possibility of human error that increases with many, repetitive
keystrokes and tasks.
2. Macros reduce the amount of time that must be spent performing basic computing
tasks, freeing users up for more complex problem-solving and idea-generating
activities.
3. They also make complex computations easier to perform.
4. Excel always executes the task in exactly the same way. (In most cases,
consistency is a good thing.)
5. Excel performs the task much faster than we can do it manually (unless, of course,
you’re Clark Kent).
6. If we are a good macro programmer, Excel always performs the task without
errors (which probably can’t be said about you or me).
7. If we set things up properly, someone who doesn’t know anything about Excel
can perform the task by running the macro.
8. we can do things in Excel that are otherwise impossible — which can make you a
very popular person around the office.
9. For long, time-consuming tasks, you don’t have to sit in front of your computer
and get bored. Excel does the work while you hang out at the water cooler.
Procedure for creating macro :
Step 1 :- viewMacrosRecord Macro
Step 2 : Enter a name for the macro in the record macro name box .
Step 3 : If you are not given any macro name Ms Word will give the macro name as
macro 1, macro 2,------ . and assign the key board short cut key.
Step 4 : Click ‘ok’ and then perform the action you want to record.
Step 5 : To stop the recording the macro click on stop button from macro record tool
from view menu tab.
Running a macro :
viewMacrosview macrosselect macro Click on Run button .
or by pressing the assigned short cut, we can run the macro.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 18

 Office Automation Tools

Deleting a macro :
viewMacrosview macrosselect macro Click on delete button.
Editing a Macro :-
viewMacrosview macrosselect macro Click on edit button make
changes, save and close the editing a macro.
After editing Run the macro.
Extra Topics in Excel
Q:- Explain about Header and Footer?
Header:- The information which can be printed as a Common for every page at the
top position is known as header.
Footer:- The information which can be printed as a Common for every page at the
bottom position is known as footer.
Generally Header and Footer contains the company name, logo, page
numbers, author name, Date and time etc.
Inserting / editing Header and Footer:-
Page setup dialogue box  header / footer tab allows the user to set the header and
footer.
Procedure to open page setup dialogue box:-
Pagelayout pageset up button.
Q. Explain about the copying and moving of data?
A. Copying data : We can copy the selected data in a work sheet by using the copy
button from home menu or by pressing Ctrl + C. For copying the content of the
selected cells follow the below method .
Step 1 : Select the cells. For example if we want to select the cells A1 to A10 , first
you should move the cell pointer into A1 cell and press Shift + Down arrow key
button up to selecting A10 and release the button .
Step 2 : Go to Home menu and click on copy button or press Ctrl + C .
Step 3 : Move the cell pointer into a particular cell where you want to copy and
press Ctrl + V (or) click on paste button from home menu.
Moving of data:
We can move the selected data in a work sheet by using the cut button from home
menu or by pressing Ctrl + X. For moving the content of the selected cells follow
the below method .
Step 1 : Select the cells. For example, if we want to select the cells A1 to A10 , first
you should move the cell pointer into A1 cell and press Shift + Down arrow key
button up to selecting A10 and release the button .
Step 2 : Go to Home menu and click on cut button or press Ctrl + X .
Step 3 : Move the cell pointer into a particular cell where you want to move and
press Ctrl + V (or) click on paste button from home menu.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 19

 Office Automation Tools

Q:- Explain about the inserting cells,rows and Columns, deleting cells,rows and
columns in worksheet.
Inserting cells:-
Homeinsert  insert cells
Inserting rows:-
Homeinsertinsert sheet rows.
Inserting columns:-
Homeinsertinsert sheet columns.
Inserting Sheet:-
Homeinsertinsert sheet.
Deleting Cells:-
HomeDeletedelete cells.
Deleting Rows:-
HomeDeletedelete sheet rows.
Deleting columns:-
HomeDeletedelete sheet columns.
Deleting sheet:-
Homedeletedelete sheet.
Before inserting/deleting cells/rows/columns, We need to select the
cells/rows/column for inserting / deleting n number of cells/rows/columns.

Q. Explain the page set up in Ms Excel ?

Page set up option is used to set the properties of a page in a spread sheet.
Following procedure used to open the page setup dialogue box.
Pagelayoutmarginscustom margins page set up dialogue box contains the
mainly four tabs.
1. Page: This tab is used to set the orientation (Portrait and Landscape) of a page,
scaling, paper size, print quality and first page number.
2. Margins: This tab allows us to set the margins of a page( Top, Bottom, Left,
Right) Header and Footer Margins and center on page option.
3. Header / Footer:
This tab is used to set the Header and footer information.
4. Sheet:
This option is used to set the print area, print titles, printing options and page order
methods.
Q:- Explain about the auto format?
Styles group frame buttons are used to apply the auto formats to the selected
cell data. Before applying formats, we must select the data.
Style group frame buttons :-

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 20

 Office Automation Tools

Conditional format :- used to apply the various formats based on condition.

Format as a table :- it is used to apply ready made format to the tabular data.
Cell styles :- it is used to apply the ready made style to the cell data.
Q:- Explain bout the font sizes?
Font Group frame (Home menu) :- These buttons are used to change the font
type, size, style, borders, font colour and back ground colours.
Font size drop down list is used to change the font size of the selected data.
Procedure :- select the data HomeFont select size from font size drop list.
Font colour :- select the data HomeFont  click on any one of the font colour .
Font shading :- select the data HomeFont  click on any one of the back
ground colour

Q:- Explain about the validations in Excel?

Excel allows us to define the range of data which will be accepted/ not
whenever the data is entered by the user.
1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.
The Data Validation dialog box is displayed. Click the Settings tab.
3. In the Allow box, select Whole Number.
4. In the Data box, select the type of restriction that you want, to set upper and
lower limits, select between.
5. Enter the minimum, maximum, or specific value to allow. You can also enter
a formula that returns a number value.
6. Specify the input message and error dialogue boxes and click on OK button.

Q:- Explain about the consolidation?

Combine values from multiple ranges to one new Range.
Procedure:-
1. Data tabdata tools consolidate
2. Select the function type.
3. Add different ranges of data.
4. click on ok button.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 21

 Office Automation Tools

Unit – IV - MS-ACCESS
Q. What is an MS Access? Explain the features of Ms Access?
Ms Access is a one of the sub package of Ms Office tools. It is a RDBMS soft
ware. It is used for maintaining the large type of data in the organization. Ms Access
files will have the extension name “.accdb” .
Features of Ms Access or advantages:
1. It supports to maintain and access large data .
2. It is used to create, edit the database with required tables, queries, reports,
macros.
3. It is used for creating tables and maintaining Relations between tables.
4. It supports for creating queries. Queries are used for accessing, updating and
deleting data from the tables.
5. It supports for creating reports. Reports are used for printing purpose.
6. It is also supports creating data enter forms and sub forms.
7. It is also supports for import or export data from other soft ware.
8. It allows the user to inserting charts and pictures.
9. It allows the user for sorting (alphabet order ) and filtering the data through
expressions and operators.
Limitations:-
1. It is not suitable for large organizations.
2. It is not suitable in multiuser environment.
3. It does not support huge transactions.

Q:- Explain about creating database, opening Database in MS-Access?

1. Creating Database:-
We can create new database whenever we are opening the ms access software.
or
If we are working with other database then we are using new option from
office button for creating new database. while creating new database we need to
define the blank database option, type the database file name and click on create
button.(Office buttons Newblank databaseclick on Create button)
Opening Database:-
We can open an existing database whenever we are opening the MS Access software.
or
If we are working with other database then we are using open option from
office button for opening an existing database. (Office Open select location
select database click on open).

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 22

 Office Automation Tools

Q:- Explain about the parts of ACCESS?

Title bar:- shows the application software name and database name.
It has 3 buttons.
d) Minimize button :- minimizes the application windows.
e) Maximize / Restore button :- displays the application window in full screen or in
resizable window.
f) Close button :- closes the database application.
Quick Access Tool bar:- contains save undo (cancels the recently done work), redo
(repeat the work which was undone by undo) and save (to store) buttons.
Tab bar:- displays all available menus.
Ribbon bar:- displays the selected menus shortcut buttons and allows user to fast
accessing.
Formula bar :- Shows the cell name / address and formula of selected cell.
Horizontal scroll bar:- used to move the spread sheet as a left / right position.
Vertical scroll bar:- used to move the spread sheet up / down position.
Status bar:- indicates the status of the ACCESS.
Objects Navigation Pane :- Displays the available objects like Tables, queries,
forms, reports and macros.
Object Display Area :- Display the opened object i.e. table or queries or forms or
reports.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 23

 Office Automation Tools

Q: Explain about Creating tables using Design View, Inserting records and
saving tables in MSACCESS?
Creating Tables using Design View:-
1. Open / create database.
2. click on create tab.
3. click on table design button from tables frame
4. define the structure of a table.
Ex:-
Field name Datatype description
Regd. No. Text
NAME Text
CLASS TEXT
SCHOOL TEXT
5. Save the table by closing the design view.
Entering records:-
1. Double click on the table.
2. Type the records.
3. Close the table.

Q: Explain about Creating tables using Table Wizard?

The easiest way to add a table to an existing database is with the Table Wizard.
The Table Wizard asks you a series of questions about which fields you want to
appear in your table, and then it does all the dirty work of creating a new table for
you. The Table Wizard can create a variety of different tables to store mailing lists,
inventory, catalogs, and more.
1. Make sure that you have a blank database open.
If you don't have a blank database open, you can create one by clicking the New
button on the toolbar, double-clicking Blank Database, entering a name for your
database, and clicking Create.
2. Click the Tables icon in the Objects bar if it is not already selected.
Access lists all the tables in the current database.
3. Double-click Create table by using wizard.
The Table Wizard dialog box appears, left of the dialog box contains the sample
tables from which you can choose. Start the Table Wizard by selecting the
sample table you want to use.
4. Add or remove the required fields and click next.
5. In final step enter the name of the table and Click Next.
6. Click Finish to create the new table.
In Access 2007, we can create table by using template. The following
templates are available to create table.
a) contacts b) tasks c) issues d) events e) assets

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 24

 Office Automation Tools

Procedure :- Create tab Table group frame Table templates select and click
on any one of the template.

Q:- Explain about the Creating the table using Datasheet View?
We can create table directly by typing the data in a Database table. The
following procedure used to create table using Datasheet view.

1. Open the database that you want to change.

2. On the Create tab, in the Tables group, click Table.

Access adds a new, blank table and opens it in Datasheet view.

3. In the datasheet, double-click the first column header (the colored cell that
reads Add New Field) and enter a name for the field.
4. Click the next field or use the arrow keys to put the focus on the next field and
enter a name for that field. Repeat this process until you finish naming your table
fields.
5. In the first blank cell below a field name, enter the appropriate data.
Access uses the data that you enter to set a data type for the field. For example, if
you enter a date, Access sets the field to the Date/Time data type.
6. Click Save.
Keyboard shortcut Press CTRL+S.
The Save As dialog box appears.
7. In the Table Name field, enter a name for the new table and click OK.

Q:- Explain about the creating table by Importing data?

Or Explain about the import table?
We can create the table by importing data from external like excel, share point
list, text, xml, odbc, html, outlook folder, dbase file, paradox file, lotus 123.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 25

 Office Automation Tools

Procedure :-
1. Goto External data tab.
2. Import frame group Excel
3. Select file name
4. Select option import source data into new table and click ok.
5. Select sheet and click next.
6. Select first row contains column heading and click next.
7. Select index and click next.
8. Select primary key and click next.
9. Enter the table name (import to table).
10. Click on finish button.

Q:- Explain about the creating table by using Link Table?

Or Explain about the linking to table with other file?
We can create the table as a link by importing data from external like excel,
share point list, text, xml, odbc, html, outlook folder, dbase file, paradox file, lotus
123.
Procedure :-
1. Goto External data tab.
2. Import frame group Excel
3. Select file name
4. Select option Link to the data source by creating linked table and click
ok button.
5. Select sheet and click next.
6. Select first row contains column heading and click next.
7. Enter the table name (import to table).
8. Click on finish button.

Q. Explain about the data types in Ms Access?

Data types is used to specify the what type of the data going to be stored in column
of a table. Data types are defined while in designing the table structure in MS-
ACCESS. Access supports the following data types.
1. Text: A text field can contain up to 255 characters.
2. Memo: A memo field contain up to 65000 characters length text and numbers
such as comments or explanation stored in the data type.
3. Number: It includes numbers.
4. Date/Time: It stores date/time. A various date/time formats are available.
5. Currency: It is used to storing the prices.
6. Counter: Sequential numbers are automatically inserted by Ms Access.
The numbering starts with `1` .

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 26

 Office Automation Tools

7. OLE object: Objects which are created in other programs are inserted as an
OLE in MS ACCESS database table.
[Link]/No: Stores Boolean Values.

Q:- Explain about the basic concepts in Ms-Access?

[Link]:- data is organized in the form of tables. Without tables we can’t do any
thing in MSAccess.
2. Query:- it is a command regarding to access the data / to perform updating /
deleting data from the database tables.
3. Form:- forms are screens which allows the users to insert records into the tables or
to Modify the existing data in the tables.
4. Reports:- These are used to create summary of data from different tables. Reports
are created for printing the required data from the multiple tables.

Q: Explain about creating tables, Inserting records and saving tables in

MSACCESS?
Creating Tables :-
1. Open / create database.
2. click on create tab.
3. click on table design button from tables frame
4. define the structure of a table.
Ex:-
Field name Datatype description
Regd. No. Text
NAME Text
CLASS TEXT
SCHOOL TEXT
5. Save the table by closing the design view.
Entering records:-
1. double click on the table.
2. type the records.
3. close the table.
Forms
Q:- Explain about forms in MS –ACCESS? / Advantages of Forms / Creating
forms using form wizard?
Microsoft Access forms allows the use of data to be presented, managed and
controlled in a user-friendly environment making Access more intuitive to use.
Forms are used for reading data from table, to enter data into table, edit the
table’s data and used to navigate in entire database.
Advantages of forms :-
1. Data Entry Forms which give users an easy to use interface to add, modify and
delete records.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 27

 Office Automation Tools

2. Screen Enquiries (Read Only) Forms that allow users to view as read only
screens restricting functionality and filtering for certain records only.
3. Dialog Box Forms which act as a communication interface between users and the
system passing data and values to interact with Access.
4. Menu Screen (Switchboard) Forms which allow users to navigate around the
database system without ever knowing or having access to the background design.
5. Forms are display the records from the table.
6. Forms are used for entering the new data into the tables.
7. Forms are interacting between the user and tables.
8. Forms are user friendly.
Procedure for creating forms:-
1. Click on create tab .
2. Goto Forms group frame  more forms  click on form wizard option.
3. Add the columns from the tables and click next button.
4. Define the layout of the form i.e. columnar / tabular / data sheet / justified and
Click next.
5. specify the style and click next.
6. define name of the form and click on finish button.

Q:- Explain about the creating forms using Design View? or

Explain about the form design view?
We can create forms based on our requirement by using design view. The
following tools are used for designing form.
Form Elements / Tools :-
Text Box :- used to enter the text into table’s column or to view the data from the
column of a linked table.
Label :- Used to display the text on the form when it is running.
Button :- Used to create action command buttons in forms.
Combo Box :- used to display data from the table and to select the required data.
List Box :- Used to display the data from the table and to select the required data.
Option button :- used to select the option.
Check Box :- Used to select list of options.
Form also contains the chart, image, attachment buttons

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 28

 Office Automation Tools

Procedure to create forms using Design view :-

3. Add the required fields from table by double click on the field from field list
window.
4. Add the required controls like list box, combo box radio buttons etc.
5. click on close button.
6. click yes and enter form name and click ok.
Opening Form / Running Form :-
We can run the form directly by double clicking on form from object navigate
window.
Editing Form :-
1. Select the form
2. Click right button of the mouse
3. Select and click on design view.
4. Make modification and close the form by saving option.

Q:- Explain about the columnar forms in ACCESS?

It is used to view, enter, manipulate and search data. Forms can be designed
in different ways like tabular, columnar, datasheet, and justified. ... While
in columnar form the data is displayed one record at a time.
Procedure to Create columnar form :-
1. Click on create tab .
2. Goto Forms group frame  more forms  click on form wizard option.
3. Add the columns from the tables and click next button.
4. Define the layout of the form i.e. columnar / tabular / data sheet / justified and
Click next.
5. specify the style and click next.
6. define name of the form and click on finish button.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 29

 Office Automation Tools

Q:- Explain about the Tabular forms?

A tabular form enables users to update multiple rows in a table at once from a
single page.
Procedure to Create Tabular Form :-
1. Click on create tab .
2. Goto Forms group frame  more forms  click on form wizard option.
3. Add the columns from the tables and click next button.
4. Define the layout of the form i.e. columnar / tabular / data sheet / justified and
Click next.
5. specify the style and click next.
6. define name of the form and click on finish button.

Q:- Explain about the data sheet forms?

A datasheet is a simple view of data arranged in rows and columns.
Procedure to Create data sheet Form :-
1. Click on create tab .
2. Goto Forms group frame  more forms  click on form wizard option.
3. Add the columns from the tables and click next button.
4. Define the layout of the form i.e. columnar / tabular / data sheet / justified and
Click next.
5. specify the style and click next.
6. define name of the form and click on finish button.

Q:- Explain about the chart wizard in ACCESS?

A chart is a graphic that displays numeric data in a compact, visual layout and
that reveals essential data relationships. You can add a chart to a form/report to
visualize your data and make informed decisions. You can bind the chart to a table or
query and customize the chart with a variety of properties. You can even make the
chart interactive.
Procedure to Create Charts :-
1. Create or open a form or report in Design view. To create, select Create > Form
Design or Report Design. To open, right click the form or report name in the
navigation bar, and then select Form Designor Report Design.

2. Select Design > Insert Chart , select a chart type, and then drop it on the
form or report. Chart wizard will be open.
3. Select the table and click next.
4. Add columns click next.
5. Select the chart type next next next.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 30

 Office Automation Tools

6. Enter the chart name.

Changing the chart settings :- Data Tab buttons are used to add or delete the chart
data. Chart tab buttons are used to change the chart settings like legend, access titles,
gridlines etc.
Procedure to edit the chart settings :-
1. Open the form or report in design view.
2. Select the chart.
3. Click right buttons of the mouse on inserted chart.
4. Chart object click on open.
5. use Data / chart tabs to change the chart settings.

Unit – V (Queries, Reports and Relation ships)

Q. Explain about the advantages of queries and types of queries in Ms Access?
A query is a request to the database which helps us to retrieve the data from
the data base table or to manipulate the data in the database tables. Queries are
different types and enable to access the data faster from the columns and rows in a
table.
Advantages of queries:
[Link] and edit data from more than one tables.
2. Select the records that we want to see and sort records.
3. Perform calculations and complete totals.
4. Up date delete append a group of record all of once (or) Make anew table.
Types of Queries:-
1. Select Query:- used to select data from more tables and also used to view the
required data.
2. Update Query:- used to modify the value of particular column / columns. Mainly
used for Calculations.
3. Delete Query:- used to delete the rows from the table based on the condition.
4. Append Query:- it fetches the rows from one table and these are added to the
another table.
5. Make Table:- it selects the rows from one table and the selected rows will be
stored in a new Table.
6. Cross Tab :- used to select the rows from more tables and display in spreadsheet
format.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 31

 Office Automation Tools

Q:- Explain about the Dynaset?

1. A dynaset (short for dynamic set) is a set of data that is dynamically linked back
to the database. Instead of having the query result stored in a temporary table, where
the data cannot be updated directly by the user, the dynaset allows the user to view
and update the data contained in the dynaset. Thus, if a university lecturer queried all
students who received a distinction in their assignment and found an error in that
student's record, they would only need to update the data in the dynaset, which would
automatically update the student's database record without the need for them to send
a specific update query after storing the query results in a temporary table.

The concept was initially put forth by Dr. E.F. Codd, the inventor of the relational
database management system (RDBMS) concept.

2. A dynaset is a temporary set of data taken from one or more tables in the
underlying file. A dynaset may be a query that was defined in an Access database, a
single table, a subset of a table, or the result of joining multiple tables. A dynaset can
be updated if the file is not locked or opened for ReadOnly. The data in the dynaset
are "live" i.e., any changes made to the data as a project (in Visual Basic) is
executing will appear in the corresponding recordset. Note: Dynasets created from
joining tables are typically non-updateable.

Q. Explain about creating Select query in Access?

We can create queries in any one of Two methods.
1. Design view: Create a new query with out visiting a wizard i.e. we need to define
the structure of the query.
2. Query wizard: This wizard creates a select query by step by step method. users
can create the following queries using Wizard.
a. Cross tab query wizard
b. Find duplicate query wizard:
c. Find unmatched query wizard:
Procedure for creating a query using design view & Executing Query:
Before creating a query you should create a table and open the data base which
is containing the tables.
Step 1: Click on create tab.
Step 2: Click on Query design button from others group frame.
Step 3: Add the tables.
Step 4: select the type of query i.e. Select form Query type frame.
Step 5 :- specify the required columns along with sort order in query structure pane.
Step 6: Save (save option from office button) and close query design view.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 32

 Office Automation Tools

Step 7: double click on the query from object navigation pane for executing query.

Q:- Explain about the multi-level sorts in query?

We can retrieve and display the data from multiple table in sorted order
by using queries.
The following procedure is used to create select query that display the
records in sorted order.
Procedure for creating a query using design view & Executing Query:
Before creating a query you should create a table and open the data base which
is containing the tables.
Step 1: Click on create tab.
Step 2: Click on Query design button from others group frame.
Step 3: Add the tables.
Step 4: select the type of query i.e. Select from query type frame.
Step 5 :- specify the required columns along with sort order in query structure pane.
Step 6: Save (save option from office button) and close query design view.
Step 7: double click on the query from object navigation pane for executing query.

Q:- Explain about the Finding incomplete match query?

We can create query which display the records in one table that have no related
records in another table.
Procedure :-
Step 1 :- Create Others click on Query Wizard select find unmatched query
wizard click on ok button.
Step 2 :- Select the table which containing records click next.
Step 3 :- select another table which contains related records  click next
click next.
Step 4 :- Add columns click next enter the name of the query
click on finish button
Step 5:- Double click on the query from object navigation pane for executing query.

Q:- Explain about the Cross tab query?

We can create query which display the records in compact, spread sheet like
format.
Procedure :-
Step 1 :- Create Others click on Query Wizard select Cross tab query wizard
click on ok button.
Step 2 :- Select the table click next.
Step 3 :- Specify the row heading column  click next.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 33

 Office Automation Tools

Step 4 :- Specify the column heading click next enter the name of the query
click on finish button.
Step 5:- Double click on the query from object navigation pane for executing query.

Q:- Explain about the saving queries in ACCESS?

Save option from office button is used to save the query.
Procedure :-
Step 1 :- Query should be in design view mode and define the structure of the query.
Step 2 :- Office button save.
Step 3 :- Enter the name of the query.
Step 4 :- click on ok button.
Reports in ACCESS
Q:- Explain about reports in MS-ACCESS?
Reports are used to view and print information from data base. A report
provides a way to retrieve and present data as meaningful information that we can
use and distribute.
Advantages of reports:
1. Display the information which is required for the user.
2. Can group records into many levels and compute totals and average by
decreasing values from many records at once.
3. A report can group records into convenient category.
4. A report usually gets data from a table or query.
5. Reports are used to take the hard copy in required format.
Procedure for creating report using wizard:-
1. Click on create tab .
2. Select and click on report wizard from reports frame.
3. Add the columns from the tables and click next button and define grouping
levels, click next button.
4. Define the sort order column and click next.
5. Specify the layout of the report (columnar, tabular, justifier) and click next.
6. Define the report style and click next.
7. Enter the name of the report and click on finish button.

Q:- Explain about the printing Reports ?

Print option from print of office button or Ctrl + P is used to print the reports.
Procedure :-
1. Open the report .
2. Office button print print.
3. Specify the printer name, print range and number of copies.
4. Click on Ok button
Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 34
 Office Automation Tools

Q:- Explain about the printing Database?

Print option from print of office button or Ctrl + P is used to print the database
Procedure :-
1. Office button print print.
2. Specify the printer name, print range and number of copies.
3. Click on Ok button
Relational Database
Q:- Explain about the Flat versus Relational Databases?
Flat Database :-
Flat file databases are typically plain text files that store one record per line,
with record fields delimited by whitespace or a delimiting character. Flat file
databases can be read directly by a variety of software applications.
Benefits of Flat database:-
Flat file databases are simple and portable, and typically can be used without
requiring special software.

Disadvantages :-
1. Data Redundancy 2. Data Dependency 3. Data inconsistency
4. Data Security problem 5. Fixed field widths 6. Data sharing problem.
7. Requires more programmers.
Relational Database :-
A relational database uses multiple table structures, cross-referencing records
between tables. Relational databases use "indexes" to quickly find records based on
search criteria. Relational databases generally require a relational database
management system (RDBMS) to manage and access the data.

Advantages of Relational Database :-

1. Minimize Data Redundancy 2. Minimize Data Dependency
3. Maximize data consistency 4. Data Security 6. Data sharing.
7. Data Integrity

Disadvantages :-
1. More expensive 2. Required skilled persons. 3. Require training to users.
4. Need extra hardware and software

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 35

 Office Automation Tools

Terminology:-

Relational Description
SQL Term database
term

Tuple or
Row A data set representing a single item
record

Attribute or A labeled element of a tuple, e.g. "Address" or "Date

Column
field of birth"

Relation or A set of tuples sharing the same attributes; a set of

Table
Base relvar columns and rows

View or result Derived Any set of tuples; a data report from the RDBMS in
set relvar response to a query

Q:- Explain about the types of relations ships?

In a relational database (Access), the data in one table is related to the data in
other tables.
In general, tables can be related in one of three different ways: one-to-
one, one-to-many or many-to-many. The relationship is used to cross reference
information between tables.
Relationship Types
One-to-one relationship:
For each record in one table, there is one and only one matching record in a
different table. It is as if two tables have the exact same primary key. Typically, data
from different tables in a one-to-one relationship will be combined into one table.
Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 36
 Office Automation Tools

One-to-many relationship:
For each record in one table, there may be zero, one or many records matching
in a separate table.
Many-to-many relationship:
This relationship states that records in both tables can have any number of
matching records in the other table.

Q:- Explain about the creating relation ship or defining relationship in

ACCESS?
A relationship helps you combine data from two different tables. In an Access
desktop database, you can create a relationship in the Relationships window.
Procedure :-
Create a relationship in an Access desktop database
1. On the Database Tools tab, in the Relationships group, click Relationships.

2. If you haven't yet defined any relationships, the Show Table dialog box
automatically appears. If it doesn't appear, on the Design tab, in
the Relationships group, click Show Table.
The Show Table dialog box displays all of the tables and queries in the
database. To see only tables, click Tables.
3. Select one or more tables, and then click Add. After you have finished adding
tables, click Close.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 37

 Office Automation Tools

4. Drag a field (typically the primary key) from one table to the common field (the
foreign key) in the other table. To drag multiple fields, press the Ctrl key, click
each field, and then drag them.
The Edit Relationships dialog box appears.

5. Verify that the field names shown are the common fields for the relationship. If a
field name is incorrect, click on the field name and select the appropriate field
from the list.
6. To enforce referential integrity for this relationship, select the Enforce
Referential Integrity box.
7. Click Create.
8. When you are finished in the Relationships window, click Save to save your
relationship layout changes.

Q:- Explain about the viewing relation ships?

Step 1 :- Goto Database Tools.
Step 2 :- Relationship group click on relation ship.
Step 3 :- Relation ship shown in relation ship pane window.

Q:- Explain about the Deleting relation ship?

To remove a table relationship in a desktop database, you delete the

relationship line in the Relationships window. Position the cursor so that it points to
the relationship line, and then click the line. The relationship line appears thicker
when it is selected. With the relationship line selected, press Delete.

1. On the Database Tools tab, in the Relationships group, click Relationships.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 38

 Office Automation Tools

2. On the Design tab, in the Relationships group, click All Relationships.

3. Click the relationship line for the relationship that you want to delete. The
relationship line appears thicker when it is selected.
All tables with relationships are displayed, showing relationship lines.
4. Press the Delete key.
5. Access might display the message Are you sure you want to permanently
delete the selected relationship from your database? If this confirmation
message appears, click Yes.
6. When you are finished using the Relationships window, click Save to save your
relationship layout changes.

Q:- Explain about the redefining or editing relationship in ACCESS?

If your Access database design changes, you might need to change a

relationship between tables. In an Access desktop database, you do this by selecting
the relationship in the Relationships window and then editing it.
Procedure :-
1. To see all the relationships between tables in a desktop database, on the Database
Tools tab, click Relationships.

2. Click All Relationships.

All tables with relationships are displayed, showing relationship lines.

3. Carefully position the cursor so that it points to the relationship line that you want
to edit, and then click the line to select it.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 39

 Office Automation Tools

The relationship line appears thicker when it is selected.

4. With the relationship line selected, double-click it. The Edit Relationships dialog
box appears.

5. Make your changes, and then click OK.

The Edit Relationships dialog box allows you to change a table relationship.
Specifically, you can change the tables or queries on either side of the
relationship, or the fields on either side. You can also set the join type, or enforce
referential integrity and choose a cascade option.

6. When you are finished in the Relationships window, click Save to save all of your
relationship layout changes.

Prepared by S.H. Khaseem MCA, MBA, Lecturer in Computer Science-9010797739 Page 40