|
MS Access
|
|
|
Microsoft Access is a Window based program created by Microsoft. It helps you store & manage a large
collection of information.
|
|
|
A
systamatically arrangeed database helps
you manage the stored information in an efficient way so that It can access
quickly whenever needed.
|
|
|
You can
easily create such a database using Access.
|
|
|
A good Database design ensure that you will be able to
perform various tasks on it efficiently and accurately and without any
hindrance.
|
|
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.
|
|
|
|
RELATIONAL 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 RELATIONAL 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 program to create and managing your databases. It has features to
help you in constructing and presentation your information.
|
|
|
|
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
|
|
|
|
In
order to use Microsoft Access, you must first open it. There are various ways
this can be done. As such, to start this program, you could click Start -> (All) Programs -> Microsoft Access:
|
|
|
|
|
|
|
|
|
|
|
|
Access
is a flexible application for creating databases. Access is very popular due
to the vast number of features it provides. Many businesses also turn Access
because it is included 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
|
|
|
|
Creating a database using the Database Wizard
|
|
|
|
1. On
starting Microsoft Access, 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.
click double the icon for the kind of database you want to create.
|
|
|
|
3.Put a
name and location for the database.
|
|
|
|
4.To
defining your new database Click Create
|
|
|
|
|
|
Creating a database without using the Database Wizard
|
|
|
|
|
|
|
|
(Below is the screen that shows up following (this step)
|
|
|
|
|
|
|
Tables
|
INTRODUCTION TO TABLE
|
|
|
|
A table
is a collection of Data, such as Result or Diary Memo. Using a separate table
for each subject means that you store that data only once, which makes your
database well-organized, .
|
|
|
|
Access
provides different ways to creating a table .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
|
|
For the viewlet of Table in Design View. Click
Here!
|
|
|
|
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.
|
|
Click
here for animated tutorial on How To Work with 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
|
||||||
|
|
||||||
|
A primary key is a field or
grouping of fields that uniquely identify a record in a table, so that an
individual record can be placed without confusion.
|
||||||
|
|
||||||
|
• NOTE:
|
||||||
|
A primary key is the field(s) (a
primary key can be made up of more than one field) that uniquely identifies
each record, i.e. the primary key is unique for each record and the value is
never duplicated in the same table, so in the above table the EmployeeID
field would be used. A constraint is a rule that defines what data is valid
for a given field. So a primary key constraint is a rule that says that the
primary key fields cannot be null and cannot contain duplicate data.
|
||||||
|
|
||||||
|
Switching Views
|
||||||
|
|
||||||
|
• To Simply click on these two
option either click on Datasheet iew or Design view
|
||||||
|
|
||||||
|
||||||
|
|
||||||
|
|
||||||
|
Entering Data
|
||||||
|
|
||||||
|
Click on shell where you want to
enter your data,each field have own attributes and column ,enter data
according coulmn and row
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
|
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
|
|
|
|
To Set
multiple tables in your database, you
need to Access how that information back each other again.This process is to
define relationships between your tables. After that, you can create queries,
forms, and reports to display information from many tables at once.
|
|
<
|
|
|
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
|
||||||||||||||||||||||
|
|
||||||||||||||||||||||
|
Use 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
|
||||||||||||||||||||||
|
|
||||||||||||||||||||||
|
Query and Expression Operators
|
||||||||||||||||||||||
|
||||||||||||||||||||||
|
|
||||||||||||||||||||||
|
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.
|
||||||||||||||||||||||
|
For the viewlet of Table in Design
View.Click Here!
|
|
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.
|
|
For the viewlet of Table in Query Wizard.Click
Here!
|
|
|
|
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.
|
|
|
|
|
|
|
Forms
|
Creating Form by Using Wizard
|
|
|
|
Forms
are used as a way to enter data into a database table. Forms create data in
efficient manner.
|
|
|
|
To create a form using the assistance of the wizard, follow
these steps:
|
|
1.
Click the Create form by
using wizard option on the database window.
|
|
|
|
2. From
the Tables/Queries
drop-down menu, select the table or query whose datasheet the form will
modify. Then, select the fields that will be included on the form by
highlighting each one the Available Fields window and clicking the single right arrow button > to move the field to the
Selected Fields window. To move all of the fields to Select Fields, click the
double right arrow button >>. If
you make a mistake and would like to remove a field or all of the fields from
the Selected Fields window, click the left
arrow < or left double arrow <<
buttons. After the proper fields have been selected, click the Next > button to move on to the next screen.
|
|
|
|
|
|
|
|
3. On the second screen, select the layout of the form.
|
|
a. Columnar - A single record is displayed at one time with
labels and form fields listed side-by-side in columns.
|
|
|
|
b. Justified - A single record is displayed with labels and
form fields are listed across the screen.
|
|
|
|
c. Tabular - Multiple records are listed on the page at a
time with fields in columns and records in rows.
|
|
|
|
d. Datasheet - Multiple records are displayed in Datasheet
View.
|
|
|
|
Click the Next > button
to move on to the next screen.
|
|
|
|
|
|
|
|
Select
a visual style for the form from the next set of options and click Next >.
|
|
|
|
|
|
|
|
On the
final screen, name the form in the space provided. Select "Open the form
to view or enter information" to open the form in Form View or
"Modify the form's design" to open it in Design View. Click Finish
to create the form
|
|
|
|
|
|
|
|
|
|
Creating Form in Design View
|
|
|
|
To create a form from scratch without the wizard, follow these
steps:
|
|
1.
Click the New button
on the form database window.
|
|
|
|
2.
Select "Design View" and
choose the table or query the form will be associated with the form from the drop-down menu.
|
|
|
|
3.
Select View|Toolbox from
the menu bar to view the floating toolbar with additional options.
|
|
|
|
|
|
|
|
|
|
Add
controls to the form by clicking and dragging the field names from the Field List floating
window. Access creates a text box for the value and label for the field name
when this action is accomplished. To add controls for all of the fields in
the Field List, double-click the
Field List window's title bar and drag all of the highlighted fields to the
form.
|
|
|
|
|
|
|
|
Adding Records Using a Form
|
|
|
|
Input
data into the table by filling out the fields of the form. Press the Tab key to move from field to field and create a new record by
clicking Tab after
the last field of the last record. A new record can also be created at any
time by clicking the New Record button
at the bottom of the form window. Records are automatically saved as they are
entered so no additional manual saving needs to be executed
|
|
|
|
|
|
Editing Forms
|
|
|
|
The follow points may be helpful when modifying forms in
Design View.
|
|
|
|
1. Grid lines - By default, a series of
lines and dots underlay the form in Design View so form elements can be
easily aligned. To toggle this feature on and off select View|Grid from the menu bar.
|
|
|
|
2. Snap to Grid - Select Format|Snap to Grid to align form objects with the grid
to allow easy alignment of form objects or uncheck this feature to allow
objects to float freely between the grid
lines and dots.
|
|
|
|
3. Resizing Objects - Form objects can be
resized by clicking and dragging the handles on the edges and corners of the
element with the mouse.
|
|
|
|
4. Change form object type - To easily change the
type of form object without having to create a new one, right click on the object with the mouse and select Change To and select an available object type from the
list.
|
|
|
|
5. Label/object alignment - Each form object and its
corresponding label are bounded and will move together when either one is
moved with the mouse. However, to change the position of the object and label
in relation to each other (to move the label closer
to a text box, for example), click and drag the large handle at the top,
left corner of the object or label.
|
|
|
|
6. Tab order - Alter the tab order of the objects on the
form by selecting View|Tab
Order... from the menu bar. Click the gray
box before the row you would like to change in the tab order, drag
it to a new location, and release the mouse button.
|
|
|
|
|
|
|
|
7. Form Appearance - Change the background
color of the form by clicking the Fill/Back Color
button on the formatting toolbar and click one of the color swatches on the
palette. Change the color of individual form objects by highlighting one and
selecting a color from the Font/Fore Color
palette on the formatting toolbar. The font and size, font effect, font
alignment, border around each object, the border width, and a special effect
can also be modified using the formatting toolbar:
|
|
|
|
|
|
|
|
8. Page Header and Footer - Headers and footers
added to a form will only appear when it is printed. Access these sections by
selecting View|Page Header/Footer on the menu bar.
|
|
Form control
|
|
|
|
This page explains the uses for other types of form controls
including lists, combo boxes, checkboxes, option groups, and command buttons.
|
|
|
|
List and Combo Boxes
|
|
If there are small, finite number of values for a certain field
on a form, using combo or list boxes may be a quicker and easier way of
entering data. These two control types differ in the number of values they
display. List values are all displayed while the combo box values are not
displayed until the arrow button is clicked
|
|
1. Open
the form in Design View.
|
|
|
|
2.
Select View|Toolbox to
view the toolbox and make sure the "Control
Wizards" button is pressed in.
|
|
|
|
3.
Click the list or combo box tool
button and draw the outline on the form. The combo box wizard dialog box will appear.
|
|
|
|
4.
Select the source type for
the list or combo box values
and click Next >.
|
|
|
|
|
|
|
|
5. On
the next dialog
box, set the width of the combo box by clicking and dragging the right edge
of the column. Click Next >.
|
|
|
|
|
|
|
|
|
|
|
|
6. The
next dialog box allows tells Access what to do with the value that is
selected. Choose "Remember the value
for later use" to use the value in a macro or procedure (the value is
discarded when the form is closed), or select the field that the value should
be stored in. Click Next > to
proceed to the final screen.
|
|
|
|
|
|
|
|
|
|
Check Boxes and Option Buttons
|
|
Use check boxes and option buttons to display yes/no, true/false, or on/off values. Only one value from a group of option buttons can be
selected while any or all values from a check box group can be chosen.
Typically, these controls should be used when five or less options are
available. Combo boxes or lists should
be used for long lists of options. To add
a checkbox or option group:
|
|
1.
Click the Option Group tool on the
toolbox and draw the area where the group will be placed on the form with the
mouse. The option group wizard dialog box will appear.
|
|
|
|
2. On
the first window, enter labels for the options and click the tab key to enter
additional labels. Click Next > when
finished typing labels
|
|
|
|
On the next window, select a default value if there is any and
click Next >.
|
|
|
|
|
|
|
|
Select values for the options and click Next >.
|
|
|
|
|
|
|
|
Choose what should be done with the value and click Next >.
|
|
|
|
|
|
|
|
Type the caption for the option group and click Finish.
|
|
This
page explains the uses for other types of form controls including lists, combo boxes, checkboxes, option groups, and command
buttons.
|
|
|
|
|
|
List and Combo Boxes
|
|
If there are small, finite number of values for a certain field
on a form, using combo or list boxes
may be a quicker and easier way of entering data. These two control types differ in the number of values they display. List values are all displayed while the combo boxvalues are not displayed until the arrow
button is clicked to open it.
|
|
1. Open
the form in Design View.
|
|
|
|
2.
Select View|Toolbox to
view the toolbox and make sure the "Control
Wizards" button is pressed in.
|
|
|
|
3.
Click the list or combo box tool button and draw the outline on the form.
The combo box wizard dialog
box will appear.
|
|
|
|
4.
Select the source type for the list or combo box values and click Next >.
|
|
|
|
|
|
|
|
5.
Depending on your choice in the first dialog box, the next options will vary.
If you chose to look up values from a table or query, the following box will
be displayed. Select the table or query from which the values of the combo
box will come from. Click Next > and
choose fields from the table or query that was selected. Click Next > to proceed.
|
|
|
|
|
|
|
|
6. On
the next dialog box, set
the width of the combo box by clicking and dragging the right edge of the
column. Click Next >.
|
|
|
|
|
|
|
|
7. The next dialog box allows tells Access what
to do with the value that is selected. Choose "Remember the value for later use" to use
the value in a macro or procedure (the value is discarded when the form is
closed), or select the field that the value should be stored in. Click Next > to proceed to the final screen.
|
|
|
|
|
|
|
|
8. Type
the name that will appear on the box's label and click Finish.
|
|
|
|
|
|
Check Boxes and Option Buttons
|
|
Use check boxes and option buttons to display yes/no, true/false, or on/off values. Only one value from a group of option buttons can be
selected while any or all values from a check box group can be chosen.
Typically, these controls should be used when five or less options are
available. Combo boxes or
lists should be used for long lists of options. To add a checkbox or option
group:
|
|
1.
Click the Option Group tool on the
toolbox and draw the area where the group will be placed on the form with the
mouse. The option group wizard dialog
box will appear.
|
|
|
|
2. On
the first window, enter labels for the options and click the tab key to enter additional labels. Click Next > when finished typing labels.
|
|
|
|
|
|
|
|
3. On
the next window, select a default value if
there is any and click Next >.
|
|
|
|
|
|
|
|
4.
Select values for the options and click Next >.
|
|
|
|
|
|
|
|
5.
Choose what should be done with the value and click Next >.
|
|
|
|
|
|
|
|
6.
Choose the type and style of the option group and click Next >.
|
|
|
|
|
|
|
|
7. Type
the caption for the option group and click Finish.
|
|
|
|
Command Buttons
|
|
|
|
In this example, a command button beside each record is used
to open another form.
|
|
1. Open
the form in Design View and
ensure that the Control Wizard button on the toolbox is pressed in.
|
|
|
|
2.
Click the command button icon on the
toolbox and draw the button on
the form. The Command Button Wizard will then appear.
|
|
|
|
3. On
the first dialog window, action categories are displayed in the left list while the right list displays the actions in each category. Select
an action for the command button and
click Next >.
|
|
|
|
|
|
|
|
4. The
next few pages of options will vary based on the action you selected.
Continue selecting options for the command
button.
|
|
|
|
5.
Choose the appearance of the button by entering
caption text or selecting a picture. Check
the Show All Pictures box to view the full list of available images. Click Next >.
|
|
|
|
|
|
|
|
6.
Enter a name for the command button and
click Finish to
create the button.
|
|
|
|
|
Reports
|
Report Using the Wizard
|
|
|
|
Reports will organize and group the information in a table or
query and provide a way to print the data in a database.
|
|
|
|
Create a report using Access' wizard by following these steps:
|
|
1.
Double-click the "Create report by
using wizard" option on the Reports Database Window.
|
|
|
|
Select
the information source for the report by selecting a table or query from
the Tables/Queries
drop-down menu. Then, select the fields that should be displayed in the
report by transferring them from the Available Fields menu to the Selected
Fields window using the single right
arrow button > to move fields one at a time or the double arrow button >> to move all of the
fields at once. Click the Next > button
to move to the next screen.
|
|
|
|
|
|
|
|
|
|
Select
fields from the list that the records should be grouped by and click the right arrow button > to add
those fields to the diagram. Use the Priority
buttons to change the order of the grouped fields if more than one
field is selected. Click Next > to
continue
|
|
|
|
|
|
|
|
|
|
If the
records should be sorted, identify a sort order here. Select the first field
that records should be sorted by and click the A-Z sort button to choose from ascending or descending order. Click
Next > to continue.
|
|
|
|
|
|
|
|
|
|
Select
a layout and page orientation for the report and click
Next >.
|
|
|
|
|
|
|
|
|
|
Select
a color and graphics style for the report and click
Next >.
|
|
|
|
|
|
|
|
|
|
On the
final screen, name the report and select to open it in either Print Preview or Design
View mode. Click the Finishbutton
to create the report.
|
|
|
|
|
|
Creating report in Design View
|
|
|
|
To create a report from scratch, select Design View from the
Reports Database Window.
|
|
|
|
Click
the New button
on the Reports Database Window.
Highlight "Design View" and
choose the data source of the report from the drop-down menu and click OK.
|
|
|
|
|
|
|
|
|
|
You
will be presented with a blank grid with a Field Box and form element toolbar
that looks similar to the Design View for
forms. Design the report in much the same way you would create a form. For example, double-click the
title bar of the Field Box to add all of the fields to the report at once.
Then, use the handles on the
elements to resize them, move them to different locations, and modify the
look of the report by using options on the formatting toolbar. Click the Print View button at the top, left corner
of the screen to preview the report.
|
|
|
|
|
|
|
|
Printing Reports
|
|
|
|
Select File|Page Setup to modify the page
margins, size, orientation, and column setup. After all changes have been
made, print the report by selecting File|Print from
the menu bar or click the Print button on the
toolbar.
|
Importing
exporting
|
Importing
|
|
|
|
Importing objects from another database will create a complete
copy of a table, query, or any
other database object that you select. Import a database object by following these steps:
|
|
|
|
1. Open
the destination database.
|
|
|
|
2.
Select File|Get External|Import from
the menu bar.
|
|
|
|
3.
Choose the database the
object is located in a click the Import
button.
|
|
|
|
4. From
the Import Objects window, click
on the object tabs to
find the object you want to import into the database. Click the Options >> button to view more options.
Under Import Tables, select "Definition
and Data" if the entire table should be copied or "Definition Only" if the table structure
should be copied but not the data. Under Import Queries, select "As Tables" if the queries should
appear as regular tables in the destination database. Highlight the object
name, and click OK.
|
|
|
|
|
|
|
|
5. The
new object will now appear with the existing objects in the database.
|
|
Exporting
|
|
|
|
The effect of importing can also be achieved using the opposite
method of exporting.
|
|
|
|
1. Open
the database
containing an object that will be copied (exported) to another database.
|
|
|
|
2. Find
the object in the Database Window and
highlight it. Then, select File|Export... from
the menu bar.
|
|
|
|
3.
Select the destination database from
the window and click Save.
|
|
|
|
4. You
will be prompted to name the new object and may also be given other options,
such as whether to copy the structure or data and structure of a table. Click
OK to complete the export
procedure.
|
|
|
|
Linking
|
|
|
|
Unlike importing, linking objects
from another database will create a link to an object in another database
while not copying the table to the current database. Create a link by
following these steps:
|
|
1. Open the destination database.
|
|
|
|
2. Select File|Get External|Link
Tables... from the menu bar.
|
|
|
|
3. Choose the database that the
table is located in and click the Link button.
|
|
|
|
4. A window listing the tables in
the database will then appear. Highlight the table or tables that should be
linked and click OK. A link to the table will appear in the Database Window
as a small table icon preceded by a small right arrow.
|
Sorting and Filtering
|
Introduction
|
|
|
|
Sorting and filtering allow you to view records in a table in a
different way either by reordering all of the records in the table or view
only those records in a table that meets certain criteria that you specify.
|
|
|
|
Sorting
|
|
|
|
You may want to view the records
in a table in a different order than they appear such as sorting by a date or
in alphabetical order, for example. Follow these steps to execute a simple
sort of records in a table based on the values of one field:
|
|
|
|
1. In table view, place the cursor
in the column that you want to sort by.
|
|
|
|
2. Select Records|Sort|Sort
Ascending or Records|Sort|Sort Descending from the menu bar or click the Sort
Ascending or Sort Descending buttons on the toolbar.
|
|
|
|
To sort by more than one column
(such as sorting by date and then sorting records with the same date
alphabetically), highlight the columns by clicking and dragging the mouse
over the field labels and select one of the sort methods stated above.
|
|
Filter by Selection
|
|
|
|
This feature will filter records that contain identical data values in a given field such as
filtering out all of the records that have the value "Smith" in a name field. To
Filter by Selection, place the cursor in the field that you want to filter
the other records by and click the Filter
by Selectionbutton on the toolbar or select Records|Filter|Filter By Selectionfrom
the menu bar. In the example below, the
cursor is placed in the City field of the second record that displays the
value "Ft. Myers" so the
filtered table will show only the records where the city is Ft. Myers.
|
|
|
|
|
|
|
|
Filter by Form
|
||||||||||
|
|
||||||||||
|
If the table is large, it may be difficult to find the record
that contains the value you would like to filter by so using Filter by Form may be advantageous
instead. This method creates a blank version of the table with drop-down
menus for each field that each contain the values found in the records of
that field. Under the default Look for
tab of the Filter by Form window, click
in the field to enter the filter criteria. To specify an alternate criteria
if records may contain one of two specified values, click the Or tab at the bottom of the window and select another criteria from
the drop-down menu. More Or tabs will
appear after one criteria is set to allow you to add more alternate criteria
for the filter. After you have selected all of the criteria you want to
filter, click the Apply Filter button
|
||||||||||
|
|
||||||||||
|
|
||||||||||
|
|
||||||||||
|
|
||||||||||
|
The
following methods can be used to select records based on the record selected
by that do not have exactly the same value. Type these formats into the field
where the drop-down menu
appears instead of selecting an absolute
value.
|
||||||||||
|
|
||||||||||
|
Filter by Form
|
||||||||||
|
|
Saving A Filter
|
|
|
|
The filtered contents of a table can be saved as a query by
selecting File|Save As Query from the menu bar. Enter a name for the query
and click OK. The query is now saved within the database.
|
|
|
|
|
|
|
|
Shortkeys
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Keyboard shortcuts can save time and the
effort of constantly switching from the keyboard to the mouse to execute
simple commands and query.with the
help of short keys you can easily access your data.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Process
|
|
|
|
1.
Verify that your system meets the basic requirements for Access. You'll need at least a 233MHz or
faster Pentium processor with 128MB
of RAM. You'll also need at least 180MB of free hard disk space.
|
|
|
|
2.
Ensure that your operating system is up-to-date. You'll need Windows 2000 or later to run Access 2003. It's a good idea to apply all security
updates and hotfixes to your system before installing access by visiting http://windowsupdate.microsoft.com/
|
|
|
|
3.
Insert the MS Office CD into
your CD-ROM drive.
The installation process will begin automatically and ask you to wait while
the system prepares the Installation Wizard.
|
|
|
|
4. The
next step of the process will prompt you to enter your name, your
organization's name and the product key found on your CD case. Once you've completed this, click Next to continue.
|
|
|
|
5. The
next screen will prompt you to accept the terms of the End User License Agreement (EULA) by
clicking the check box. Once you've done so, click Next to continue.
|
|
|
|
6. If
you'd like to install the entire Office
suite (or you're using an Access-only CD), you can choose "Install Now" on the next screen. If
you'd like to customize your installation, you may do so at this stage.
Otherwise, accept the default Install Now and click Next.
|
|
|
|
7. The
next screen summarizes the installation. After you've reviewed the comments,
click the Install button to
begin the installation.
|
|
|
|
8. When
the installation completes, you may be prompted to restart your computer. Go ahead and do so.
|
|
|
|
9. When
your comptuer restarts, the first thing you should do is visit the Office
Update site at http://officeupdate.microsoft.comto
download any security patches for Access. This is a critical step.
|
