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.

Access Table Data
The table data we are going to query.

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


    Click the QUERY DESIGN icon located in the QUERIES group of the CREATE ribbon. This brings up the QUERY DESIGN GRID.

    Query Design Icon

  2. 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.

    Show Table Dialog Box
    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.

  3. 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.

    Query Design Grid
    The QUERY DESIGN GRID with tblEmployees fields selected.

  4. 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.

    Query Criteria
    The QUERY CRITERIA to extract records where a person's age is
    greater than or equal to 35.

  5. 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.

    Run Query Icon
    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 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.