.jpg)
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
Behind the Scenes
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 first chapter of this series, I didn't even mention the wizards and this is the only time I will mention them. This is because I never use them 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 (Requires Access and Excel 2000 or above)
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. If the database window with all the objects doesn't appear, press F11 to view it 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.
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 by telling the query what to look for in the table.
- 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. 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.
By double-clicking on the table fields in the top half of the window, you can add them to your query, specifying what information is to be pulled from 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 (Σ). This row works especially 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 with it extensively. 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 sort by 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. You can use this to sort the query results any way that you need to.
The next row in the query window, Show, specifies if a field should be shown or hidden in the query results. Hiding a field by clearing the checkbox will not change the way records are selected and this can be a good way of including query instructions while keeping the results manageable. 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 as it is either true or false. With a boolean field, 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.) | |
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
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 result 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 irreversible.
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.
Behind the Scenes
As I said earlier, Access generates the SQL code needed to pull this information behind the scenes. All you have to do is select the right settings in the design grid and you have the listing shown above. By selecting SQL View instead of Datasheet view, you can view and edit the code as necessary. This is not the place for an in-depth discussion on SQL but I do want to point out some basics.
The above queries actually do the same thing but have variations that are important when using SQL. At first, it looks like a confusing mess until you focus on the keywords that I've highlighted in red in the above pictures. Notice that all of these keywords are followed by table and field names. Each of these keywords corresponds in some way to a part of the QBE window.
- SELECT / FROM - First, the query selects the fields and tables specified in the corresponding rows of the design grid. It simply lists the fields that are to be shown in the order they are placed in the grid. Notice that tblPersonnel.Current is not shown here as it is set to be hidden in the results.
- WHERE - This clause specifies the conditions to be met by the data. In this example, the value for tblPersonnel.Current must be "Yes". It specifies criteria for individual rows rather than groups of rows.
- LEFT JOIN ... ON - SQL uses Join clauses to refer to the relationships between tables. In this case, it means that all of the records from the table on the left side of the relationship (tblPersonnel) are available to the query while only the corresponding records from the right side of the relationship (tblAddresses) are available. This join displays all employees, including those whose addresses are not listed in tblAddresses. This is also referred to as a LEFT OUTER JOIN. An INNER JOIN would only show employees who had records in both tables. The ON keyword specifies the fields on which the tables are to be joined. The use of the words LEFT and RIGHT do not refer to the tables' placement in the QBE window but rather the order in which they were designated when the relationship was created. If you double-click on the relationship in the query, you'll notice that tblPersonnel is called the Left table and the tblAddresses is the Right table.
- GROUP BY - Since the query is grouping by all of the fields, they are all listed here in the order shown on the design grid, including the hidden fields. If there was a field in the query that represented a total or an average, such as the number of absences, then the query would calculate this total based on the grouped fields and the record would contain a field representing the average or total requested. This is one way in which Access SQL differs in practice from other brands of SQL. Access uses Group By as the default in the QBE grid and therefore groups on all fields by default which has the effect of doing no grouping at all. When writing SQL in other databases, such as SQL Server, I only include the Group By statement when it's needed. If you remove it manually in the SQL View of this query, 'Group By' will be replaced by 'Expression' for each field and the query results will be the same.
- HAVING - This keyword introduces any criteria from the design grid. In this case, it shows that only the records containing a True value for tblPersonnel.Current are to be retrieved. HAVING is used to specify criteria for a group defined by the GROUP BY clause and cannot be used unless GROUP BY is present. The difference between WHERE and HAVING is that WHERE is applied to individual records before the query performs any grouping. HAVING is applied after the grouping is done.
- ORDER BY - The final keyword lists any fields used to sort the query results. Again, you can sort by multiple fields and the results will be sorted according to the order of the sorted fields in the SQL code.
SQL contains many other statements, commands, operators and functions but the ones listed above will be enough to keep you from panicking when you view the SQL for a query. Until you're confident using SQL, be very careful about editing any of the code behind a query as a mistake will keep your query from running or perhaps even entering design view until it's corrected.
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, say 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 an 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 actually 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. This wouldn't be a problem in a database like this where all the people are in the same state but if you had a customer database that contained people all over the country or in different countries, it would be better to break the information down into 'atomic' values, meaning that the field data cannot be broken down into smaller useable parts.
This is actually dealt with by the rules of database normalization referred to in Part I . There is some flexibility when it comes to something like the employee name. In this database, I've chosen to break it into separate fields although it's likely that I would never need to deal with the names separately unless I wanted to sort by first name for some reason. In this case, it 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. That data can come from any source, including links to files outside the database. 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.