Access Basics:
Query Wizard

A query is a way to view, analyze, or organize your records. You can also make some kinds of changes using a query, like deleting or updating a group of records.

The magic behind a query is the programming language SQL (Structured Query Language). Happily, Access provides a wizard for creating simple queries and a visual Query Design View when your query is more complex. You won't have to write the SQL code yourself. At least, not for these lessons!

Examples below are from starwars.mdb.

Query Wizard

The Access query wizard makes it very easy to create simple queries. It walks you through most of the choices that you need to make. Afterwards, you can use the Query Design View to add sorting and criteria.

• Step 1: Choose a table or query.
               Choose which fields you want to see in your results

• Step 2: Choose to show all the fields that you chose
                       or to do a summary

• Step 3: Name the new query.
               Choose to display the results
                       or to display the Query Design View to make changes.

Click to see the wizard steps:
 Step 1  >  Step 2  >  Step 3  > Design View

Views

Query Design View

Create or edit your query in this view. You choose which fields from which tables or other queries to include. You choose how to sort and what criteria to use.

The query in the illustration shows only the fields that are in the grid at the bottom and only the records where the value of the Condition field is "Mint". The results will be sorted on the ItemName field.

There are many blank columns to the right in the grid, as well as more columns used by the query.
 

Query Datasheet View

After you run the query, the results are shown in a datasheet. This looks like the Table Datasheet View, but the original table had 14 records. This query datasheet shows only 7 records, sorted on ItemName.

The original table had more fields than are in the query, but that is harder to tell since some fields may be out of view at the right of the window.
 

Is it a table datasheet or a query datasheet?
The window's Title bar tells you:

Is it filtered? Any datasheet can be filtered! You must look for one of the signs that a filter is in place:

• (Filtered) in the Navigation bar
   
• Remove Filter button on the toolbar
   
• FLTR mode indicator on the Status bar.

Query vs. Filter

You learned about filters in the previous lesson. A filter selects records from a table based on criteria. So can a query. An advanced filter can sort based on several fields. So can a query. The Advanced Filter/Sort grid looks a lot like a query grid. The only difference is the row in the grid for Table.

This brings up a question...

What can a query do that a filter cannot?
A query can...

• Show only some of the fields.

• Can change the order of display for the fields.

• Show fields from more than one table.

• Create new fields and do calculations using existing fields.

• Accept input as criteria.

• Summarize and total data.

• Update records with new data.

• Append new records to a table.

• Delete records based on criteria.

• Make a new table of just the records that you want.

• Can be saved to be used later.

• Can be used as the source of data for a form or report.

Types of Queries

There are several types of queries in Access. Except for the action queries, you can view the query results directly in the datasheet or use the query as the source for a form or a report.
 

Select query	Selects certain fields and certain records from a table or from a set of related tables and shows them in a datasheet. Can sort on several fields. Can filter using simple or complex criteria. Most often used type of query.
Parameter query	Asks for input for the criteria. Example: You are asked to type in a value to select records that match that value. Parameter dialog appears when you run the query   Query shows only the records that match the parameter value
Crosstab query	Calculates totals of various sorts based of two types of information, one across the top of the results datasheet and one down the left side. Example: Show total UnitsinStock, by Condition (across the top) for each EpisodeID (down the left).
Action query
Delete	Deletes  a group of records based on criteria.
Append	Selects certain records and adds those records to a table.
Update	Changes values in a group of records.
Make-Table	Creates a new table from the results of the query.
SQL query	Query is written in SQL. Simple example:  Select several fields from the table Star Wars Collectibles but shows records only for those that are in "Mint" condition. Some advanced queries cannot be designed in the Access Query Design View but must be written in SQL.

Step-by-Step: Create a Query

---

What you will learn:

to create a simple query with the Query Wizard to navigate query results to edit a query in Query Design View to change the order of fields in the query datasheet to filter query results in Datasheet View to close a query to run an existing query

---

Start with: mytrips.mdb from previous lesson

Simple Query Wizard

