Microsoft Access for Beginners
Part III - Writing the Queries
Contents
Introduction
A Word on Wizards
Background
The Mailing Query
Navigating the Design Grid
Getting the Results
Customizing the Query
Conclusion
This article has been republished on Microsoft Office Online.
Introduction
A database storing every bit of knowledge you could ever need wouldn't be of much use without some way to retrieve the data. After I create the tables, my next step is usually to create the forms for the database but first I need to provide some information on queries. Many of the queries that you use will actually be created automatically as you design forms and reports which often use them to obtain the required data. You may never even notice them. If you do any significant amount of work with Access, however, you will need to know how to construct queries for those times when you need something specific.
A Word on Wizards
For many of the objects in Access such as tables and forms, there are wizards that you can use to quickly create an object by answering a few questions and making a few selections. The result is a ready-made solution designed to do exactly what you specified, whether that's what you wanted or not. In the previous chapters of this series, I didn't even mention the wizards and this is the only time I will mention them. This is because I don't use them for anything significant when designing a database. I don't believe that they are good tools for creating professional applications.
If you want to 'create' something you know nothing about and spend a lot of time thinking that it doesn't meet your needs, then use the wizards. You don't need my help on that. On the other hand, if you want to take ownership of the project and be able to support it, do it yourself. Eventually, you will have a library of your own work that you can consult whenever you want to figure out how to do something. You'll also maintain your skills that way and it's a lot more fun.
Sample Files
If you have not already done so, please download the demonstration files:
Demo.zip - Access demonstration database and original Excel spreadsheet (These files require Access and Excel 2000 or later.)
Background
Microsoft Access uses Structured Query Language (SQL - often pronounced "sequel") in order to manipulate the data in the database tables and other queries. It is not necessary to know this language in order to create queries and work with Access but going the extra mile and being familiar with it will make you more productive if you decide to continue working with Access long-term. In addition to viewing the data as shown in the example in this article, queries can also be used to add, delete and change data from various sources based on the criteria that you specify.
If you don't already have the demo database open, open it now. Press F11 to view the database window and then select Queries under the Objects selector. Open the query titled "Mailing" by selecting it in the window and clicking the Design icon or by right-clicking on the query name and selecting 'Design View'.
When working with Access, like with many other programs, you'll find that there's usually more than one way to do something. In this case, either way will bring up a screen where you can create a query. It's just that one might take an extra step so Access can figure out exactly what you want to do. (Click on image for Access 2007 example.)
As I said earlier, Access uses SQL in order to write queries. It writes this language behind the Query by Example (QBE) screen which enables the average user to quickly create the desired query by selecting the appropriate tables and fields and setting the right options to pull the correct data. This is the screen you'll see when you open the query in design view.
The Mailing query is a simple query based on tblPersonnel and tblAddresses that demonstrates the different functions of the QBE window. This type of query is known as a Select query as it doesn't actually affect the data in any way. It only selects it for viewing based on the user's specifications. By default, whenever you create a new query, it will be a select query until you specify otherwise.
Other query types include:
- Append - Queries can be used to append data to a table from another source such as other tables, queries, user input, real-time calculations or a combination of these.
- Delete - You can delete groups of records from a table based on one or more criteria.
- Make Table - Queries can create new tables from the same sources used by Append queries.
- Update - Values can be changed throughout a table using an Update query. This might be handy if a certain mistake has been made consistently when entering numerous records or if a piece of data like an area code changes.
All of these query types and more can be created using the Query menu in the query design view. (In Access 2007, look for the Query Design option under the Create menu.) Check the help files for more information on each of them and take some time to play around with the different options.
The Mailing Query
In its original form, the Mailing query is too wide to be shown here but you can view it in its entirety by clicking on the partial picture below.
When you create a new query, you'll see that the program immediately asks you to add tables or other queries to the new query. In this example, I added the two tables shown above and you can see how the relationship that already existed between the tables is automatically included. For this query, the default relationship works fine as it will show all employees in tblPersonnel and any corresponding records in tblAddresses. If there is no address entered for an employee, blank fields will be shown. If I wanted to, I could double-click on the relationship line shown here and change the relationship for this query to get different results. It would not affect anything else in the database.
You can add fields to your query by double-clicking on the table fields in the top half of the window. You can also select multiple fields by holding down the CTRL or ENTER keys and add them by dragging the selected fields to the grid in the bottom half of the window. Selecting the * at the top of a the field list selects all fields in the table. In the Mailing query, I've selected some basic information from the employees table including the company assigned employee number, name and employment information. From the addresses table, I've selected the address information and telephone number.
Notice that I have not selected the EmpKey field from either table, even though this is the primary key. It's simply not necessary. The query will pull the appropriate records based on the relationship between the two tables. You do not need to specify the key within the query unless you're using it to specify which record you want.
Navigating the Design Grid
The bottom half of the query window is known as the design grid. The first two rows in the grid show the relevant field and table names. If you place your cursor in any of these spaces, you'll notice that a selector tab appears on the right side of the text. This tab enables you to quickly change the selected fields or table if necessary.
The Total row shown in the example does not appear automatically when you create a new query. You activate it by clicking on the toolbar icon showing the sigma symbol (Σ) or by right-clicking on the design grid and selecting it from the context menu. This row works with numeric fields and lets you add automatic calculations such as sums and averages to queries. This is only a small example of what you can do with it, however, and you should take the time to experiment. Note that once the row is displayed, each field must have a selection in it.
The Sort row lets you sort the data in the query based on one or more fields. In this example, I've sorted by last name but I could also choose any other field such as the employee number or the shift. If you select more than one field to sort on, the query will give preference to the first selected field on the left and work its way to the right of the query. In other words, if I sorted by last name and shift, the records would be sorted by last name and then if there was more than one person with the same last name, it would sort within that name by shift. You can change the order of the columns in the query window by selecting and dragging them.
The next row in the query window, Show, specifies if a field should be shown or hidden in the query results. In this query, I have two fields that are hidden; the Current field is not shown as it is only used to supply criteria for the results and none of the records have a value for the Address2 field so I hid it just for this example.
The last rows in the design grid supply the criteria or desired values for each field in the query. In the Mailing query, there is only one field that has criteria added and that is the Current field. This is what is called a boolean field in which True or False can be specified a number of ways:
| True | False |
| -1 or 1 | 0 |
| Yes | No |
| On | Off |
| (Note: -1,1,0 are sometime used in specialized fields requiring three possible values. The safest way to test for a True value in the criteria is to use the expression "<> 0" rather than "= -1") | |
In this case, I've specified Yes so that only current employees will be shown in the query results. The criteria fields are very flexible and can even contain equations and specialized functions that present choices based on user input. Using the 'Or' row and the rows below it, you can specify multiple criteria for a field.
Getting the Results
Query display options on the toolbar. In Access 2007, these are available only from the context menu displayed by right-clicking on the query design grid.
After setting up the query, it's time to run it. There are two ways of
seeing the results of a query. In the case of a Select query like this one,
there is no difference between the two. It's important to know the difference
when running other types of queries, though, and to be in the habit of using the
right one because otherwise the consequences can be disastrous.
Datasheet View, available through a pull-down option on the left of the toolbar, will show you the records affected by your query without actually running it. This distinction becomes important when you are working with queries that are adding or changing data in a table. By using datasheet view, you can see the affected records without actually committing the changes.
The Run option, activated by the exclamation symbol highlighted in the screenshot above, actually runs the queries and performs any actions specified. If data will be changed or deleted, Access will warn you about the changes and ask for verification unless this option has been turned off. Once verified, however, the changes are permanent.
To be safe, it's best to use Datasheet View when working with select queries like this one so as to develop the right habits.
After selecting Datasheet View, the query returns the records shown above. Click on the picture to see the query results in their entirety.
Customizing the Query
The original Mailing query is straightforward and easy to understand but doesn't begin to show the power available through the use of queries. In this last section, I want to show you just a couple more things that are possible using the different features of the design grid.
The second query in the database window, Mailing2, is a revision on the original mailing query. I've added some features to it that might come in handy when doing an actual mailing to a group of employees.
In the original query, the employee name is contained in three separate fields. While this might be okay in some cases, I want the query to combine these three values into one field that shows the entire name. To do this, I'm going to change the way the names of the field are listed in the design grid. I'll start by removing two of the fields, FirstName and MiddleName, from the grid since I'll only need one field.
By right-clicking on the LastName field in the Title row and selecting Build from the pop-up menu, you can display the Expression Builder. This tool is very useful for building expressions and adding them to query fields.
In the example shown here, you can see how the lower left-hand box in the builder displays all of the objects within the database by category along with some extra features such as functions that you can use in your expressions. The first item in the box is the Mailing2 query that the Builder was opened from. In the middle box, it displays all of the fields within the query.
To build an expression, just double-click on the necessary objects in the lower boxes and add the correct operators using the buttons below the expression area at the top. You can also paste an expression in the box and modify it as needed. For this example, I've created a simple expression that will concatenate (combine) the three name fields into one:
[LastName] & ", " & [FirstName] & " " & [MiddleName]
This expression combines the three values into one using the concatenation character ( &). I've added a comma between the last and first name and a single blank space between the first and middle names. Any non-field text that's added must be surrounded by quotation marks. Thus "Merz" "Kathie" "M" becomes "Merz, Kathie M".
It's not necessary to include the table names since the field names are unique to tblPersonnel. If you were using a field name that existed in more than one table and both tables were represented in the query, it would then be necessary to enter it with the table name (i.e. tblPersonnel.EmpKey).
After entering the expression and clicking OK, Access inserts the expression back in the design grid in place of the field name that was there before. It also adds a name to the expression since the query field needs a single name for the field. This name can be changed to anything that conforms with Access field name rules.
The brackets around the field names in the expression are the way Access indicates the name of an object such as a field, table or form name. The brackets are optional unless there is a space in the name (and I don't use spaces in field names). In the SQL section above, the SQL code did not include brackets but if you view the code for this query or if you build a query with spaces in the field names, you'll see them included.
After creating a similar expression for the city, state and ZIP, I've saved the query and run it again.
One question you may be asking is "Why go to the trouble of storing the name or the city-state information in three parts only to combine it again?" One reason is that you have greater flexibility in the way data is used. The CityStateZip field in the above query cannot be sorted by state, for example. Breaking the information down into 'atomic' values, meaning that the field data cannot be broken down into smaller useable parts, makes the data more versatile. This is also referred to in the rules of database normalization from Part I. There is some flexibility when it comes to something like the employee name and sometimes the decision depends on the needs of the organization.
Conclusion
This is just the beginning when it comes to the things you can do with queries. In another article, "No More Repeats!", I detailed how to remove duplicate records from a table using the power of queries.
As you continue to work with Access and get into some of the more advanced functions, you'll see that the program often treats tables and queries very much the same. Both are sources of data with which operations are performed. A query can read from another query or other sources, including links to files outside the database, just as it can read from Access tables. As a database management system, Access deals primarily with data, not the objects in which they are stored. Further, both tables and queries can exist as abstract collections of data in memory without being bound to the table and query objects that you see in the database window. This may not be something you need to be concerned with now but understanding this can help you avoid some confusion later on.
See Also:
Access SQL: Basic Concepts, Vocabulary and Syntax - From Microsoft Office Online, a series of articles on using SQL to work with data in Access.