Home

Introduction to Queries

 

Introduction

A query is the result of getting or isolating a list of values from a table or another query. Querying is the technique of examining data to create a dependent list. You can perform querying on a table, a form, or a query. Microsoft Access provides various means of performing such an operation visually. As an alternative, Microsoft Access supports SQL that allows you to query a database using code.

There are various ways you create a query in Microsoft Access.

The Query Wizard

The simplest way to create a query is by using the Query Wizard. It presents a list of tables and queries you can select from the current database.

To use the Query Wizard, on the Ribbon, you can click the Create tab and, in the Other section, click Query Wizard Query Wizard. This would display the New Query dialog box:

The Mew Query Dialog Box

On the New Query dialog box, you can click Simple Query Wizard and click OK. The first page of the Simple Query Wizard expects you to choose the origin of the query as a table or an already created query. After selecting the table or query, the second page of the wizard would present the fields of that list and you can select those you want:

Simple Query Wizard

The next page of the wizard allows you to specify the name of the query:

Simple Query Wizard

Introduction to Query Design

Like other objects of a database, a query can be designed. You design a query using the Design View:

  • To display a query in Design View, from the Navigation Pane, you can right-click a query and click Design View
  • To start designing a new query, in the Other section of the Create tab of the Ribbon, click Query Design

This would display the Show Table dialog box that allows you to specify the table or query that holds the fields you want to use in the intended query

Query Design

  If the Show Tables dialog box is closed or for any reason you want to display it:

  • In the Query Setup section of the Design tab of the Ribbon, you can click the Show Table button Show Table
  • You can right-click anywhere on the query window and click Show Table...

When a query is displaying in Design View, the Design tab of the Ribbon displays the buttons used for a query: 

Query Type

Query Setup

Show/Hide

The Query Window

The Query window allows you to design and manage various aspects of a query. You can right-click the title bar of the Query window to access a menu:

The menu of a query window

One of the operations you can perform on the Query window consists of resizing its top and bottom sections by dragging the splitter bar up or down:

Using the Splitter

Selecting the Columns

To create the fields for a query, you use the table(s) or query( queries) displayed in the upper section of the window. Once you have decided on the originating object(s), you can select which fields are relevant for your query:

  • To select one field from the list, just click it
  • To select many fields on the same range, you can click one of them, press and hold Shift. Then click one field on the other end of the desired range
  • To select fields at random, click one of the desired fields, press and hold Ctrl; then click each one of the desired fields
  • To select all fields, you can click the * line on the list of fields

To Add Columns

To make a field participate in a query, you have various options:

  • Once you have made your selection on the list in the top part of the query window, you can drag it and drop it in the bottom section of the query window
     
    Query Design: Adding One Field
  • You can also select more than one field and drag them:
     
    Query Design: Adding Various Fields
  • Instead of dragging a field or all fields, you can either double-click a field to add it to the query, or double-click the line with * to add all fields to the query
  • In the bottom part of the query window, click an empty Field box to show a combo box. Then click the arrow of that combo box and select an item from the list:

Executing a Query

To execute a query:

  • If the query is currently closed, from the Navigation Pane:
    • You can double-click it
    • You can right-click it and click Open
  • If the query is already opened and it is in Design View, on the Ribbon:
    • You can click the Run button Run
    •  You can click the View button or you can click the arrow of the View button and click Datasheet View

If you manually write a SQL statement and want to execute it, change the view to Datasheet View.

Selecting a Column

Some operations require that you select a column from the bottom section of the query window:

  • To select a field in the lower section of the view, click the tiny bar of the column header:
     


    The whole column will be selected
  • To select a range of columns, click the column header of one at one end, press and hold Shift, then click the column header at the other end

Since selecting a column in the Query window is a visual operation, there is no equivalent in SQL.

Removing a Column From a Query

As seen above, a query is built by selecting columns from the originating list and adding them. If you do not need a column anymore on a query, which happens regularly during data analysis, you can either delete it or replace it with another column:

  • To delete a column:
    • Once it is selected, you can press Delete
    • Right-click the column header and click Cut
  • To delete a group of columns, select them and press Delete

Replacing a Column

To replace a column, click the arrow on the combo box that displays its name and select a different field from the list:

To replace a column, click the arrow on the combo box that displays its name and select a different field from the list

Moving a Column

Columns on a query are positioned incrementally as they are added to it. If you do not like the arrangement, you can move them and apply any sequence of your choice. Before moving a column or a group of columns, you must first select it. Then:

  • To move a field, click its column header once. Click it again and hold your mouse down, and drag in the direction on your choice
     
    Query Design: Moving a Column
  • To move a group of columns, first select the group and then proceed as if it were one column

Data Entry on a Query

You can perform data entry on a query. To do this, display it in Datasheet View, click the desired cells and type the information as necessary.

Query Printing

You can print the record of a query. To do this:

  • Right-click the query from the Navigation Pane and click Print
  • Open the query or select it in the Navigation Pane, then use the Office Button to access the Print menu
 

Home Copyright © 2008-2012 FunctionX