Tables & Queries:
Select Query

Step-by-Step: Select Query

---

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:  , Project database open.

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

Query Design View

1. With the Projects database open, in the Database Window, select the Staff table.
    
2. Click on the New Objects button and select   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.
    

   

---

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. Move 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 to execute this query. You will see all of the fields and all of the records in the Staff table.

   

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

4. Switch back to the Query Design View by clicking the View button .
    
5. From 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.

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

   

---

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. Click on the arrow that appears to open the list of choices and select Ascending.
    
3. Repeat for the fields MiddleName and LastName.

   

4. Switch to Datasheet view by clicking the View button .
   Be 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!

   

   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. Switch back to Query Design View by clicking the Views button .
   (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 and then click.
   The whole column is selected.
    
7. Position your mouse pointer over the top of the LastName column until it changes to the Select shape .
    
8. Drag to the left.
   The mouse pointer changes to the dragging shape .
   A black bar between two columns shows you where dropping will place the column that you are dragging.
    
9. Drop to the left of the FirstName column.
   The LastName column moves.
    
10. 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!

---

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. 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. 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.
   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. Run the query. Only records with Argentina in the second column are showing.

   

---

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. Switch back to the Query Design View.
    
2. In 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. 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.

   

---

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. Switch back to Query Design View.
    
2. Delete the criteria in column 1.
    
3. In column 2, edit the criteria to read  "Argentina" Or "Australia" .
    
4. Run the query.
   Six records match either Argentina or Australia.

   

   Alternate 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. Switch back to Query Design View.
    
2. Replace the entry in the Criteria row for column 2 with Not "usa".
    
3. 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"
    
   Note: 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. Switch back to Query Design View.
    
2. Delete the entry in the Country/Region criteria row.
    
3. In the Criteria row for MiddleName, type Is Null.
    
4. Run the query.
   Only two records are missing a middle name.
    

   
    

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

Finding 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. Switch back to Query Design View.
    
2. Delete the existing criteria.
    
3. 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. Run the query.
   Two last names start with the letter g.
    
5. Switch back to Query Design View.
    
6. 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. Run the query.
   Two last names have exactly 6 characters.

   

---

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. Switch back to Query Design View.
    
2. Remove all of the criteria.
    
3. From 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 .