Queries

Video Summary

Covered in this section:

1. Queries Using a Filter
2. Queries Using an Advanced Filter
3. Creating Complex Queries
4. Saving a Filter as a Query
5. Query Quick Reference
6. More Examples

Queries using a Filter

A Query enables you to find records that match multiple conditions. With sophisticated queries, you can specify conditions to find text, numbers, or dates that fall within certain ranges.

To specify a query, select the field that you would like to filter then click on the filter button at the top of the screen.

This will bring up a menu at the top of the field that you would like to filter.

Highlight the Text Filters to bring up a list of filters that can be used on this field.

Example 1: Equals...

Select one and it will bring up a small popup menu. For my example I will select Equals. Now we are going to search for "Nathan Springer"

This will give us the following results.

Click on the Toggle Filter button to show all your records again.

Example 2: Greater Than...

Lets try another more real world example. Say you want to see all the students who have a test grade over 75%, you would select the Field "Test Grade", Then select the filter button and then select the Number filters and select Greater Than

When you select Greater Than you will get this pop up screen that you can put in your 75

Once you have put in your 75 select OK.

You will see all the students that have 75 or greater for a Test Grade.

To see how many records are in your query, at the bottom left of the query window there is a count. It looks like this:

To again view all of the records in the table, click on the Toggle Filter button to remove the filter.

Note: if no records fit the filter criteria, No records will show up.

Exercise:

Enter a query for "Name" field being "equal to" "Insert a Name in your database here" for instance "Abbie Good".
You should see just that three records displayed. The rest of your database is still there, but it is just not shown.
Remove the filter.

Queries using an Advanced Filter

To specify an advanced query, go to the toolbar at the top of the screen. In the box that is labelled Sort & Filter click on Advanced then click on the Advanced Filter/Sort... button to get the query page.

The queries page should look something like this:

To specify a Query you need to let Access know what you are interested in querying. You have done this once before in the Queries using a Filter but say you want to see all the students who have a test grade over 75%, you would change the Field to "Test Grade", then enter ">=75" in the Criteria cell under "Test Grade"

Click on the Toggle Filter icon to apply the Query. You will see all the students that have 75 or greater for a Test Grade.

To see how many records are in your query, at the bottom left of the query window there is a count. It looks like this:

To again view all of the records in the table, click on the Toggle Filter button to remove the filter.

Note: if no records fit the filter criteria, No records will show up.

Exercise:

Enter a query for "Name" field being "equal to" "Insert a Name in your database here" for instance "Abbie Good".
You should see just that one record displayed. The rest of your database is still there, but it is just not shown.
Remove the filter.

Creating Complex Queries

You can create more complex queries by using applying criteria to multiple fields.

For example, say you want to see all the students who passed cs 100 you would set up the query as follows: 

When you apply the query, you will see the CS100 students who have passed. Only those records matching the two criteria, that is, a CS100 student, and a student who passed, are displayed.

To create an AND or an OR statement start a new filter by following the same steps as above. To create an AND statement type AND between the entries, like this:

An OR statement is created like this:

Exercise:

Using Advanced Filter/Sort, implement a query where:

• Class Name is equal to "cs 100" AND Test Mark is greater than 75 but less than 90

Using Advanced Filter/Sort, implement a query where:

• Class Name is equal to "cs 100" OR Class Name is equal to "bio 100"

Saving a Filter as a Query

If you would like to save a filter as a query so that you can use it later all you have to do is have your filter open, click Advanced and then Save As Query (just like the screenshots below).

You can name your Query anything you would like, but think of the content when saving your query. In this example the filter retrieves all students who are in "cs 100" or "biol 200" and have a Test Grade which is greater than or equal to 80. Since this filter shows students that have a grade of 80 or higher we might call our query HonourRoll.

After you've named your query and clicked OK you should see your query's name on the left side of your screen. If you double-click that name a new tab will appear and display the results of your query.

Exercise:

Using Advanced Filter/Sort, create and save a query where:

• Class Name is either "enel 240" OR "arth 100"
• Test Grade is greater than or equal to 80

Query/Filter Quick Reference

General Filter:

Text Filter:

Number Filter:

ATTENTION: General Filter criteria will work for ALL Data Types. Also, be mindful of quotations (" ") and wildcards (*) if you are using the Advanced Filter/Sort method. Credit goes to http://www.gcflearnfree.org/access2010 for these handy quick reference tables.

For more examples see Microsoft Office's official site:

• Text, Memo, and Hyperlink fields
• Number, Currency, and AutoNumber fields
• Date/Time fields

More Examples

Example 1: Contains...

In this example we will filter the Student # field to only show student numbers that contain either "111" or "222". By consulting the reference table above we can see that the general way to use Contains is:

    Like "*x*"

In this case our x would be "111" or "222". Since we want to find student numbers that contain either "111" OR "222" we need to use an Or statement to link together two "Contains" statements. Doing so would yield something that looks like this:

    Like "*x*" Or Like "*x*"

Now all we have to do is replace our x's with "111" and "222" for our statement to be complete:

    Like "*111*" Or Like "*222*"

Here is a screenshot of what this would look like from your Advanced Filter/Sort menu:

And here are the results after clicking Toggle Filter:

Example 2: Begins with...

In this example we will filter the Name field to only show names that begin with either "Cl" or "Ja". By consulting the reference table above we can see that the general way to use Begins with is:

    Like "x*"

In this case our x would be "Cl" or "Ja". Since we want to find names that begin with either "Cl" OR "Ja" we need to use an Or statement to link together two "Begins with" statements. Doing so would yield something that looks like this:

    Like "x*" Or Like "x*"

Now all we have to do is replace our x's with "Cl" and "Ja" for our statement to be complete:

    Like "Cl*" Or Like "Ja*"

Here is a screenshot of what this would look like from your Advanced Filter/Sort menu:

And here are the results after clicking Toggle Filter:

Example 3: Ends with...

In this example we will filter the Name field to only show names that end with either "on" or "er". By consulting the reference table above we can see that the general way to use Ends with is:

    Like "*x"

In this case our x would be "on" or "er". Since we want to find names that contain either "on" OR "er" we need to use an Or statement to link together two "Ends with" statements. Doing so would yield something that looks like this:

    Like "*x" Or Like "*x"

Now all we have to do is replace our x's with "on" and "er" for our statement to be complete:

    Like "*on" Or Like "*er"

Here is a screenshot of what this would look like from your Advanced Filter/Sort menu:

And here are the results after clicking Toggle Filter: