Setting Criteria

In This Site

In This Section

The number one source for making your website sell!

Setting Criteria in Your Microsoft Access Queries

So far, you've learned how to create queries that take advantage of multiple tables. The real power of queries becomes evident when you begin to add criteria to the tables so that you refine your views very precisely.

In our first lesson, Getting Started with Queries, we touched on this topic when you typed London OR Seattle into the Criteria field for City. This limited the display to ONLY those individuals based in London OR Seattle.

This represents the use of criteria in the very simplest form. The criteria you enter can help you to filter huge databases to pull out very unique records.

There are several ways to set criteria in the query design window. The two main areas are through the Criteria field or though the Totals field. You can also set criteria using calculated fields. We'll look at this in our next lesson.

Limiting Records Using the Criteria Field

In the query design window, you will see a Criteria field beneath every field value. You can create either simple or very complex criteria. Here are some examples:

Sample Criteria Statements
Exact Matches	=100	Finds values that equal 100
	=01/09/1997	Finds the date Jan. 9, 1997
	=London	Finds London
Less Than	<100	Finds values ranging from 1 to 99
	<01/09/97	Finds records taking place prior to Jan. 9, 1997
	<N	Finds records starting with the letters A-M
Less Than or Equal to	<=100	Finds records ranging from 1 to 100
	<=01/09/97	Finds records taking place prior to and including Jan. 9, 1997
	<=N	Finds the letter N and records starting with the letters A-M
Greater Than	>100	Finds records greater than 100
	>01/09/97	Finds records that took place after Jan. 9, 1997
	>N	Finds records beginning with the letters N-Z
Greater Than or Equal to	>=100	Finds records greater than or equal to 100
	>=01/09/97	Finds records that took place on Jan. 9, 1997 or later
	>=N	Finds the letter N and records beginning with the letters N-Z
Not Equal To	<>100	Finds records that do not equal 100
	<>01/09/97	Finds records that did not take place on Jan. 9, 1997
	<>London	Finds records that do not equal London
Find List of Items	In ( 100,105,110 )	Finds values that equal 100, 105 or 110
	In ( #01/09/97# , #01/10/97# )	Finds records taking place on Jan. 9, 1997 or Jan. 10, 1997. Dates must be contained within the number symbol (#)
	In ( London,Seattle,Madrid )	Finds records that equal London, Seattle or Madrid)
Between...And	Between 100 And 150	Finds values that range from 100 to 150 inclusive
	Between 01/09/97 And 01/09/98	Finds records taking place between Jan. 9, 1997 and Jan. 9, 1998 inclusive
	Between A and E	Finds records equal to the letter E and records that start with the letters A to D.
Wildcards	When specifying wildcards, use the * symbol to represent one or more characters and the ? symbol to indicate a single wildcard character
	Like *	Finds all records of any length
	Like Jim*	Finds any words starting with Jim, such as Jim, Jimbo, Jimmy, etc.
	Like *th	Finds any words ending with the letters "th", such as Fifth, Ninth, Tenth, etc.
	Like Min?	Finds any four letter words beginning with the letters "Min", such as Mind, Mini, Mine, etc.
Empty Fields	Is Null	Finds fields that do not contain any data
Empty Fields	Is Not Null	Finds fields that DO contain data

Using the Totals Field

In your query design window, you should see a line labeled Totals between the Table and Sort row on the left of the Query Design screen. If you do not, then select View-Show Totals and it will make that row visible.

Open the Northwind database and click on the queries tab. Select New and Design View. add the Customers, Orders, Order Details and Products tables to the query window.

From the Orders table, drag the ShipCountry Field into the first column of the query design window. Next, drag the ProductName field from the Products table and finally, Quantity from the Order Details table. Your screen should look like the following:

You will notice that the Totals field automatically shows the term Group By. This means that it will show all records, essentially doing nothing. If you click within the Totals field, you will see a drop-down arrow appear. Click on the arrow and a variety of options are presented to you. You can see them in the following image:

The options include:

Group By	The default. The query will group the data by this field.
Sum	Show a total for all of the values
Avg	Show the average for the value
Min	Show the minimum value
Max	Show the maximum value
Count	Count the number of records
StDev	Show the standard deviation
Var	Show the statistical variance
First	Show the value of the first record
Last	Show the value of the last record
Expression	Use this if you are creating a calculating field where you create a new expression by combining other field values
Where	Select Where in the Group by field and enter an expression in the Criteria field to limit the group data BEFORE it performs any aggregate functions such as sum, max or min.

Let's select Sum in the Quantity field and switch to the preview window. This will change the name of the Quantity field to SumOfQuantity and will show the total amount of units ordered for each product within each country. It will look like the following:

In this case, a total of 20 units of Boston Crab Meat was shipped to Argentina. 20 units of Chef Anton's Gumbo Mix, 2 of Côte de Blaye, etc.

If you change the Total field to Count, it will show you how many orders were placed. If you select Max, you'll see the maximum amount ordered at one time.

Save your query as qGroupBy.

In our next lesson, you'll learn how to perform calculations in your queries.

These tutorials are part of an upcoming training course called "FrontPage Magic - How To Create A Database Driven Website For Non-Programmers". Stay tuned for more details on this exciting new product.