Access Basics:
Query Wizard

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.