Microsoft Access - Introduction To Database, Introduction To Access, Tables, Handling Queries

Introduction To Database

Database

Database is a collection of information or you can say it is hub of information For reteriving information we fall back on databases. With the help of database you can easily get information in structural form and You can easily search & update your information and easily change/modified your data in database.

Most popular database management system(DBMS) in market is like MS ACCESS. Microsoft Access provides users with one of the simplest and most flexible DBMS solutions on the market today. Regular users of Microsoft products will enjoy the familiar Windows "look and feel" as well as the tight integration with other Microsoft Office family products.

RATIONAL DATABASE MANAGEMENT SYSTEM

Suppose you have two two database-a database containging book detail and the other containing details of the company .Now you wish to display on the screen the information about the book A.and the details of the company which published it.What will you do to display the information?.you will display the name of company which has published the book A.Both these database were related.such a system is called is RDBMS(Relational Database Management System).

RDBMS is defined as method of viewing information from several ,separate database that relate to one an another through keyword or values.The main advantage of relational database management system is that you can use simultaneously use more than one database to see information stored in them.

Suppose customer wants to details of publisher any book so with the help of RDBMS the seller can give the detail of particular book.Most of the organization are using RDBMS because it provide facilty like you can add and delete your data also you can modified or update your data and easily find your data when you have large amount of data.

RDBMS play important role in bussines marketing like a dealer who have data of customer and also want to data of customer daily purchasing details so with the help of RDBMS he can take data easily from database.

OBJECT OF RATIONAL DATABASE MANAGEMENT SYSTEM

Tables

All data is stored in tables. When you create a new table, Access asks you define fields (column headings), giving each a unique name, and telling Access the data type. Use the "Text" type for most data, including numbers that don't need to be added e.g. phone numbers or postal codes. Using Wizards, Access will walk you through the process of creating common tables such as lists of names and addresses. Once you have defined a table's structure, you can enter data. Each new row that you add to the table is called a record. To define relationships between tables, click Database Tools | Relationships in Access 2007, or choose Relationships from the Tools menu in Access 95, 97, 2000, or choose Relationships from the Edit menu

Queries

Use a query to find or operate on the data in your tables. With a query, you can display the records that match certain criteria (e.g. all the members called "Barry"), sort the data as you please (e.g. by Surname), and even combine data from different tables. You can edit the data displayed in a query (in most cases), and the data in the underlying table will change. Special queries can also be defined to make wholesale changes to your data, e.g. delete all members whose subscriptions are 2 years overdue, or set a "State" field to "WA" wherever postcode begins with 6.

Forms

These are screens for displaying data from and inputting data into your tables. The basic form has an appearance similar to an index card: it shows only one record at a time, with a different field on each line. If you want to control how the records are sorted, define a query first, and then create a form based on the query. If you have defined a one-to-many relationship between two tables, use the "Subform" Wizard to create a form which contains another form. The subform will then display only the records matching the one on the main form.

Reports

If forms are for input, then reports are for output. Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period, or a set of mailing labels. Again the Access Wizards walk you through the process of defining reports.

Pages

(Access 2000 - 2003). Use pages to enter or display data via Internet. Pages are stored as HTML files, with data read from and written to the database. Michael Kaplan has published a to convert Access forms and reports into Data Access Pages.

Macros

An Access Macro is a script for doing some job. For example, to create a button which opens a report, you could use a macro which fires off the "OpenReport" action. Macros can also be used to set one field based on the value of another (the "SetValue" action), to validate that certain conditions are met before a record saved (the "CancelEvent" action) etc. Each line of a macro performs some action, and the bottom half of the macro screen provides the details of how the action is to apply.

Modules

This is where you write your own functions and programs if you want to. Everything that can be done in a macro can also be done in a module, but you don't get the Macro interface that prompts you what is needed for each action. Modules are far more powerful, and are essential if you plan to write code for a multi-user environment, since macros cannot include error handling. Most serious Access users start out with macros to get a feel for things, but end up using modules almost exclusively. On the other hand, if your needs are simple, you may never need to delve into the depths of Access modules.

Introduction To Access

Access

Microsoft Access is a powerful program to create and manage your databases. It has many built in features to assist you in constructing and viewing your information. Access is much more involved and is a more genuine database application than other programs such as Microsoft Works.

