Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Basics > Access Objects > Query Wizard
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

   Basics: Access Objects: 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.accdb Icon: On Site, if you'd like to follow along.


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:What type of query?
               Simple, Crosstab, Find Duplicates, or Find Unmatching

  • Step 2:Which table or query and which fields to include?
  • Step 3: Show all the fields that you chose or do a summary?

  • Step 4: Name the new query. (Use a name that is clearly for a query.)
  • Step 5: Display the results or 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  > Step 4 > Design View


Views

Query Design ViewButton: View - Design (Access 2010) Query Design View

Create or edit your query in Design 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 is designed to show in the results only the fields that are in the grid at the bottom. The records that show will be only those where the value of the Condition field is "Mint". The results will be sorted on the ItemName field in Ascending order.

TipColumns out of sight: The scroll bar at the bottom of the grid shows that there are many columns to the right in the grid. Some are used in this example query, but there are blank columns at the far right! You have to scroll over and look to find out if those out-of-sight columns are blank or not.

Query Datasheet ViewButton: View  - Datasheet (Access 2010) Query Datasheet View

After you run the query or switch to Datasheet View, the results are shown in a datasheet.

The illustration shows the result of the query above. The results looks a lot like a Table Datasheet View, but the original table had 14 records and more fields, too. This query datasheet shows only the 7 records that match the query criteria, sorted on ItemName. There are fewer fields and the fields are in a different order from the table.

Icon: Trouble Is it a table datasheet or a query datasheet?
The title tab for the object shows the name of the object plus an icon for the object type: Title tabs for table and query objects (Access 2010)

Icons:  Icon: Table =Table; Icon: Select Query = Select Query

There are other types of queries which have their own icons.

Icon: Trouble Is it filtered?
Remember the clues that a filter is in place:

  • Datasheet Navigation Bar says so:  Filtered
    Record Navigation buttons (Filtered)
    Record Navigation buttons (Filtered) (Access 2013)
  • Status Bar says so: Filtered
    Status Bar: Filtered (Access 2010)
    Status Bar: Filtered (Access 2013)
  • Toggle Filter button on the ribbon is highlighted.
       Remove Filter button on Standard toolbar  Remove Filter button on Standard toolbar (Access 2013) Remove Filter button on Standard toolbar (Access 2016)
  • Column heading shows Filter icon
      Column heading for field has Filter icon (Access 2010)  Column heading for field has Filter icon (Access 2013)

Query vs. Filter

You learned a bit 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 design grid looks a lot like a query design grid. The only obvious difference is the row in the query grid for Table.

This brings up a question...

What can a query do that a filter cannot?

A query can...

  • Use fields from more than one table or other queries.

  • Show only some of the fields from the tables and queries used in the query.
  • Change the order of display for the fields.
  • Create new fields by concatenating text or by calculating using existing number fields.
  • Accept input as criteria in a parameter query.
  • 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 and fields that you want.
  • Be 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.

Icon: Select Query (Access 2010) Select query

Most often used type of query.

Selects certain fields and certain records from a table or from a set of related tables or other queries and shows them in a datasheet. Can sort on several fields. Can filter using simple or complex criteria.

Icon: Query - Crosstab (Access 2010) 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 count of UnitsinStock, by Condition (across the top) for each EpisodeID (down the left).

Starwars Crosstab query datasheet (access 2010)

Parameter query Any query that 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 results datasheet shows only
records that match the parameter value

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

SQL view shows the fields to include and the criteria in words; results in the query's datasheet

Some advanced queries cannot be designed in the query grid but must be written in SQL.

Action queries:

Icon: Query - Delete (Access 2010) Delete

Most dangerous of all queries! Deletes a group of records based on criteria.

Icon: Query - Append (Access 2010) Append

Selects certain fields and records and adds those records to a table.

Icon: Query - Update (Access 2010)  Update

Second most dangerous query! Changes values in a group of records.

Icon: Query - Make Table (Access 2010) Make-Table

Creates a new table from the results of the query.


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 filter query results in Datasheet View
to close a query
to run an existing query

