Information Technology Lab Record

Microsoft Excel
Introduction to Microsoft Excel:
MS Excel is a component of Ms-Office. It is known as a spreadsheet package. It is used mainly for calculation purpose. It deals with characters and numbers. The older versions used are lotus 123, VisiCalc etc. The default file name in Excel is BOOK1. we call the files in Excel as workbooks. The extension for workbooks is XLS. Apart from other tools like standard and formatting tool bars, in Excel there is another tool bar known as formula bar present below the formatting bar. This formula bar contains a name box to the left of it where you can get the address of the active cell. In a workbook, you have worksheets where you have to work. By default, 3 sheets are present and you can insert a maximum of 255 worksheets in a workbook. You can switch between the sheets by clicking on them. In a worksheet, you will have cells, which are a combination of rows and columns. A maximum of 65536 rows and 256 columns are present. Columns are number from A,B,C,..Z, AA, AB,IV and rows are numbered from 1 to 65536. a pointer known as Cell pointer is used to identify the active cell. The address of the cell is given by its column name and row number like B25, C5 etc. you can navigate throughout the worksheet by using the arrow keys.

Formatting Cells
Formatting text Formatting refers to changing the appearance of text in a cell or range of cells. To make a text stand out from the remaining text, you can format all of the text in a cell or selected characters. Cells:- It is used to format the contents in the cells. When you select this option, it will open a dialog box where you can see 6 tabs. They are number, alignment, font, border, patterns and protection. To give formatting to the cells, you have to select the cells and then opt for this command. The number tab is used to set or apply the number formats to the selected cells like currency, percentage, scientific, date, time, decimal places etc., The alignment tab allows you to either align the contents in the text to the left, right, center or justify in horizontal. You can also align top, bottom and center of the cell in vertical or to can give the text an angle so that it will appear inclined in the cell. Fonts tab is used apply different font styles, font name, font size and other effects to the cell contents. Border tab allows you to set borders to the cells. You can give border style by selecting one from the list. You can also set required color for the border. Sheet:- It is used to format the sheet. It opens another menu with options rename, hide, unhide and background. Rename is used to give a name to the current worksheet. You can also get this command by right clicking on the sheet or by double clicking on the sheet. You can give different background effects for the sheet with pictures that are available in the files. This you can do by selecting the background option. The same command changes to delete background after you apply background.

Page No: 1

Information Technology Lab Record Auto format:- This option allows you to format the selected range of cells with the required format by select any one from the list. When you select a particular format, the preview of it also can be seen.

Figure No: 1 Conditional formatting:- As the name itself says conditional formatting, the cells will be formatting with the format that we give and when the given condition is met. Steps: 1. Select the range of cells to which a conditional format has to be applied. 2. Click the format menu and choose the conditional Formatting option. 3. Specify the required condition in the Condition1 section of the Conditional formatting dialog box.

Figure No: 2 4. Click the Format button to open the Format Cells dialog box. 5. Select a required colour from the Color list box and click the OK button. 6. Click the OK button of the conditional formatting dialog box. The cells that match the specified conditions will be highlighted by the selected colour. You can add another condition by clicking on the Add button in the conditional formatting dialog box. The dialog box expands and displays the second condition.

Page No: 2

Information Technology Lab Record

Figure No: 3 Excel allows you to specify a maximum of three conditions. If none of the specified conditions are true, the cells keep their existing format. If more than one specified condition is true, Excel applies only the formats of the first true condition.

Functions:A function is a predefined program which gives the result to specified values of needed calculations. It reduces the complex task to a simple one. A function consists of a formula, which in turn consists of cell references. There area different types of functions available in Ms-Excel. They are as follows. 1. Mathematical & Trigonometric functions. 2. Date & Time functions 3. Text functions 4. Logical Functions. 5. Database functions 6. Statistical Functions 7. Lookup & reference 8. Information 9. Financial Functions. Parts of Function: Every function is Excel comprises of two parts: Function name Function arguments Function Arguments: Arguments are data received by the function. Each function receives a specific kind of arguments. It can be numbers, text, date or logical values such as True or False Syntax: =<Function name> (<List of arguments>) The following example illustrates the usage of the ROUND function. =ROUND(c6,3) Here, ROUND is the name of the function, which rounds the decimal part of a number to the specified number digits. In this example, the ROUND function will round off the value in the cell C6 up to three decimal places.

Page No: 3

Information Technology Lab Record Rules for Using Functions: Just like a formulae, functions also follow certain rules. 1. All functions must begin with sign. 2. The arguments of a function must be enclosed within brackets. 3. The arguments should be separated by a comma. 4. The cell range should be mentioned using a colon. When entering a functions, you should start it with an = (equal to) symbol. Function is identified with a parenthesis. Eg: Sum( ), avg( ). Whenever you select any function, a function wizard appears which will show the next step to execute the given function. When you select the function command, a dialog box appears where to the left side you can see the different types of functions. When you select a particular category, the functions in that category are shown to its right side. Mathematical & Trigonometric Functions:All the mathematical calculations like addition, multiplication, power etc can be done easily by using these functions. You can also find the trigonometric values of sin, cos, tan etc., using these functions. a) Sum( ):- This function is used to add the given values that you pass as the parameters. The parameters can be the values or may be the cell addresses containing the values. Syntax: =sum(number1, number2,) b) Fact( ):- It will return the factorial of a given number. Syntax: =Fact(number) c) Power( ):- This function gives the result after raising the number to its power. Eg: =power(2,6) Result: 64 d) Mod( ):- Gives the remainder after dividing the given number. Eg: =mod(7,3) Result: 1 Date & Time functions:These functions are related to system date and time. In Excel, every date and time is identified with a number. a) DateValue( ):- This function returns the numbers that represents a date in Excel. The parameter you have to specify is the date in text i.e., in single quotes. When entering the date, you have to give month, day and then the year. Syntax: =datevalue(date_text) Eg: =datevalue(12/22/2007) b) Today( ):- It will not take any parameters but will display the system date. Eg: =today( ) c) Month( ):- It will return the number of the month (1 to 12) from the given date. Eg: =month(date_text) d) Day( ):- It will return the day of the month (1 to 31) from the given date that is given as a string. Syntax: =day(date_text) Eg: =day(12/22/2007) Result: 22

Page No: 4

Information Technology Lab Record e) Timevalue( ):- This function displays the value of the time in Excel. The value for the time will be less than 1. Syntax: =timevalue(time_text) Text functions:These functions deals with text or the string you enter in the cells. a) Lower( ):- It is used to convert the given string into lower case. Syntax: =lower(string) Eg: =lower(AURORA) Result: aurora b) Upper( ):- It converts the given string into upper case. Syntax: =upper(string) Eg: =upper(madhu) Result: MADHU c) Proper( ):- Converts the starting character of each word into upper case and rest into lower case. d) Len( ):- It gives the number of characters present in the given string including spaces. Logical Functions:These functions are used to display the appropriate value by checking the given condition. a) If( ):- This function checks for a condition specified as the first parameter, and when it is true displays the second parameter in the cell otherwise third parameter value is displayed. Syntax: =if(condition, true value, false value) Eg: =if(3>5, good, best) Result: best b) And( ):- It will return true when all the conditions passed as the parameters are true. Otherwise it will return false. Syntax: =and(conditiona 1, condition 2) Eg: =and(2>1, 5>4,7>3 ) Result: True c) Or( ):- It will return true when any of the condition is true. Syntax: =or(conditiona 1, condition 2) Eg: =or(3>5,9>2,45>23 ) Result: True Statistical Functions:It is used to find the average, standard deviation, correlation etc., values. a) Average( ):- It returns the average of the given values that you pass as the parameters. Syntax: =average(number1, number2, number3,) b) Max( ):- It returns the maximum value from a given range of values. c) Min( ):- It returns the minimum value from the given number.

The Insert Function Feature:

By now, you must be familiar with various functions available in Excel. Working with these functions are as easy as learning them. Excel provides a feature called Insert Function, which helps you to work with these functions more easily. To invoke the Insert Function dialog box,

Page No: 5

Information Technology Lab Record 1. Click the Insert menu. 2. Choose the Function option. The Insert Function dialog box allows you to search for a function text box. 1. Type the function in the Search for function text box. 2. Click the Go button.

Figure No: 4 Inserting Functions: The insert Function dialog box enables you to insert a specific function for the data in the worksheet. Assume that you have the marks of a student in five subjects. You can find the average mark of the student using the Average function under the Statistical category.

Figure No: 5 3. Enter the cell range in the Number1 text box 4. Click the OK button.

Page No: 6

Information Technology Lab Record

Charts:
Charts are pictorial representation of large volumes of data. Representing data in terms of charts makes it interesting and easy for the users to see comparisons and trends in data. There are different types of charts available namely bar graphs, pie charts, three dimensional charts, etc. Components Charts: A chart is linked to the data for which it is created. The chart will be updated automatically when you change the worksheet data. There are four components of a chart. They are: Data Axis labels Legend Data Labels

Y-Axis Label

Data Label

Legend

Figure No: 6

Steps for Chart Preparation

X-axis Label To create a chart of particular type, first type the data in the sheet, place the cell pointer at any of the cell of the data, and select this chart option. When you select this command, it will open a chart wizard, in which the following steps are provided to create an effective and attractive chart. A wizard is the one that shows the step by step procedure to executive a particular command. Step-1:: In this step, you will be allowed to select the type of the chart like column, pie, xy scatter, etc. in this step, all the chart types are provided with samples. Select any one and click on next button. Step-2:: In this step we will be allowed to enter the data source(range) of which we want to create a chart. In the same dialog box we will be allowed to opt or specify the series which has to be took on x-axis. Specify the required data and click on next button. Step-3:: In this step, the chart options will be provided according to the chart type selected. They are as follows:

Page No: 7

Information Technology Lab Record a) Title: This tab allows the user to give the name(titles) to the char. They are chart title, X-axis and Y-axis. b) Axes: This tab allows the user to have or remove the axes series i.e., display of X-axis contents or Y-axis contents c) Gridlines: This tab allows the user to have or remove the gridlines in the graph from X-axis and Y-axis d) Legends: This tab allows the user to have or remove the legends(marks identifiers) and placement of legends(left, right, top, bottom). e) Data labels: This tab allows the user to have he values in the graphs. f) Data table: This tab allows us to have a data table of the chart along with the chart in the sheet. Specify the above settings and click on the next button. Step-4:: This is the final step of the chart wizard where we will be allowed to specify the location of the chart whether in the same sheet or in the new sheet. Click on the finish button and a chart is inserted in the location you specify. The chart and data table are linked. When you make any changes in the table, they will get updated in the chart. You can also make changes to the chart like chart type, data source, format the chart etc. this you can make by selecting the chart in the sheet and selecting the required command from the short cut menu, which appears when you right click on the chart. Types of Charts You can display the chart along with the data in the same worksheet or display I in a separate worksheet. Thus, the charts in Excel can be classified as: Chart Sheet: A chart sheet is a part of a worksheet that contains only a chart. Chart sheet can be used when you want to view the chart or any report separately from the worksheet data. Embedded Chart:- An embedded chart is placed on a worksheet rather than on a separate sheet. Embedded charts can be used to view or print a report with its source data or other information in a worksheet.

Page No: 8

Information Technology Lab Record

EXPERIMENTS
Experiment 1:- Create a MS-Excel worksheet to illustrate sorting. To Sort the Data:

Steps: 1. Select data on list to be sorted. For example salary in the above figure. 2. Click the Data Menu and select the sort option. The sort dialog box appears. 3. Select the ascending and descending option in the Sort by section

4. Click the OK button

Page No: 9

Information Technology Lab Record

Experiment 2:
Create worksheet with following fields Empno, Ename, Basic Pay(BP), Travelling Allowance(TA), Dearness Allowance(DA), House Rent Allowance(HRA), Income Tax(IT), Provident Fund(PF), Net Pay(NP) Given: DA= 30% of BP, HRA=20% of BP, TA=17.5% of BP, IT=15% of BP, PF=12.5% of BP

Steps:1. Create an Excel Worksheet for an employee pay roll system. 2. Enter the details of Employee as given and calculate the DA, TA, HRA, IT, PF as a percentage on the basis of Basic Pay. 3. Calculate the Net Pay by using the formulae Gross Pay= DA+TA+HRA+BP Deductions=IT+PF Net Pay= Gross Pay-Deductions

Page No: 10

Information Technology Lab Record

Experiment 3:Create an Excel Worksheet with fields as Roll No, Name, Marks and percentage.

Steps:1. Create an Excel Worksheet by inserting field names 2. Calculate total marks, Averages, Results and Divisions.(By using Mathematical and Logical Functions) 3. Represent the Data by inserting the Pie Chart.

Page No: 11

Information Technology Lab Record

Experiment-4:
Create an Excel Worksheet for the monthly sales of a product and also represent the data by using bar chart?

Steps: 1. Create an Excel sheet with the given Data 2. Assuming sales of a product for different months can be represented by using bar chart.

Page No: 12

Information Technology Lab Record Experiment 5:- Demonstrate any two Statistical functions using MS-Excel. Standard Deviation: The most comprehensive description of dispersion are those that deal with the average deviation from some measure of central tendency. Two of these measures are important to our study of statistics : the variance and standard deviation both of these tell us an average distance of any observation in the data set from the mean of the distribution. SD = (X-)^2/N = 0.051/15 = 2 X= Observation = Population Mean N=Total number of elements in the population = Sum of all the values(x-) 2 or all the values x2 = Population standard Deviation 2 = Population Variance

Answer:

2=

(x-) 2/N

0.05096/15 = 0.0034 percent squared

Page No: 13

Information Technology Lab Record Experiment:6 Demonstrate financial functions using MS-Excel.

Capital Budgeting Techniques

Traditional (or) Non-discounting Pay back period Accounting rate of return Time-adjusted, (or) discounted cash flows Net Present value Profitability Index Discounted pay back Terminal value Internal rate of return

NPV: - The NPV of an investment proposal may be defined as the sum of the present

values of all the cash inflows less the sum of present values of all the cash out flows associated with a proposal. A rate of discount must be specify and applied to both inflows and outflows in order to find out their present values

CALCULATION OF NPV:NPV=Excess of PV of inflows over PV of outflows

Page No: 14

Information Technology Lab Record = PV of cash inflows-PV of outflows CF1 + (1+k)1 n NPV= CFi i =o (1+k)i Here, NPV= Net Present Value CFi= Cash Flows Occuring at Time (0,1,2,.n) K=Discount Rate n =Life Of The Project in years CF2 + (1+k)2 CFn (1+k)n __ CFo

Page No: 15

Information Technology Lab Record

Microsoft Access
Introduction to Microsoft Access::
Microsoft Access is a powerful multi user DMBS developed by Microsoft Corporation. It is used to store and manipulate large amounts of information. MS Access database consists of objects such as tables, Queries, Forms, Reports and Macros. MS Access enables you to: Create tables to organize data into manageable related units (tables). Enter, retrieve and modify data. Extract information based on specific criteria. Create forms to enter data. Generate reports Database in Access have a default extension of .mdb. Access maintains index files for tables. An index is an internal table of values maintained by MS Access to store the order of records. Index objects thus provide faster and efficient retrieval of data stored in a table. Database indexes works just like book indexes. When you want to find a particular topic in a book, you turn to the index and look up the content page number. Then you turn to the relevant page. Similarly, in a table, indexes controls the way the data is accessed.

Features of MS Access
MS Access can be used by both beginners and those who have previous DBMS experience. Some of the features of MS Access are described here. Windows-based Application MS Access is a Windows-based application. You can cut, copy and paste data from any windows application to and from Access. You need to ensure that either Windows NT or Windows 95 is installed as the operating system on you machine before you install MS Access. Large Data Management Capacity MS Access maintains a single disk file for a database and all its associated objects. Access allows more than 32,000 tables in a database. MS Access can create links to tables in other databases, such as FoxPro. MS Access can support more than 250 users to access data at a time. These features make Access a powerful DBMS, capable of handling large volumes of data, spread across several databases and multi users. Importing and Exporting External Files MS Access allows you to import data from or export to FoxPro, Excel, Oracle and other data formats. Importing data from other sources creates and Access table, exporting and Access table creates a file in the format that you are exporting to. For example, when you imports table from Excel format, an Excel Workbook will be created. Wizard Wizard is a utility that provides guided sequence to perform complex task easily and quickly Wizards ask you questions about the contents, style and format of the object that you want to create, such as a table or a report, and then create the object automatically. You need to answer the questions by selecting options like Yes/No.

Page No: 16

Information Technology Lab Record Built-in Functions MS Access includes small programs known as functions that perform simple calculations or data formatting. MS Access includes mathematical, date, time and string functions. It can be used to create expressions for calculations in forms, reports and queries. Built-in Security Since MS Access is a multi-user database, security features are built into the DBMS. You can assign a password for loading Access so that only authorized users can use it. Selective hiding of database objects such as reports, forms are also possible.

Designing a Database:
Before you start creating a database, it is essential to create a good database design. Planning the design structure is important, as an improper design will cause changing the design structure. A good database design ensures that the database is easy to maintain and store data. To plan and design a database the points to be considered are: The purpose of the database The number of tables and the type of information each table was contain Columns that will form each tables Relationship between tables The implementation of the above points is explained precise in the following paragraph. Consider a scenario, where you have to create a Player database. The purpose of the database is to store and retrieve the data promptly. The database contains two tables the Player Details and Team Details. The Player Details table will contain fields, such as Player No, Player Name, Runs Scored and Wickets taken. The Team Details table will contain fields, such as Team No, Team Name, Ranking and Coach Name. Team ID T001 T002 T003 T004 T005 The Team Details table is shown below Team Name Coach Name India John Wright Australia John Buchanan England Duncan Fletche Sri Lanka Dave Whatmore South Africa Eric Simons Overall Ranking 5 1 6 3 2

Primary Key
The data available in the database needs to be checked for accuracy and correctness. In order to ensure this, data integrity has to be enforced. Data integrity refers to the process of maintaining error free data in databases that can be easily retrieved. In order to maintain data integrity, keys are used. Keys help in identifying the records in a table. Thus, they play a very important role in database management. Primary Key Every table should contain a field that uniquely identifies each record stored in the table. This field is called the primary key of the tab le. Once you set a primary key for a table, to ensure uniqueness, MS Access will prevent any duplicate values from being entered in the primary key field. When tow players have same name, such as Waugh or Flower, it is difficult to relate to the player unless their first name are different. To ensure that each player is identified

Page No: 17

Information Technology Lab Record more precisely, a unique reference value can be attached to their detail, just like how each student in a class has a roll number. The primary key helps in uniquely identifying the records in a table. For example the player No is the primary key in the players Details table and the Team No is the primary key in the Team Details table.

Getting Started
Database Objects
MS Access allows you to develop easy-to-use forms and create meaningful reports using the processed data. MS Access database consists of database objects such as table, queries, forms, reports, pages, macros and modules. These objects are stored in a single database. The database objects are discussed below. Tables:- Contain data about particular topic. In a table data is arranged in row and column format. Queries:- Retrieve information from a database on a certain condition. The set of records returned by a query is known as dynaset. For example, a query can be used to extract details of players in the England team. Forms:- Provide an easy to use interface for users to enter, view and modify data in a table. Reports:- Present data from tables in printed format. It is also possible to generate a report based on a query Pages:- Enable users to view data from the Internet that is stored in an MS Access database or MS-SQL server database. Macros:- Series of commands and functions that can be executed to perform a specific task. For example, you can create a macro to print a report automatically. Modules:- Set of Visual Basic programs that are used to perform advanced database operations.

Starting MS Access
To start MS Access: 1. Click the Start button. 2. Choose the All Programs Option. 3. Click the Microsoft Access option

Page No: 18

Information Technology Lab Record

Figure No: 7 The MS Access windows appears

Components of MS Access Window

When you invoke MS Access application, the Microsoft Access windows appears. The various parts of the MS Access window.
Title Bar
Menu Bar

Figure No: 8 Title bar It shows the name of the database. Menu bar:- It contains a set of menus available in MS Access. Each menu consists of a set of commands of a specific type. Toolbar:- It contains shortcut for commands that are available in various menus.
Page No: 19

Task Pane

Information Technology Lab Record Status bar:- It shows the current status of the ongoing task. Task Pane:- It contains shortcuts to the frequently performed tasks like creating a new database or opening an existing database.

Creating Database
Creating Database A database can either be created by using the Database Wizard option or by using the Blank database option. Database Wizard Used to create tables, forms, queries and reports by following a series of steps provided by the wizard. The wizard provides a step-by-step process to achieve the desired result. Blank Database Option Used to create a blank database. All other database objects should be created manually. Creating a Database Using the Database Wizard MS Access provides a number of templates that can be used to create a database of a specific type. For example, you can create a database that contains your list of friends and their contact information. MS Access also provides easy methods to create the database of your choice. Database Wizard in MS Access provides you with a sequence of guided steps to create databases. The steps to invoke the database wizard option are: 1. Click the General Templates. Option in the New from template section of New File task pane. 2. The Templates dialog box appears (refer Figure 9)

Figure No: 9 3. Select the Databases tab. 4. Select the Contact Management option. 5. Click the OK button.

Page No: 20

Information Technology Lab Record The File New Database dialog box appears (refer Figure 10)

Figure No: 10 6. Enter the name of the database in the File name text box. In Figure 10, Contact_list is the database name entered in the File name text box. 7. Click the Create button. The Database Wizard dialog box with a list of tables that can be created under the Contact_list database appears (refer Figure 11)

Figure No: 11 8. Click the Next button. The Database Wizard dialog box with the table and their corresponding field named appears (refer Figure 12)
Page No: 21

Information Technology Lab Record

Figure No: 12 9. Select the required table from the Tables in the database list box. 10. Select the fields to be added from the Fields in the table list box. 11. Click the Next button. The Database Wizard dialog box with different style for the screen display appears (refer Figure 13)

Figure No: 13 12. Select a style from the What style would you like for screen displays? Section. 13. Click the Next button. A list of formats for displaying reports appears in the Database Wizard dialog box (refer Figure 14)

Page No: 22

Information Technology Lab Record

Figure No: 14 In Figure 8 the Style Format is selected. A Database Wizard dialog box prompting you to enter the name of the database appears (refer Figure 15)

Figure No: 15 14. Enter the title of the database in the What would you like the title of the database to be? Text box The Database Wizard dialog box appears (refer Figure 16).

Page No: 23

Information Technology Lab Record

Figure No: 16 15. Enable the Yes, start the database checkbox. 16. Click the Finish button. The Main Switchboard dialog box appears (refer Figure 17) The database Contact_list will get created with all the required forms, reports corresponding to the database

Figure No: 17 The Contact_list database window appears (refer Figure 18).

Page No: 24

Information Technology Lab Record

Figure No: 18

Components of Database window

The various components of the database window (refer Figure 18) are: Title bar:- It displays the name of the database Database window Tool bar:- It contains buttons for opening, designing, creating, removing and viewing the various objects in a database. Object bar:- It contains the buttons for various database objects, which are Tables, Forms Queries, Reports, Macros or Modules. When you click these buttons the corresponding objects open on the right pane. Right Pane:- It outlines all the corresponding elements associated with selected database objects in the object bar.

Creating Database Using blank Database

The blank database option helps you to create a blank database in which the tables, queries, forms and reports can be added according to your requirement. The database templates provide limited number of options. If you would wish to create a database for which you do not find a suitable template, you can use the blank database option to create a blank template and create tables and other the objects on your own. The steps to create a blank database are: 1. Click the Blank Database option on the New section of the New File task pane. The File New Database dialog box appears (refer Figure 19).

Page No: 25

Information Technology Lab Record

Figure No: 19 2. Enter the name of the database in the File name text box. In Figure 19 the database is named as Cricket. 3. Click the Create button to complete the procedure.

Figure No: 20

Creating Tables
Creating Tables: MS Access allows you to create tables using the Design View, Datasheet view and Table Wizard options. These options are available in the New Table dialog box. To invoke the New Table dialog box:
Page No: 26

Information Technology Lab Record 1. Open the database. The database window will appear. 2. Click the Tables button on the Objects bar. 3. Click the New button on the database tool bar. The New Table dialog box appears (refer Figure 21)

Figure No: 21 The following table explains each option in the New Table dialog box. Option Purpose Datasheet View Used to create a table in a datasheet format Design View Used to assign fields for the new table and modify field properties Table Wizard Used to create a table through a guided sequence of steps. Import Table Used to import external data from other applications Link Table Used to link to a data source which has already been created. You can create a table of desired format by selecting the appropriate option on the New Table dialog box. The steps to create tables using different options in the New Table dialog box are given below.

Creating a Table Using Table Wizard

The Table Wizard is used to create tables by taking you through a series of steps. You have to answer the Wizards queries by clicking buttons, selecting fields, and entering text to create a table. For example, you can create a Guest_list table using the Table Wizard. The Guest_list table provides you guidance through a sequence steps to create a database that can store details of guest to be invited for a party. To create a table using a table wizard: 1. Open the New Table dialog box. 2. Select the Table Wizard option. 3. Click the OK button.

