Queries are a powerful
way to find multiple records which meet one condition or multiple
conditions. You can set up the query to list the found records
in a certain order and to not print certain fields even if they
have been used to search for the records. To create a query you need to click on the Queries section of the database window and click the New button to create a new query.
We are going to use the Design View
to create our query but you can use the Wizard to do the same job
and follow the instructions which lead you through the process.
Make sure Design
View is selected from the New Query window as below and then click
the OK button to start designing your query.
You now need to select the source of
data for your query from the window which is shown below, in our
case the Employees table. Click the appropriate table in the window which appears
and then click the Add button to add it to the area at the top of
the query. It
is also possible to run queries on the results of queries and you
could choose an existing query from the Queries section of the window
below if necessary.
You should now be able to see a window
as below with a box containing the fields in your table at the top
of window and a datasheet type area at the bottom where you can
enter your criteria. The query below searches for everyone with a salary
greater than £9,000, prints only the First Name, Surname and
Department and sorts by ascending Date of Birth.
Note that the
ticks are omitted from the ‘show’ line for Date of Birth and Salary
as these fields are not to be printed, even though they are necessary
for the design of this query.
If you wanted to see the Structured Query Language
(SQL) which is behind the query you have created you can click on
the View drop-down menu and choose SQL View. The SQL for the query we have created is shown
Employees.[First name], Employees.Surname, Employees.Department
ORDER BY Employees.[Date of Birth];
When you have finished designing your query, click
on the floppy disk Save icon and a small window will appear for
you to enter a meaningful name for your query, this is shown below.
Click on the OK button and you will see the Queries
section of the database window with your new query listed.
To run the query,
click on it and then click the Open button as you would to open
a table or form or just double-click the query name.
You can see the results of the query we have created below.
When you have finished with the results from the query,
simply click the cross in the top right corner of the window to
close it and you will be returned to your database window.
Click here to
return to the Access index.