Start with:Icon: Class disk,Icon: Back Up disk, mytrips-Lastname-Firstname.accdb 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-Lastname-Firstname.accdb .
  2. Icon: Back Up diskBack up this database now so you will have a good copy to use if you need to start over. This works well while the database is small.

    Icon: Access 2007Access 2007: Button: Office > Manage > Back Up Database

    Icon: Access 2010 Access 2010: File > Save and Publish > Back Up Database > Save As button

    Icon: Access 2013 Icon: Access 2016 Access 2013, 2016: File > Save As > Back Up Database > Save As button

  3. In the ribbon, click on the Create tab.
    There are two buttons about queries: Query Wizard and Query Design.

    Ribbon: Create: Queries tab group (Access 2010) Ribbon: Create: Queries tab group (Access 2013)

  4. Click the button Query Wizard.
    Query Wizard: choose type (Access 2010)The New Query dialog opens.
     
    Your first job is to pick which wizard to run out of four choices:
    • Simple Query
    • Crosstab Query
    • Find Duplicates Query
    • Find Unmatched Query
  5. Click on Simple Query Wizard and then click on the OK button.
    The Simple Query Wizard launches.

    In the first page of the dialog you must two decisions:

    • Which table(s) and/or queries to use
    • Which fields from those tables/queries to include
  6. Query Wizard - choose table or query (Access 2010)Select the table Trips.

    The drop list of tables and queries only has one entry, the table Trips. So that part was easy!

    The Available Fields list below the Tables/Queries list shows all of the fields in the selected table.

    Now you must choose which fields you want to see in your query.

  7. Query Wizard - Choose fields (Access 2010) 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.
  8. Repeat for the fields PhotoLink and Date Started.
  9. Click on the Next button.
     
  10. Query Wizard - Name the query (Access 2010)Type Trip Dates in the box for the title of your query.
     
  11. If necessary, select  "Open the query to view information" by clicking its radio button (the small circle).
     
  12. Click on the Finish button.
     
    Datasheet for query from Simple Query WizardThe datasheet for the query opens, showing only the fields that you selected. The wizard did not have a way to sort or filter. For that you will have to go to the Query Design View, the grid.
     

Query Datasheet: Navigate

This datasheet works the same way the Table Datasheet did.

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

Methods to Navigate a Datasheet:

  • Type the row number of the record you want in the navigation box.
     Remember - the row number may not be the same as the TripID number if the datasheet is sorted or filtered or has had records deleted.

  • Click a navigation arrow.

  • Scroll to see the record that you want.

  • Icon: Keyboard Using keystrokes:

TAB or ENTER Next field to the right.
If in the last field in a record, moves to the first field in the next record.
SHIFT + TAB Backs up one field to the left
HOME First field in current record
END Last field in current record
CTRL + HOME First field in the first record
CTRL + END Last field in the last record
Arrow keys One field to the left/right
One record up/down in the same column.

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, switch to the Home ribbon tab.
  2. Click on the Views button, which currently shows the Design View icon Button: Design View (Access 2010).

    Icon: Design View The Design View shows the table Trips at the top and the three fields that you choose for this query in the grid at the bottom. Nothing has been sorted or filtered yet. Those rows are blank.
  3. Sort:
    Query Design View: Trip Dates: DateStarted - AscendingClick
    in the third row under DateStarted.
    An arrow appears at the right. 
  4. Click on the down arrow and select Descending.
     
  5. Query Design View: Trip Dates grid - PhotoLink = Is NullFilter:
    In the first Criteria row under PhotoLink, type Is Null.
     
  6. 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.

     
  7. Query grid: Ready to drag columnClick to select the whole column.
  8. Move the mouse pointer over the top of the column and hold down the left mouse button.
    The pointer shape changes to the Drag shape Pointer: Drag (Access 2010).
     
  9. Query Design: Dragging column left (Access 2010)Drag to the left and drop to the left of the TripName column.
    A black vertical bar shows where the column will drop.

    Query Design: DateStarted column moved to left (access 2010)The DateStarted field is now the first column at the left. This change does not change the order of the fields in the original table itself but does change how they show in the query datasheet.

  10. Run Query:
    Query datasheet: Trip DatesClick
    on Button: Run the Run button on the ribbon to run the query.
     
    Icon: Datasheet View The datasheet shows the records that match the criteria- PhotoLink value is null. They are sorted, with the most recent trip first (descending order).

    Why would you want a query like this, which finds records with blank fields? It is actually very useful to get a quick list of what is missing.

    Icon: Tip Update records from query datasheet: If you fill in missing data or make corrections in a query datasheet, the underlying table is updated automatically.


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. Ribbon: Query Tools: Advanced > Filter By Form (Access 2010)Open the Filter By Form:
    Click on the button Advanced in Home ribbon tab in the Sort & Filter tab group to open its list and then on Filter By Form.
    The single row form appears.

  2. Filter By Form for the query datasheet (Access 2010)Click in the DateStarted cell.
    This is where you put the value you want the filtered records to match.
  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, putting a hash mark # before and after the date. Remember that the automatic formatting is different in different versions.

    Filter By Form - criteria automatically reformatted (Access 2010) Filter By Form - criteria automatically reformatted (Access 2016)
     

  5. Query Datasheet View: filtered resultsClick on Remove Filter button on Standard toolbar the Toggle Filter button.
    The filter is applied to the records that the query found.
     
    Note that the Navigation Bar and the column Date Started show that this datasheet is 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 Toggle 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. Navigation Pane: Queries = Trip Dates (Access 2013)Navigation Pane: Queries = Trip Dates (Access 2010)If necessary, click on Queries in the Objects list to expand the list.
    Only one query so far!
  2. Double-click on the query Trip Dates to run it.
     

    Query Datasheet View: Trip DatesIcon: Datasheet View The query runs and displays the datasheet of results. The filter was NOT applied automatically. That's a good thing!

    The Toggle Filter button is available (not grayed out) because your last filter was saved with the query.  

  3. Close the query by clicking its Close button.