MS Access Query Data

MS Access - Query
Data
QUERY
A query is a request for data results, and
for action on data.
You can use a query to answer a simple
question, to perform calculations, to
combine data from different tables, or
even to add, change, or delete table data.
•As tables grow in size they can have hundreds of
thousands of records, which makes it impossible for
the user to pick out specific records from that table.
•With a query you can apply a filter to the table's
data, so that you only get the information that you
want.
•Queries that you use to retrieve data from a table
or to make calculations are called select queries.
•Queries that add, change, or delete data are called
action queries.
•You can also use a query to supply data for a form or
report.
•In a well-designed database, the data that you want to
present by using a form or report is often located in
several different tables.
•The tricky part of queries is that you must understand
how to construct one before you can actually use them.
Create Select Query
If you want to review data from only
certain fields in a table, or review data
from multiple tables simultaneously
or maybe just see the databased on
certain criteria, you can use the Select
query.
Let us now look into a simple example
in which we will create a simple query
which will retrieve information from
tblEmployees table. Open the
database and click on the Create tab.
Click Query Design.
In the Tables tab, on the Show Table
dialog, double-click the tblEmployees
table and then Close the dialog box.
In the tblEmployees table, double-
click all those fields which you want to
see as result of the query. Add these
fields to the query design grid as
shown in the following screenshot.
Now click Run on
the Design tab, then
click Run.
MS Access - Query Criteria
Query criteria helps you to retrieve specific items from an Access database. If
an item matches with all the criteria you enter, it appears in the query
results. When you want to limit the results of a query based on the values in
a field, you use query criteria.
•A query criterion is an expression that Access compares to query field values
to determine whether to include the record that contains each value.
•Some criteria are simple, and use basic operators and constants. Others are
complex, and use functions, special operators, and include field references.
•To add some criteria to a query, you must open the query in the Design View.
•You then identify the fields for which you want to specify criteria.
Example
Let’s look at a simple example in which we will use
criteria in a query. First open your Access database and
then go to the Create tab and click on Query Design.
In the Tables tab on Show Table dialog, double-click on
the tblEmployees table and then close the dialog box.
Let us now add some field to the query grid such as
EmployeeID, FirstName, LastName, JobTitle and Email
as shown in the following screenshot.
Let us now run your query and you will see only these
fields as query result.
If you need to use the functionality of

the AND operator, then you have to specify the other
condition in the Criteria row. Let us say we want to
retrieve all Accounting Assistants but only those
Marketing Coordinator titles with “Pollard” as last name.
If you want to add criteria for
multiple fields, just add the criteria
in multiple fields. Let us say we want
to retrieve data only for “Marketing
Coordinator” and “Accounting
Assistant”; we can specify the OR
row operator as shown in the
following screenshot
Let us now run your query again and
you will see the following results.
If you need to use the
functionality of
the AND operator, then you have
to specify the other condition in
the Criteria row. Let us say we
want to retrieve all Accounting
Assistants but only those
Marketing Coordinator titles with
“Pollard” as last name.
Let us now run your query again

and you will see the following
results.
MS Access - Action Queries
In MS Access and other DBMS systems, queries can do a lot more than just displaying
data, but they can actually perform various actions on the data in your database.
•Action queries are queries that can add, change, or delete multiple records at one
time.
•The added benefit is that you can preview the query results in Access before you run
it.
•Microsoft Access provides 4 different types of Action Queries −
• Append
• Update
• Delete
• Make-table
An action query cannot be undone. You should consider making a backup of any
tables that you will update by using an update query.
Create an Append Query
You can use an Append Query to
retrieve data from one or more
tables and add that data to
another table. Let us create a new
table in which we will add data
from the tblEmployees table.
This will be temporary table for
demo purpose.
Let us call it TempEmployees and
this contains the fields as shown in
the following screenshot.
In the Tables tab, on the Show
Table dialog box, double-click
on the tblEmployees table
and then close the dialog box.
Double-click on the field you
want to be displayed.
Let us run your query to
display the data first.
Now let us go back to Query design
and select the Append button.
In the Query Type, select the Append
option button. This will display the
following dialog box.
Select the table name from
the drop-down list and click
Ok.
In the Query grid, you can see
that in the Append To row all
the field are selected by
default except Address1.
This because that Address1
field is not available in
the TempEmployee table.
So, we need to select the field
from the drop-down list.
Let us look into the Address
field.
Let us now run your query and

you will see the following
confirmation message.
Click Yes to confirm your
action.
Create an Update Query
You can use an Update Query to
change the data in your tables,
and you can use an update query
to enter criteria to specify which
rows should be updated. An
update query provides you an
opportunity to review the updated
data before you perform the
update. Let us go to the Create
tab again and click Query Design
Table dialog box, double-click on
the tblEmployees table and then
close the dialog box.
On the Design tab, in the
Query Type group, click
Update and double-click on
the field in which you want
to update the value. Let us
say we want to update the
FirstName of “Rex” to
“Max”.
In the Update row of the Design
grid, enter the updated value and
in Criteria row add the original
value which you want to be
updated and run the query. This
will display the confirmation
message.
Create a
Delete Query
You can use a delete query to delete data from
your tables, and you can use a delete query to
enter criteria to specify which rows should be
deleted. A Delete Query provides you an
opportunity to review the rows that will be deleted
before you perform the deletion. Let us go to the
Create tab again and click Query Design.
In the Tables tab on the Show Table
dialog box, double-click
the tblEmployees table and then close
the dialog box.
On the Design tab, in the Query
Type group, click Delete and double-
click on the EmployeeID.
Click Yes and go to your Datasheet View
and you will see that the specified
employee record is deleted now.
Create a Make Table Query
You can use a make-table
query to create a new table
from data that is stored in
other tables. Let us go to
the Create tab again and
click Query Design.
Table dialog box, double-click
the tblEmployees table and
then close the dialog box.
Select all those fields which
you want to copy to another
table.
In the Query Type, select
the Make Table option
button.
You will see the following
dialog box. Enter the
name of the new table
you want to create and
click OK.
Now run your query.
You will now see the following
message.
Click Yes and you will see a new table

created in the navigation pane.
MS Access - Parameter Queries
The best part about queries is that you can save and run
the same query again and again, but when you run the
same query again and again by only changing the
criteria then you might consider the query to accept
parameters.
•If you frequently want to run variations of a particular
query, consider using a parameter query
•Parameter query retrieves information in an interactive
manner prompting the end user to supply criteria before
the query is run.
You can also specify what type of data a parameter should accept.
You can set the data type for any parameter, but it is especially
important to set the data type for numeric, currency, or date/time
data.
When you specify the data type that a parameter should accept,
users see a more helpful error message if they enter the wrong type
of data, such as entering text when currency is expected.
If a parameter is set to accept text data, any input is interpreted as
text, and no error message is displayed.
Example
Let us now take a look at a simple example
by creating a parameter query. Let us open
your database and select Query Design in
the Create table tab.
Double-click on
the tblProjects and close
the Show dialog box.
Select the field you want to see as
a query result as shown in the
following screenshot.
In the query design grid, in the Criteria row
of the ProjectStart column, type [Enter a
project start data]. The string [Enter a
project start data] is your parameter
prompt. The square brackets indicate that
you want the query to ask for input, and
the text is Enter a project start data is the
parameter prompt displays.
Thank You

MS Access Query Data

Uploaded by

Copyright:

Available Formats

MS Access Query Data

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MS Access Query Data

Uploaded by

Copyright:

Available Formats

MS Access - Query

If you need to use the functionality of

Let us now run your query again

Let us now run your query and

Click Yes and you will see a new table

You might also like