Microsoft Access can be used for personal information management (PIM), in a small business to organize and manage all data, or in an enterprise to communicate with server.

Microsoft Access stores information in what is called a database. For now it is good enough to know that your data is put into a database and not worry about the details. We will be explaining databases and other key Access elements in a later lesson.

There are four major steps to using Microsoft Access:

1. Database Creation: Create your Microsoft Access database and specify what kind of data you will be storing. A retail business might create a database to store all their sales information (i.e. items sold, customer, employee, commission, etc)

2. Data Input: After your database is created the data the store gathers every business day can be entered into the Access database.

3. Query: This is a fancy term to basically describe the process of retrieving information from the database.

Report (optional): Information from the database is organized in a nice presentation that can be printed in an Access Report

Database File

This is your main file that encompasses the entire database and that is saved to your hard-drive or floppy disk.

Example) StudentDatabase.mdb

Table

A table is a collection of data about a specific topic. There can be multiple tables in a database.

Example #1) Students

Example #2) Teachers

Field

Fields are the different categories within a Table. Tables usually contain multiple fields.

Example #1) Student LastName

Example #2) Student FirstName

Datatypes

Datatypes are the properties of each field. A field only has 1 datatype.

FieldName) Student LastName

Datatype) Text

Getting Start

Like any other computer application, in order to use Microsoft Access, you must first open it. There are various ways this can be done. Microsoft Access is a classic computer application and it gets launched like the usual products you have probably been using. As such, to start this program, you could click Start -> (All) Programs -> Microsoft Access:

Access is a versatile application for creating databases. Although it may not be as powerful as industrial strength database management systems, Access is very popular due to the vast number of features it provides. Many businesses also favor Access because it is integrated with all the other Microsoft Office products.

Creating New, and Opening Existing Databases

The above picture gives you the option to:

1. Create a New Database from scratch

2. Use the wizard to create a New Database

3. Open an existing database

The white box gives you the most recent databases you have used. If you do not see the one you had created, choose the More Files option and hit OK. Otherwise choose the database you had previously used and click OK.

Creating a database using the Database Wizard

1. When Microsoft Access first starts up, a dialog box is automatically displayed with options to create a new database or open an existing one. If this dialog box is displayed, click Access Database Wizards, pages, and projects and then click OK.

If you have already opened a database or closed the dialog box that displays when Microsoft Access starts up, click New Database on the toolbar.

2. On the Databases tab, double-click the icon for the kind of database you want to create.

3. Specify a name and location for the database.

4. Click Create to start defining your new database

Creating a database without using the Database Wizard

If you have already opened a database or closed the dialog box that displays when Microsoft Access starts up, click New Database on the toolbar, and then double-click the Blank Database icon on the General tab. Specify a name and location for the database and click Create.

