Access Database Queries
Access Queries Process Data
If you have been following the previous lessons in this tutorial, you will have created a table with a number of records. In this lesson we are going to create an Access Database QUERY, which will process the data from the screenshot below, which you have entered into your table.
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.
Creating an Access Database Query
- Open the QUERY DESIGN GRID
Click the QUERY DESIGN icon located in the QUERIES group of the CREATE ribbon. This brings up the QUERY DESIGN GRID.The QUERY DESIGN
- Select the database TABLE for use in the QUERY
The next stage is to select which table we are going to use in our query. When the QUERY DESIGN GRID opened, the SHOW TABLE dialog box should have opened with it. If necessary you can open this window manually by clicking the SHOW TABLE icon in the QUERY SET UP group of the DESIGN Ribbon.The SHOW TABLE dialog box.
So far there is only one table in your database - "tblEmployees". This should be highlighted when the SHOW TABLE dialog opens, but if not, just click on it to select. Next click ADD. When you close the dialog by clicking X, you should see a box entitled "tblEmployees" above the query design grid.
- Select fields from the tblEmployees table box
In the tblEmployees box, you will see a list of its field names. We are going to use all the fields for our query, so select each one individually by double clicking on their names (alternatively you can drag the field names down onto the grid). You will now see the field names at the top of each column in the QUERY DESIGN GRID.The QUERY DESIGN GRID with tblEmployees fields selected.
- Enter query CRITERIA
As you may remember, the purpose of our query is to extract records where the persons age 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.The QUERY CRITERIA to extract records where a person's age is
greater than or equal to 35.
- SAVE and RUN Query
Click the SAVE icon above the Access Ribbon. When prompted for the query name, enter "qryEmployeesAge". To run your query, click the RUN icon in the RESULTS group of the DESIGN Ribbon.The RUN query icon.
You should now be presented with a datasheet displaying the query results as in the screenshot below.Employees Age Query - employees with ages 35 and over.
As you can see, out of the original list of sixteen, Access has filtered out the six records where employees have ages of 35 and over.
You have now created and run your first query. The next lesson in the tutorial is about Access Database Reports.