Accessxp(completeandupdated

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 report in MS Access . • Report is an object designed for formatting, calculating, printing, and summarizing 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.

Table Creation

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

Primary Key And Foreign Key • The primary key is a column or set of columns that uniquely identifies a record in the table. • For example rollno is a Primary key for studentdetail table . • When the primary key of one table is represented in a second table to form a relationship, it is called a Foreign key in the second table. • For example rollno column in studentmarks will be foreign key for the rollno existing in studentdetail table .

Relationship Between Tables

Relationship Between Tables • After you've set up different tables for each subject in your Microsoft Access database, you need a way of telling Microsoft Access how to bring that information back together again. • The first step in this process is to define relationships between your tables. • After you've done that, you can create queries, forms, and reports to display information from several tables at once.

Relationship Between Tables… Example This form includes information from four tables

Cont …… • In given example the fields in four tables must be coordinated so that they show information about the same order. • This coordination is accomplished with relationships between tables. • A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the Primary key from one table, which provides a unique identifier for each record, and a Foreign key in the other table.

Primary Key And Foreign Key to create Relationship between Tables

1. A one-to-many relationship : one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A. Example …………

A one-to-many relationship… Example

2 . A many-to-many relationship •

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A.

A many-to-many relationship…. Example

3. A one-to-one relationship • In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. Example ………….

A one-to-one relationship….. Example

Forms • A form is a type of a database object that is primarily used to enter or display data in a database.

Create a form • There are three ways to create a form. – Based on a single table or query by using AutoForm. AutoForm creates a form that displays all fields and records in the underlying table or query. – Based on one or more table or query with a wizard. The wizard asks you detailed questions about the record sources, fields, layout, and format you want and creates a form based on your answers. – On your own in Design view. You create a basic form and customize it in Design view to suit your requirements.

Create a form ...... Based on a single table or query by using AutoForm • • •

In the Database window, click Forms under Objects. Click the New button on the Database window toolbar. In the New Form dialog box, click one of the following wizards: – – – – –

• •

AutoForm: Columnar Each field appears on a separate line with a label to its left. AutoForm: Tabular The fields in each record appear on one line, with the labels displayed once at the top of the form. AutoForm: Datasheet The fields in each record appear in row-andcolumn format, with one record in each row and one field in each column. The field names appear at the top of each column. AutoForm: PivotTable The form opens in PivotTable view. You can add fields by dragging them from the field list to the different areas in the view. AutoForm: PivotChart The form opens in PivotChart view. You can add fields by dragging them from the field list to the different areas in the view.

Click the table or query that includes the data you want to base your form on. Click OK.

Create a form .... Based on one or more table or query with a wizard. • In the Database window, click Forms under Objects. • Click the New button on the Database window toolbar. • In the New Form dialog box, click the wizard that you want to use. A description of the wizard appears in the left side of the dialog box. • Click the name of the table or other record source that includes the data you want to base your form on. • Click OK. • Follow the instructions in the wizard.

Create a form .... On your own in Design view • In the Database window, click Forms under Objects. • Click the New button on the Database window toolbar. • In the New Form dialog box, click Design View. • Click the name of the table or other record source that includes the data you want to base your form on.

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 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

Importing and linking data and database objects • Microsoft Access provides two choices for using data from an external data source. You can: • Import the data into a new Microsoft Access table, which is a way to convert data from a different format and copy it into Microsoft Access. You can also import database objects into another Microsoft Access database or Microsoft Access project. • Link to the data, which is a way to connect to data from another application without importing it so that you can view and edit the data in both the original application and in an Access file. (In previous versions of Microsoft Access, this process was referred to as attaching.) • You can import or link data from a variety of supported databases, programs, and file formats.

Import or link tables from another Microsoft Access file • •

• • •

•

Open a database, or switch to the Database window for the open database. Do one of the following: – To import tables, on the File menu, point to Get External Data, and then click Import. – To link tables, on the File menu, point to Get External Data, and then click Link Tables. In the Import (or Link) dialog box, in the Files of type box, make sure that Microsoft Access (*.mdb; *.adp; *.mda; *.mde; *.ade) is selected. Click the arrow to the right of the Look in box, select the drive and folder where the Access file that you want to import or link is located, and then double-click the database's icon. In the Import Objects (or Link Tables) dialog box, click each table that you want to import or link. If you're importing, and you want to import just the selected tables' definitions (not the data they contain), click Options, and then under Import Tables, click Definition Only. Note   If you import a table that is already linked, then Access does not import the data; instead, it links the table to its data source (in effect, copies the link information).