(Below is the screen that shows up following (this step)

Tables

INTRODUCTION TO TABLE

A table is a collection of data about a specific topic, such as students or contacts. Using a separate table for each topic means that you store that data only once, which makes your database more efficient, and reduces data-entry errors.

Access provides three ways to create a table for which there are icons in the Database Window. Double-click on the icons to create a table.

CREATE TABLE

(A) create table in Design view will allow you to create the fields of the table. this is the most common way of creating a table and is explained in detail below.

(B) Create table using wizard will step you through the creation of a table.

(C) Create table by entering data will give you a blank datasheet with unlabelled columns that looks much like an Excel worksheet. Enter data into the cells and click the Save button. You will be prompted to add a primary field. After the table is saved, the empty cells of the datasheet are trimmed. The fields are given generic names such as "Field1", "Field2", etc. fields, select Format|Rename olumn from the menu bar or highlight the column, right-click on it with the mouse, and select Rename Column from the shortcut menu

Table in Design View

Design View will allow you to define the fields in the table before adding any data to the datasheet. The window is divided into two parts: a top pane for entering the field name, data type, and an option description of the field, and a bottom pane for specifying field properties

Steps:

1. Select 'Tables' tab From the Database view, click 'New' and then choose 'Design View' from the 'New Table' dialog box, and click OK.

2. Enter a field name in the first row of the Field Name Column.

3. Press Enter or Tab to move to the Data Type Field.

4. Enter a data type for this field. Access will scroll using the first letter of the data type, or you can also use the drop down list provided to you.

5. Alter the Field Properties section of the table design grid as needed.

6. Add a comment in the description column for your better understanding

Using Table Wizard

Like all Wizards, the Table Wizard simplifies the Layout of the fields. You are guided visually through a series of steps, which help you to decide on the type to be created, and then the table is created automatically. The steps in creating a table through 'Table Wizard' are as follows:

1. Open the 'New Table' Dialog box by using one of the followings:

Select the 'Tables' option from the Insert menu. Select the 'Tables' tab and then click on 'New' button in the Database window. Select the 'New' Object toolbar button and choose the Table option.

2. Select table type from the 'New Table' Dialog box.

3. Select table and field from the list of sample tables and sample fields.

4. Give title of the table and create relationship if exist.

Datasheet View to Make a Table

1. Launch MS Access and start a new blank database. Access now launches a blank table for use.

2. Enter data for the first field in the first row.

3. Right click on the first column header (labeled Field 1) and choose Rename from the shortcut menu. This allows you to change the column header, change it to Employee ID for this field.

4. Enter come name data for each of the next three fields, editing the field names to Last Name, First Name, and Middle Name respectively.

5. Move to the next column, Enter yes.

6. Move to the next column to enter date.

7. Click on the Save icon in the toolbar.

8. Click the view button to switch to Design View.

WORKING ON TABLES

NAMING FIELD

1. This will bring up the Table Design View

2. There are three columns here that should be explained in detail

o Field Name: This is where you type the name for your column. A common practice is to make it one word and to use capitalization for multiple words squished into one (e.g. SaleNumber)

o Data Type: This column is where you specify the type of data that will be stored. If you are storing money then select Currency. The most common types of data are: Text, Number, Currency and Date/Time.

o Description: Here you can type optional notes to remind yourself or provide useful information for others who might be viewing this file later.

3. The first column in our tbl_Sales example was Employee, so let's enter in Employee in the Field Name column and choose Text from the Data Type column. If click inside the Data Type column you will see that it is actually a drop down select box with many options to choose from. Select the Text option.

4. Enter the following information for our remaining three columns of tbl_Sales:

o Field Name: Product, Data Type: Text

o Field Name: Price, Data Type: Currency

o Field Name: SaleNumber, Data Type: Number

5. Before we are finished here, we need to make a Primary Key. A primary key is restriction that we place on a column stating that there can be no duplicate values in that column. We will be talking about keys later, but for now right-click in the SaleNumber row and choose Primary Key from the pop-up menu.

6. We have finished our table's outline so click the X in the top right to close the design view (don't close Access, just the Design Window).

7. Click yes and enter "tbl_Sales" for your table's name

Although this process of creating an Access table might seem overly complicated, with time you'll be able to create and edit existing tables very quickly. Feel free to revisit this page if you are having trouble creating an Access table.

DESCRIPTION OF DESIGN VIEW

• Field Name - This is the name of the field and should represent the contents of the field such as "Name", "Address", "Final Grade", etc. The name can not exceed 64 characters in length and may include spaces.

• Data Type is the type of value that will be entered into the fields.

• Text - The default type, text type allows any combination of letters and numbers up to a maximum of 255 characters per field record.

• Memo - A text type that stores up to 64,000 characters.

• Number - Any number can be stored.

• Date/Time - A date, time, or combination of both.

• Currency - Monetary values that can be set up to automatically include a dollar sign ($) and correct decimal and comma positions.

• AutoNumber - When a new record is created, Access will automatically assign a unique integer to the record in this field. From the General options, select Increment if the numbers should be assigned in order or random if any random number should be chosen. Since every record in a datasheet must include at least one field that distinguishes it from all others, this is a useful data type to use if the existing data will not produce such values.

• Yes/No - Use this option for True/False, Yes/No, On/Off, or other values that must be only one of two.

• OLE Object - An OLE (Object Linking and Embedding) object is a sound, picture, or other object such as a Word document or Excel spreadsheet that is created in another program. Use this data type to embed an OLE object or link to the object in the database.

