MS ACCESS
2010
Relational Database
This is a most common type of DBMS. Data
elements are stored in different tables that are made up of rows & columns.
It data in different tables through the use of common data elements this is
done through primary key & foreign key.
Example of
DBMS
·
Access
·
FoxPro
·
Alpha Five
·
FileMaker Pro
·
Oracle
·
MS SQl
·
DBASE
DATA BASE
A
database is an organized of information. A collection of related of related
records
RECODE
A
recode is all the information about one person , thing or place. A single
recode is contained in one row of the table.
FILED
A
field is the smallest item of information about a recode. Each field is displayed In a column of the
table.
Advantages of using DBMS
1.
Sharing of Data
2.
Flexible
3.
Data integrity
Entities
An
entity is a thing that you are storing information about in a data file or
database.
E.g.
Person , plant, animal, Mineral, etc.
Attributes
A
characteristic or quantity that further describes a particular entity
E.
g a person address, height, age, etc.
Relationships
Details
of relationship between different entities
keys
Primary Key
An
attribute (or combination of attributes) that uniquely identifiers each rows in
a relation employee (Emp No, Emp name, department)
Composite
Key
A
primary key that consists more than one attribute salary
(Emp No, Emp Name, Emp sal)
Foreign
Key
A
Foreign Key is set of columns in one table that serve as the primary key in
another table.
INDEX
An
ordered set of pointers to the data in the table.
Type of
files
1.
Program files 2.Data files
Two commonly
used data files
·
Master file -: A files containing relatively permanent records and
is generally update periodically
·
Transaction flies -: A
temporary holding files that hold all changes to made to the maters files.
PLANNING
DATABASE
1.
Planning
2.
Crating
3.
Entering data
4.
Editing
5.
Creating forms
6.
Analyzing Data
7.
Creating Reports
8.
Preview & Printing
What Is a Relational Database?
The term database means different things to different people. For
many years, in the world of dBase (that is, date BASE, FoxPro, CA-Clipper, and
other older database technologies), database was used to describe a collection
of fields and records. (Access refers to this type of collection as a table.)
In a client/server environment, database refers to all the data, schema,
indexes, rules, triggers, and stored procedures associated with a system. In
Access terms, a database is a collection of all the tables, queries, forms,
reports, macros, and modules that compose a complete sys-tem. Relational refers
to concepts based on set theory.
How to start Ms Access
1st Method
1. Click on the Start button
2. Highlight Programs
3. Highlight Microsoft Office
4. Click on Microsoft
Access 2013
2nd Method
Double
click on the desktop Ms Access shot cut Icon
3rd Method.
1.Start
2.Run
3.Type msaccess
How to Exit Access
1st Methord
1.Click
the file tab
2.Click
Exit Access
2nd Methord
Click
the close button on the title bar
3rd Methord
Press Alt key + F4 key
Create a New Database
1. Click File tab
2. Select New
3.
Select Blank Database or
Blank Web Database
4.
Select a drive/folder where you will place the database.
|
Figure:1.1
|
5. Type a filename for the database.
6. Click the Create button.
Access Main Object
1.
Tables
2.
Queries
3.
Forms
4.
Reports
5.
Macros
6.
modules
The Navigation Pane
To manipulate
the Navigation pane:
·
Click the double right –arrows
>> to open it. The arrows change to double left
–arrows <<
·
Click the double left- arrows
<< to close it. The
arrows change to double right –arrows >>.
Access Objects
To view or hide the objects on the Navigation
pane:
You click the double down –arrows to view objects. The double down –arrows
change to double up-arrows
You click the double up arrows to
hide objects. The double
up-arrows change to double down –arrows
As stated earlier , the Navigation pane
stores the objects in your database : table , queries, forms , reports, macros,
and modules.
Objects always display with an icon to the
right .Object type:
Table, query, form,
report macro and module.
|
Object
|
|
|
Tables
|
In Access,
data is stored in tables. A table is a set of columns and rows, with each
column referred to as a field. Each value in a field represents a single type
of data. Each row of a table is referred to as a record.
|
|
Queries
|
You use
queries to retrieve specific data from your database and to answer questions
about your data .for example, you can
use a query to find the names of the employees in your database who live in a
particular state.
|
|
Forms
|
Forms give
you the ability to choose the format and arrangement of fields. You can use a
form to enter, edit and display data.
|
|
Reports
|
Report
organize or summarize your data so you can print it or view it on screen. you
often use reports when you want to analyze your data or present your data or
present your data to others.
|
|
Macros
|
Macros
give you the ability to automate tasks. You can use a Macros add
functionality to a form , report , or control.
|
|
Modules
|
Like
macros, modules give you the ability to automate tasks and add functionality
to a form, report, or control. Macros are created by choosing from a list of
macro actions, whereas modules are written in Visual Basic for
Applications.
|
1.
Click File tab
2.
Select close Database
Building a New Table
You can add a new table to an Access
2010 database in several ways: You can design the table from scratch, build the
table from a datasheet (a spreadsheet-like format), import the table from
another source, or link to an external table.
Building a Table from a
Datasheet
Building a table from a datasheet was
very limited in earlier versions of Access. With Access 2010, you can do quite
a bit while in Datasheet view. To use the datasheet method, follow these steps:
1.
Click Create tab
2.
Select Tables group
3.
Click Table Button
4.
A new datasheet appears,
5.
Select Data type
6.
Type Field Name
7.
Click table tools
8.
Click field tab
9.
Select properties group
10. Set you want field
properties
11. After you have added all the columns and data you want,
click the Save button on the Quick Access
toolbar
1.
Click Create tab
2.
Select Tables group
3.
Click Table Design
Button
|
6
|
|
5
|
4.
A new Table Design appears,
5.
Type Field Name in the
field name Column
6.
Select Data type
7.
Tab to the Description column and enter a description for
the data.
|
8
|
8.
Add field properties using
field properties Section
9.
Save table
How to Delete Table
1. Select Table Name
2. Click Mouse Right button
3. Select Delete
4. Click Yes
How to Rename Table
1. Select Table Name
2. Click Mouse Right button
3. Select Rename
4. Type New Nmae
How to Save Table
1. Click file Tab
2. Select Save
3. Type Table name
4. Click ok
How to Change Table
views
1. Click Home Tab
2. Select View group
3. Click view Button
4. Select Data Sheet
View, Pivot Table View, Pivot
Chart View, Design View
Data Types
Every field has a data
type. A field's data type indicates the kind of data that the field stores,
such as large amounts of text or attached files.
A
data type is a field property, but it differs from other field properties as
follows:
You
set a field's data type in the table design grid A
field's data type determines what other properties the field has. You
must set a field's data type when you create the field.
1. Go to table design
grid,
2. locate the field for which you want to set the data type.
3. Click the Data Type column,
4. Choose a data type from the list.
|
Data type
|
Use to store
|
Size
|
|
Text
|
Alphanumeric characters Use for
text, or for numbers that are not used in calculations
|
Up to 255 characters.
|
|
Memo
|
Alphanumeric characters (longer
than 255 characters in length) or text that uses rich text formatting. Use
for text that is more than 255 characters in length
|
Up to 1 gb of characters, or 2
gb of storage (2 bytes per character), of which you can display 65,535
characters in a control.
|
|
Number
|
Numeric values (integers or
fractional values). Use for storing numbers that are used in calculations,
except for monetary values (use the Currency data type for monetary values).
|
1, 2, 4, or 8 bytes, or 16 bytes
when used for a replication ID.
|
|
Date/ Time
|
Dates and times. Use for storing
date/time values..
|
8 bytes.
|
|
Currency
|
Monetary values. Use for storing
monetary values (currency).
|
8 bytes.
|
|
Auto Number
|
A unique numeric value that
Access automatically inserts when a record is added.
|
4 bytes or 16 bytes when used
for replication ID.
|
|
Yes/No
|
Boolean values (yes/no). You can
use one of three formats: Yes/No, True/False, or On/Off.
|
1 bit (8 bits = 1 byte).
|
|
OLE Object
|
OLE objects or other binary
data. Use for storing OLE objects from other Microsoft Windows programs.
|
Up to 1 gigabyte.
|
|
Attachment
|
Pictures, Images, Binary files,
Office files. This is the preferred data type for storing digital images and
any type of binary file.
|
For compressed attachments, 2
gigabytes.
|
|
Hyperlink
|
Hyperlinks. Use for storing
hyperlinks to provide single-click access to Web pages through a or files
through a name in UNC format. You can also link to Access objects stored in a
database.
|
Up to 1 gigabyte of characters,
or 2 gigabytes of storage
|
|
Lookup Wizard
|
Not actually a data type;
instead, this starts the Lookup Wizard. Use to start the Lookup Wizard so
that you can create a field that uses a combo box to look up a value in
another table, query, or list of values.
|
Table or query based: The size
of the bound column. Value based: The size of the Text field used to store
the value.
|
Keys
Primary key
A table can have only one primary key.
A primary key consists of one or more fields that uniquely identify each record
that you store in the table. Often, there is a unique identification number,
such as an ID number, a serial number, or a code, that serves as a primary key
Foreign key A
table can also have one or more foreign keys. A foreign key contains values
that correspond to values in the primary key of another table
A table relationship, shown in the
Relationships window.
1. Go to design view
2. Select Table
3. Click table Tools
4. Select Design Tab
5. Click tools Group
6. Select Field name
7. Click primary Key Button
Or
1. Go to design view
2. Select Table
3. Select Field name
4. Click mouse right button
5. Click primary Key
How to Remove Primary Key
1. Go to design view
2. Select Table
3. Click table Tools
4. Select Design Tab
5. Click tools Group
6. Select Field name
7. Click primary Key Button
Or
1. Go to design view
2. Select Table
3. Select Field name
4. Click mouse right button
5. Click primary Key
How to
insert row
1.
Go to design view
2. Select Table tools
3. Select Design
4. Click tools group
5. Click insert row
How to
delete row
1. Go to design view
2. Select Table tools
3. Select Design
4. Click tools group
5. Click delete row
How to
insert Column
1. Go to design view
2. Select Table tools
3. Select Design
4. Click Add & Delete
group
5. Click insert Column
How to
delete Column
1. Go to design view
2. Select Table tools
3. Select Design
4. Click Add & Delete
group
5. Click delete Column
How to
change row height
1. Select Row
2. Go to design view
3. Select Table tools
4. Select Design
5. Click record group
6. Click row height
How to
Change Column width
1. Select column
2. Go to design view
3. Select Table tools
4. Select Design
5. Click record group
6. Click Column width
How to hide & Unhide column
1. Select column
2. Go to design view
3. Select Table tools
4. Select Design
5. Click record group
6. Click hide & Unhide Column width
Set
the Validation Rule Property from Design View
To
establish a field-level validation rule (from Design view), follow these steps:
1. Select
the desired field name from the top pane of the Design view window.
2. Click
the Validation Rule text box in the Field Properties pane.
3. Type
the desired validation rule.
Set
the Validation Text Property from Design View
To
add validation text, follow these steps:
1.
Click the Validation Text text box in the Field Properties
pane.
2.
Type the desired text (for example, Age Must be Between 0
and 120).
3.
You can require users of a database to enter a valid value
in selected fields when
a.
editing or adding records. For example, you can require a
user to enter a date for
b.
each record in an Invoice table.
Use
the Lookup Wizard
To
use the Lookup Wizard (from Design view), follow these steps:
1.
Select the desired field.
2.
Choose Lookup Wizard as the data type
3.
Select the desired source of the values, and then click
Next.
4.
Select the table or query to provide the values, and then
click Next.
5.
Double-click the fields that contain the desired values, and
then click Next.
6.
Drag the Lookup column to the desired width, and then click
Next.
7.
Type a name for the Lookup column, and then click Finish.
Activating the lookup wizard
The Types of Relationships
Three types of
relationships can exist between tables in a database:
1.
one to many,
2.
one to one,
3.
many to many.
Setting up the proper type of relationship
between two tables in a database is imperative. The right type of relationship
between two tables ensures
•
Data integrity
•
Optimal performance
•
Ease of use in designing system objects
The
reasons behind these benefits are covered throughout this hour. Before you can
understand the benefits of relationships, though, you must understand the types
of relationships available.
One-to-Many Relationships
A one-to-many relationship is by far the most common type of
relationship. In a one-to-many relationship, a record in one table can have
many related records in another table.
One-to-One Relationships
In a one-to-one relationship, each record in the table on
the “one” side of the relationship can have only one matching record in the
table on the “many” side of the relationship. This relationship is not common
and is used only in special circum- stances. Usually, if you have set up a
one-to-one relationship, you should have combined the fields from both tables
into one table.
Many-to-Many Relationships
In a many-to-many relationship, records in two tables have
matching records. You cannot directly define a many-to-many relationship in
Access; you must develop this type of relationship by adding a table called a junction
table. You relate the junction table to each of the two tables in one-to-many
relationships. For example, with an Orders table and a Products table, each
order will probably contain multiple products, and each product is likely to be
found on many different orders. The solution is to create a third table, called
Order Details. You relate the Order Details table to the Orders table in a
one-to-many relationship based on the Order ID field. You relate it to the
Products table in a one-to-many relationship based on the Product ID field.
|
|
Establish a Relationship between Two Tables
1. Click database tools tab
2. Select relationship group
3.
Select relationship
4.
The Show Table dialog box appears. Select each
table you want to relate and click Add. & Click Close when you are finished.
5.
Click and drag the field
from one table to the matching field in the other table.
6.
The Edit Relationships
dialog box appears.
7.
Determine whether you want
to establish referential integrity and whether you want to cascade update
related fields or cascade delete related records by enabling the appropriate
check boxes
8.
Click ok button
9.
Save relation ship
1.
Click
database tools tab
2. Select relationship group
3. Click relationship button
4. Go to relationship window
5. Select the line
joining the two tables
6. Click right mouse button
7. Select Delete
How to
Edit relationship
1.
Click
database tools tab
2. Select relationship group
3. Click relationship button
4. Go to relation ship window
5. Select the line joining
the two tables
6. Click right mouse button
7. Select Edit
8.
The Edit Relationships
dialog box appears.
9.
Determine whether you want to establish referential
integrity and whether you want to cascade update related fields or cascade
delete related records by enabling the appropriate check boxes
10. Click ok button
11. Save relation ship
How
to open show Table
Click
relationship tools tab
1. Click design
2. Select relationship group
3. Click show Table button
normalization rules
|
Normal
Form
|
Rule
|
|
An Entity
Type Is In 1nf When It Contains No Repeating Groups Of Data.
|
|
|
An Entity Type Is In 2nf When It Is In 1nf
And When All Of Its Non-Key Attributes Are Fully Dependent On Its Primary Key.
|
|
|
An Entity Type Is In 3nf When It Is In 2nf
And When All Of Its Attributes Are Directly Dependent On The Primary Key.
|
Set field properties
1. Go to table design
grid,
2. select the field for which you want to set properties.
3. Go to the Field
Properties pane. & click General Tab
4. enter the settings that you want for each property,
|
Field property
|
Description
|
|
Field Size
|
Set the maximum size for data
stored as a Text, Number, or AutoNumber data type.
|
|
Format
|
Customize the way that the field
appears by default when displayed or printed.
|
|
Decimal Places
|
Specify the number of decimal
places to use when displaying numbers.
|
|
New Values
|
Specify whether an AutoNumber
field is incremented or assigned a random value when a new record is added.
|
|
Input Mask
|
Display characters that help to
guide data entry. For more information about creating and using an input
mask, see the See Also section.
|
|
Caption
|
Set the text displayed by
default in labels for forms, reports, and queries.
|
|
Default Value
|
Automatically assign a default
value to a field when new records are added.
|
|
Validation Rule
|
Supply an expression that must
be true whenever you add or change the value in this field.
|
|
Validation Text
|
Enter a message to display when
a value violates the expression in the Validation Rule property.
|
|
Required
|
Require that data be entered in
a field.
|
|
Allow Zero Length
|
Allow entry (by setting the
property's value to Yes) of a zero-length string ("")
in a Text or Memo field.
|
|
Indexed
|
Speed up access to data in this
field by creating and using an index.
|
|
Unicode Compression
|
Compress text stored in this
field when a small amount of text is stored (< 4,096 characters).
|
|
IME Mode
|
Control conversion of characters
in an East Asian version of Windows.
|
|
IME Sentence Mode
|
Control conversion of sentences
in an East Asian version of Windows.
|
|
Smart Tags
|
|
|
Append Only
|
Track the history of field
values (by setting the property's value to Yes).
|
|
Text Format
|
Choose the property's Rich
Text value to store text as HTML and allow rich formatting. Choose the
property's Plain Text value to store only unformatted text.
|
|
Text Align
|
Specify the default alignment of
text within a control.
|
Set up data validation
rules
Set up a field validation rule and
message
1. Select the field to which you want to add a validation rule.
2. On the ribbon, click the Fields tab.
3. In the Field Validation group, click Validation, and then
click Field Validation Rule.
4. The Expression Builder opens.
5. Use the Expression Builder to create your validation rule.
For Help using the Expression Builder, see the article Use
the Expression Builder.
6. In the Field Validation group, click Validation, and then click
Field Validation Message.
7. Type the message that you want to display when input data is
not valid, and then click OK.
Examples of Validation Rules
|
Validation
Rule
|
Validation
Text Examples
|
|
>0
|
Please
enter a valid Employee ID Number.
|
|
“H”
or “S” or “Q”
|
Only
H or S or Q codes will be accepted.
|
|
Between
Date()-365 and Date()+365
|
Date
cannot be later than one year ago today or more than one year from today.
|
|
>0
or is Null
|
Enter
a valid ID number or leave blank if not approved.
|
|
Between
0 and 9 or is Null
|
Rating
range is 0 through 9 or is blank.
|
|
>Date()
|
Date
must be after today.
|
Set
up a record validation message
You
can use a record validation rule to prevent duplicate records, or to require a
certain combination of facts about the record are true, such as [Start Date] is
greater than January 1, 2010, and less than [End Date]
1. Open the table to which you want to add a validation rule.
2. On the ribbon, click the Fields tab.
3. In the Field Validation group, click Validation, and then
click Record Validation Rule.
4. The Expression Builder opens.
5. Use the Expression Builder to create your validation rule.
For Help using the Expression Builder, see the article Use
the Expression Builder.
6. In the Field Validation group, click Validation, and then
click Record Validation Message.
7. Type the message that you want to display when input data is
not valid, and then click OK.
Forms
A form
is a database object that you can use to create a user interface for a database
application. A "bound" form is one that is directly connected to a
data source such as a table or query, and can be used to enter, edit, or
display data from that data source.
• Single Form—Allows only one record to
be viewed at a time
• Continuous Forms—Displays as many records as will fit
within the form window at on time, presenting each as the detail
section of a single form
• Datasheet—Displays the records in a spreadsheet-like
format, with the rows representing records and the columns representing fields
• PivotTable—Displays the records in a
Microsoft Excel-type pivot table format
• PivotChart—Displays the records in a
Microsoft Excel-type pivot chart Format
Create a new form
1.
In the Navigation
Pane, click the table or query that contains the data you want to see on your
form.
2.
Click On the Create
tab,
3.
Select in the Forms group,
4.
click Form.
To create a split form by using the Split
Form tool:
1.
In the Navigation
Pane, click the table or query that contains the data that you want on your
form. Or open the table or query in Datasheet view.
2.
Click On the Create
tab,
3.
Select in the Forms group
4.
click Split
Form.
Create
a form that displays multiple records by using the Multiple Items tool
When
you create a form by using the Form tool, the form that Access creates displays
a single record at a time. If you want a form that displays multiple records
but is more customizable than a datasheet, you can use the Multiple Items tool.
1.
In the Navigation
Pane, click the table or query that contains the data you want to see on your
form.
2.
Click On the Create
tab,
3.
Select in the Forms group
4.
click More
Forms,
5.
click Multiple
Items.
1.
Click On the Create
tab,
2.
Select in the Forms group
3.
Click Form
Wizard.
4.
Select Table
/Queries
5.
Select field you
want on your form
6.
Click next button
7.
Select layout
& click next button
8.
Select style &
click next button
9.
Type form name
& click finish button
Create a form by using the Blank
Form tool
1.
Click On the Create
tab,
2.
Select in the Forms group
3.
click Blank
Form.
4.
Access opens a
blank form in Layout view, and displays the Field List pane.
5.
In the Field
List pane, click the plus sign (+) next to the table or tables that
contain the fields that you want to see on the form.
6.
To add a field to
the form, double-click it or drag it onto the form.
Create a form by using the
Datasheet
1.
Click On the Create
tab,
2.
Select in the Forms group
3.
click More Form.
4.
Click Datasheet
button
Create
a form by using the Model Dialog
1.
Click On the Create
tab,
2.
Select in the Forms group
3.
click More Form.
4.
Click Model Dialog
button
How
to apply them for form
1.
Go to form design
view
2.
Click form tools
3.
Select design
4.
Select themes
group
5.
Select you want to
them
1.
Go to form design
view
2.
Click form tools
3.
Select design
4.
Select themes
group
5.
Click you want to Color group
1.
Go to form design
view
2.
Click form tools
3.
Select design
4.
Select themes
group
5.
Click you want to Font
How
to apply field list
1.
Go to form design
view
2.
Click form tools
3.
Select design
4.
Select tools group
5.
Click add existing
fields
6.
click the plus
sign (+) next to the table or tables that contain the fields that you
want to see on the form.
7.
To add a field to
the form, double-click it or drag it onto the form.
How
to add control for forms
1.
Go to form design
view
2.
Click form tools
3.
Select design
4.
Select
controls group
5.
Click & add
control to form
How
to add Header /Footer
1.
Go to form design
view
2.
Click form tools
3.
Select design
4.
Select Header/Footer
group
Open
a Form
1. Click Forms in the list of objects.
2. Right-click the form you want to open
Delete
form
1. Click Forms in the list of objects.
2. Select form
3. Right-click the form you want to delete
4. Click delete
The Command Button Wizard:
Programming Without Typing
1.
Go to form design
view
2.
Click form tools
3.
Select design
4.
Select controls group
5.
Click & add
control to form
6.
Select operation
categories & suitable Action & click Next button
7.
Select text or
picture & click Next button
8.
Click finish
button
QUERIES
Using a query,
you can answer very specific questions about your data that would be difficult
to answer by looking at table data directly. You can use queries to filter your
data, to perform calculations with your data, and to summarize your data. You
can also use queries to automate many data management tasks and to review
changes in your data before you commit to those changes.
When you want to review, add, change, or delete data
from your database, consider using a query.
Adding Fields to Queries
• You can double-click the name of the field you want to
add.
• You can click and drag a single field from the table in
the top half of the query window to the query design grid below.
• You can select multiple fields at the same time by using
the Shift key (for a contiguous range of fields) or the Ctrl key (for a
noncontiguous range). You can double-click the title bar of the field list to
select all fields and then click and drag any one of the selected fields to the
query design grid.
Removing a Field from the Query Design Grid
To
remove a field from the query design grid, follow these steps:
1. Find the field you want to remove.
2. Click the column
selector (that is, the small horizontal gray button) immediately above the name
of the field. The entire column of the query design grid should become black
3. Press the Delete key or select Delete from
the Edit menu. Access removes the field from the query.
- Click Query Tools
Design tab
- Select Result tab
- Click Run
command.
To Apply a Simple One-Table
Query:
- Click Create tab
- Select the Queries
group.
- Click the Query
Design command. Access will switch to Query Design view. In the
Show Table dialog box that appears,
- Select the table you
would like to run a query on.
- Click Add,
- Click Close.
- Double click a field
name to add to the query
- After you have set
query
- Click Query Tools
Design tab
- Select Result tab
- Click Run
command.
To
Hide a Field within a Query:
- Open the query Design
View.
- Go to show row
- Locate the field you
would like to hide.
- Click the checkbox
in the Show: row to uncheck it.
- Click Query Tools
Design tab
- Select Result tab
- Click Run
command.
To
Sort Field within a Query:
- Open the query Design
View.
- Go to Sort row
- Locate the field you
would like to sort
- Click the checkbox
in the Show: row to uncheck it.
- click the drop-down arrow to
select either an ascending or descending
sort.
- Click Query Tools
Design tab
- Select Result tab
- Click Run
command.
How to apply criteria
1.
Open the query Design View.
2.
Go
to criteria row
3.
Locate
the field you would like to criteria .
4.
Click Query
Tools Design tab
5.
Select
Result group
6.
Click Run
command.
|
Criteria
|
Effect
|
|
> 234
|
Returns all numbers greater than
234. To find all numbers less than 234, use < 234.
|
|
>= "Callahan"
|
Returns all records from
Callahan through the end of the alphabet.
|
|
Between #2/2/2007# And
#12/1/2007#
|
Returns dates from 2-Feb-07
through 1-Dec-07 (ANSI-89). If your database uses the ANSI-92 wildcard
characters, use single quotation marks (') instead of pound signs. Example:
Between '2/2/2007' And '12/1/2007'.
|
|
Not "Germany"
|
Finds all records where the
exact contents of the field are not exactly equal to "Germany." The
criterion will return records that contain characters in addition to
"Germany," such as "Germany (euro)" or "Europe
(Germany)".
|
|
Not "T*"
|
Finds all records except those
beginning with T. If your database uses the ANSI-92 wildcard character set,
use the percent sign (%) instead of the asterisk (*).
|
|
Not "*t"
|
Finds all records that do not end
with t. If your database uses the ANSI-92 wildcard character set, use the
percent sign instead of the asterisk.
|
|
In(Canada,UK)
|
In a list, finds all records
containing Canada or UK.
|
|
Like "[A-D]*"
|
In a Text field, finds all
records that start with the letters A through D. If your database uses the
ANSI-92 wildcard character set, use the percent sign instead of the asterisk.
|
|
Like "*ar*"
|
Finds all records that include
the letter sequence "ar". If your database uses the ANSI-92
wildcard character set, use the percent sign instead of the asterisk.
|
|
Like "Maison Dewe?"
|
Finds all records that begin
with "Maison" and that also contain a 5-letter second string in
which the first 4 letters are "Dewe" and the last letter is unknown
(indicated by a question mark). If your database uses the ANSI-92 wildcard
character set, use the underscore (_) instead of the question mark.
|
|
#2/2/2007#
|
Finds all records for February
2, 2007. If your database uses the ANSI-92 wildcard character set, surround
the date with single quotation marks instead of pound signs (#).
Example: '2/2/2007'.
|
|
< Date() - 30
|
Returns all dates more than 30
days old.
|
|
Date()
|
Returns all records containing
today's date.
|
|
Between Date() And
DateAdd("M", 3, Date())
|
Returns all records between today's
date and three months from today's date.
|
|
Is Null
|
Returns all records that contain
a null (blank or undefined) value.
|
|
Is Not Null
|
Returns all records that contain
a value.
|
|
""
|
Returns all records that contain
a zero-length string. You use zero-length strings when you need to add a
value to a required field, but you don't yet know what that value is. For
example, a field may require a fax number, but some of your customers may not
have fax machines. In that case, you enter a pair of double quotation marks
with no space between them ("") instead of a number.
|
- Create or open a query
you would like to use as a totals query
- Click Query tools
- Select In
the Query Design tab,
- Select Show/Hide group
- Select the Totals command.
- A row will be added to
the table in the Design Grid, with all values in that row set to Group
By. Select the cell in the Total: row of the field you would
like to perform a calculation on, and click the drop-down arrow that
appears.
- Selecting the totals
row of the field we want to perform a calculation on
- Click Query Tools
Design tab
- Select Result tab
- Click Run
command.
Create and Run
a Parameter Query:
- Create or open a query
you would like to use as a Parameter Query
- Locate the field or
fields that you would like to include variable criteria, and place your
cursor in the Criteria: row.
- Type the phrase you
would like to appear in the prompt that will pop up every time you run
your query. Make sure to enclose the phrase in brackets [ ]. For
example, [What date?].
- Click Query Tools
Design tab
- Select Result group
- Click Run
command.
Date
Criteria Expressions
|
Expression
|
Meaning
|
Expression
|
Result
|
|
Date()
|
Current date
|
Date()
|
Records the current date within
a field.
|
|
Day(Date)
|
The day of a date
|
Day ([Order Date])=1
|
Records the order date on the
first day of the month.
|
|
Month(Date)
|
The month of a
date
|
Month ([Order Date])=1
|
Records the order date in January.
|
|
Year(Date)
|
The year of a date
|
Year
([Order Date])=1991
|
Records the order date in 1991.
|
|
Weekday(Date)
|
The weekday of a
date
|
Weekday
([Order Date])=2
|
Records the order date on a
Monday.
|
|
Between Date
And Date
|
A range of dates
|
Between #1/1/95#
and #12/31/95#
|
Finds all records in 1995.
|
|
Date Part(Interval,
Date)
|
A specific part of a
date
|
Date Part
(“q”,[Order Date])=2
|
Finds all records in the second
quarter.
|
Updating Query Results
If you haven’t realized it yet, you can usually update the
results of a query. This means that if you modify the data in the query output,
Access permanently modifies the data in the tables underlying the query.
Update Query table
- Create or open a query
you would like to use as a update Query
- Click Query Tools
Design tab
- Select query type group
- Click update query
- Go to update to row
- Select Result tab
Locate the field or fields that you would like to include update,
- Click Query Tools
Design tab
- Select Result group
- Click Run
command.
- Click yes for update
massage
Make
calculations based on your data
- Click On the Create tab,
- Select in the Query group,
- click Query Design.
- In the Show Table dialog
box, on the Tables tab, double-click table name
- Close the Show Table dialog
box.
- Apply the you want to
fields using table.
- Go to last column filed In the
second column of the grid, right-click the Field row, and then
click Zoom on the shortcut menu.
- In the Zoom box, type or
paste the you want calculation
Subtotal: ([Quantity]*[Unit
Price])-([Quantity]*[Unit Price]*[Discount])
9.
Click OK.
- On the Design tab, in the Results
group, click Run.
Make query table
1.
Click On the Create
tab,
2.
Select in the Query group,
3.
Click Query
Design.
4.
In the Show
Table dialog box, on the Tables tab, double-click table name
5.
Close the Show
Table dialog box.
6.
Apply the you want
to fields using table.
7.
Click On the Design
tab, in the Results group, click Run.
8.
Type table name
9.
Click ok button
Create
a crosstab query by using the crosstab query wizard
Using the Crosstab Query Wizard
requires that you use a single table or query as the record source for your
crosstab query. If a single table does not have all the data that you want to
include in your crosstab query, start by creating a select query that returns
the data that you want. For more information about creating a select query,
refer to the See Also section.
1.
Click On the Create
tab,
2.
Select in the query
group,
3.
click Query
Wizard.
4.
click Crosstab
Query Wizard,
5.
click OK.
6.
The Crosstab Query
Wizard starts.
7.
On the first page
of the wizard, choose the table or query that you want to use to create a
crosstab query.
8.
On the next page,
choose the field that contains the values that you want to use as row headings.
9.
You can select up
to three fields to use as row headings sources, but the fewer row headings you
use, the easier your crosstab datasheet will be to read.
10.
On the next page,
choose the field that contains the values that you want to use as column
headings.
11.
On the next page,
choose a field and a function to use to calculate summary values. The data type
of the field that you select determines which functions are available.
12.
On the same page,
select or clear the Yes, include row sums check box to include or
exclude row sums.
13.
On the next page
of the wizard, type a name for your query and then specify whether you want to
view the results or modify the query design.
Create
duplicate value
1.
Click On the Create
tab,
2.
Select in the query
group,
3.
click Query
Wizard.
4.
click find
duplicate query Wizard,
5.
click OK.
6.
Select table for
duplicate value & click next
7.
Select field might
contain duplicate value & click next
8.
Select additional
field & click next
9.
Type query name
10.
Click finish
Create
unmatched query
1.
Click On the Create
tab,
2.
Select in the query
group,
3.
click Query
Wizard.
4.
click find
duplicate query Wizard,
5.
click OK.
6.
Select table for query
& click next
7.
Select matching field
& click < = > button & click
next
8.
Select field for query
& click next
9.
Type query name
10.
Click finish
Printing Query Results
It is easy to print query results. Although not as elegant
as a printed report, printed query results are often sufficient to meet
people’s needs. Here’s how you print query results:
1. Run the query whose
results you want to print.
2. Click the File tab.
3. Select Print.
or
1. Click Print Preview. The results will appear as in( Figure 3.13)
Closing a Query
You close a query using the close
button (the X) in the upper-right corner of the Query Design tab. How Access
responds depends on the following three conditions.
• Whether you previously named and saved the query
• Whether you made changes to the layout of the query while
you were in Datasheet view
Reports
Report can have the
following sections:
1.
Report Header section
2.
Report Footer section
3.
Page Headers section
4.
Page Footers section
5.
detail
Creating Reports
1.
Click the Create tab
2.
Select
the Reports group.
3. Click
the Report wizard command.
4. Select the Tables/Queries drop-down list box,
5.
Select the fields you want to include from that table.
6. Select each additional table or query you want to include on
the report, selecting the fields you need from each Click Next.
7.
select you how you want to view the data. You can accept Access’s suggestion or you
can choose from any of the available options, Click Next.
8. Select you want to add any grouping levels. Click Next.
9.
select how you want the records in the report’s Detail section
to be sorted
10. Click the Summary Options button to specify the summary
calculations.
11. Select report layout
12. type the report title
13. Click Finished button
Create label report
1.
Click On the Create tab,
2. Select in the Reports group,
3. click Labels button.
4. Select the
manufacturer and product number of the labels:
a. Choose the manufacturer in the Filter by
manufacturer box.
b. Select your label in the What label size would you
like? box.
c. Select the Dimensions and Number across
columns match your labels.
5. Select Sheet feed or Continuous under
Label Type and then click Next.
6.
Select the font name, size, weight and text color you
want on your label, and click Next.
7. Select the fields you want on your labels from the Available
fields list, and double-click them to add to the Prototype label
box. Click next
8. Select Sort field
9. Type report name
10.
Click finish button
To Create a Report: wizard
1. Open
the table or query you would like to use in your report.
2. Click the Create tab
3. Select
the Reports group.
4. Click
the Report command.
5. Access
will create a new report based on your object.
6.
It's
likely that some of your data will be located on the other side of the page
break. To fix this, resize your fields. Simply select a field, then click
and drag its edge until the field is the desired size. Repeat
with additional fields until all of your fields fit.
7. To save
your report, click the Save command on the Quick Access Toolbar.
When prompted, type a name for your report and then click OK
Creating Reports
1. Open
the table or query you would like to use in your report.
2. Click the Create tab
3. Select
the Reports group.
4. Click
the Report Design command.
5. Go to report
design tools
6.
Select
tools group
7.
Click
add existing fields
8.
click
the plus sign (+) next to the table or tables that contain the fields
that you want to see on the report .
9.
To
add a field to the Report, double-click it or drag it onto the report
How to apply them
for Report
1. Go to Report
design view
2. Click Report tools
3. Select design
4. Select themes
group
5. Select you want to
them
How to Change
them-color for Report
1. Go to Report
design view
2. Click Report tools
3. Select design
4. Select themes
group
5. Click you want to Color group
How
to Change them font for Report
1.
Go to Report design
view
2. Click Report tools
3. Select design
4. Select themes
group
5. Click you want to Font
How to apply field
list
1. Go to Report
design view
2. Click Report tools
3. Select design
4.
Select
tools group
5.
Click
add existing fields
6.
click
the plus sign (+) next to the table or tables that contain the fields
that you want to see on the Report.
7.
To
add a field to the Report, double-click it or drag it onto the Report.
Deleting Fields
You
might find that your report contains some fields you don't really need to view.
For instance, our report contains the Zip Code field, which isn't really
necessary in a list of orders. Fortunately, you can delete fields in
reports without affecting the table or query you got your data from.
To Delete a Field in a
Report:
- Click any cell in the
field you would like to delete.
When
you delete a field, be sure to delete its header as well. Simply select the
header and press the delete key.
Deleting
a field header after deleting the related field
Printing and Saving Reports in Print Preview
To Print a Report:
- Click view group
- Select Print
Preview Your report will be shown as it will appear on the printed
page.
- If necessary, modify
the page size, margin width, and page orientation
using the related commands on the Ribbon.
- Click the Print
command.
- The Print
dialog box will appear. Set any desired print options, then click OK.
To Export a Report:
1.
On the
Home tab of the Ribbon, click the View command and select Print
Preview from the drop-down list.
2. Locate
the Data group on the Ribbon.
3. Select
one of the file type options on the Ribbon,
4. Click More
to see options to save your report as a Word or HTML file.
5. Save
your file.
a. If
you're saving the report as a PDF or XPS file:
6. Select
the location where you wish to save the report.
7. Place
your cursor in the file name text box and type a name for your report if
you wish to name it something other than the report title.
Create
Macros
1. Click create
tab
2. Select
macros & code
3. Click macros
buttons
4. Select
Action using Action Catalog
5. Select
Action Argument
6. Save macros
using Save button
7. Select Macro
object Click Right mouse button
8. Click run
Apply Macro for command Button
1. Go to form design
view
2. Draw command
button
3. Go to
property Sheet
4. Click event
Tab
5. Select you
want to event
6. Click arrow
head
7. Select macro
name
Modifying
an Existing Macro
1. Select the Macros group on the Navigation Pane.
2.
Select the macro you want to modify.
3.
Right-click and select Design View.
Deleting Macro Actions
Follow
these steps to delete a macro action:
1.
Select you want to delete macro .
2.
Right-click and select
Design View
3.
Click delete
Creating an Embedded Macro
1. In Design view, click to select the object to which you want
to associate the macro (for example, a command button).
2. Open the Property Sheet.
3. Click the Event tab of the Property Sheet.
4. Click within the event to which you want to associate the
embedded macro,
5. Click event is selected.
6. Click the build button (the ellipse). The Choose Builder
dialog box appears
7. Select Macro Builder and click OK. A Macro Design window
appears
8. Click, indicating
that the macro is associated with the On Click event of cmdSayHello.
9. Enter the macro commands as you would for any macro
10. Close the Macro Design window. Access prompts you to save
changes to the macro and update the property
11. Click Yes to save your changes and close the dialog box. You
have now created the embedded macro.
Exporting
to an Excel Spreadsheet
You
might want to export table data or query results to an Excel spreadsheet so
that you can use Excel’s analytical features. You can accomplish this in many
ways. You can export an object by right-clicking it, you can export an object
using drag and drop, or you can export it using the External Data tab of the
Ribbon.
Exporting
to an Excel Spreadsheet Using the Context-Sensitive Menu
To export table data or query results
to an Excel spreadsheet, follow these steps:
2. Select Excel from the menu. The Export – Excel
Spreadsheet dialog appears
|
Figure 6.4
|
4. Specify the desired export options
5. Click OK to complete the process.
Exporting to an Excel Spreadsheet Using Drag and Drop
You can export a table or
query to Microsoft Excel by dragging and dropping it directly onto an Excel
spreadsheet. This whiz-bang technology makes the integration between these two
powerful products virtually seamless. Here are the steps involved:
1.
Arrange the Access and Excel application windows so that both are visible.
2.
Drag the object (that is, the table or query) from the Access Navigation Pane
onto the Excel spreadsheet. The results of dragging and dropping the Customers
table from the Access Navigation Pane to Microsoft Excel appear in Figure 7.1
Exporting
to an Excel Spreadsheet by Using the External Data Tab of the Ribbon
You
can use the External Data tab of the Ribbon to export an Access table or query
to Microsoft Excel, as follows:
1. Select the object you
want to export.
2. Select Excel from the
Export group on the External Data tab of the Ribbon. The Export – Excel
Spreadsheet dialog appears.
Import Excel Database
1. Click external data
2. Select import & link
3. Click excel button
4. Select file name using brows button
5. Select import sourse data into a new table in the current
database
6. Click ok The Link
Spreadsheet Wizard appears.
7.
Select Show Worksheets or Show Named Ranges, as appropriate,
and then click Next.
8. Click to select First Row Contains Column Headings, if
appropriate.
9. Click Next.
10. Type a name for the linked table.
11. Click Finish, and then click OK. An icon associated with the
linked table appears
Create a new table
by importing or linking to external data
- Click On the External Data
tab,
- Select in the Import group,
- click one of the available data
sources.
- Follow the instructions in the
dialog boxes that appear at each step.
5.
Access creates the
new table and displays it in the Navigation Pane.
Backup a database
When
you backup a database, Access saves and closes objects that are open in Design
view and saves a copy of the database file by using a name and location that
you specify.
1.
Open the database
for which you want to create a backup copy.
2.
Click the File
tab.
3.
Click Share.
4.
In the Save
Database As area, under Advanced click Back Up Database.
5.
In the Save As
dialog box, in the File Name box, review the name for your database
backup.
You
can change the name if you want, but the default name captures both the name of
the original database file and the date that you make the backup.
Tip When you restore data or
objects from a backup, you want to know which database the backup came from and
when the backup was created. Therefore, it is usually a good idea to use the
default file name.
6. Select which file type you would like
the backup database to be saved as from the Save As type list, and then
click Save.
SQL- Structured Query Language
CREATE
TABLE
The
SQL CREATE TABLE statement is used to create a new table.
Syntax:
Basic
syntax of CREATE TABLE statement is as follows:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
Example:
CREATE TABLE CUSTOMERS(
ID
INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE
INT NOT NULL,
ADDRESS
CHAR (25) ,
SALARY
DECIMAL (18, 2),
PRIMARY KEY (ID)
);
The SQL DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.
Syntax:
Basic syntax of DROP TABLE statement is as
follows:DROP TABLE table_name;INSERT INTO
The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
Syntax:
There
are two basic syntaxes of INSERT INTO statement as follows:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
Example:
INSERT INTO
CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
SELECT
SQL SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets.
Syntax:
The basic syntax of SELECT statement is as
follows:SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
Example:
Following is an example, which would fetch
ID, Name and Salary fields of the customers available in CUSTOMERS table:SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS;
WHERE
he SQL WHERE clause is used to specify
a condition while fetching the data from single table or joining with multiple
tables.
Syntax:
The basic syntax of SELECT statement with
WHERE clause is as follows:SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
Example:
Following is an example which would fetch ID,
Name and Salary fields from the CUSTOMERS table where salary is greater than
2000:SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000;
The AND
Operator:
The AND operator allows the existence
of multiple conditions in an SQL statement's WHERE clause.
Syntax:
The basic syntax of AND operator with WHERE
clause is as follows:SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN]
Example:
SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000 AND age < 25;This would produce the following result:
The OR Operator:
The OR operator is used to combine multiple
conditions in an SQL statement's WHERE clause.
Syntax:
The basic syntax of OR operator with WHERE
clause is as follows:SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000 OR age < 25;
UPDATE
SQL UPDATE
Query is used to modify the existing records in a table.
You can use WHERE clause with UPDATE query to
update selected rows otherwise all the rows would be affected.
Syntax:
The basic
syntax of UPDATE query with WHERE clause is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];You can combine N number of conditions using AND or OR operators.
Example:
UPDATE CUSTOMERS
SET ADDRESS = 'Pune'
WHERE ID = 6;
UPDATE CUSTOMERS
SET ADDRESS = 'Pune', SALARY = 1000.00;
DELETE
The SQL DELETE
Query is used to delete the existing records from a table.
You can use WHERE clause with DELETE query to
delete selected rows, otherwise all the records would be deleted.
Syntax:
The basic
syntax of DELETE query with WHERE clause is as follows:
DELETE FROM table_name
WHERE [condition];You can combine N number of conditions using AND or OR operators.
Example:
Following is an example, which would DELETE a
customer, whose ID is 6:SQL> DELETE FROM CUSTOMERS
WHERE ID = 6;The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:
·
The percent
sign (%)
·
The
underscore (_)
The percent sign represents zero, one, or multiple
characters. The underscore represents a single number or character. The symbols
can be used in combinations.
Syntax:
The basic syntax of % and _ is as follows:SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value
ORDER BY
The SQL ORDER
BY clause is used to sort the data in ascending or descending order, based
on one or more columns. Some database sorts query results in ascending order by
default.
Syntax:
The basic syntax of ORDER BY clause is as
follows:SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;
No comments:
Post a Comment