Tables & Queries:
Select Query

Title: Jan's Illustrated Computer Literacy 101


A select query Button: Select Query does just that... it selects records and fields to show. Such a query might just sort records into a particular order or pick out a few of a table's fields for display. But, usually a query includes criteria that the records must match.

Your query can pick out records that match a particular value or that do not match a value or that fit between two values. So many choices!

Criteria Expressions

The criteria that you  enter to pick records are expressions. An expression is like a formula in an spreadsheet. It can be as simple as ="California" or  >57 or it can be quite complex.

Expressions combine values with operators, identifiers, constants, and functions.

  • Operator: Includes normal operators for arithmetic like +, -, *, or / and logical comparisons like =, <, >, <>, Not, Is Null, Is Not Null
  • Identifier: name of a field, control, or property
  • Constant: something that remains fixed while Access is running. True, False, and Null are the most commonly used constants.
  • Function: Access has a large number of built-in functions, like Avg for averaging, Min which finds the smallest value, Max which finds the largest value, and Date which provides the current date.

Expressions are used in:

  • Validation rules
  • Default values
  • Calculated fields, in queries
  • Calculated controls, in forms and reports

We will stick to simple expressions in this lesson. In the next lessons on calculated values you will get a bit more complicated with your query criteria expressions. You will also use expressions in the Forms and Reports lessons.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    Summary
    Quiz
    ExercisesTo subtopics

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



Examples of Query Criteria

For criteria in the query design grid, you do not have to type the equals sign (=). Access assumes that is what you mean unless you type something else. In other situations, you may have to type it in.

Example What it means
=45
="Greenfield"
="12345"
Equal to 45, a Number data type
Exact match to
Greenfield, a Text data type
Exact match to
12345, a Text data type, like a zip code.
[Quotes surround text values but not numbers!]
<45
>119
<>8  
<="T"

Less than 45
Greater than
119
Not equal to
8  
Alphabetically before  the letter
T. Includes T but not Ta.

Between "A" and "G"

Text that is alphabetically between A and G, including A and G, but not Ga.

Between 100 and 1000

Numbers between 100 and 1000, including both the values 100 and 1000.

Not "New York" Matches any value except "New York"
In ("Smith", "Jones", "Wang") Matches any one of  the values in the list
Like "Los Angeles"
Like "L*" 
Like "*ang*"
Matches exact value only.
Matches any value that starts with
L
Matches any value that contains the letters
ang, anywhere in the value. So Los Angeles, angle, language,  and fang would all match.
Is Null Matches when the field is null
Is Not Null Matches when the field has any value at all, including zero-length string like ""

Using Wild Card Characters

The Like operator compares values to a pattern that usually includes wild card characters.  These characters are placeholders, either for 1 character or for an unknown number of characters.

Use the character:

Matches in an expression:

? or _ (underscore) Any single character
* or % Zero or more characters
# Any single digit (0 9)
[listofcharacters] Any single character in listofcharacters
[!listofcharacters] Any single character not in listofcharacters

Examples:

Like "A*" - values that start with the letter A

Like "A????" -values that start with A but have exactly 5 characters

Like "A[d,p]*" - values that start with Ad or Ap

Like "##QR#*" - values that start with 2 digits, then the letters QR, then another digit, followed by any number of other characters. For example, the registration code for a copy of Windows 95 that comes with a new computer has the form #####-OEM-########-#####, where OEM stands for Original Equipment Manufacturer.

Icon: Step-by-Step 

Step-by-Step: Select Query

 Icon: Step-by-Step

What you will learn:

to design a select query manually
to add all fields from a table at once to grid
to clear the design grid
to add fields to design grid
to move columns in a query
to use criteria in a select query
to combine criteria in a select query
to find Null or Not Null values
to use Like operator
to name and save a query

Start with:  Class disk, Project database open.

First you will create and modify a basic Select query. Then you will experiment with different types and combinations of criteria.

Icon: Design Query Design View

  1. Database Window: Staff table selectedWith the Projects database open, in the Database Window, select the Staff table.
     
  2. Button: New Object = QueryClick on the New Objects button and select   Query .
    Dialog: New Query The New Query dialog  appears.
     
  3. Select Design View.
    The Query Design View opens with the Staff table already showing in the top part of the window.  The query grid at the bottom is blank.
     

    Query Design View: Staff table, no fields selected


Include All Fields in Query

You add fields to the query by dragging them from the table at the top and dropping them onto the grid. You can drag more than one at a time.

  1. Animation: Drag asterisk to Design gridMove your  mouse pointer over the * at the top of the list of fields in the Staff table.
     
  2. Drag the * down to the first column and drop.
    The image at the right is an animation. Refresh this window to see it run again.
     
    The * asterisk represents all of the fields in the table.
     
  3. Click the Run button Button: Run to execute this query. You will see all of the fields and all of the records in the Staff table.

    Datasheet View: Query - all fields from Staff table

    Of course this query is not very useful! You might as well have used the table itself.

  4. Icon: Design Switch back to the Query Design View by clicking the View button Button: Views - Design (2003).
     
  5. Menu: Edit | Clear gridFrom the menu select  Edit | Clear Grid .
    The grid is now blank again. This command is especially useful when there too many fields to select them all easily.

    Icon: Keyboard Remove a column: You can also select the columns you don't want and press the Delete key.
     


Add Fields to Query Design Grid

Recall that sorting rearranges the records while using criteria hides some records.

When selecting fields to drag to the design grid, you can use the normal selection methods.

  • Single field: Drag any single field.
  • Adjacent fields: Hold SHIFT down to select all items between the two you click on.
  • Non-adjacent fields: Hold CTRL down to select items individually which are not next to each other.
  1. Single field:
    Click
    on the field EmployeeID and drag it down to the design grid and drop it on the first column.
     
  2. Staff table in query design. FirstName field is selected.Multiple fields:
    1. Click on the field FirstName.
      Scroll, if necessary, until the field LastName shows.
       
    2. Hold the SHIFT key down and click on the field LastName.
      Three fields are selected: FirstName, MiddleName, and LastName.
       
    3. Drag the selection down to the design grid and drop on the second column.
      All three fields appear, in the same order as in the table list.

    Query Design View: 4 fields from Staff table in grid


Sort: Multiple Fields

You will often want to sort several fields in your query. The order of the columns is important. Sorting and criteria are applied starting with the left-most column and moving to the right.

  1. Click in the Sort row in the second column, FirstName.
     
  2. Query Design View: sort ascendingClick on the arrow that appears to open the list of choices and select Ascending.
     
  3. Repeat for the fields MiddleName and LastName.

    Query Design View: three name fields sorted ascending

  4. Icon: Datasheet Switch to Datasheet view by clicking the View button Button: View - Datasheet.
    TipBe careful of using the Run button instead of the View button to switch to datasheet view. When your query is an action query, you will want to view the datasheet before actually performing the action!

    Query Design View: Sort Ascending - FirstName, MiddleName, LastName

    Hmmm. The first names are in alphabetical order. What we really want is to sort on the LastName and then on FirstName and then on MiddleName. Happily, this is easy to fix!

  5. Icon: Design Switch back to Query Design View by clicking the Views button Button: Views - Design (2003).
    (Isn't it handy that this button changes as you change views?)
     
  6. Move your mouse pointer over the top of the LastName column until it turns into the Select Column shape Pointer: Select Column and then click.
    The whole column is selected.
     
  7. Query Design View: Select columnPosition your mouse pointer over the top of the LastName column until it changes to the Select shape Pointer: Select.
     
  8. Query Design View: dragging column to new positionDrag to the left.
    The mouse pointer changes to the dragging shape Pointer: Drag.
    A black bar between two columns shows you where dropping will place the column that you are dragging.
     
  9. Query Design View: LastName column movedDrop to the left of the FirstName column.
    The LastName column moves.
     
  10. Icon: Datasheet Switch to datasheet view. Aha! Now you have the sort order that you need. Of course since none of the first or middle names are the same, it is hard to be sure!

Query Datasheet View: Names are sorted


Set Criteria

To select only some of the records, you must enter something in the Criteria row in the Query Design View. Only records that fit those rules will be shown in the datasheet.

To make things a bit more interesting, let's add a field to the query.

  1. Icon: Design Switch back to the Query Design View.
     
  2. Scroll the table fields list at the top of the window until you see the field Country/Region. Drag the field and drop it on the second column.
    Unexpectedly, this field does not replace the second column.
    The new field appears between the Employee ID and Last Name columns.
     
  3. Query Design View: Criteria ='Argentina'In the Criteria row, type Argentina in the second column and press TAB to exit the cell.
    Access adds double quote marks around your text automatically after you exit the cell.
    Tip You did not have to type an = before the value. Access assumes that it is there in the Criteria row unless you type something else, like < or >, for example.
     
  4. Icon: Run Run the query. Only records with Argentina in the second column are showing.

    Query Datasheet View: Staff - Country/Region = Argentina


Multiple Criteria Rows

You can use as many different criteria in your query as you want and in any combination.

The records in a query's results datasheet must match the entries in one whole row in the Criteria section. Each row, however, is a separate set of criteria.

  1. Icon: Design Switch back to the Query Design View.
     
  2. Query Design View: Multiple criteriaIn the first Criteria row in the first column, type <5.
     
  3. In the second Criteria row in the first column, type >5.
    You now have two different criteria. The query will display records for which:
    • EmployeeID < 5 AND Country/Region = "Argentina"
      OR
    • EmployeeID > 5
       
  4. Icon: Run Run the query.
    Eight of the nine records fit one or the other of the two criteria sets. Records 6, 7, and 8 do not have Argentina as their Country/Region, but they do have EmployeeID numbers > 5. The choice of criteria did not reveal anything interesting, but it did show what can happen when you choose badly!

    Notice that the records are not sorted by which of the criteria rows they satisfied but on the name fields, as before.

    Query Datasheet View: multiple criteria


Using OR for Criteria for a Field

You can combine criteria for a single field with OR, such as:
     >15 OR <5
    "Chavez" OR "Fuller"

  1. Icon: Design Switch back to Query Design View.
     
  2. Query Design View: combining criteria for a single field: Argentina Or AustraliaDelete the criteria in column 1.
     
  3. In column 2, edit the criteria to read  "Argentina" Or "Australia" .
     
  4. Icon: Run Run the query.
    Six records match either Argentina or Australia.

    Query Datasheet View: Criteria - Argentina or Austalia

    TipAlternate method: You would get the same results by putting Argentina and Australia on separate Criteria rows. However, if there are also criteria for other columns, you would have to repeat those entries on the second row.


Using NOT

To eliminate records that match a value, the NOT operator is handy.

  1. Icon: Design Switch back to Query Design View.
     
  2. Query Design View: Not "usa"Replace the entry in the Criteria row for column 2 with Not "usa".
     
  3. Icon: Run Run the query. You get the same results as before! The only values available in the Country/Region field right now are Argentina, Australia, and USA. So Not "usa" is the same thing as "Argentina" Or "Australia"
     
    TipNote: You did not have to match the capitalization of the value USA.

Finding Null or Non-Null Values

It is common to search tables for blank values. These may be Null or zero-length strings. In the Table Design View, you can choose whether or not to allow those sneaky zero-length strings. The default is N, so none of the fields in the Staff table are allowed to hold a zero-length string.

  1. Icon: Design Switch back to Query Design View.
     
  2. Delete the entry in the Country/Region criteria row.
     
  3. Query Design View: MiddleName  - Is NullIn the Criteria row for MiddleName, type Is Null.
     
  4. Icon: Run Run the query.
    Only two records are missing a middle name.
     

    Query Datasheet View: MiddleName is null
     

  5. Icon: Design Switch back to Query Design View and change the criteria to Is Not Null.
     
  6. Icon: Run Run the query.
    This query now shows the 7 records which do have a value for MiddleName.

TipFinding Null or zero-length string: You can combine two criteria if you do not know whether or not the field allows zero-length strings, like Is Null Or "".


Using Like

Sometimes you want to see records that match a pattern rather than matching a particular value exactly. For example, you might want to see all names that start with L.

With the Like operator you can create a pattern using wildcard characters. This is a very flexible and powerful method.

  1. Icon: Design Switch back to Query Design View.
     
  2. Delete the existing criteria.
     
  3. Query Design View: criteria - Like "g"In the Criteria row in the LastName column, type Like "g*".
    The asterisk * is a wildcard character which stands for "any number of characters". So this criterion will match any text value that has the letter g as the first character. Without the *, the query would match only text that had g as the only character.
     
  4. Query Datasheet View: Criteria Like'g*'Icon: Run Run the query.
    Two last names start with the letter g.
     
  5. Icon: Design Switch back to Query Design View.
     
  6. Query Design View: Like ??????Edit the criterion to read Like "??????".
    The question mark ? is a wildcard character that stands for 1 character. So this criterion asks for values with exactly 6 characters.
     
  7. Icon: Run Run the query.
    Two last names have exactly 6 characters.

    Query Datasheet View: Like ??????


Save and Name a Query

Later it will be useful to you to be able to tell a query from a table from just its name. Queries and tables are shown alphabetically in the lists from which you choose a source for your forms and reports.

Naming objects:

Many database programmers start the name of a database object with an abbreviation for the kind of object it is, resulting in awful names like tblStaff for a table or qryStaffNames for a query or rptStaff for a report. Such names can be useful when writing the small programs (macros and procedures) that the programmer uses to control how the database behaves. However, recently programmers are getting away from that style of naming. Programmers generally also avoid using spaces in names to make it easier on them to write their programs.

  1. Icon: Design Switch back to Query Design View.
     
  2. Remove all of the criteria.
     
  3. Dialog: Save As - Query1 as QSort Staff NamesFrom the menu select  File | Save As .
    The Save As dialog appears. Notice that you are not saving the database file, just the query. You cannot use Save As on the database as a whole! Quite different from other programs.
     
  4. Name your query QSort Staff Names.
     
  5. Click on OK to save the query and close the dialog.
     
  6. Close the query by clicking its Close button Button: Close.