Hyperlink - A hyperlink will link to an Internet or Intranet site, or another location in the database. The data consists of up to four parts each separated by the pound sign (#): DisplayText#Address#SubAddress#ScreenTip. The Address is the only required part of the string

Properties of Fields

• Field Size is used to set the number of characters needed in a text or number field. The default field size for the text type is 50 characters. If the records in the field will only have two or three characters, you can change the size of the field to save disk space or prevent entry errors by limiting the number of characters allowed. Likewise, if the field will require more than 50 characters, enter a number up to 255. The field size is set in exact characters for Text type, but options are give for numbers:

o Byte - Positive integers between 1 and 255

o Integer - Positive and negative integers between -32,768 and 32,768

o Long Integer (default) - Larger positive and negative integers between -2 billion and 2 billion.

o Single - Single-precision floating-point number

o Double - Double-precision floating-point number

o Decimal - Allows for Precision and Scale property control

Primary Key

• One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key does not allow Null values and must always have a unique value. A primary key is used to relate a table to foreign keys in other tables.

• NOTE:

You do not have to define a primary key, but it's usually a good idea. If you don't define a primary key, Microsoft Access asks you if you would like to create one when you save the table.

• For our tutorial, make the Soc Sec # field the primary key, meaning that every student has a social security number and no 2 are the same.

Switching Views

• To switch views form the datasheet (spreadsheet view) and the design view, simply click the button in the top-left hand corner of the Access program.

Datasheet View	Design View

Displays the view, which allows you to enter raw data into your database table.	Displays the view, which allows you to enter fields, data-types, and descriptions into your database table.

Entering Data

Click on the Datasheet View and simply start "chugging" away by entering the data into each field.

NOTE:

Before starting a new record, the Soc Sec # field must have something in it, because it is the Primary Key. If you did not set a Primary Key then it is OK.

Manipulating Data

Adding Records

Add new records to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record. You can also click the new record button at the bottom of the datasheet to skip to the last empty record.

Editing Records

To edit records, simply place the cursor in the record that is to be edited and make the necessary changes. Use the arrow keys to move through the record grid. The previous, next, first, and last record buttons at the bottom of the datasheet are helpful in maneuvering through the datasheet.

Deleting Records

Delete a record on a datasheet by placing the cursor in any field of the record row and select Edit|Delete Record from the menu bar or click the Delete Record button on the datasheet toolbar.

Adding and Deleting Columns

Although it is best to add new fields (displayed as columns in the datasheet) in design view because more options are available, they can also be quickly added in datasheet view. Highlight the column that the new column should appear to the left of by clicking its label at the top of the datasheet and select Insert|Column from the menu bar.

Entire columns can be deleted by placing the cursor in the column and selecting Edit|Delete Column from the menu bar.

Resizing Rows and Columns

The height of rows on a datasheet can be changed by dragging the gray sizing line between row labels up and down with the mouse. By changing the height on one row, the height of all rows in the datasheet will be changed to the new value.

Column width can be changed in a similar way by dragging the sizing line between columns. Double click on the line to have the column automatically fit to the longest value of the column. Unlike rows, columns on a datasheet can be different widths. More exact values can be assigned by selecting Format|Row Height or Format|Column Width from the menu bar.

Freezing Columns

Similar to freezing panes in Excel, columns on an Access table can be frozen. This is helpful if the datasheet has many columns and relevant data would otherwise not appear on the screen at the same time. Freeze a column by placing the cursor in any record in the column and select Format|Freeze Columns from the menu bar. Select the same option to unfreeze a single column or select Format|Unfreeze All Columns.

Hiding Columns

Columns can also be hidden from view on the datasheet although they will not be deleted from the database. To hide a column, place the cursor in any record in the column or highlight multiple adjacent columns by clicking and dragging the mouse along the column headers, and select Format|Hide Columns from the menu bar.

To show columns that have been hidden, select Format|UnhideColumns from the menu bar. A window displaying all of the fields in the table will be listed with check boxes beside each field name. Check the boxes beside all fields that should be visible on the data table and click the Close button.

Finding Data in a Table

Data in a datasheet can be quickly located by using the Find command.

1. Open the table in datasheet view.

2. Place the cursor in any record in the field that you want to search and select Edit|Find... from the menu bar.

3. Enter the value criteria in the Find What: box.

4. From the Look In: drop-down menu, define the area of the search by selecting the entire table or just the field in the table you placed your cursor in during step 2.

5. Select the matching criteria from Match: to and click the More >> button for additional search parameters.

6. When all of the search criteria is set, click the Find Next button. If more than one record meets the criteria, keep clicking Find Next until you reach the correct record.

Replace

The replace function allows you to quickly replace a single occurrence of data with a new value or to replace all occurrences in the entire table.

1. Select Edit|Replace... from the menu bar (or click the Replace tab if the Find window is already open).

2. Follow the steps described in the Find procedure for searching for the data that should be replaced and type the new value of the data in the Replace With: box.

Click the Find Next button to step through occurrences of the data in the table and click the Replace button to make single replacements. Click Replace All to change all occurrences

Relationships

After you've set up multiple tables in your Microsoft Access database, you need a way of telling 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.

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. For example, teachers can be associated with the students they're responsible for by creating a relationship between the teacher's table and the student's table using the TeacherID fields

Having met the criteria above, follow these steps for creating relationships between tables.

1. In the database window view, at the top, click on Tools ---> Relationships.

2. Select the Tables you want to link together, by clicking on them and selecting the Add Button.

3. Select Enforce Referential Integrity

4. When the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records.

o When the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table.

5. Click Create and Save the Relationship

Handling Queries

Introduction to Query

Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. The resulting collection of records, called a dynaset (short for dynamic subset), is saved as a database object and can therefore be easily used in the future. The query will be updated whenever the original tables are updated. Types of queries are select queries that extract data from tables based on specified values, find duplicate queries that display records with duplicate values for one or more of the specified fields, and find unmatched queries display records from one table that do not have corresponding values in a second table.

Creating A QUERY IN DESIGN VIEW

Follow these steps to create a new query in Design View:

1. From the Queries page on the Database Window, click the New button

2. Select Design View and click OK.

3. Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query.

4, Click Close when all of the tables and queries have been selected.

5. Add fields from the tables to the new query by double-clicking the field name in the table boxes or selecting the field from the Field: and Table: drop-down menus on the query form. Specify sort orders if necessary.

Enter the criteria for the query in the Criteria: field. The following table provides examples for some of the wildcard symbols and arithmetic operators that may be used. The Expression Builder

can also be used to assist in writing the expressions

Query and Expression Operators

Operator	Explanation
?	The question mark is a wildcard that takes the place of a single letter.
*	The asterisk is the wildcard that represents a number of characters.
<100	Value less than 100
>=1	Value greater than or equal to 1
<>"FL"	Not equal to (all states besides Florida)
Between 1 and 10	Numbers between 1 and 10
Is Null Is Not Null	Finds records with no value or all records that have a value
"a*"	All words beginning with "a"
>0 And <=10	All numbers greater than 0 and less than 10
"Bob" Or "Jane"	Values are Bob or Jane

6. After you have selected all of the fields and tables, click the Run button on the toolbar.

7. Save the query by clicking the Save button.

Query wizards

Click the Create query by using wizard icon in the database window to have access step you through the process of creating

QUERY

1. From the first window, select fields that will be included in the query by first selecting the table from the drop-down Tables/Queries menu. Select the fields by clicking the > button to move the field from the Available Fields list to Selected Fields. Click the double arrow button >> to move all of the fields to Selected Fields. Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. Click Next > when all of the fields have been selected.

2. On the next window, enter the name for the query and click Finish.

Finding Duplicate Query

1. Click the New button on the Queries database window, select Find Duplicates Query Wizard from the New Query window and click OK.

2. Select the table or query that the find duplicates query will be applied to from the list provided and click Next >.

3. Select the fields that may contain duplicate values by highlighting the names in the Available fields list and clicking the > button to individually move the fields to the Duplicate-value fields list or >> to move all of the fields. Click Next > when all fields have been selected.

4. Select the fields that should appear in the new query along with the fields selected on the previous screen and click Next >.

Name the new query and click Finish.

Next Chapter »

SHARE THIS PAGE

Microsoft Access - Introduction To Database, Introduction To Access, Tables, Handling Queries

0 Comments:

Post a Comment