Access Basics:
Query Wizard

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016


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.

Query Wizard - step 1

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


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access Objects Arrow: subtopic open
    Icon: StepTable: Design View
    Icon: StepTable: Datasheet View
    Icon: StepManage a Table
    Icon: StepSort & Filter a Table
    Icon: StepQuery Wizard
    Icon: StepAutoForm
    Icon: StepAutoReport
    Icon: StepData Access Page
        About Printing
    Icon: StepPrint Objects
    RelationshipsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



Views

Query Design ViewButton: Views - Design View (2003) 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.

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

Query Datasheet ViewButton: Views - Datasheet (2003) 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.
 

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

Title bar: Starwars Collectibles: Table   Title bar: Starwars Collectibles Query: Select Query

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

  • Toolbar: Navigate records - records are filtered(Filtered) in the Navigation bar
     
  • Remove Filter button on Standard toolbarRemove Filter button on the toolbar
     
  • Toolbar: Status Bar: FLTR onFLTR 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.

Dialog: Parameter Query

Parameter dialog appears when you run the query

 Query Datasheet View: Parameter 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).

Query Datasheet View: Crosstab query

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.

Query SQL View

Query Datasheet View: SQL query

Some advanced queries cannot be designed in the Access Query Design View but must be written in SQL.


Icon Step-by-Step 

Step-by-Step: Create a Query

 Icon Step-by-Step

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. Link: Create query by using wizardIf necessary, open mytrips.mdb.
     
  2. If necessary, in the Database Window, click on Queries.
     
  3. Double-click the link Create query by using wizard.
    Simple Query Wizard - initial viewThe 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. Simple Query Wizard: Fields selectedClick 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. Simple Query Wizard: TitleType 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.
     
    Datasheet for query from Simple Query WizardThe 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. Query Design View: Trip DatesWith the query's datasheet open, click on the Views button on the toolbar, which currently shows the Design View icon Button: Design View.

     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:
    Query Design View: Trip Dates: DateStarted - AscendingClick
    in the third row under DateStarted. An arrow appears at the right.
     
  3. Click on the down arrow and select Descending.
     
  4. Query Design View: Trip Dates grid - PhotoLink = Is NullFilter:
    In the first Criteria row under PhotoLink, type Is Null.
     
  5. Pointer changes to Select column shapeOrder of fields:
    Move
    your mouse pointer over the top edge of the column DateStarted. The pointer changes to Pointer: Select Column the Select Column shape.
     
  6. Query grid: column selectedClick to select the whole column.
     
  7. Query grid: dropping selected column at far leftDrag 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:
    Query datasheet: Trip DatesClick
    on Button: Run 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. Filter by Form:Trip DatesClick on the button Button: Filter By Form (2003) 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. Query Datasheet View: filtered resultsClick on Button: Apply Filter - ready 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. Message: Do you want to save changes to the design of query 'Trip Dates'?Close the query window by clicking its Close button Button: Close.
     A message box appears.
     
  2. Click on Yes to save your changes to the query.
     

Run an Existing Query

  1. Database Window: Query - Trip DatesIf 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.
     

    Query Datasheet View: Trip DatesThe query runs and displays the datasheet of results.
     

  5. Close the query by clicking its Close button.
     

LessonsWorking with Databases Previous Page Next Page




Teachers: Request permission to use this site with your class
 
Copyright 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?


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


Last updated: 30 Apr 2012