Access 2007 Query

Access Queries Process Data

If you have been following the previous lessons in this tutorial, you will have created a table with five records. In this lesson we are going to create an Access 2007 QUERY, which will process the data below which you have entered into your table.

ID Number FirstName Surname Age
1 John Jones 35
2 Tracey Smith 25
3 Anne McNeil 30
4 Joe Brown 39
5 Sue Moore 33

What we want Access to do is extract all the records in the above table where the persons age is greater than or equal to 35. Access Queries can do far more than this, of course, but this simple task should serve as a useful learning exercise.

Open the Query Design Grid

The first stage is to select the CREATE TAB and then go to the OTHER group on the far right of the ribbon. Then click on the QUERY DESIGN ICON to bring up the query design grid.

Query design icon under the Create Tab

Select Table for Query

The next stage is to select which table you are going to use in this query. When the query design grid opened, the SHOW TABLE pop up window should also have opened. If necessary you can open this window manually by clicking the SHOW TABLE icon in the QUERY SET UP group on the Access Ribbon.

Query design grid with Show Table pop-up window

So far there is only one table in your database - TABLE1. This should be highlighted when the window opens, but if not, just click on it once. Next click ADD. When you close the pop-up window by clicking x,you should see a box labelled TABLE1 above the query design grid.

Select fields from Table

In the table1 box, you will see a list of its field names. We are going to use all the fields in this query, so select each one individually by double clicking on their names. You will now see the field names at the top of each column in the Query Design Grid.

Query design grid

Enter query criteria

As you may remember, the purpose of our query is to extract records where the persons name is greater than or equal to 35. To do this we enter the criteria into the appropriate cell of the query design grid. In this case you need to go to the AGE column of the CRITERIA row, and enter the formula >=35.

Query Criteria

Save and Run Query

Click the save icon above the Access Ribbon. When prompted for the query name, just use the default QUERY1. To run your query, click the RUN icon in the RESULTS GROUP of the Access Ribbon. You should now be presented with a datasheet displaying your query results - JOHN JONES 35 and JOE BROWN 39.

Query results

You have now created and run your first query. The next lesson in the tutorial is about Access 2007 Reports.