Access Manual 3

Access Presentation 3
Rasagnya Waghray
Ali Murtuza
Create Queries
Using Query Wizard
Using Design view
Wild cards
Using relational operators in criteria
Using compound criteria
Overview (cont)
Omit duplication from query result
 Main query types
Select Query
Update Query
Delete Query
Creating query from wizard
Queries are simply questions , the answer to
which are in database. For creating a query
you must have already defined your tables
and entered data in your tables. The example
database used in this manual is the student
database we created in “Access presentation
For creating query from wizard, click on the
“Create” tab then click on “query wizard”.
Then in the window that pops up, choose
“simple query wizard” option. Click “ok”.
Select the table you want to have query
from and add the fields that you want to
show in your query, click on next and
 If you need to make any changes to the
query, right click on the query in
navigation bar and click on “design view”
Query wizard
Step1: Choose
simple query
Creating query in Design View
Another way to create a query is to click
on the “create “ tab and click on “query
design” button . Most of the time you will
use design view to create queries.
 A “show table” dialog box appears which
shows all the table in the database. Select
the tables that you want to query from and
hit “add” button . After adding all the tables
you want, click on the “close” button to
close the show table dialog box.
Hit close after you
selected your
Add the tables you
want to use for
your query
Design Grid
Creating Query in Design view
When creating queries , you can request for
specific records and accompanying fields to
be shown based on certain criteria.
In Microsoft Access, there are six areas that
can be utilized in creating a query.
Fields: Used to specify which fields should be a
part of the query : for adding a field, you need to
double clicking on the field name in each table.
Table: Used to specify the table that houses the
fields that will be a part of the query.
Sort: Used to specify how the records will be
sorted when the query is run. It has two options:
Ascending and Descending, you can sort the
query result based on the related field.
Shows: Used to indicate whether a field involved
in the query should be shown in the results or
not, it is useful when you want to have a criteria
/condition on a field but you do not want to
show the field in the query result
Criteria: Is very important row and indicates the
condition on the related field
Or: Indicated compound criteria
As an example suppose we want to design a query that
gives us the first name, last name, and GPA of the
students whose majors are “Education”, and we want
the results to be sorted by student’s first name. For
doing that we need to do the following steps:
Double click on the “FN”, “LN”, “GPA” and “MAJ” fields on
the student table to add these fields to the design grid.
In the “sort” row, under the FN field, choose ascending.
Uncheck the “show” checkbox under the MAJ field
In the “criteria “row under MAJ field, type “education”
Save the query and give it a name, e.g. “query1”.
Click on the “run query” button to see the query result.
The “Run query”
Query result
You should switch to
the design view of the
query if you want to
make changes to the
As mentioned before , the criteria is a row where
you specify your conditions on different fields.
• Use comparison criteria: Unless you specify,
access assumes that the criteria you enter involve
equality(exact match). In the last query for
example the criteria was actually “=Education”. If
you want something other than exact match you
must enter appropriate comparison. For example
if you want to change the criteria of the previous
example to see the students whose GPA is
greater than 3.00, you write “>3.00” in the
criteria row, under the GPA field.
Criteria GPA > 3
Using Wildcards for criteria
Wildcards are symbols that represent any
characters or combination of characters. It’s a
pattern that specifies what will be matched.
Wildcards use special characters to specify
the types of information to match. The
common wildcards are :
Asterisk (*), which represents any collection of
characters. For example “El*” represents the
letters EI followed by any collection of characters.
Examples of * are:
Cont.. Of examples
Like “s*” : Matches all the values that start
with “s”.(eg- “Smith”,but not “Davies”)
 Like “*s” : Matches all the values that end
with “s”.(eg- “Davies”, but not “Smith”)
 Like “c*n” : Matches all the values that start
with “c” and end with “n”.(eg- “Cohen”)
 Like “c*a*n” : Matches all the values that
start with “c”, have an “a” somewhere in the
middle and end with an “n”.(eg- “Cohan”)
 Like “c*an” : Matches all the values that start
with “c” and end with “an”.(eg- “Cohan” but
not “Cohain”)
As an example query that uses wildcard,
suppose that we want to see the students
whose last name ends in “n”. As soon as
you write wild cards in criteria row,
access adds “like” keyword automatically.
Last name ends
in “n”
Query result
Cont.. Of Wildcards
 Question
mark(?): which represents
any individual character, for example T?M ,
represents the letter T, followed by any
single character followed by letter M. (
such as TOM or TIM).Examples:
 Like “a??” : Matches “ape”, “aaa” but not
 Like “*a?” : Matches “bread” and “lean”
but not “apple” and not “fella”.
Cont.. Of Wildcards
 Number
Sign(#): which matches a single
numerical digit. Examples are:
• Like “###abc” : (there are three number
matches “123abc”
But not “a123abc”
And not “abc123”.
Compound Criteria
We can have more than one criteria for a query.
There are two types of compound criteria
1. And Criterion: Each individual criterion has to be
true in order for the compound criterion to be true.
To combine criteria with And, we should place them
in the same row ( in criteria row).
2. OR Criterion: At least one of the individual
criterion has to be true in order for the compound
criterion to be true. To combine criteria with OR,
we should place them in separate lines.
Compound criterion Example
As an example of And criterion suppose
the query is to show the First Name, Last
Name, major and GPA of the students
whose Last Name begins with “S” and/or
their GPA is greater than 3.00.
And criteria should
be in the same row
Or criteria should be
in the separate rows
And query result
Or query result
Query Types
Until now we just mentioned the queries
that filter the table, this type of query is
called “select” query. Select query does
not modify the content of table
 There are two other types of query that
modify the content of tables in database.
These queries are:
Update queries
Delete queries
Delete Query
Deletes some records from a table that
satisfy certain criteria.
 For creating a delete query, click on the
“delete button” in query type group. Then
select the field that you want to have
criteria on, and specify your criteria.
 As an example, delete all students whose
Major is “education”. When you run this
query a window pops up and asks you to
confirm deleting 2 records.
Click on Delete
Specify the
Update Query
Updates the records that satisfy certain
For creating a delete query, click on the
“update button” in query type group. Then
select the fields that you want to update,
specify the new values that these field should
be updated to and specify the criteria.
As an example, update the records of all
students whose major are “Education” and
their GPA is greater than 3.0 , change their
major to “edu”
Update query
type button

similar documents