Creating Queries

To ask questions of, extract, and highlight data you want from your tables, you design queries based on some specified criteria.

The table objects you create in Microsoft Access are already designed to show you everything, including all the fields and all the records. However, there will be times when you only need to see only a portion of records and perhaps only a few relevant fields. This is where queries come into the picture.

A query is nothing more than a question (i.e. the Find command) you ask about your data in a language Microsoft Access can understand. Microsoft Access will then search for your data based on this question from one or more tables you have selected and present the results in the manner you want it (i.e. the kinds of fields you want shown in your "solution" table).

Solution tables can be either editable or read-only. A solution table derived from a query which can be edited is called a dynaset. A solution table derived from a query which cannot be edited is called a snapshot.

Once a dynaset or snapshot is created using your queries, certain actions can be performed automatically such as calculating totals; updating, deleting, or appending a group of records all at once; or making a new table.

How do I create a query?

To create a query:

1. Click the Database Window button on the toolbar.
2. Click the Query button in the Database window.
3. Click New button to create a new query.
4. You will have a choice of how you wish to create a query. Click the New Query button.
5. Microsoft Access will open a Select Query (or Query Design) window and the Add Table/Query dialog box.
6. Before you can begin typing a query, you need to specify the table(s) and/or query table(s) you want to ask a question. This involves highlighting the table or query you want in the Add Table dialog box by clicking on it with your mouse and pressing the Add button. Continue this process until you have all the tables and/or queries you want.
7. When finished, click Close.
8. You are now looking at your Query window, ready for you to ask a question. Let us begin by designing the most common type of query: the select query.
9. You will observe in the lower pane of the Query window an empty query from which you will design from scratch. It consists of 6 rows named Field, Table, Sort, Show, Criteria, and or.
10. To select fields you need for your query, click on the fields from your chosen list of tables and/or queries in the upper pane of the Query window and drag them into each column of the Query Design window. The Table row in your Query Design will tell you which table or query the field comes from. To specify which of these fields in your Query design you want shown in your "solution" table, click on the check boxes in the row marked Show. To sort the records in your "solution" table, click in the cell beneath the field you want sorted in the Sort row and choose Ascending or Descending from the choices given. And to specify the query or question you will ask of your data, click in the Criteria and/or Or cells and type the question you want.
11. Once you have your query set up the way you want, save the query first before running it.

How do I save my query?

To save your query:

1. Click the Save button on the toolbar.
2. For new query designs, type a name for your query. Remember, do not give your query the same name as an existing table.
3. Click OK.

How do I view a query datasheet?

It is a good idea at this stage before running any query to view its datasheet first to see what kind of data has been found. This is particularly important when we move to other types of queries known as action queries where the data in your database may be irretrievably modified or lost.

To view a query datasheet:

1. Click the Datasheet View button on the toolbar.
2. For more sophisticated query designs where you have set parameters, enter the criteria you want when Microsoft Access prompts you for them, and then click OK.

My data looks alright. How do I run my query?

If you are ready to run your query, click the Run button on the toolbar. For action queries, a dialog box will appear asking you to confirm that you want to make changes to your data.

I need help in specifying criteria

The only tricky part in designing queries is in the criteria for specifying how you will ask questions of your data.

You specify criteria with an expression. The expression is in a format that Microsoft Access understands.

For us humans, we tend to ask questions of our records or data by saying, "Of all the people in our database, who is more than 50 years old?" We may simplify this by saying, "Age is greater than 50". In the language of Microsoft Access, we would type, ">50" in the Criteria cell of our Query Design table.

To understand the language of Microsoft Access and therefore know what questions we can ask, we have to remember a certain way of entering these questions in our Query Design table.

To begin with, two or more criteria expressions written in more than one cell in the same row are understood by Microsoft Access to mean the expressions are connected with "AND", whereas criteria expressions written in the Criteria and Or grid boxes are connected with "OR". Hence you can ask more than one question of your data in any one Query Design table.

If you need to specify more than one question in a single cell (i.e. one field), you can type the word "And" and "Or" within your expression directly.

If you want to search for a pattern of characters, you can use wildcard characters such as the question mark (?), asterick (*), or number sign (#).

If you want to specify a criteria each time you run the query instead of having the criteria built-in and automated, you must specify a parameter name in the Criteria cell. To activate this feature, enclose the name or phrase inside square brackets ([....]). For example, a paramater name inside the Category field could be written as follows:

Each time you run the query, Microsoft Access will display the prompt, "Enter a Category ID." Supply the criteria when the prompt appears and click OK.

Multiple prompts for two or more parameter names can also be specified in your Microsoft Access query.

And to write the actual expressions for asking questions, the table below will hopefully provide enough clues as to how to enter them. But if you need further information about writing expressions, ask Microsoft Access for help.

Can I use calculation fields in my query?

Yes you can. A calculation field is nothing more than a new field (i.e. a new column) in your Query Design table for the purposes of storing the results of a calculation you want to perform on other fields. For example, you can create a new field called "Total Sales" in the Field row of your Query Design table by typing it in. Then to calculate something, type the letter colon (:) next to your new field name and write the expression for calculating what you want. To include the names of other fields in your calculation, type the field name in square brackets ([....]).

For further help about constructing an expression in your calculated field, use the Expression Builder button in the cell.

Can I rename fields in my query?

You can rename fields in your query such that they will appear in your "solution" table, or datasheet view of your dynaset. The original field names will not be shown. For example, to change the existing field name of Salary to Average Salary, type the new field name in the Field row followed by a colon (:) and then the existing field name like so,

Can I move around fields in my query?

To move fields around in your query is to rearrange the columns in the query grid. This may be helpful if you want your dynaset to display fields in the order you want them. To move a column in the grid:

1. Click the field selector above the field your want to move.
2. Click the field selector again and while holding down the mouse button, drag the mouse pointer until it is pointing in the place you want the column to be positioned.
3. Release the mouse button.

I am creating a query in one table and I only want to see related data in another table. How is this done?

If you don't want to create a permanent relationship in table design mode, you will need to create table relationships in a query just like you would in designing tables using the Table Design method.

As you may already know, tables specified for a query which are unjoined will result in a dynaset that contains all possible combinations of data from both tables for you to question the data on. So if two tables selected for your query contains 10 records each, and you specify fields from each table in your Query Design table, the dynaset will contain 100 records. By joining the tables, you will get only those records you want to question by having only the related data in your dynaset.

To join tables in your query together, you click and drag a field from one table onto a field containing the same kind of name and data type in another table. Usually, the field you drag is a primary key field (displayed in bold).

The table relationship you have created (as shown by a line connecting the fields in two tables) applies only to this query. If you want this table relationship to affect all other queries, you should create a permanent relationship between the tables using the Table Design view.

To delete the relationship between tables, click the join line to select it and press the Del key. The join line disappears.

There will also be times when you still need to get all the records from one table despite the presence of a table relationship. This is where the join type can be modified by Microsoft Access. To get all the records from one table in a query, we have to change the join type from the standard equi-join to the outer join type.

To change the join types:

1. Place the mouse pointer over the middle of the join line and double-click in this area.
2. The Join Properties dialog box appears. Select the option you want. The number 1 is an equi-join. The numbers 2 and 3 are outer joins specifying which of the two tables you want to get all the records from.
3. Click OK.

You will see after clicking OK which table will have all its records extracted. The table that has the arrow on the join line pointing away from it will be the table where all the records will be gathered.