The Simple Query Wizard walks you through the choices you need to make to create a simple query. You will create a query that will show just the trip names, the photo links,  and the starting dates.

1. If necessary, open mytrips.mdb.
    
2. If necessary, in the Database Window, click on Queries.
    
3. Double-click the link Create query by using wizard.
   The Simple Query Wizard opens.
    
   Your first job is to pick what tables or queries to use in the query and what fields from them to show in the results.
   
   The drop list of tables and queries only has one entry, the table Trips. So that part is easy!
    
   At the left is a list of the fields in the selected table.
    
   You must choose which fields you want to see in your query.
     
4. Click on TripName and then click on the > button in the middle. The field TripName appears in the list of Selected Fields and disappears from the list of Available Fields.
    
5. Repeat for the fields PhotoLink and Date Started.
    
6. Click on the Next button.
    
7. Type Trip Dates in the box for the title of your query.
    
8. If necessary, select  "Open the query to view information" by clicking its radio button (the small circle).
    
9. Click on the Finish button.
    
   The datasheet for the query opens, showing only the fields that you selected.

 

---

Query Datasheet: Navigate

This datasheet works the same way the Table Datasheet did.

For review, practice with the Navigation buttons and with the shortcut keys that move you around in the datasheet.

---

Query Design View: Edit

The Query Wizard is only the start of what you can do with a query. Did you notice that the wizard did not ask you about how to sort or if you wanted to use criteria to filter the records? You can do that in Query Design View. You can also change the order of the fields.

1. With the query's datasheet open, click on the Views button on the toolbar, which currently shows the Design View icon .
   
    The Design View shows the table Trips at the top and the three fields that you choose for this query at the bottom. There is no sorting or criteria.
    
2. Sort:
   Click in the third row under DateStarted. An arrow appears at the right.
    
3. Click on the down arrow and select Descending.
    
4. Filter:
   In the first Criteria row under PhotoLink, type Is Null.
    
5. Order of fields:
   Move your mouse pointer over the top edge of the column DateStarted. The pointer changes to the Select Column shape.
    
6. Click to select the whole column.
    
7. Drag to the left and drop to the left of the TripName column.
   The DateStarted field is now the first column at the left. This change does not change the order of the fields in the table itself.
    
8. Run Query:
   Click on the Run button on the toolbar to run the query and show the datasheet.
    
   The datasheets shows the records that match the criteria. They are sorted, with the most recent trip first (descending order).
    

---

Query Datasheet View: Apply a Filter

Any datasheet can be filtered, including a query datasheet. Yes, you already used criteria in the query design. You can still apply an additional filter.

1. Click on the button Filter By Form in the toolbar.
   The single row form appears.
    
2. Click in the DateStarted cell.
    
3. Type >1/1/2000
   This criterion asks that the date be later than January 1, 2000.
    
4. Press the TAB key to move to the next cell. (Access automatically corrects your date's format to proper syntax, using a hash mark # before and after the date.)
    
5. Click on the Apply Filter button on the toolbar.
   The filter is applied to the records that the query found.
    
   Note that the Navigation Bar shows (Filtered).
    

Why didn't we include the filter's criteria in the original query?? To be honest... so you could create a filter, of course!

In the real world, a filter is often used when you want to pick out certain records temporarily, but you do not expect to need to use those exact criteria regularly.

---

Close Query

Your filter will be saved with the query, but it will not be applied automatically when you run the query again. Until you create a new filter for this query, you can apply the saved filter by clicking the Apply Filter button.

1. Close the query window by clicking its Close button .
    A message box appears.
    
2. Click on Yes to save your changes to the query.
    

---

Run an Existing Query

1. If necessary, switch to the Database Window.
    
2. If necessary, click on Queries in the Objects list.
   The list of existing queries shows at the right. Only one so far!
    
3. Click on the query Trip Dates to select it.
    
4. Click the Open button in the Database Window toolbar.
    

   The query runs and displays the datasheet of results.
    

5. Close the query by clicking its Close button.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 22 Jan 2008