Queries ask a question of the information in a table and then retrieve and display the results. For example, if you wanted to know which employees had worked for the company for more than five years, you could create a query to examine the contents of the HireDate field to find all the records in which the hire date is more than five years old. Access would retrieve the information that meets your criteria and display it in a datasheet.

These are the queries that extract or show us data. They will show data that complies with specific criteria.

Once we have the result we can consult the data and edit it (this can or cannot be done, depending on the query). A select query generates a logical table (named this way because it is not actually in the hard drive but in the memory of the computer, and every time we open it is recalculated).

This is the most common type of query.

Action queries.

These are the queries that carry out changes to the records. Various types of action queries exist to delete, update, insert data, and to create a new table from one existing table. These queries are named delete queries, update queries, append queries and Make-Table queries.

Crosstab queries.

We use these queries to calculate and restructure data for easier analysis. Crosstab queries calculate a count, average, sum, or other type of total for data that is grouped by two types of information (two fields), one down the left side of the datasheet and another across the top.

SQL queries.

When we want some action to be made on the data, we must tell Microsoft Jet engine to do it. SQL is the language that Microsoft jet engine understands and permits us to comunicate to it.

When you create a query in Query Design View, Access constructs the equivalent SQL statement behind the scenes for you. If you want, you can view and edit the SQL statement in SQL view.

After you make changes to the query in SQL view, the Query Design view will change and adapt to the new SQL sentence. However, sometimes, query might not be displayed in Query Design view because of the SQL sentence.

There is some statements that can not be defined from the Query Design View but rather directly in SQL, these are SQL-specific queries.

These queries will not be studies in this course as in order to define them knowledge of SQL is necessary, which is not part of the objective of this course.

• Open the database where the query will be created.
• Click on the Queries object .
• Double click on the Create query by using wizard option.

  In this case the wizard window will open in which we are asked from which table we choose to extract the data, the fields we wish to visualize and the title of the query, next it will automatically generate the corresponding query.

• Double click on Create query in Design view.

  In this case the Query design window will open on which we will elaborate further on, and within which we can define our query in more detail.

• Select the table from which we wish to filter the data and click on the Add button (or double-clik on its name).
• If we wish to extract data from another query, click on Queries tab an select it.
• If we wish to extract data from various tables we should continue in the same manner.
• Finally click on the Close button.

  The query Design view window will appear.

   

Every column in the grid corresponds to a field.

Every row has a function:

Field: here we place the field to use which will usually be the field to visualize, it could be a field from the table or a calculated field.

Table: name of the table we want to extract the field from. This will be usefull when we are defining queries based on various tables.

Sort: serves to arrange the resulting rows in a determinated order.

Show: if not marked, the column does not appear in the result, it is usually unmarked when we want to use the field to define the query but don't want the field to appear in the result.

Criteria: A lookup criteria is a condition that records need to comply with to appear in the result of the query. It can be made up from one or from various conditions, joined by AND and OR operators.

or: this row and those after are used to make multiple-conditions

To add fields to the grid we can:

• Double click on the name of the field appearing in the tables area Or
  
• click on the name of the field, and drag it to the grid, over the column in front of which we want to leave the field that we are adding. Or
  
• Click on the Field: row of an empty column in the grid, an arrow will appear to the right with a drop down list with all the fields from all the tables that appear in the tables area. If we have many fields and various tables we can reduce the list by first selecting a table from the Table: row, this way only fields from this selected table will appear. Or
  
• We can also type the name of the field directly in the Field: row of an empty column in the grid.

If we want all the fields from the table to appear in the result of the query we can use the asterisk * (synonymous with 'all the fields').

Running a query

We can run the query from the Query design window or from the Database window.

From the Database window:

• select the query to run by clicking on it
  .
• click on the open button in the database window.

From the design view of the query:

Click on the Run button on the toolbar.

When the query is a select query, we can also click Datasheet view on button