The Group By aggregate function aggregates all the records in the specified field into unique groups. Here are a few things to keep in mind when using the Group By aggregate function.

To illustrate the last bullet point, create a query that looks similar to the one shown in Figure 3-7. This query will count all the transactions logged in the "200701" Period.

Figure 3.7. This query returns only one line showing total records for the 200701 period.

Now return to the Query Design view and add ProductID, as shown here in Figure 3-8. This time, Access treats each combination of Period and Product Number as a unique item. Each combination is grouped before the records in each group are counted. The benefit here is that you have added a dimension to your analysis. Not only do you know how many transactions per ProductID were logged in 200701, but if you add all the transactions, you will get an accurate count of the total number of transactions logged in 200701.

Figure 3.8. This query results in a few more records, but if you add the counts in each group, they will total to 503.

These aggregate functions all perform mathematical calculations against the records in your selected field. It is important to note that these functions exclude any records set to null. In other words, these aggregate functions ignore empty cells.

Unlike other aggregate functions, these functions evaluate all the records in the designated field or grouping and return a single value from the group.

One of the steadfast rules of aggregate queries is that every field must have an aggregation performed against it. However, there will be situations where you will have to use a field as a utility. That is, use a field to simply perform a calculation or apply a filter. These fields are a means to get to the final analysis you are looking for, rather than part of the final analysis. In these situations, you will use the Expression function or the Where clause. The Expression function and the Where clause are unique in that they don't perform any grouping action per se.

The Expression Aggregate Function

This function is generally applied when you are utilizing custom calculations or other functions in an aggregate query. Expression tells Access to perform the designated custom calculation on each individual record or group separately.

To use this function, you create a query in Design view that looks like the one shown in Figure 3-9.

Note

Note that you are using two aliases in this query: "Revenue" for the LineTotal field and "Cost" for the custom calculation defined here. Using an alias of "Revenue" gives the sum of LineTotal a user-friendly name.

Now you can use [Revenue] to represent the sum of LineTotal in your custom calculation. The Expression aggregate function ties it all together by telling Access that [Revenue]*.33 will be performed against the resulting sum of LineTotal for each individual Period group. Running this query will return the total Revenue and Cost for each Period group.

Figure 3.9. The Expression aggregate function allows you to perform the designated custom calculation on each Period group separately.

The Where Clause

The Where clause allows you to apply a criterion to a field that is not included in your aggregate query, effectively applying a filter to your analysis. To see the Where clause in action, create a query in Design view that looks like the one shown in Figure 3-10.

Figure 3.10. Running this query causes an error message because you have no aggregation defined for Period.

As you can see in the Total row, you are grouping ProductID and summing LineTotal. However, Period has no aggregation selected, because you only want to use it to filter out one specific period. You have entered "200701" in the criteria for Period. If you run this query as is, you will get the following error message: "You tried to execute a query that does not include the specified expression Dim.Dates.Period="200701" as part of an aggregate function."

To run this query successfully, click the Totals dropdown for the Period field and select "Where" from the selection list. At this point, your query should look similar to the one shown here in Figure 3-11. With the Where clause specified, you can successfully run this query.

Note

Here is one final note about the Where clause. Notice in Figure 3-9 that the check box in the "Show" row has no check in it for the Period. This is because fields tagged with the Where clause cannot be shown in an aggregate query. Therefore, this checkbox must remain empty. If you place a check in the "Show" checkbox of a field with a Where clause, you will get an error message stating that you cannot display the field for which you entered Where in the Total row.

Figure 3.11. Adding a Where remedies the error and allows you to run the query.

In simple terms, if you create a query and would like to capture the results of your query in its own table, you can use a Make-Table query to create a hard table with your query results. You can then use your new table in some other analytical process.

When you build a Make-Table query, you must specify the name of the table that will be created when the Make-Table query is run. If you give the new table the same name as an existing table, the existing table will be overwritten. If you accidentally write over another table with a Make-Table query, you will not be able to recover the old table. Be sure that you name the tables created by your Make-Table queries carefully to avoid overwriting existing information.

The data in a table made by a Make-Table query is not linked to its source data. This means that the data in your new table is not updated when data in the original table is changed.

You have been asked to provide the marketing department with a list of customers along with information about each customer's sales history. To meet this task, follow these steps:

When your query has completed running, you will find a new table called SalesHistory in your Table objects.

TRICKS OF THE TRADE: TURNING AGGREGATE QUERY RESULTS INTO HARD DATA

The results of aggregate queries are inherently not updatable. This means you will not be able to edit any of the records returned from an aggregate query. This is because the relationship between the aggregated data and the underlying data only goes one way. That is, changing the aggregated data will not change the underlying data.

However, you can change your aggregate query into a Make- Table query and create a static table with your aggregate query's results. With your new hard table, you will be able to edit at your heart's content.

To illustrate how this works, create the query shown in Figure 3-15 in design view. Then, change the query into a Make-Table query, enter a name for your new table, and run it.

Figure 3.15. Running this query as a Make-Table will allow you to edit the aggregate query's results.

Tip

Notice that in the previous figure you defined a column with an alias of Customer. After the alias, you simply entered All in quotes. When you run the query, you will notice that your new table has a column named Customer in which the value for every record is All. This example illustrates that when running a Make-Table query, you can create your own columns on the fly by simply creating an alias for the column and defining its contents after the colon.

