Most calculations typically consist of hard-coded numbers or constants. A constant is a static value that does not change. For example, in the expression [List_Price]*1.1, 1.1 is a constant; the value of 1.1 will never change. Figure 5-1 demonstrates how a constant can be used in an expression within a query.

Figure 5.1. In this query, you are using a constant to calculate a 10 percent price increase.

In this example, you are building a query that will analyze how the current price for each product compares to the same price with a 10 percent increase. The expression, entered under the alias "Increase," will multiply the List_Price field of each record with a constant value of 1.1, calculating a price that is 10 percent over the original value in the List_Price field.

Not all your calculations require you to specify a constant. In fact, many of the mathematical operations you will carry out are performed on data that already resides in fields within your dataset. You can perform calculations using any fields formatted as numbers or currency.

For instance, in the query shown in Figure 5-2, you are not using any constants. Instead, your calculation is executed using the values in each record of the dataset. This is similar to referencing cell values in an Excel formula.

Figure 5.2. In this query, you are using two fields in a Dollar Variance calculation.

Using the result of an aggregation as an expression in a calculation allows you to perform multiple analytical steps in one query. In the example in Figure 5-3, you are running an aggregate query. This query executes in the following order.

Keep in mind that you are not limited to one calculation per query. In fact, you can use the results of one calculation as an expression in another calculation. Figure 5-4 illustrates this concept.

Figure 5.3. In this query, you are using the aggregation results for each market as expressions in your calculation.

Figure 5.4. This query uses the results of one calculation as an expression in another.

In this query, you are first calculating an adjusted forecast and then using the results of that calculation in another calculation that returns the variance of Actual versus Adjusted Forecast.

Look at the query in Figure 5-5. The calculation in this query returns a number with a fractional part. That is, it returns a number that contains a decimal point followed by many trailing digits. You want to return a round number, however, making the resulting dataset easier to read.

To force the results of your calculation into an integer, you can use the Int function. Int is a mathematical function that removes the fractional part of a number and returns the resulting integer. This function takes one argument, a number. However, instead of hard-coding a number into this function, you can use your calculation as the argument. Figure 5-6 demonstrates this concept.

Figure 5.5. The results of this calculation are difficult to read because they are fractional numbers that have many digits trailing a decimal point. Forcing the results into round numbers makes for easier reading.

Figure 5.6. You can use your calculation as the argument in the Int function, allowing you to remove the fractional part of the resulting data.

You might remember from your algebra days that when working with a complex equation executing multiple mathematical operations, the equation does not necessarily evaluate left to right. Some operations have precedence over others and therefore must occur first. The Access environment has similar rules regarding the order of operator precedence. When you are using expressions and calculations that involve several operations, each operation is evaluated and resolved in a predetermined order. It is important to know the order of operator precedence in Access. An incorrectly built expression may cause errors in your analysis.

The order of operations for Access is as follows:

How can understanding the order of operations ensure that you avoid analytical errors? Consider this basic example: The correct answer to the calculation (20+30)*4 is 200. However, if you leave off the parentheses (as in 20+30*4), Access will perform the calculation like this: 30*4 = 120 + 20 = 140. The order of operator precedence mandates that Access perform multiplication before subtraction. Therefore, entering 20+30*4 gives you the wrong answer. Because the order of operator precedence in Access mandates that all operations in parentheses be evaluated first, placing 20+30 inside parentheses ensures the correct answer.

A Null value represents the absence of any value. When you see a data item in an Access table that is empty or has no information in it, it is considered Null.

The concept of a Null value causing errors in a calculation might initially seem strange to Excel power users. In Excel, if there is a Null value within a column of numbers, the column can still be properly evaluated because Excel simply reads the Null value as zero. This is not the case in Access. If Access encounters a Null value, it does not assume that the Null value represents zero. Instead, it immediately returns a Null value as the answer. To illustrate this behavior, build the query shown in Figure 5-14.

Figure 5.14. To demonstrate how Null values can cause calculation errors, build this query in Design view.

Run the query, and you will see the results shown in Figure 5-15. Notice that the Variance calculation for the first record does not show the expected results; instead, it shows a Null value. This is because the forecast value for that record is a Null value.

Looking at Figure 5-15, you can imagine how a Null calculation error can wreak havoc on your analysis, especially if you have an involved analytical process. Furthermore, Null calculation errors can be difficult to identify and fix. This is a good place to remind you that you should rarely use Null values in your tables. Instead, you should use a logical value that represents "no data" (for example, 0, "NA," or "Undefined").

That being said, you can avoid Null calculation errors by using the Nz function, which enables you to convert any Null value to a value you specify.

Figure 5.15. As you can see, when any variable in your calculation is Null, the resulting answer is a Null value.

Nz(variant, valueifnull)

Armed with this new information, you can adjust the query in Figure 5-14 to utilize the Nz function. Since the problem field is Forecast, pass the Forecast field through the Nz function. Figure 5-16 shows the adjusted query.

Figure 5.16. Pass the Forecast field through the Nz function to convert Null values to zero.

As you can see in Figure 5-17, the first five records now show a Variance value even though the values in the Forecast field are Null. Note that the NZ function does not physically place a zero in the Null values. The NZ function merely tells access to treat the Nulls as zeros when calculating the Variance field.

Figure 5.17. The first five records now show a Variance value.

Basic syntax mistakes in your calculation expressions can also lead to errors. Follow these guidelines to avoid slip-ups:

Date is a built-in Access function that returns the current system date—in other words, today's date. With this versatile function, you never have to hard-code today's date in your calculations. That is to say, you can create dynamic calculations that use the current system date as a variable, giving you a different result every day. In this section, you look at some of the ways you can leverage the Date function to enhance your analysis.

Finding the Number of Days Between Today and a Past Date

Imagine that you have to calculate aged receivables. You need to know the current date to determine how overdue the receivables are. Of course, you can type the current date by hand, but that can be cumbersome and prone to error.

To demonstrate how to use the Date function, create the query shown in Figure 5-20.

Figure 5.20. This query returns the number of days between today's date and each order date.

Using the Date Function in a Criteria Expression

You can use the Date function to filter out records by including the function in a criteria expression. For example, the query shown in Figure 5-21 returns all records with an order date older than 90 days.

Figure 5.21. No matter what day it is today, this query will return all orders older than 90 days.

Calculating an Age in Years Using the Date Function

Imagine that you have been asked to provide a list of account managers, along with the number of years they have been employed by the company. To accomplish this task, you must calculate the difference between today's date and each manager's hire date.

The first step is to build the query shown in Figure 5-22.

Figure 5.22. You are calculating the difference between today's date and each manager's hire date.

When you look at the query results, shown in Figure 5-23, you realize that the calculation results in the number of days between the two dates, not the number of years.

To fix this, switch back to Design view and divide your calculation by 365.25. Why 365.25? That is the average number of days in a year when you account for leap years. Figure 5-24 demonstrates this change. Note that your original calculation is now wrapped in parentheses to avoid errors due to order of operator precedence.

Figure 5.23. This dataset shows the number of days, not the number of years.

Figure 5.24. Divide your original calculation by 365.25 to convert the answer to years.

A look at the results, shown in Figure 5-25, proves that you are now returning the number of years. All that is left to do is to strip away the fractional portion of the date using the Int function. Why the Int function? The Int function does not round the year up or down; it merely converts the number to a readable integer.

Tip

Want to actually round the number of years? You can simply wrap your date calculation in the Round function. The Round function is highlighted in Appendix A of this book.

Wrapping your calculation in the Int function ensures that your answer is a clean year without fractions (see Figure 5-26).

Figure 5.25. Your query now returns years, but you must strip away the fractional portion of your answer.

Figure 5.26. Running this query returns the number of years each employee has been with the company.

You can calculate a person's age by using the same method. Simply replace the hire date with the date of birth.

The Year, Month, Day, and Weekday Functions

The Year, Month, Day, and Weekday functions are used to return an integer that represents their respective parts of a date. These functions require a valid date as an argument. For example:

• Year(#12/31/1997#) returns 1997.

• Month(#12/31/1997#) returns 12.

• Day(#12/31/1997#) returns 31.

• Weekday(#12/31/1997#) returns 4.

Note

The Weekday function returns the day of the week from a date. In Access, weekdays are numbered from 1 to 7, starting with Sunday. Therefore, if the Weekday function returns 4, the day of the week represented is Wednesday.

Figure 5-27 demonstrates how you use these functions in a query environment.

Figure 5.27. The Year, Month, Day, and Weekday functions enable you parse out a part of a date.

TRICKS OF THE TRADE: AN EASY WAY TO QUERY ONLY WORKDAYS

Suppose that you have been asked to provide the total amount of revenue generated by product but only revenue generated during workdays in calendar year 2008. Workdays are defined as days that are not weekends or holidays.

The first thing you need to accomplish this task is a table that lists all the company holidays in 2008. Figure 5-28 shows that a Holidays table can be nothing more than one field listing all the dates that constitute a holiday.

Figure 5.28. In this database, the CY_2008_Holidays table contains a column called Holidays that lists all the dates that counted as company holidays.

Once you have established a table that contains all the company holidays, it's time to build the query. Figure 5-29 demonstrates how to build a query that filters out non-workdays.

Figure 5.29. Using the CY_2008_Holidays table and a simple Weekday function, you can filter out non-workdays.

Take a moment to analyze what is going on in Figure 5-29.

1. You create a left join from TransactionMaster to CY_2008_Holidays to tell Access that you want all the records from TransactionMaster.

2. You then use the Is Null criteria under Holidays. This limits the TransactionMaster to only those dates that do not match any of the holidays listed in the CY_2008_Holidays table.

3. You then create a field called Day Check where you are returning the weekday of every service date in the TransactionMaster.

4. You filter the newly created Day Check field to filter out those weekdays that represent Saturdays and Sundays (1 and 7).

5. Finally, you filter for only those records whose order date falls in the year 2008.

A common analysis for many organizations is to determine on which date a certain benchmark is reached. For example, most businesses want to know on what date an order becomes 30 days past due. Furthermore, on what date should the customer receive a warning letter? An easy way to perform these types of analyses is to use the DateAdd function. The DateAdd function returns a date to which a specified interval has been added.

The query shown in Figure 5-30 illustrates how the DateAdd function determines the exact date a specific benchmark is reached. You are creating two new fields with this query: Warning and Overdue. The DateAdd function in the Warning field returns the date that is three weeks from the original order date. The DateAdd function in the Overdue field returns the date that is one month from the original order date.

Figure 5.30. This query gives you the original order date, the date you should send a warning letter, and the date that the order is 30 days overdue.

Why would you need to group your dates into quarters? Most databases store dates rather than quarter designations. Therefore, if you want to analyze data on a quarter-over-quarter basis, you have to convert dates into quarters. Surprisingly, there is no Date/Time function that allows you to group dates into quarters. There is, however, the Format function.

The Format function belongs to the Text category of functions and allows you to convert a variant into a string based on formatting instructions. From the perspective of analyzing dates, you can pass several valid instructions to a Format function.

The query in Figure 5-31 shows how you group all the order dates into quarters and then group the quarters to get a sum of revenue for each quarter.

Figure 5.31. You can group dates into quarters by using the Format function.

If you want to get fancy, you can insert the Format function in a crosstab query, using Quarter as the column (see Figure 5-32).

Figure 5.32. You can also use the Format function in a crosstab query.

As you can see in Figure 5-33, the resulting dataset is a clean look at revenue by product, by quarter.

Figure 5.33. You have successfully grouped your dates into quarters.

The DateSerial function allows you to construct a date value by combining given year, month, and day components. This function is perfect for converting disparate strings (that together represent a date) into an actual date.

The wonderful thing about the DateSerial function is that you can pass other date expressions as arguments. For example, pretend that the system date on your PC is August 1, 2005. For those of you who have been paying attention, this means that the Date function would return August 1, 2005. That being the case, the following expression would return August 1, 2005:

DateSerial ( Year(Date()), Month(Date()), Day(Date()) )

So how is this helpful? Well, now you can put a few twists on this by performing calculations on the expressions within the DateSerial function. Consider some of the possibilities: