Analyzing Data Using Access

Analyzing Data Using Access

What is Data • Data refers to a collection of organised fact or information , usually the result of experience , observation or experiment

Database and related terms • Field : field is a data structure for a single piece of data , For example rollno , name • Record : Related fields are organized into records .For example student record is a collection of rollno , name , course , semester • Table : collection of related records called a table . For example student table to store records of students of a particular class .

Database • A DATABASE is a collection of information usually organized in a form of table or file in such a way that a computer program can quickly select desired pieces of data. • A telephone book is an example of a database.

Features of Database

DBMS….. Database Management System • DBMS, is a computer software program that is designed as the means of managing all databases that are currently installed on a system hard drive or network

DBMS Features • Provides a way to structure data as records, tables, or objects • Accepts data input from operators and stores that data for later retrieval • Provides query languages for searching, sorting, reporting, and other "decision support" activities that help users correlate and make sense of collected data

DBMS Features….Contd • Provides multiuser access to data, along with security features that prevent some users from viewing and/or changing certain types of information • Provides data integrity features that prevent more than one user from accessing and changing the same information simultaneously • Provides a data dictionary (metadata) that describes the structure of the database, related files, and record information • Example : Oracle , MS SQL Server , MS Access

RDBMS .. Realtion Database Management System • a type of database management system (DBMS) that stores data in the form of related tables. • An important feature of RDBMS is that a single database can be spread across several tables • Example : MS ACCESS , Oracle

MS ACCESS Features 1.RDBMS : It is a Relational Database Management system that is a type of database management system (DBMS) that stores data in the form of related tables. 2.Database : We can create database in MS ACCESS to store related tables together . For Example Employee database to store employee information such as personaldetail, salary detail , departmentdetail .

MS ACCESS Features…… Contd 1.

Tables : MS Access is used to create table in a datbase , there are different options available in MS Access to create a table . • Each table contains information about a particular subject, such as customers or orders. Table contains Fields (or columns) that store different kinds of data, such as a name or an address, and, Records (or rows) that collect all the information about a particular instance of the subject. • E.g. All the information about a company • You can define a primary key (one or more fields that have a unique value for each record) and, one or more indexes on each table to help retrieve your data more quickly

MS ACCESS Features…… Contd 1.

Query : We can create quey in MS Access • Query in an object that provides a custom view of data from one or more tables. • In Access, you can use the graphical query by example (QBE) facility or you can write SQL statements to create your queries. • You can define queries to select, update, insert, or delete data. • You can also define queries that create new tables from data in one or more existing tables.

MS ACCESS Features…… Contd 1.

Form : We can create forms in MS Access • Form is an object designed primarily for data input or display or for control; of application execution. • You use forms to customise the presentation of data that your application extracts for queries or tables. • You can also print forms. • You can design a form to run a macro al Basic program in response to any of a number of events- for example, to run a program when the value of data changes.

MS ACCESS Features…… Contd 1.

2. 3.

Report: We can create query in MS Access . • Report is an object designed for formatting, calculating, printing, and summarising selected data. • You can view a report on your screen before you print it. Data Access Page : An object that includes an HTML file and supporting files to provide access to your data from Microsoft Internet Explorer Macro : Macro is an object that is a structured definition of one or more actions that you want Access to perform in response to a defined event.

Creating a new database

To create a new database •

Start Access. In the Task Pane, click Blank Database.

•

The File New Database dialog box opens.

Creating a New Database •

Choose a file name and location, and then click Create. Your new database will be opened in the Database Container. Note that Access 2002 will create a new database in Access 2000 file format for ease with individuals using Access 2000.

Create Table • To create a blank (empty) table for entering your own data, you can: – Use the Table Wizard to choose the fieldsfor your table from a variety of predefined tables such as business contacts, household inventory, or medical records. – Create a table in Design view, where you can add fields, define how each field appears or handles data, and create a primary key. – Enter data directly into a blank datasheet. When you save the new datasheet, Microsoft Access will analyze your data and automatically assign the appropriate data type and format for each field.

Create Table by using Table Wizard 1. 2. 3. 4.

Press F11 to switch to the Database window. Click Tables under Objects, and then click New on the Database window toolbar. Double-click Table Wizard. Follow the directions in the Table Wizard dialog boxes.

Create Table by using Table Wizard

Create a table in Design view 1. 2. 3. 4. 5. 6.

Press F11 to switch to the Database window. Click Tables under Objects, and then click New on the Database window toolbar. Double-click Design View. Define each of the fields in your table. Field name , Datatype and description Define a primary key field before saving your table. When you are ready to save your table, click Save on the toolbar, and then type a unique name for the table.

Create a table in Design view Enter a field name that describes the purpose of the field.

Click the Data Type list arrow to see a list of types to select from. A field description is optional, but it is a good idea to supply one.

Other field properties can be set in the Properties window.

Data types in Microsoft Access • Text :Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. Memo :Use for lengthy text and numbers, such as notes or descriptions. • Number :Use for data to be included in mathematical calculations, except calculations involving money (use Currency type). • Date/Time : Use for dates and times. • Currency : Use for currency values and to prevent rounding off during calculations.

Data types in Microsoft Access..Contd • AutoNumber Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. • Yes/No :Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off.. • OLE Object Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE protocol. • Hyperlink :Use for hyperlinks. A hyperlink can be a UNC path or a URL.

Create a table by entering data in a datasheet • Press F11 to switch to the Database window • Click Tables under Objects, and then click New on the Database window toolbar. • Double-click Datasheet View. A blank datasheet is displayed. The default column names are Field1, Field2, and so on. • Rename each column you will use: double-click the column name, type a name for the column, and then press ENTER. • You can insert additional columns at any time: click in the column to the right of where you want to insert a new column, and then on the Insert menu, click Column. Rename the column as described in step 4. • Enter your data in the datasheet. • When you've added data to all the columns you want to use, click Save on the toolbar to save your datasheet.

Create a table by entering data in a Datasheet

Creating a simple query

To create a simple query •

In the Objects list, click Queries, and then click New on the Database Container toolbar.

•

In the New Query dialog box, click Simple Query Wizard, and then click OK. The Simple Query Wizard starts.

•

Creating a Simple Query Select the fields that you want to include in the query, and then click the > button. (Make sure that one of the fields that you select is the Age field.) Click Next.

•

Click Next to produce a detail query.

•

In the What title do you want for your query box, name your query to describe the question that you hope to answer by running the query. For example, type Students by enrolled major

•

Click Finish to display the completed query.

Getting specific with criteria

To get specific with criteria •

After creating the previous query, change to Design view by clicking Design View on the View menu. -orClick the Design View button on the toolbar.

Getting Specific with Criteria •

Click the criteria box in the age column, and then type >45.

•

Change to Datasheet view by clicking Datasheet View on the View menu. -orClick the Datasheet View button on the toolbar.

•

You will see the results of your query. Notice that all the ages of the survey respondents are greater than 45.

•

Change back to Design view to revise your query.

•

On the File menu, click Save As, and then type a name in the space provided.

Creating a crosstab query

To create a crosstab query create Container, a crosstab • In To the Database in thequery Objects(cont.) list, click Queries, • •

and click New on the Database toolbar. View on the • thenChange to Datasheet view byContainer clicking Datasheet View menu. Click Crosstab Query Wizard, and then click OK. -orClick Next to Datasheet indicate thatView you want base your query on the Click on thetotoolbar. results table. • On the File menu, click Save. Double-click -or- major to select its contents as the row headings, and thenClick clickSave Next.on the toolbar. Double-click gender to select its contents as the column headings.

Creating a Crosstab Query • • •

Click OwnPC as the values for your crosstab, and then click Next.

•

Click Modify the design, and then click Finish to accept the default name Survey_Crosstab.

Creating a report

To create create aa report report (cont.) To ••

•

In theNext. Database Container, in the Objects list, click Reports, Click and then click New on the Database Container toolbar. Select a field to sort by. (For the lab, in the drop-down list, click Click Report Wizard, and then, in the box at the bottom of the name.) Click Next. dialog box, select the table or query that will provide the data. Choose the layout by clicking the appropriate (Select Survey forand the orientation lab.) options. (You can preview the affect of each by looking in the Click OK. Theon Report Wizard Preview pane the left.) Clickstarts. Next.

••

Select aallstyle fields clicking theand >> then button, and then click Next. Select forby your report, click Next.

••

Double-click age, andclick thenFinish. click Grouping Options. Type a title, and then Your report will open in Print Preview, so you can examine the proposed output before it is In the Grouping Intervals dialog box, in the Grouping printed. intervals drop-down list, click 10s, and then click OK. On the File menu, click Save As. Type the name you want, and then click OK.

• • •

• •

Creating a Report

Creating data access pages

To create data access pages •

In the Database Container, in the Objects list, click Pages, and then click New on the Database Container toolbar.

•

Click Page Wizard, and then click OK.

•

In the Tables/Queries box, click the appropriate table or query. (Click Survey_Crosstab for the lab.)

•

Click the >> button to select all fields, and then click Next.

•

Click Next to continue past the screen that allows the grouping of records.

•

Click Next to continue past the sorting selection.

•

Type a name for your page, and then click Finish.

•

On the View menu, click Page View to see the page as it will look in a browser.

Creating Data Access Pages