Although you can delete records by hand, there are situations where using a delete query is more efficient. For example, if you have a very large dataset, a delete query will delete your records faster than a manual delete. In addition, if you want to delete certain records based on several complex criteria, you will want to utilize a delete query. Finally, if you need to delete records from one table based on a comparison with another table, a delete query is the way to go.

As with all other action queries, you will not be able to undo the effects of a delete query. However, a delete query is much more dangerous than the other action queries because there is no way to remedy accidentally deleted data.

Given that deleted data cannot be recovered, you should get into the habit of taking one of the following actions to avoid a fatal error.

The marketing department has informed you that the SalesHistory table you gave them includes records that they do not need. They want you to delete all history before the 200806 Period. To meet this demand, do the following:

TRICKS OF THE TRADE: DELETING RECORDS FROM ONE TABLE BASED ON RECORDS FROM ANOTHER

You will encounter many analyses where you will have to delete records from one table based on records from another. This is relatively easy to do. However, many users get stuck on this because of one simple mistake.

The query in Figure 3-18 looks simple enough. It tells Access to delete all records from the Customer_ListA table if the customer is found in the Customer_ListB table.

If you run this query, Access throws up the message shown in Figure 3-19. This message asks you to specify which table contains the records you want to delete.

This message stumps many Access users. Unfortunately, this message does not clearly state what you need to do to remedy the mistake. Nevertheless, the remedy is a simple one:

1. Clear the query grid by deleting the CustomerName field.

2. Double-click the asterisk (*) in the Customer_ListA table. This explicitly tells Access that the Customer_ListA table contains the records you want to delete. Figure 3-20 demonstrates the correct way to build this query.

Figure 3.18. This delete query seems as though it should run fine, but there is something wrong.

Figure 3.19. Access does not know which table you want the records deleted from.

Figure 3.20. This is the correct way to build this query.

With an append query, you are essentially copying records from one table or query and adding them to the end of another table. In that light, append queries come in handy when you need to transfer large datasets from one table to another. For example, if you have a table called Old Transactions where you archive your transaction records, you can add the latest batch of transactions from the New Transactions table by using an append query.

The primary hazard of an append query is losing records during the append process. That is, not all of the records you think you are appending to a table actually make it to your table. There are generally two reasons why records can get lost during an append process.

Another hazard of an append query is that the query may simply fail to run. There are two reasons why an append query might fail:

Luckily, Access will clearly warn you if you are about to cause any of these errors. Figure 3-21 demonstrates this warning message.

Figure 3.21. The warning message tells you that you will lose records during the append process.

As you can see, this warning message tells you that you cannot append all the records due to errors. It goes on to tell you exactly how many records will not be appended because of each error. In this case, 5979 records will not be appended because of key violations. You have the option of clicking Yes or No. The Yes button ignores the warning and appends all records minus the two with the errors. The No button cancels the query, which means that no records will be appended.

Keep in mind that as with all other action queries, you will not be able to undo your append query once you have pulled the trigger.

The marketing department contacts you and tells you that they made a mistake. They actually need all the sales history for the 2008 Fiscal year. So they need periods 200801 thru 200805 added back to the SalesHistory report.

To meet this demand:

The primary reason to use update queries is to save time. There is no easier way to edit large amounts of data at one time than with an update query. For example, imagine you have a Customers table that includes the customer's zip code. If the zip code 32750 has been changed to 32751, you can easily update your Customers table to replace 32750 with 32751.

As is the case with all other action queries, you must always take precautions to ensure that you are not in a situation where you cannot undo the effects of an update query. Get into the habit of taking one of the following actions in order to give yourself a way back to the original data in the event of a misstep.

You have just received word that the zip code for all customers in the 33605 zip code has been changed to 33606. To keep your database accurate, you must update all the 33605 zip codes in your Dim_Customers table to 33606.

TRICKS OF THE TRADE: USING EXPRESSIONS IN YOUR UPDATE QUERIES

You will come across situations where you will have to execute record-specific updates. That is, you are not updating multiple records with one specific value; instead, you are updating each record based on an expression.

To demonstrate this concept, start a query in Design view based on the SalesHistory table you created in the "Make-Table Queries" section of this chapter. Build your query like the one shown in Figure 3-32.

Figure 3.32. This update query uses an expression to make record-specific updates.

This query is telling Access to update the Period to concatenate the text "PD " (note the space after PD) with the value in the Period field.

After you run this query, all the values in the Period field will have a prefix of PD. For example, 200801 will be updated to PD 200801.

Remember, this is just one example of an expression you can use to update your records. You can use almost any expression with an update query, ranging from mathematical functions to string operations.

Not all datasets are updatable. That is, you may have a dataset that Access cannot update for one reason or another. If your update query fails, you will get one of these messages: "Operation must use an updatable query" or "This Recordset is not updateable."

Your update query will fail if any one of the following applies:

As useful as crosstab queries can be, you may find that you need to apply some of your own customizations in order to get the results you need. In this section, you will explore a few of the ways to customize your crosstab queries to meet your needs.

Figure 3.49. The crosstab displays all regions as columns in alphabetical order.

This may be fine in most situations, but if your company headquarters is in California, the executive management may naturally want to see the West region first. You can explicitly specify the column order of a crosstab query by changing the Column Headings attribute in the Query Properties.

To get to the Column Headings attribute:

Figure 3.50. The Column Headings attribute is set to have the column read in this order: West, Canada, Midwest, North, Northeast, South, Southeast, and Southwest.

When working with the Column Headings attribute, keep the following in mind: