Access07:Heights:Queries

A query is a fundamental part of any MS-Access form or report. Basically a query defines what data the form or report is allowed to show.

A query is actually a Structured Query Language (SQL) statement which is applied to a table (or tables). As such, Access displays a ghost table, or what is called a dynaset, that shows the data selected.

In this example we will create a simple query, using MS-Access’s Query-by-example (QBE) facility, that displays the heights of people above 160 cm.

Creating a Query to display people taller than 160cm
1. Withthe Table tblPeople selected, select the Create tab and choose Query Wizard



2. Select "Simple Query Wizard"

3. Table:tblPeople should be the table selected. Transfer all the Available fields to the selected fields using the [>>] button. Then click [Next]

4. Choose Detail query, then click [Next]

5. Change the title to qryPeople160

the query results are displayed.. but wait they are all showing... we need to now add a filter to only show the people with a height greater than 160cm

Looking at the SQL of the Query
A query is actually an SQL Statement which is applied to the database. To see the SQL, click on the Home tab, (make sure the query is selected) then choose View and change to SQL view.



Have a look at the SQL.. can you make sense of what is is saying??

SELECT tblPeople.[PeoID], tblPeople.[PeoFullName], tblPeople.[PeoHeight] FROM tblPeople;

Its actually pretty obvious (in this case)

Modifying a query
Change the View of the Query to design view. Access now changes to what is known as the Query by example (QBE) view.

This is a very powerful feature and is availabe for all forms and reports as well.

To add the condition that we only want to see people taller than 160 cm enter >160 into the Criteria row in the PeoHeight column.



Change the view to datasheet and you will see (hopefully) only people with heights greater than 160cm

Finally save the query.