Page No: 27

Information Technology Lab Record The Table Wizard dialog box appears (refer Figure 22)

Figure No: 22

There are two categories of tables namely, Personal and Business based on which the list of tables under the Sample Tables list box changes. 4. Select the table category. << 5. Select the table from the Sample Tables list box. 6. Select the fields to be added in the table from the Sample Fields list box. In Figure 22 the Personal category is selected. The Guests table and few of its corresponding fields are selected. 7. Click the Next button. The Table Wizard dialog box prompting you to enter the table name will be displayed( refer Figure 23).

Page No: 28

Information Technology Lab Record

Figure No: 23 You can either select the primary key to be set for the table or the wizard will set a primary key by itself. 8. To set a primary key select the radio button No, Ill set the primary key, in the Do you want the wizard to set a primary key for you ? section. In Figure 23 Guest_list is the table name entered in the text box. 9. Click the Next button. The Table Wizard dialog box prompting you to enter the details about the primary key field appears (refer Figure 24)

Figure No: 24 10. Select the field, which has to be set, as the primary key from the what field will hold data that is unique for each record? List box 11. Select the Numbers I enter when I add new records option.

Page No: 29

Information Technology Lab Record 12. Click the Next button The Table Wizard dialog box prompting you to enter the action to be performed after creating the table appears (refer Figure 25)

Figure No: 25 13. Select the Enter data directly into the table option. 14. Click the Finish button to complete the procedure. The datasheet view of the Guest_list table will be displayed (refer Figure 26)

Figure No: 26

Page No: 30

Information Technology Lab Record Experiment 1:

Creating a Table Using the Design View

It is easy to create tables using Table wizard. The Table wizard provides only a limited number of templates to create tables. You can use the Design View option in the New Table dialog box to create a table that is not listed in the sample table templates provided by the Table Wizard. For example, you can use the design view to create a table in the Cricket database. To create a table in design view: 1. Open the database. 2. Select Tables option from the Objects bar. 3. Click the New button on the database tool bar. 4. Open the New Table dialog box. 5. Select the Design View option. 6. click the OK button The table will be displayed in the design view (refer Figure 27)

Field entry pane

Field Properties pane

Figure No: 27 The table design view window consists of two parts: The Field Entry pane Is used for entering the field names, data type and their description. The Field Properties pane Is used for setting the required properties for each of the fields. The Field Properties pane is used to set the properties for each of the fields in the Field Entry pane. Some of the important options of the Field Properties pane are as follows: Field Size:- It determines the number of characters or digits that can be stored in a field. Format:- It specifies the format in which a particular field value has to be displayed. The format varies for different data types. Input Mask:- It allows you to control the user input. It consists of literal characters such as brackets, periods or hyphens and mask characters, which specify the valid characters allowed to be stored in the field. Caption:- It can be used to display alternate names for fields in forms and reports. Default Value:- It displays a value automatically for records corresponding to a field. This value can be changed during data entry if required.

Page No: 31

Information Technology Lab Record Validation Rule:- It is used to accept only certain values into a field if it follows the given condition. Validation Text:- It is used to display an error message if the Validation Rule is not satisfied. Required:- This property when set to Yes will ensure that the user does not skip an entry for the field during data entry. Allow Zero Length:- This property when set to Yes would denote that the field can be left blank. Indexed:- An index is used for faster data retrieval. Indexed property indicates if the table data should be indexed based on the specific field. The fields whose data are retrieved often, can be indexed to facilitate faster retrieval of records. 7. Enter the data in the Field entry and Field Properties areas. The table will be created in the design view.

Setting the Primary Key

You need to ensure that the primary key field uniquely identifies each record stored in the table before you set the primary key of a table. In the following table the Team ID field can be used to uniquely identify the records. Hence, this field can be set as the primary key field in the table. The steps to set primary key are: 1. Select the field to be set as a primary key. 2. Click the Edit menu. 3. Click the Primary key option. The design view window appears with a key symbol next to the field set as the primary key (refer Figure 28)

Figure No: 28

Working with Fields and Records

Working with fields MS Access supports modification of the design of the existing table. After creating a table, you may like to. Change field names
Page No: 32

Information Technology Lab Record Delete one or more unwanted fields Insert one or more new fields in the table Rearrange the order in which fields appear The procedures for changing the field name, deleting, inserting, and moving the fields are discussed below. Changing a Field Name In a database it is very essential to give meaningful names to the fields. If the name is not meaningful, it is quite possible that you might enter incorrect data. The Player Details table has already been created. One of the fields in the table is No of Centuries. You can change the field name to No of Hundreds. To change the field name: 1. Open the Cricket database. The database windows appears. 2. Click the Tables option on the Objects bar. 3. Select the Player Details table on the right pane. 4. Click the Design button on the database window tool bar. The design view window appears with the name of the table on the Title bar (refer Figure 29)

Figure No: 29 5. Click the No of Centuries field in the Field Name column. 6. Delete the word Centuries and type Hundreds 7. Save the table design. The field will be renamed (refer Figure 30)

Page No: 33

Information Technology Lab Record

Figure No: 30 Deleting a Field It is possible to remove any field if you feel that it is unwanted. For example you can delete the Ranking filed in the Player Details table. To delete a field: 1. Open the Cricket database. 2. Click the Tables option on the Objects bar. 3. Select the Player Details table on the right pane. 4. Click the Design button on the database window toolbar. 5. Select the field Ranking. 6. Click the Delete option on the Edit menu. The caution message box asking for a confirmation to delete appears. (refer Figure 31)

Figure No: 31 7. Click the Yes button. The field will be deleted (refer Figure 32)

Page No: 34

Information Technology Lab Record

Figure No: 32 8. Save the table design. Inserting a Field You can insert a field in the Design View at the end of the table or above an existing field. For example, you can insert a field to store age in the Player Details table above the Runs Scored field. To insert a field: 1. Open the Cricket database. 2. Click the Tables option on the Objects bar. 3. Select the Player Details table on the right pane. 4. Click the Design button on the database window toolbar. 5. Select the field Runs Scored. 6. Click the Insert menu. 7. Click the Rows option. A new row is inserted above the Runs Scored field. 8. Enter the name Age in the Field Name column. 9. Select the data type Number from the drop-down list box in the Data Type column. 10. Save the table design. A new field Age will be created (refer Figure 33)

Page No: 35

Information Technology Lab Record

Figure No: 33

Working with Records

MS Access allows you to make changes to a record or remove an unwanted record. To modify records, you have to open the table in the datasheet view. Editing a Record Data stored as records can be modified at any point in time. For example, in the Player Details table, the value entered in the field No of Matches Played corresponding to the player Anil Kumble can be changed from 200 to 210 to update the data according to current statistics. To make changes to a record: 1. Open the table in datasheet view. 2. Place the cursor in the required cell. 3. Remove the existing value 200 4. Enter the new data 210. The value in the No of Matches Played field corresponding to Anil Kumble will be changed. Deleting a Record You can delete a record if it is not required. To delete a record: 1. Open the table in the datasheet view. 2. Select the required record. To select a record place the mouse pointer to the left of the record and click the mouse button. The record selector will appear and the record will be highlighted. 3. Click the Edit menu. 4. Click the Delete option. A warning message will be displayed asking for confirmation. 5. Click the Yes button. The selected record will be deleted. Finding and Replacing a Record When a table contains a large volume of data, searching for a particular piece of information will take a long time. The Find feature of MS Access allows you to do exactly the same. To find a particular value and replace it with required value:
Page No: 36

Information Technology Lab Record 1. Open the table in datasheet view. 2. Click the Edit menu. 3. Click the Find option. The Find and Replace dialog box appears (refer Figure 34)

Figure No: 34 4. Enter the required value, which has to be found in the Find What: text box 5. Select the field in which the value has to be searched in the Look In: list box. 6. Click the Find Next button. The cursor will be positioned at the required value. You can also replace this value if required. 7. Click the Replace tab. The list boxes corresponding to Replace option will be displayed (refer Fig. 35)

Figure No: 35 8. Enter the value to be replaced with in the Replace With: text box. 9. Click the Replace button. The search value will be found and replaced with the required value. Sorting Records Sorting is arranging records in a table, so that the records can be retrieved quickly and easily. It is possible to sort records based on one or more fields. For example, records can be arranged in the Team Details table based on the ascending order of the Team Name field. To sort the records: 1. Open the Team Details table in the datasheet view. 2. Select the Team Name field. 3. Click the Records menu. 4. Choose the Sort option.
Page No: 37

Information Technology Lab Record 5. Click the Sort Ascending option. The records will be sorted based on the Team Name field(refer Fig. 36)

Figure No: 36 In addition to single field sorting, MS Access supports multiple fields sorting. To sort records based on multiple fields: 1. Open the table whose fields have to be sorted in the datasheet view. 2. Select the fields to be sorted 3. Click the Records menu. 4. Choose the Sort Option. 5. Click the required sort Order. The records will be sorted based on the selected field. Filtering Records: The Filter option is one of the interesting features of MS Access. It is used to extract a specific set of records. For example, you can get information on the players who have played for the Australian team using the filter option. To extract the information using Filter option: 1. Open the Player Details table in the Datasheet View. 2. Place the cursor in the field Team ID. The information on the players who have played for the Australian team has to be extracted. Therefore, place the cursor in any record for which the Team ID is T002. 3. Click the Records menu. 4. Choose the Filter option. 5. Select the Filter by Selection option. The records of players playing for the Australian team appear in a new datasheet (refer Fig. 37)

Page No: 38

Information Technology Lab Record

Figure No: 37 Removing the Filter: To remove an existing filter: 1. Click the Records menu. 2. Click the Remove Filter/Sort option. The original datasheet will be displayed.

Extracting Data Across Tables

MS Access allows you to retrieve data across tables. When related data are distributed across tables, you have to establish relationship among tables to retrieve data. For example, to retrieve the details of players playing for the South African team you need to relate Player Details and Team Details tables. Having known the name of the team, you need the Team ID to fetch the players playing for South African team. The field Team ID, which is the primary key of the Team Details table, is present in the Player Details table. The Team ID field in the Player Details table can contain only the data that are present in the Team ID field of the Team Details table. Hence, the Team ID field in the Player Details table can be set as the foreign key to relate the two tables. To set a relationship: 1. Click the Tools menu. 2. Click the Relationships. Option Relationships windows with the Show Table dialog box appears (refer Fig. 38)

Page No: 39

Information Technology Lab Record

Figure No: 38 3. Select the Team Details table and click the Add button 4. Select the Player Details table and click the Add button. 5. Click the Close button. The tables Team Details and Player Details will be added to the Relationships window (refer Fig. 39)

Figure No:39 6. Select the field Team ID in the Team Details table. 7. Drag and drop the field on the Team ID field in the Player Details table. The Edit Relationships dialog box appears(refer Fig.40)

Page No: 40

Information Technology Lab Record

Figure No: 40 The Ensure Referential Integrity in the Edit Relationship dialog box provides a set of options, which ensures that the relationship between the two tables is maintained based on the data stored in a common field. The Cascade Update Related Fields option ensures that when there is a change in the value of the primary key field, the corresponding value changes in all the tables in which the field is a foreign key. The Cascade Delete Related Records option ensures that when a primary key record is deleted, all related records in other tables are deleted. 8. Select the Ensure Referential Integrity option. 9. Click the Create button. A one to many relationship will be created between the two tables(refer Fig.41)

Figure No: 41 You can make changes to the existing relationship. It is possible to change the fields on which the tables are related. To change the existing relationship: 1. Click the Tools menu. 2. Click the Relationships option. The Relationships windows appears 3. Click the Relationships menu. 4. Click the Edit Relationship.. option The Edit Relationship dialog box appears. 5. Change the field names based on which the relationship should be set. 6. Click the Create New button. The new relationship will be created.
Page No: 41

Information Technology Lab Record

Experiment-2: Querying tables:

Creating a Query Queries are one of the most important features of database management system. The process of accessing the database and retrieving data selectively is known as querying. Query is database object, which is used for extracting data from one or more tables based on specific conditions. The set of records returned by a query is known as Dynaset. Queries can be created using the Query Wizard or the Design View. Creating a Query using the Query Wizard The Query Wizard option provides guided steps to create a query. It helps to view the selected fields and records from one or more tables with required information. For example, you can crate a query to view the name, runs and wickets details of all the players. To create a query as discussed above: 1. Open the database. 2. Click the Queries option on the Objects bar. The New Queries dialog box appears (refer Fig.42).

Figure No: 42 The following table explains options in the New Query dialog box. Option Purpose Design View Used to create a query in Design View. Simple Query Used to create a select query from selected fields. Crosstab Query Used to summarize the query in a spreadsheet format Wizard Find Duplicates Used to locate duplicate records in a table. Query Wizard Find Unmatched Used to locate records in one table that do not have matching Query Wizard records in a related table. 3. Select the Simple Query Wizard Option. 4. Click the Ok button. The simple Query Wizard dialog box appears(refer Fig.43)

Page No: 42

Information Technology Lab Record

Figure No: 43 5. Select the Player Details table from the Tables/Queries drop-down list box. 6. Select the Player Name, Runs and Wickets from the Available Fields list box. 7. Click the Next button. The Simple Query Wizard dialog box prompting you to enter the query format appears (refer Fig.44)

Figure No: 44 The would you like a details or summary query? Section consists of two options namely details and summary. Details: An option to display a Dynaset of records of all the fields selected in each of the tables. Summary: An option to display a dynaset of records in which the average, sum, minimum and maximum of certain fields are displayed. 8. Select the Details option.

Page No: 43

Information Technology Lab Record 9. Click the Next button. The Simple Query Wizard dialog box prompting you to enter the query title appears (refer Fig.45)

Figure No: 45 10. Enter the name of the query as Player_Details in the What title do you want for you query? Text box. 11. Select the Open the query to view information option in the Do you want to open the query or modify the querys design? Section. 12. Click the Finish button. The Player_Details query will be created. Fig.46 shows the dynaset of the Player_Details query.

Figure No: 46

Page No: 44

Information Technology Lab Record

Creating a Query in the Design View

The Design View option can be used to create a query without the wizards help. The tables, fields and criteria for the query should be specified individually to create a query in the design view. For example, you can extract the name, player name and player ID of all the layers using the design view option. To create a query in design view: 1. Open the Cricket database. 2. Click the Queries option on the objects bar. 3. Select the Design View option. 4. Click the OK button. The Graphical Query By Example window along with the Show Table dialog box appear. 5. Select the table Player Details and click the Add button. 6. Select the table Team Details and click the Add button. Both the tables will be added to the graphical query by example window(refer Fig.47)

Figure No: 47 7. Click the Close button. 8. Select the Team Details table from the Table: drop-down list box. 9. Select the Team Name field from the Field: drop-down list box. 10. Move to the next column in the design grid. 11. Select the Player Details table from the Table: drop-down list box. 12. Select the Player ID field from the Field: drop-down list box. Similarly, you can select the Player Name field(refer Fig 48)

Page No: 45

Information Technology Lab Record

Figure No: 48 After designing the query, it has to be executed to extract the records. The steps to run the query are shown below. 13. Click the Query menu. 14. Click the Run option. The result of the query will be displayed in the datasheet (refer Fig.49)

Figure No: 49 Filtering Records The purpose of the query is to allow the user to selectively view the records based on specific conditions. This task can b achieved y suing comparison operators and logical operators. These are excellent tools provided by MS Access to support query management. The following table outlines the comparison operators available in MS Access. Operator Description > Greater than < Less than = Equal to <= Less than or equal to >= Greater than or equal to <> Not equal to

Page No: 46

Information Technology Lab Record The query carrying a comparison operator returns a dynaset, which contains the matching values in one or more fields based on the condition specified. For example, you can use the filter option to view the player details who have scored more than 5000. To filter Records: 1. Open a new query in the Design View. 2. The Show Table dialog box appears. 3. Select the Player Details table. 4. Click the Add button. The table will be added to Graphical Query By Example window. 5. Click the Close button. 6. Select eh fields Player ID, Player Name, Age, Runs and Matches Played. 7. Enter >5000 in the Criteria: box under the Runs field(refer Fig. 50)

Figure No: 50 8. Run the query The details of players who have scored more than 5000 runs will be displayed(refer Fig.51)

Figure No: 51 9. Save the query as Top Scorers. You can specify multiple conditions using the logical operators OR and AND. When the OR operator is used and dynaset will contain the fields that satisfies at least one condition specified. When the AND operator is used for querying the Dyanset will return the fields that satisfies all the conditions specified.

Page No: 47

Information Technology Lab Record For example, you can retrieve the player details of either the Australian team or the South Africa team using the OR operator. 1. 2. 3. 4. 5. To use the logical operator Open a new query in the design view. Add the table Player Details to the Graphical Query By Example window. Select the required fields. Enter T007 or T004 in the Criteria: box under the Team ID field. Run the query The details of players in the Australia and South Africa teams are displayed in datasheet.

Figure No: 52

Page No: 48

Information Technology Lab Record

Experiment-3:

Creating and Using Forms:

Creating forms Forms are database objects, similar to tables and queries. They are created to customize data entry and enable easy editing of the existing data in tables. Forms can be created using the AutoForm, Form Wizard or Design View. Creating Forms Using the AutoForm The quickest way to create a form is to use the AutoForm feature. It is possible to create form on an existing query. For example, the AutoForm feature can be used to create a form on Top Scorers query. The steps to create a form using AutoForm feature: 1. Open the Cricket database. 2. Click the Forms object on the Objects bar. 3. Click the New button on the database toolbar. The New form dialog box appears. 4. Select the AutoForm: Columnar option. 5. Select the To Scorers query from the drop-down list box (refer Fig. 53)

Figure No: 53 6. Click the OK button. The form will be created (refer Fig. 54)

Figure No: 54 7. Save the form as Top Scorers.

Page No: 49

Information Technology Lab Record

Creating Forms Using the Form Wizard

The Form wizard simplifies the process of creating a form. Form Wizard provides visual guidance through a series of steps. Each step helps to decide on the fields to be added and the type of form to be created. You can create a form on Cricket database using the Form Wizard option. To create a form: 1. Open the Cricket database. 2. Open the New Form dialog box. 3. Select the Form Wizard option. 4. Click the Ok button. The Form Wizard dialog box prompting to select the tables/queries and fields appears. 5. Select the Team Details table from the Tables/Queries list box(refer Fig. 55) The fields in the Team Details table will be listed in the Available Fields: list box.

Figure No: 55 6. Select all the fields to be added to the selected Fields: list by clicking the >> button. 7. Select the table Player Details from the Tables/Queries list box. 8. Select all the Fields. 9. Click the Next button. The form wizard appears. 10. Select the by Team Details option (refer Fig. 56)

Page No: 50

Information Technology Lab Record

Figure No: 56 11. Select the Form with subform(s) option. The by Team Details option can be used to create a main form, which would contain the fields from the Team Details table and a sub from, which would contain the records of related information from the Player Details table. The Preview of the form appears on the right side (refer Fig. 56) 12. Click the Next button. A list of designs for the display of sub forms appears (refer Fig. 57). Each of these designs uses different formats to display the records in the sub form.

Figure No: 57

Page No: 51

Information Technology Lab Record 13. Select a layout for the form. A preview of the form appears on the left side. 14. Click the Next button. A list of form styles appears (refer Fig. 58)

Figure No: 58 15. Select a style from the list. 16. Click the Next button. The form Wizard dialog box prompting to enter the title for the main form and the sub form will appear.

Page No: 52

Information Technology Lab Record Figure No: 59 17. Enter a name for the main form in the Form: text box (refer Fig. 59) 18. Enter a name for the sub form in the Subform: text box. 19. Select the Open the form to View or enter information option. 20. Click the Finish button to complete the procedure. The form will be created (refer Fig. 60)

Figure No: 60 21. Save the form as Team_Details. The navigation button are used to switch between the records in the forms.

Creating Forms Using The Design View

Design view provides flexibility to add, delete, move and resize controls. The design view is used to create forms without the help of the wizard. You can create a form to view the details of all the players. To create a form in the design view: 1. Open the Cricket database. 2. Open the New Form dialog box. 3. Select the Design View option. 4. Select the Player Details table from the drop-down list box. 5. Click the Ok button The Form Design window appears along with the toolbox for adding controls. The fields in the Player Details table will be displayed in the Player Details list box (refer Fig.61)

Page No: 53

Information Technology Lab Record

` Figure No: 61 The Design View Window of the form contains three sections: Form Header, Detail and Form Footer. The Form Header contains the title that appears for every record in the form. The Detail section contains the data for each of the fields in the form. The Form Footer contains the information that appears at the bottom of every record in the form. The form that has been created does not contain any fields in it. The fields in the Player Details list box should be added to the form. To add fields to form: 1. Drag and drop the fields into the Detail area

Field list box Tool Box

Figure No: 62 The name of the fields appears automatically next to the text boxes. The form has been designed. Open the form to view the records.
Page No: 54

Information Technology Lab Record To open the form in form view: 1. Click the View menu on the database toolbar. 2. Select the Form View option. 3. The Player Details form will be displayed in the form view (refer Fig.63)

Figure No: 63

Page No: 55

Information Technology Lab Record

Experiment-4: Creating and Using Reports

Creating Reports Report is one of the important features of database management system. Reports are used for presenting information in a customized format on screen and paper. Reports can be created using Report Wizard, Auto Report or Design View. Creating Reports Using the Report Wizard A report can be used to retrieve information from one or more tables or queries and display it in specific format. Report Wizard provides the easiest way to create a report. It provides guided steps to design a report. For example, the Report Wizard option can be used to generate report of players in different teams. To create a report by using Report Wizard 1. Open the Cricket database. 2. Click the Reports option on the Objects bar. 3. Click the New button on the database window toolbar. The New Reports dialog box appears (refer Fig. 64)

Figure No:64 4. Select the Report Wizard option. 5. Click the OK button. The Report Wizard dialog box appears (refer Fig. 65)

Page No: 56

Information Technology Lab Record

Figure No: 65 6. Select the table Player Details from the drop-down list box. The list of fields in the table Player Details will be listed in the Available Fields: list box. 7. Click the >> button to add all the fields to the Select fields: list box. 8. Click the Next button. The Report Wizard dialog box appears with a prompt to select the record on which the grouping has to be performed (refer Fig. 66) The Records can be grouped based on the Team ID field.

Figure No: 66

Page No: 57

Information Technology Lab Record 9. Click the Team ID field. 10. Click the > single select button. A preview of the fields, after the grouping, appears on the right side. 11. Click the Next button. The Report Wizard dialog box appears with prompt to enter the field on which the sorting has to be performed (refer Fig. 67) The records can be sorted on Player Name field in ascending order.

Figure No: 67 12. Select the field Player Name from the drop-down list box. 13. Click the Ascending button. 14. Click the Next button. Wizard dialog box appears with a prompt to enter the layout for the report(ref. Fig. 68)

Page No: 58

Information Technology Lab Record

Figure No: 68 15. Select the Outline 1 option from the Layout section. The preview of the design appears on the left side of the Report Wizard dialog box. 16. Select the Landscape option in the Orientation section. 17. Click the Next button. The Report Wizard dialog box appears, prompting to select the style for the report (refer Fig. 69)

Figure No: 69 18. Select a style. The preview of the report appears on the left side of the Report Wizard dialog box.

Page No: 59

Information Technology Lab Record 19. Click the Next button. The Report Wizard dialog box appears, prompting to enter the name of the report (refer Fig. 70)

Figure No: 70 20. Enter the report name as Player Details in the textbox. 21. Click the Finish button to complete the procedure. The report will be created (refer Fig. 71)

Figure No: 71

Page No: 60

Information Technology Lab Record

Experiment -5:

Data transfer between Excel and Access. Steps for importing data from Excel to Access. Step-1: create a excel file. For example: create a marks file using fields ROLLNO, NAME, SUB1, SUB2, SUB3, TOTAL and AVERAGE. Step-2: and save it with a name marks. Step-3: then open ms-access and open a blank data base. Step-4: save it as marks.mdb. Step-5: then open file menu and select option GET EXTERNAL DATABASE and select IMPORT option. Step-6: then it asks for file name of importing file name. Specify the file name with address through BROWSE WINDOW. Step: 7: it will display as follows

Step 8: then press next Step9: then select the radio button: FIRST ROW CONTAINS COLUMN HEDINGS. Step10: then it will ask WHARE WOULD YOU LIKE TO SAVE? Specify as NEW TABLE and press next. Step11: then it will ask for primary key. Select CHOOSE MY OWN PRIMARK KEY from radio Radio buttons. And give ROLL NO AS primary key. Step12: press next and specify the file name as MARKS. Step13: then press finish. Then it will automatically import file fro marks.xls to marks.mdb.

Page No: 61