MS Access - Query Data
Previous
Quiz
Next
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.
The query runs, and displays only data in those field which is specified in the
query.
MS Access - Query Criteria
Previous
Quiz
Next
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
Lets 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 want to see only those whose JobTitle are Marketing Coordinator then
you will need to add the criteria for that. Lets go to the Query Design again
and in Criteria row of JobTitle enter Marketing Coordinator.
Let us now run your query again and you will see that only Job title of
Marketing Coordinators are retrieved.
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
Previous
Quiz
Next
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 −
o Append
o Update
o Delete
o 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.
When you open the TempEmployee table, you will see all the data is added
from the tblEmployees to the TempEmployee table.
MS Access - Create Queries
Previous
Quiz
Next
Let us understand how to create queries in this chapter.
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.
In the Tables tab, on the Show 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.
Click Yes and go to Datasheet View and you will see the first record
FirstName is updated to Max now.
Advertisement
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.
In the Criteria row of the Design Grid, type 11. Here we want to delete an
employee whose EmployeeID is 11.
Let us now run the query. This query will display the confirmation message.
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.
In the Tables tab, on the Show 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
Previous
Quiz
Next
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.
Let us now run your query and you will see the following prompt.
Let us now enter the following date.
Click OK to confirm.
As a result, you will see the details of the project which started on 2/1/2007.
Let us go to the Design View and run the query again.
Enter the date as in the above screenshot and click Ok. You will now see the
details of the project which started on 5/1/2008.