Microsoft Access - Introduction To Database, Introduction To Access, Tables, Handling Queries
Introduction To Database
Database
RATIONAL DATABASE MANAGEMENT SYSTEM
OBJECT OF RATIONAL DATABASE MANAGEMENT SYSTEM
Introduction To Access
Access
Getting Start
Creating New, and Opening Existing Databases
Creating a database using the Database Wizard
Creating a database without using the Database Wizard
Tables
Introduction to table
Create Table
Table in Design View
Working on Tables
Description of Design View
Properties of Fields
Primary Key
Switching Views
Entering Data
Manipulating Data
Relationships
Handling Queries
Introduction to Query
Creating A Query In Design View
Query wizards
Finding Duplicate Query
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
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 Blank
Access Database, 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, 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.
Value less than 100
>=1
Value greater than or equal to 1
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
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 »