One advantage of using queries is that they allow you to find data easily in multiple related tables. Queries are also useful, however, for sifting through the data in a single table. All the techniques you use for working with a single table apply equally to more complex multiple-table queries. This chapter covers the basics about building queries to select data from a single table. The next chapter shows you how to build more complex queries with multiple tables, totals, parameters, and more.
The easiest way to start building a query on a single table is to click the Query Design button in the Other group on the Create tab (see Figure 7–1). Open the Conrad Systems Contacts database and then click the Query Design button. Access 2007 displays the Show Table dialog box on top of the query design grid as shown in Figure 7–3.
Figure 7–3: The Show Table dialog box allows you to select one or more tables or queries to build a new query.
Select tblContacts on the Tables tab of the Show Table dialog box and then click Add to place tblContacts in the upper part of the Query window. Click Close in the Show Table dialog box to view the window shown in Figure 7–4.
Figure 7–4: The Query window in Design view for a new query on tblContacts shows the table with its list of fields in the top part of the window.
As mentioned earlier, the Query window in Design view has two main parts. In the upper part you find field lists with the fields for the tables or queries you chose for this query. The lower part of the window is the design grid, in which you do all the design work. Each column in the grid represents one field that you’ll work with in this query. As you’ll see later, a field can be a simple field from one of the tables or a calculated field based on several fields in the tables.
You use the first row of the design grid to select fields-the fields you want in the resulting recordset, the fields you want to sort by, and the fields you want to test for values. As you’ll learn later, you can also generate custom field names (for display in the resulting recordset), and you can use complex expressions or calculations to generate a calculated field.
The second row shows you the name of the table from which you selected a field. If you don’t see this row, you can display it by clicking Table Names in the Show/Hide group on the Design tab below Query Tools. This isn’t too important when building a query on a single table, but you’ll learn later that this row provides valuable information when building a query that fetches data from more than one table or query.
In the Sort row, you can specify whether Access 2007 should sort the selected or calculated field in ascending or in descending order. In the Show row, you can use the check boxes to indicate the fields that will be included in the recordset. By default, Access 2007 includes all the fields you place in the design grid. Sometimes you’ll want to include a field in the query to allow you to select the records you want (such as contacts born in a certain date range), but you won’t need that field in the recordset. You can add that field to the design grid so that you can define criteria, but you should clear the Show check box beneath the field to exclude it from the recordset.
Finally, you can use the Criteria row and the row(s) labeled Or to enter the criteria you want to use as filters. After you understand how a query is put together, you’ll find it easy to specify exactly the fields and records that you want.
The first step in building a query is to select the fields you want in the recordset. You can select the fields in several ways. Using the keyboard, you can tab to a column in the design grid and press Alt+Down Arrow to open the list of available fields. (To move to the design grid, press F6.) Use the Up Arrow and Down Arrow keys to highlight the field you want, and then press Enter to select the field.
Another way to select a field is to drag it from one of the field lists in the upper part of the window to one of the columns in the design grid. In Figure 7–5, the LastName field is being dragged to the design grid. When you drag a field, the mouse pointer turns into a small rectangle.
Figure 7–5: You can drag a field from the table field list to a column in the design grid.
At the top of each field list in the upper part of the Query window (and also next to the first entry in the Field drop-down list in the design grid) is an asterisk (*) symbol. This symbol is shorthand for selecting “all fields in the table or the query” with one entry on the Field line. When you want to include all the fields in a table or a query, you don’t have to define each one individually in the design grid unless you also want to define some sorting or selection criteria for specific fields. You can simply add the asterisk to the design grid to include all the fields from a list. Note that you can add individual fields to the grid in addition to the asterisk in order to define criteria for those fields, but you should clear the Show check box for the individual fields so that they don’t appear twice in the recordset.
For this exercise, select ContactID, LastName, FirstName, WorkStateOrProvince, and BirthDate from the tblContacts table in the Conrad Systems Contacts database. You can select the fields one at a time by dragging and dropping them in the design grid. You can also double-click each field name, and Access will move it to the design grid into the next available slot. Finally, you can click on one field you want and then hold down the Ctrl key as you click on additional fields or hold down the Shift key to select a group of contiguous fields. Grab the last field you select and drag them all to the design grid. If you switch the Query window to Datasheet view at this point, you’ll see all the records, containing only the fields you selected from the underlying table.
|Inside Out-Another Way to Select All Fields|| |
Another easy way to select all the fields in a table is to double-click the title bar of the field list in the upper part of the Query window-this highlights all the fields. Then click any of the highlighted fields and drag them as a group to the Field row in the design grid. While you’re dragging, the mouse pointer changes to a multiple rectangle icon, indicating that you’re dragging multiple fields. When you release the mouse button, you’ll see that Access 2007 has copied all the fields to the design grid for you.
In general, a field that is output by a query inherits the properties defined for that field in the table. You can define a different Description property (the information that is displayed on the status bar when you select that field in a Query window in Datasheet view), Format property (how the data is displayed), Decimal Places property (for numeric data other than integers), Input Mask property, Caption property (the column heading), and Smart Tags property. We’ll show you the details of how to use a smart tag in Chapter 12, “Customizing a Form.”
For details about field properties, see Chapter 4, “Creating Your Database and Tables.”
When you learn to define calculated fields later in this chapter, you’ll see that it’s a good idea to define the properties for these fields. If the field in the query is a foreign key linked to another table, you can also set the Lookup properties as described in Chapter 5, “Modifying Your Table Design.” Access propagates Lookup properties that you have defined in your table fields; however, you can use the properties on the Lookup tab in the query’s Property Sheet pane to override them.
The Access 2007 query designer lets you define Lookup properties for any text or numeric field (other than AutoNumber). The field doesn’t have to be a defined foreign key to another table. You might find this useful when you want the user to pick from a restricted value list-such as M or F for a Gender field.
To set the properties of a field, click any row of that field’s column in the design grid, and then click the Property Sheet button in the Show/Hide group of the Design contextual tab to display the property sheet, shown in Figure 7–6. Even though the fields in your query inherit their properties from the underlying table, you won’t see those properties displayed here. For example, the BirthDate field in tblContacts has both its Description and Caption set to Birth Date and a Format set to mm/dd/yyyy. If you click in the BirthDate field in your query and open the property sheet, you will see that none of the properties show values. Use the property settings in the property sheet to override any inherited properties and to customize how a field looks when viewed for this query. Try entering new property settings for the BirthDate field, as shown in Figure 7–6.
Figure 7–6: In the property sheet, you can set properties for the BirthDate field.
|Inside Out-Switching Views to Check Field Prope|| |
One of the quickest ways to see if a field in a query has the properties you want is to switch to Datasheet view. If the field isn’t displayed the way you want, you can switch back to Design view and override the properties in the query.
If you make these changes and switch to Datasheet view, you’ll see that the BirthDate column heading is now Birthday; that the date displays day name, month name, day number, and year; and that the text on the status bar matches the new description, as shown in Figure 7–7. (Grab the right edge of the Birthday header with your mouse and drag it right to open the column so that you can see all the date values.)
Figure 7–7: The BirthDate field is now displayed with new property settings.
The next step is to further refine the records you want by specifying criteria on one or more fields. The example shown in Figure 7–8 selects contacts working in the state of California.
Figure 7–8: When you specify “CA” as the selection criterion in the design grid, Access returns only records with a WorkStateOrProvince equal to California.
Entering selection criteria in a query is similar to entering a validation rule for a field, which you learned about in Chapter 4. To look for a single value, simply type it in the Criteria row for the field you want to test. If the field you’re testing is a text field and the value you’re looking for has any blank spaces in it, you must enclose the value in quotation marks. Note that Access adds quotation marks for you around single text values. (In Figure 7–8, we typed CA, but Access replaced what we typed with “CA” after we pressed Enter.)
If you want to test for any of several values, enter the values in the Criteria row, separated by the word Or. For example, specifying CA Or NC searches for records for California or North Carolina. You can also test for any of several values by entering each value in a separate Criteria or Or row for the field you want to test. For example, you can enter CA in the Criteria row, NC in the next row (the first Or row), and so onbut you have to be careful if you’re also specifying criteria in other fields, as explained in the section “AND vs. OR” on page 357.
|Inside Out-Be Careful When Your Criterion Is Also a Keyword|| |
You should be careful when entering criteria that might also be an Access 2007 keyword. In the examples shown here, we could have chosen to use criteria for the two-character abbreviation for the state of Oregon (OR)-but or, as you can see in the examples, is also a keyword. In many cases, Access is smart enough to figure out what you mean from the context. You can enter
Or Or Ca
in the Criteria row under State, and Access assumes that the first Or is criteria (by placing quotation marks around the word for you) and the second Or is the Boolean operator keyword. If you want to be sure that Access interprets your criteria correctly, always place double quotation marks around criteria text. If you find that Access guessed wrong, you can always correct the entry before saving the query.
In the section “AND vs. OR,” you’ll see that you can also include a comparison operator in the Criteria row so that, for example, you can look for values less than (<), greater than or equal to (>=), or not equal to (<>) the value that you specify.
Access 2007 stores dates and times as 8-byte decimal numbers. The value to the left of the decimal point represents the day (day zero is December 30, 1899), and the fractional part of the number stores the time as a fraction of a day, accurate to seconds. Fortunately, you don’t have to worry about converting internal numbers to specify a test for a particular date value because Access 2007 handles date and time entries in several formats.
You must always surround date and time values with pound signs (#) to tell Access that you’re entering a date or a time. To test for a specific date, use the date notation that is most comfortable for you. For example, #April 15, 1962#, #4/15/62#, and #15-Apr-1962# are all the same date if you chose English (United States) in the Regional And Language Options in Windows Control Panel. Similarly, #5:30 PM# and #17:30# both specify 5:30 in the evening.
|Inside Out-Understanding Date/Time Criteria|| |
You must be careful when building criteria to test a range in a date/time field. Let’s say you want to look at all records between two dates in the ContactEvents table, which has a date/time field-ContactDateTime-that holds the date and time of the contact. For all contact events in the month of January 2007, you might be tempted to put the following on the Criteria line under ContactDateTime.
>=#1/1/2007# AND <=#1/31/2007#
When you look at the results, you might wonder why no rows show up from January 31, 2007 even when you know that you made and recorded several calls on that day. The reason is simple. Remember, a date/time field contains an integer offset value for the date and a fraction for the time. Let’s say you called someone at 9:55 A.M. on January 31, 2007. The internal value is actually 39,113.4132-January 31, 2007 is 39,113 days later than December 30, 1899 (the zero point), and .4132 is the fraction of a day that represents 9:55 A.M. When you say you want rows where ContactDateTime is less than or equal to January 31, 2007, you’re comparing to the internal value 39,113-just the day value, which is midnight on that day. You won’t find the 9:55 A.M. record because the value is greater than 39,113, or later in the day than midnight. To search successfully, you must enter
>=#1/1/2007# AND <#2/1/2007#
When you enter criteria for several fields, all the tests in a single Criteria row or Or row must be true for Access 2007 to include a record in the recordset. That is, Access 2007 performs a logical AND operation between multiple criteria in the same row. So if you enter CA in the Criteria row for StateOrProvince and <#1 JAN 1972# in the Criteria row for BirthDate, the record must be for the state of California and must be for someone born before 1972 to be selected. If you enter CA Or NC in the Criteria row for StateOrProvince and >=#01/01/1946# AND <#1 JAN 1972# in the Criteria row for BirthDate, the record must be for the state of California or North Carolina, and the person must have been born between 1946 and 1971.
Figure 7–9 shows the result of applying a logical AND operator between any two tests. As you can see, both tests must be true for the result of the AND to be true and for the record to be selected.
Figure 7–9: When you specify the logical AND operator between two tests, the result is true only if both tests are true.
When you specify multiple criteria for a field and separate the criteria by a logical OR operator, only one of the criteria must be true for Access 2007 to select the record. You can specify several OR criteria for a field, either by entering them all in a single Criteria cell separated by the logical OR operator, as shown earlier, or by entering each subsequent criterion in a separate Or row. When you use multiple Or rows, if the criteria in any one of the Or rows is true, Access 2007 selects the record. Figure 7–10 shows the result of applying a logical OR operation between any two tests. As you can see, only one of the tests must be true for the result of the OR to be true and for Access 2007 to select the record.
Figure 7–10: When you specify the logical OR operator between two tests, the result is true if either or both of the tests is true.
Let’s look at a specific example. In Figure 7–11, you specify CA in the first Criteria row of the WorkStateOrProvince field and >=#01/01/1946# AND <#1 JAN 1972# in that same Criteria row for the BirthDate field. (By the way, when you type #1 JAN 1972# and press Enter, Access changes your entry to #1/1/1972#.) In the next row (the first Or row), you specify NC in the WorkStateOrProvince field. When you run this query, you get all the contacts from the state of California who were born between 1946 and 1971. You also get any records for the state of North Carolina regardless of the birth date.
Figure 7–11: You can specify multiple AND and OR selection criteria in the design grid with additional OR lines.
In Figure 7–12, you can see the recordset (in Datasheet view) that results from running this query:
Figure 7–12: The recordset of the query shown in Figure 7–11 shows only the records that match your criteria.
|Inside Out-Don’t Get Confused by And and Or|| |
It’s a common mistake to get Or and And mixed up when typing compound criteria for a single field. You might think to yourself, “I want all the work contacts in the states of Washington and California,” and then type WA And CA in the Criteria row for the WorkStateOrProvince field. When you do this, you’re asking Access to find rows where (WorkStateOrProvince=“WA”) And (WorkStateOrProvince=“CA”). Because a field in a record can’t have more than one value at a time (can’t contain both the values WA and CA in the same record), there won’t be any records in the output. To look for all the rows for these two states, you need to ask Access to search for (WorkStateOrProvince=“WA”) Or (WorkStateOrProvince=“CA”). In other words, type WA Or CA in the Criteria row under the WorkStateOrProvince field.
If you also want to limit rows from contacts in North Carolina to those who were born between 1946 and 1971, you must specify >=#01/01/1946# AND <#1/1/1972# again under BirthDate in the second Or row-that is, on the same row that filters for NC under WorkStateOrProvince. Although this seems like extra work, this gives you complete of flexibility to filter the data as you want. You could, for example, include people who were born before 1969 in California and people who were born after 1970 in North Carolina by placing a different criterion under BirthDate in the two rows that filter WorkStateOrProvince.
In addition to comparison operators, Access provides three special operators that are useful for specifying the data you want in the recordset. Table 7–1 describes these operators.
Useful for specifying a range of values. The clause Between 10 And 20 is the same as specifying >=10And <=20.
Useful for specifying a list of values separated by commas, any one of which can match the field being searched. The clause In ("CA", "NC", "TN") is the same as "CA" Or "NC" Or "TN".
Useful for searching for patterns in text fields. You can include special characters and ranges of values in the Like comparison string to define the character pattern you want. Use a question mark (?) to indicate any single character in that position. Use an asterisk (*) to indicate zero or more characters in that position. The pound-sign character (#) specifies a single numeric digit in that position. Include a range in brackets () to test for a particular range of characters in a position, and use an exclamation point (!) to indicate exceptions. The range [0–9] tests for numbers, [a-z] tests for letters, and 110–9] tests for any characters except 0) through 9. For example, the clause Like"?[a-k]d[0–9]*" tests for any single character in the first position, any character from a through k in the second position, the letter d in the third position, any character from 0 through 9 in the fourth position, and any number of characters after that.
 As you’ll learn in Chapter 27, “Building Queries in an Access Project,” and Article 2, “Understanding SQL,” the pattern characters supported by SQL Server when you are working in an Access project file are different. The pattern characters discussed here work in desktop applications (.accdb and .accde files) only.
Suppose you want to find all contacts in the state of California or Pennsylvania who were born between 1955 and 1972 and whose first name begins with the letter J. Figure 7–13 shows how you would enter these criteria. Figure 7–14 shows the recordset of this query.
Figure 7–13: You can also restrict records by using Between, In, and Like all in the same design grid.
Figure 7–14: The recordset of the query shown in Figure 7–13 shows only the records that match your criteria.
|Inside Out-Choosing the Correct Date/Time Criteria|| |
If you’re really sharp, you’re probably looking at Figure 7–13 and wondering why we chose Between #1/1/1955# And #12/31/1972# instead of >= #1/1/1955# And <, #1/1/1973# to cover the case where the BirthDate field might also include a time. In this case we know that the BirthDate field has an input mask that doesn’t allow us to enter time values. So we know that using Between and the simple date values will work for this search.
For additional examples that use the Between, In, and Like comparison operators, see ZDefining Simple Field Validation Rules” on page 168 and the “Predicate” sections in Article 2, Understanding SQL,” on the companion CD.
You can use an expression to combine fields or to calculate a new value from fields in your table and make that expression a new field in the recordset. You can use any of the many built-in functions that Access 2007 provides as part of your expression. You concatenate, or combine, text fields by stringing them end-to-end, or you use arithmetic operators on fields in the underlying table to calculate a value. Let’s switch to the HousingDataCopy.accdb database to build some examples.
One common use of expressions is to create a new text (string) field by concatenating fields containing text, string constants, or numeric data. You create a string constant by enclosing the text in double or single quotation marks. Use the ampersand character (&) between fields or strings to indicate that you want to concatenate them. For example, you might want to create an output field that concatenates the LastName field, a comma, a blank space, and then the FirstName field.
Try creating a query on the tblEmployees table in the HousingDataCopy.accdb database that shows a field containing the employee last name, a comma and a blank, first name, a blank, and middle name. You can also create a single field containing the city, a comma and a blank space, the state or province followed by one blank space, and the postal code. Your expressions should look like this:
LastName &: ", " &: FirstName &: " " &: MiddleName City &: ", " &: StateOrProvince &: " " &: PostalCode
You can see the Query window in Design view for this example in Figure 7–15. We clicked in the Field row of the second column and then pressed Shift+F2 to open the Zoom window, where it is easier to enter the expression. Note that you can click the Font button to select a larger font that’s easier to read. After you choose a font, Access 2007 uses it whenever you open the Zoom window again.
Figure 7–15: If you use the Zoom window to enter an expression, you can see more of the expression and select a different font.
Access 2007 requires that all fields on the Field row in a query have a name. For single fields, Access uses the name of the field. When you enter an expression, Access generates a field name in the form ExprN:. See “Specifying Field Names” on page 377 for details about changing the names of fields or expressions. Notice also that Access automatically adds brackets around field names in expressions. It does this so that the field names in the SQL for the query are completely unambiguous. If this table had been designed with blanks in the field names, you would have to type the brackets yourself to ensure that the query designer interprets the names correctly.
When you look at the query result in Datasheet view, you should see something like that shown in Figure 7–16.
Figure 7–16: Here is a query result with concatenated text fields.
Try typing within the Exprl field in Datasheet view. Because this display is a result of an expression (concatenation of strings), Access 2007 won’t let you update the data in this column.
|Inside Out-Eliminating Extra Spaces When Concatenating Null Values|| |
If you look very closely at Figure 7–16, you can see that we captured the image with the insertion point displayed at the end of the Exprl field on the first row. Do you notice that there’s an extra space after the first name? This happened because that person has no middle name, so what we’re seeing is the extra blank we inserted after first name that is supposed to provide spacing between first name and middle name.
This isn’t too much of a problem in this particular expression because you’re not going to notice the extra blank displayed at the end of the name. But if you create the expression First (blank) Middle (blank) Last and if a record has no middle name, the extra blank will be noticeable.
When you use an ampersand, any Null field in the expression doesn’t cause the entire expression to be Null. A little secret: You can also use the arithmetic plus sign (+) to concatenate strings. As you’ll learn when you create arithmetic expressions, if a field in the expression is Null, the expression evaluates to Null. So, to solve the extra blank problem, you can create an expression to concatenate the parts of a name as follows:
FirstName &: (" "+MiddleName) &: " " &: LastName
If MiddleName is a Null, the arithmetic expression inside the parentheses evaluates to Null, and the extra blank disappears!
In a reservations record (tblReservations in the Housing Reservations database), code in the form that confirms a reservation automatically calculates the correct TotalCharge value for the reservation before Access 2007 saves a changed row. If you strictly follow the rules for good relational table design (see Article 1, “Designing Your Database Application,” on the companion CD), this isn’t normally a good idea, but we designed it this way to demonstrate what you have to code to maintain the calculated value in your table. (Access 2007 won’t automatically calculate the new value for you.) You can see how this code works in Chapter 20, “Automating Your Application with Visual Basic.” This technique also saves time later when calculating a total by month or total by facility in a report.
Table 7–2 shows the operators you can use in arithmetic expressions.
Adds two numeric expressions.
Subtracts the second numeric expression from the first numeric expression.
Multiplies two numeric expressions.
Divides the first numeric expression by the second numeric expression.
Rounds both numeric expressions to integers and then divides the first integer by the second integer. The result is truncated to an integer.
Raises the first numeric expression to the power indicated by the second numeric expression.
Rounds both numeric expressions to integers, divides the first integer by the second integer, and returns only the remainder.
The expression to calculate the TotalCharge field is complex because it charges the lower weekly rate for portions of the stay that are full weeks and then adds the daily charge for extra days. Let’s say you want to compare the straight daily rate with the discounted rate for longer stays. To begin, you need an expression that calculates the number of days. You can do this in a couple of different ways. First, you can use a handy built-in function called DateDiff to calculate the difference between two Date/Time values in seconds, minutes, hours, days, weeks, months, quarters, or years. In this case, you want the difference between the check-in date and the check-out date in days.
The syntax for calling DateDiff is as follows:
DateDiff(<interval>, <date1>, <date2>[, <firstdayofweek>])
The function calculates the difference between <date1> and <date2> using the interval you specify and returns a negative value if <date1> is greater than <date2>. You can supply a <firstdayofweek> value (the default is 1, Sunday) to affect how the function calculates the "ww" interval. Table 7–3 explains the values you can supply for interval.
Calculates the difference in years. DateDiff subtracts the year portion of the first date from the year portion of the second date, so DateDiff("yyyy", #31 DEC 2006#, #01 JAN 2007#) returns 1.
Calculates the difference in quarters. If the two dates are in the same calendar quarter, the result is 0.
Calculates the difference in months. DateDiff subtracts the month portion of the first date from the month portion of the second date, so DateDiff("m", #31 DEC 2006#, #01 JAN 2007#) returns 1.
Calculates the difference in days. DateDiff handles this option the same as "d" below. (For other functions, this extracts the day of the year.)
Calculates the difference in days.
Calculates the difference in weeks based on the day of the week of <date1>. If, for example, the day of the week of the first date is a Tuesday, DateDiff counts the number of Tuesdays between the first date and the second date. For example, March 28, 2007 is a Wednesday, and April 2, 2007 is a Monday, so DateDiff("w", #28 MAR 2007#, #02 APR 2007#) returns 0.
Calculates the difference in weeks. When the first day of the week is Sunday (the default), DateDiff counts the number of Sundays greater than the first date and less than or equal to the second date. For example, March 28, 2007 is a Wednesday, and April 7, 2007 is a Monday, so DateDiff("ww", #28 MAR 2007#, #02 APR 2007#) returns 1.
Calculates the difference in hours.
Calculates the difference in minutes.
Calculates the difference in seconds.
You can also use the settings you find in Table 7–3 for the interval argument in the DatePart function (which extracts part of a Date/Time value) and DateAdd function (which adds or subtracts a constant to a Date/Time value).
The second way to calculate the number of days is to simply subtract one date from the other. Remember that the integer portion of a Date/Time data type is number of days. If you’re sure that the fields do not contain any time value, subtract the check-in date from the check-out date to find the number of days. Let’s see how this works in the sample database.
Open the HousingDataCopy.accdb database if you have closed it and start a new query on tblReservations. Add EmployeeNumber, FacilityID, RoomNumber, CheckInDate, CheckOutDate, and TotalCharge to the query design grid. You need to enter your expression in a blank column on the Field row. You’ll build your final expression in two parts so you can understand the logic involved. Using DateDiff, start the expression by entering
DateDiff("d", [CheckInDate], [CheckOutDate])
To calculate the number of days by subtracting, the expression is
To calculate the amount owed at the daily rate, multiply either of the previous expressions by the DailyRate field. With DateDiff, the final expression is
DateDiff("d", [CheckInDate], [CheckOutDate]) * [DailyRate]
If you want to use subtraction, you must enter
([CheckOutDate]-[CheckInDate]) * [DailyRate]
You might be wondering why the second expression includes parentheses. When evaluating an arithmetic expression, Access evaluates certain operations before others, known as operator precedence. Table 7–4 shows you operator precedence for arithmetic operations. In an expression with no parentheses, Access performs the operations in the order listed in the table. When operations have the same precedence (for example, multiply and divide), Access performs the operations left to right.
Access Evaluates Operators in the Following Order:
Negation-a leading minus sign (−)
Multiplication and division (*, /)
Integer division (\)
Addition and subtraction (+, −)
Access evaluates expressions enclosed in parentheses first, starting with the innermost expressions. (You can enclose an expression in parentheses inside another expression in parentheses.) If you do not include the parentheses in the previous example, Access would first multiply CheckInDate times DailyRate (because multiplication and division occur before addition and subtraction) and then subtract that result from CheckOut-Date. That not only gives you the wrong answer but also results in an error because you cannot subtract a Double value (the result of multiplying a date/time times a currency) from a date/time value.
After you select the fields from the table and enter the expression to calculate the total based on the daily rate, your query design grid should look something like Figure 7–17.
Figure 7–17: Use an expression to calculate the amount owed based on the daily rate.
When you switch to Datasheet view, you can see the calculated amount from your expression as shown in Figure 7–18.
Figure 7–18: Access displays the results of your calculated expression in Datasheet view.
Note that not all the calculated amounts are larger than the amount already stored in the record. When the reservation is for six days or fewer, the daily rate applies, so your calculation should match the existing charge. You might want to display only the records where the new calculated amount is different than the amount already stored. For that, you can add another expression to calculate the difference and then select the row if the difference is not zero.
Switch back to Design view and enter a new expression to calculate the difference in an empty column. Your expression should look like this:
TotalCharge-(([CheckOutDate]-[CheckInDate]) * [DailyRate])
In the Criteria line under this new field, enter <> 0. Your query design should look like Figure 7–19, and the datasheet for the query now displays only the rows where the calculation result is different, as shown in Figure 7–20.
Figure 7–19: This expression and criterion finds the rows that are different.
Figure 7–20: The datasheet now shows only the rows where the calculation is different than the stored value.
Finding the rows that differ in this way has the added benefit of displaying the calculated difference. If you’re only interested in finding the rows that differ but don’t care about the amount of the difference, you don’t need the second expression at all. You can find the rows you want by placing the expression <>[TotalCharge] in the Criteria line under the first expression you entered. This asks Access to compare the amount calculated at the straight daily rate with the value in the TotalCharge field stored in the record and display the row only when the two values are not equal.
You might have inferred from the earlier discussion about entering criteria that you can use only constant values in the Criteria or Or lines. As you can see, you can also compare the value of one field or expression with another field or expression containing a reference to a field.
|Inside Out-Adding Parentheses to Expressions for Clarity|| |
You might have noticed that we placed an extra set of parentheses around the original expression we built to calculate the amount at the daily rate before subtracting that amount from the stored value. If you study Table 7–4 carefully, you’ll see that we really didn’t have to do this because Access would perform the multiplication before doing the final subtract However, we find it’s a good practice to add parentheses to make the sequence of operations crystal clear-we don’t always remember the order of precedence rules, and we don’t want to have to go looking up the information in Help every time we build an expression. Adding the parentheses makes sure we get the results we want.
So far, you have built fairly simple expressions. When you want to create a more complex expression, sometimes the Expression Builder can be useful, as discussed in the next section.
For more complex expressions, Access 2007 provides a utility called the Expression Builder. Let’s say you want to double-check the total amount owed for a reservation in the sample database. You have to work with several fields to do this-CheckInDate, CheckOutDate, DailyRate, and WeeklyRate. You need to calculate the number of weeks to charge at the WeeklyRate and then charge the remaining days at the DailyRate. To see how the Expression Builder works, start a new query on the tblReservations table. Click in an empty field in the design grid, and then click the Builder button in the Query Setup group of the Design contextual tab. Access opens the Expression Builder dialog box shown in Figure 7–21.
Figure 7–21: The Expression Builder dialog box helps you build simple and complex expressions.
In the upper part of the dialog box is a blank text box in which you can build an expression. You can type the expression yourself, but it’s sometimes more accurate to find field names, operators, and function names in the three panes in the lower part of the dialog box and to use the various expression operator buttons just below the text box.
The expression you need to build, which we’ll walk you through in detail in the next few pages, will ultimately look like this:
((DateDiff("d", [tblReservations]![CheckInDate], [tblReservations]![CheckOutDate]) \ 7) * [WeeklyRate])+((DateDiff("d", [tblReservations]![CheckInDate], [tblReservations]![CheckOutDate]) Mod 7) * [DailyRate])
You can use the Expression Builder to help you correctly construct this expression. Start by double-clicking the Functions category in the left pane, then select Built-In Functions to see the list of function categories in the center pane, and the list of functions within the selected category in the right pane. Select the Date/Time category in the center pane to narrow down the choices. Here you can see the DateDiff function (that you used earlier) as well as several other built-in functions you can use. (You can find a list of the most useful functions and their descriptions in Article 4, “Visual Basic Function Reference,” on the companion CD.)
Double-click the DateDiff function in the right pane to add it to the expression text box at the top of the Expression Builder. When you add a function to your expression in this way, the Expression Builder shows you the parameters required by the function. You can click any parameter to highlight it and type a value or select a value from one of the lists in the bottom panes. Click <<interval>> and overtype it with "d". (See Table 7–3 for a list of all the possible interval settings.) You need to insert the CheckInDate field from tblReservations for <<date1>> and the CheckOutDate field for <<date2>>. Click <<date1>> to highlight it and double-click Tables in the left pane to open up the list of table names. Scroll down until you find tblReservations and select it to see the list of field names in the second pane. Double-click CheckInDate. Then click <<date2>>, and double-click CheckOutDate. You don’t need the <<firstweekday>> or <<firstweek>> parameters, so click them and press the Delete key to remove them. (You can also remove the extra commas if you like.) The Expression Builder should now look like Figure 7–22.
Figure 7–22: Create a calculation using table field names in the Expression Builder dialog box.
You’ll notice that the Expression Builder pastes [tblReservations]![CheckInDate] into the expression area, not just CheckInDate. There are two good reasons for this. First, the Expression Builder doesn’t know whether you might include other tables in this query and whether some of those tables might have field names that are identical to the ones you’re selecting now. The way to avoid conflicts is to fully qualify the field names by preceding them with the table name. When working in queries, separate the table name from the field name with a period or an exclamation point. Second, you should enclose all names of objects in Access in brackets (). If you designed the name without any blank spaces, you can leave out the brackets, but it’s always good practice to include them.
|Inside Out-Understanding Name Separators in SQL|| |
As you’ll learn in Chapter 20, in most cases you should separate the name of an object from the name of an object within that object (for example, a field within a table) with an exclamation point. When you build an expression in the Expression Builder, you’ll find that the Expression Builder separates names using exclamation points. However, as you’ll learn in Article 2, “Understanding SQL,” on the companion CD, the standard for the SQL database query language uses a period between the name of a table and the name of a field within the table. To be most compatible with the SQL standard when constructing a query expression, use a period between a table name and a field name. Access accepts either an exclamation point or a period in query design.
Next, you need to divide by 7 to calculate the number of weeks. You’re not interested in any fractional part of a week, so you need to use the integer divide operator (\). Note that there is no operator button for integer divide. The operator buttons are arranged horizontally below the expression text box. So, you can either type the operator or scroll down in the leftmost pane, select Operators to open that list, select Arithmetic in the second pane, and then double-click the integer divide operator (\) in the rightmost list to add it to your expression. Make sure the insertion point in the expression box is positioned after the integer divide operator and type the number 7.
The next operation you need is to multiply the expression you have thus far by the WeeklyRate field from tblReservations. If you like, you can add left and right parentheses around the expression before adding the multiply operator and the field. Remember from Table 7–4 that multiplication and division are of equal precedence, so Access evaluates the division before the multiplication (left to right) even if you don’t add the parentheses. But, as we noted earlier, we like to make the precedence of operations crystal clear, so we recommend that you add the parentheses. Press the Home key to go to the beginning of the expression, click the left parenthesis button, press the End key to go to the end, click the right parenthesis button, click the multiply operator (*) button, and finally select the WeeklyRate field from the tblReservations field list.
WeekyRate and DailyRate are currency fields. DateDiff returns an integer, and the result of an integer divide (\) or a modulus (Mod) operation is an integer. Whenever you ask Access to evaluate an arithmetic expression, it returns a result that has a data type sufficiently complex to contain the result. As you might expect, multiplying an integer (a simple data type) with a currency field (a more complex data type) returns a currency field.
You need to add this entire expression to the calculation for remaining days at the daily rate, so press Ctrl+Home again and add one more left parenthesis, press the Ctrl+End key, and click the right parenthesis button to complete this first part of the expression. Click the addition operator to add it to your expression. Rather than scan back and forth to add parentheses as we build the second part of the expression, click the left parenthesis button twice to start building the calculation for extra days. Add the DateDiff function again, click <<interval>>, and type "d". Click <<date1>>, find CheckInDate in tblReservations again, and double-click it to add it to your expression. Click <<date2>> and double-click the CheckOutDate field. Remove <<firstweekday>> and «firstweek» from the function.
Now, you need to know how many days beyond full weeks are in the reservation. You might be tempted to divide by 7 again and try to extract the remainder, but there’s a handy operator that returns only the remainder of a division for you-Mod. Scroll down in the left pane and select Operators. In the middle pane, select Arithmetic to see only the arithmetic operators in the right pane. Double-click Mod to add it to your expression after the parentheses.
We’re almost done. Type the number 7 and click the right parenthesis button to close the Mod calculation. Click the multiply operator button, and then go back to tblReservations and double-click the DailyRate field. Click the right parenthesis button one last time to finish the expression. Verify that your completed expression exactly matches the one in Figure 7–23.
Figure 7–23: Your completed expression in the Expression Builder dialog box should match this figure.
Click OK to paste your result into the design grid. Go ahead and add ReservationID, FacilityID, RoomNumber, CheckInDate, CheckOutDate, and TotalCharge to your query grid. When you switch to Datasheet view, your result should look like Figure 7–24.
Figure 7–24: Switch to Datasheet view to see the result of your complex calculation expression.
Do you notice any stored values that don’t match what you just calculated? (Hint: Look at the highlighted row.) If you haven’t changed the sample data, you’ll find several rows that we purposefully updated with invalid TotalCharge values. Here’s a challenge: Go back to Design view and enter the criteria you need to display only the rows where your calculated charge doesn’t match the TotalCharge stored in the table. You can find the solution saved as qxmplUnmatchedCharges in the HousingDataCopy.accdb sample database.
|Inside Out-Is the Builder Useful? You Decide|| |
We personally never use the Expression Builder when we’re creating applications in Access 2007. We find it more cumbersome than directly typing the expression we think we need and then trying it out. We included this discussion because some beginning developers might find that the Expression Builder helps them learn how to build correct expression and function call syntax.
We used the DateDiff function to solve this problem, but Access 2007 has several other useful functions to help you deal with date and time values. For example, you might want to see only a part of the date or time value in your query. You might also want to use these functions to help you filter the results in your query. Table 7–5 explains each date and time function and includes filter examples that use the ContactDateTime field in the tblContactEvents table in the Conrad Systems Contacts sample database.
Returns a value from 1 through 31 for the day of the month.
To select records with contact events that occurred after the 10th of any month, enter Day([ContactDateTime]) in an empty column on the Field line and enter >10 as the criterion for that field.
Returns a value from 1 through 12 for the month of the year.
To find all contact events that occurred in March (of any year), enter Month([ContactDateTime]) in an empty column on the Field line and enter 3 as the criterion for that field.
Returns a value from 100 through 9999 for the year.
To find contact events that happened in 2007, enter Year([ContactDateTime]) in an empty column on the Field line and enter 2007 as the criterion for that field.
As a default, returns a value from 1 (Sunday) through 7 (Saturday) for the day of the week.
To find contact events that occurred between Monday and Friday, enter Weekday([ContactDateTime]) in an empty column on the Field line and enter Between 2 And 6 as the criterion for that field.
Returns a value from 0 through 23 for the hour of the day.
To find contact events that happened before noon, enter Hour([ContactDateTime]) in an empty column on the Field line and enter <12 as the criterion for that field.
DateAdd (interval, amount, date)
Adds an amount in the interval you specify to a date/time value.
To find contact events that occurred more than six months ago, enter <DateAdd("m", −6, Date()) as the criterion under ContactDateTime. (See also the Date function below.)
DatePart (interval, date)
Returns a portion of the date or time, depending on the interval code you supply. Useful interval codes are "q" for quarter of the year (1 through 4) and "ww" for week of the year (1 through 53).
To find contact events in the second quarter, enter DatePartf("q", [ContactDateTime]) in an empty column on the Field line, and enter 2. as the criterion for that field.
Returns the current system date.
To select contact events that happened more than 30 days ago, enter <(Date() −30) as the criterion under ContactDateTime.
For additional useful functions, see Article 4, “Visual Basic Function Reference,” on the companion CD.
Every field must have a name. By default, the name of a simple field in a query is the name of the field from the source table. However, when you create a new field using an expression, the expression doesn’t have a name unless you or Access assigns one. You have seen that when you create an expression in the Field row of the design grid, Access adds a prefix such as Exprl followed by a colon-that is the name that Access is assigning to your expression. Remember, the column heading for the field is, by default, the field name unless you specify a different caption property setting. As you know, you can assign or change a caption for a field in a query by using the field’s property sheet.
In the world of tables and queries, every field-even calculated ones-must have a name. When you create a field in a table, you give it a name. When you use a table in a query and include a field from the table in the query output, the name of the field output by the query is the same as the field name in the table. If you create a calculated field in a query, you must assign a name to that field. If you don’t, Access assigns an ugly ExprN name for you. But you can override this and assign your own field name to expressions. You can also override the default field name for a simple field with another name. When you use a query in another query or a form or report, or you open a query as a recordset in Visual Basic, you use the field name to indicate which field you want to fetch from the query.
You can also define a Caption property for a field. When you do that, what you put in the caption becomes the external label for the field. You’ll see the caption in column headings in Datasheet view. Later, when you begin to work with forms and reports, you’ll find that the caption becomes the default label for the field. If you don’t define a caption, Access shows you the field name instead.
You can change or assign field names that will appear in the recordset of a query. This feature is particularly useful when you’ve calculated a value in the query that you’ll use in a form, a report, or another query. In the queries shown in Figures 7–15, 7–17, and 7–19, you calculated a value and Access assigned a temporary field name. You can replace this name with something more meaningful. For example, in the first query you might want to use something like FullName and CityStateZip. In the second query, RecalculatedCharge might be appropriate. To change a name generated by Access, replace ExprN with the name you want in the Field row in the query design grid. To assign a new name to a field, place the insertion point at the beginning of the field specification and insert the new name followed by a colon. Figure 7–25 shows the first query with the field names changed.
Figure 7–25: You can change the Exprl and Expr2field names shown in Figure 7–16 to display more meaningful field names.
Note that we could have made the column headings you see even more readable by also assigning a caption to these fields via the field’s property sheet. We might have chosen something like Person Name for the first field and City-State-Zip for the second field. Keep in mind that setting the caption does not change the actual name of the field when you use the query in a form, a report, or Visual Basic code.
Normally, Access 2007 displays the rows in your recordset in the order in which they’re retrieved from the database. You can add sorting information to determine the sequence of the data in a query. Click in the Sort row for the field you want to sort on, click the arrow in this row, and then select Ascending or Descending from the list. In the example shown in Figure 7–26, the query results are to be sorted in descending order based on the calculated NewTotalCharge field. (Note that we have given the calculated field a field name.) The recordset will list the most expensive reservations first. The resulting Datasheet view is shown in Figure 7–27. You can find this query saved as qryXmplChargeCalcSorted in the HousingDataCopy.accdb sample database.
Figure 7–26: Access sorts the query results on the NewTotalCharge field in descending order.
Figure 7–27: Datasheet view shows the recordset of the query shown in Figure 7–25 sorted on the NewTotalCharge field.
|Inside Out-Why Specifying Sort Criteria Is Important|| |
When Access 2007 solves a query, it tries to do it in the most efficient way. When you first construct and run a query, Access might return the records in the sequence you expect (for example, in primary key sequence of the table). However, if you want to be sure Access always returns rows in this order, you must specify sort criteria. As you later add and remove rows in your table, Access might decide that fetching rows in a different sequence might be faster, which, in the absence of sorting criteria, might result in a different row sequence than you intended.
You can also sort on multiple fields. Access honors your sorting criteria from left to right in the design grid. If, for example, you want to sort by FacilityID ascending and then by NewTotalCharge descending, you should include the FacilityID field to the left of the NewTotalCharge field. If the additional field you want to sort is already in the design grid but in the wrong location, click the column selector box (the tinted box above the field row) to select the entire column and then click the selector box again and drag the field to its new location. If you want the field that is out of position to still appear where you originally placed it, add the field to the design grid again in the correct sorting sequence, clear the Show check box (you don’t want two copies of the field displayed), and set the Sort specification. Figure 7–28 shows the query shown in Figure 7–26 modified to sort first by FacilityID and then by NewTotalCharge, but leave FacilityID displayed after ReservationID. We saved this query in the HousingDataCopy.accdb sample database as qxmplChargeCalcSortedTwo.
Figure 7–28: This example sorts on two fields while maintaining the original field sequence in the query output.
|Inside Out-A Reminder: Why Lookup Properties Can Be Confusing|| |
If you open the datasheet of qxmplChargeCalcSortedTwo and scroll down in the recordset, you’ll find the Facility column sorted Main Campus Housing A, Main Campus Housing B, South Campus Housing C, and North Satellite Housing D. Why does South appear before North if the values are supposed to be sorted in ascending order? Remember that in Chapter 5 we warned you about Lookup properties confusing the display you see. The information you’re seeing in the datasheet comes from the Lookup defined on the FacilityID column in tblReservations-you’re seeing the related facility name from tblFacilities. However, the actual value of FacilityID is a number. You can click on the FacilityID column, open the field’s property sheet, click the Lookup tab, and set the Display Control property to Text Box to see the actual number value. When you do this and look at the datasheet again, you’ll see that the values are sorted correctly.