Creating an aggregate query

To get a firm understanding of what an aggregate query does, consider the following scenario: You've just been asked to provide the sum of total revenue by period. In response to this request, start a query in Design view and bring in the Dim_Dates.Period and Dim_Transactions.LineTotal fields, as shown in Figure 10.1. If you run this query as is, you'll get every record in your dataset instead of the summary you need.

In order to get a summary of revenue by period, you'll need to activate Totals in your design grid. To do this, go to the Ribbon and select the Design tab, and then click the Totals button. As you can see in Figure 10.2, after you've activated Totals in your design grid, you'll see a new row in your grid called Total. The Total row tells Access which aggregate function to use when performing aggregation on the specified fields.

Notice that the Total row contains the words Group By under each field in your grid. This means that all similar records in a field will be grouped to provide you with a unique data item. We'll cover the different aggregate functions in depth later in this chapter.

The idea here is to adjust the aggregate functions in the Total row to correspond with the analysis you're trying perform. In this scenario, you need to group all the periods in your dataset, and then sum the revenue in each period. Therefore, you'll need to use the Group By aggregate function for the Period field, and the Sum aggregate function for the LineTotal field.

Since the default selection for Totals is the Group By function, no change is needed for the Period field. However, you'll need to change the aggregate function for the LineTotal field from Group By to Sum. This tells Access that you want to sum the revenue figures in the LineTotal field, not group them. To change the aggregate function, simply click the Totals drop-down list under the LineTotal field, shown in Figure 10.3, and select Sum. At this point, you can run your query.

As you can see in Figure 10.4, the resulting table gives a summary of your dataset, showing total revenue by period.

About aggregate functions

In the example shown in Figure 10.3, you selected the Sum aggregate function from the Totals drop-down list. Obviously, you could've selected any one of the 12 functions available. Indeed, you'll undoubtedly come across analyses where you'll have to use a few of the other functions available to you. So, it's important to know what each one of these aggregate functions means for your data analysis.

Group By

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:

• Access performs the Group By function in your aggregate query before any other aggregation. If you're performing a Group By along with another aggregate function, the Group By function will be performed first. The example shown in Figure 10.4 illustrates this concept. Access groups the Period field before summing the LineTotal field.
• Access sorts each group-by field in ascending order. Unless otherwise specified, any field tagged as a group-by field will be sorted in ascending order. If your query has multiple group-by fields, each field will be sorted in ascending order starting with the leftmost field.
• Access treats multiple group-by fields as one unique item. To illustrate this point, create a query that looks similar to the one shown in Figure 10.5. This query counts all the transactions that were logged in the 201201 period.

Now return to the Query Design view and add ProductID, as shown here in Figure 10.6. 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've added a dimension to your analysis. Not only do you know how many transactions per ProductID were logged in 201201, but if you add up all the transactions, you'll get an accurate count of the total number of transactions logged in 201201.

Expression, Where

One of the steadfast rules of aggregate queries is that every field must have an aggregation performed against it. However, in some situations you'll 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're looking for, rather than part of the final analysis. In these situations, you'll 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.

• Expression: The Expression aggregate function is generally applied when you're 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.
• Where: 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 Expression aggregate function in action, create a query in Design view that looks like the one shown in Figure 10.7. Note that you're 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.

To see the Where clause in action, create a query in Design view that looks like the one shown in Figure 10.8. As you can see in the Total row, you're 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've entered 201201 in the criteria for Period. If you run this query as is, you'll get the following error message:
You tried to execute a query that does not include the specified expression 'Period' as part of an aggregate function.

To run this query successfully, click the Totals drop-down list for the Period field and select Where. At this point, your query should look similar to the one shown here in Figure 10.9. With the Where clause specified, you can successfully run this query.

Make-table queries

A make-table query creates a new table consisting of data from an existing table. The table that is created consists of records that have met the definitions and criteria of the make-table query.

In simple terms, if you create a query, and you want 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. Then you can use your new table in some other analytical process.

Let's say you've been asked to provide the marketing department with a list of customers, along with information on each customer's sales history. A make-table query will get you the data you need. To create a make-table query, follow these steps:

1. Create a query in the Query Design view that looks similar to the one shown in Figure 10.10.

   

2. Select the Design tab of the Ribbon, and then click the Make Table button. The Make Table dialog box (shown in Figure 10.11) appears.

   

3. In the Table Name field, enter the name you want to give to your new table. For this example, type SalesHistory. Be sure not to enter the name of a table that already exists in your database, because it'll be overwritten.
4. Click OK to close the dialog box, and then click the Run command to run your query. Access throws up the warning message shown in Figure 10.12, letting you know that you won't be able to undo this action.

   

5. Click Yes to confirm and create your new table.

   When your query has finished running, you'll find a new table called SalesHistory in your Table objects.

Delete queries

A delete query deletes records from a table based on the definitions and criteria you specify. That is, a delete query affects a group of records that meet a specified criterion that you apply.

Although you can delete records by hand, in some situations using a delete query is more efficient. For example, if you have a very large dataset, a delete query deletes your records faster than a manual delete can. In addition, if you want to delete certain records based on several complex criteria, you'll want to use a delete query. Finally, if you need to delete records from one table based on a comparison to another table, a delete query is the way to go.

Given the fact that deleted data can't be recovered, get in the habit of taking one of the following actions in order to avoid a fatal error:

• Run a select query to display the records you're about to delete. Then review the records to confirm that these records are indeed the ones you want to delete, and then run the query as a delete query.
• Run a select query to display the records you're about to delete. Then change the query into a make-table query. Run the make-table query to make a backup of the data you're about to delete. Finally, run the query again as a delete query to delete the records.
• Make a backup of your database before running your delete query.

Now let's say the marketing department has informed you that the SalesHistory table you gave them includes records that they don't need. They want you to delete all history before the 201206 Period. A delete query based on the SalesHistory table you created a moment ago will accomplish this task. To create a delete query, follow these steps:

1. Bring in the Period field and enter <PD201206 in the Criteria row. Access will automatically add quotations around your criteria. Your design grid should look like the one shown in Figure 10.13.

   

2. Perform a test by running the query.
3. Review the records that are returned, and take note that 2,781 records meet your criteria. You now know that 2,781 records will be deleted if you run a delete query based on these query definitions.
4. Return to Design view.
5. Select the Design tab of the Ribbon, and then click the Delete button.
6. Run your query again. Access throws up the message shown in Figure 10.14, telling you that you're about to delete 2,781 rows of data and warning you that you won't be able to undo this action. This is the number you were expecting to see, because the test you ran earlier returned 2,781 records.

   

7. Because everything checks out, click Yes to confirm and delete the records.

Append queries

An append query appends records to a table based on the definitions and criteria you specify in your query. In other words, with an append query, you can add the results of your query to the end of a table, effectively adding rows to the table.

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

There are generally two reasons why records can get lost during an append process:

• Type conversion failure: This failure occurs when the character type of the source data doesn't match that of the destination table column. For example, imagine that you have a table with a field called Cost. Your Cost field is set as a Text character type because you have some entries that are tagged as TBD (to be determined), because you don't know the cost yet. If you try to append that field to another table whose Cost field is set as a Number character type, all the entries that have TBD will be changed to null, effectively deleting your TBD tag.
• Key violation: This violation occurs when you're trying to append duplicate records to a field in the destination table that is set as a primary key or is indexed as No Duplicates. In other words, when you have a field that prohibits duplicates, Access won't let you append any record that is a duplicate of an existing record in that field.

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

• Lock violation: This violation occurs when the destination table is open in Design view or is opened by another user on the network.
• Validation rule violation: This violation occurs when a field in the destination table has one of the following property settings:
  • Required field is set to Yes: If a field in the destination table has been set to Required Yes and you don't append data to this field, your append query will fail.
  • Allow Zero Length is set to No: If a field in the destination table has been set to Zero Length No and you don't append data to this field, your append query will fail.
  • Validation rule set to anything: If a field in the destination table has a validation rule and you break the rule with your append query, your append query will fail. For example, if you have a validation rule for the Cost field in your destination table set to >0, you can't append records with a quantity less than or equal to zero.

Luckily, Access will clearly warn you if you're about to cause any of these errors. Figure 10.15 demonstrates this warning message, which tells you that you can't append all the records due to errors. It also tells you exactly how many records won't be appended because of each error. In this case, 5,979 records won't be appended because of key violations. You have the option of clicking Yes or No. If you click Yes, the warning is ignored and all records are appended, minus the records with the errors. If you click No, the query will be canceled, which means that no records will be appended.

Let's say the marketing department tells you that they made a mistake—they actually need all the sales history for the 2012 fiscal year. So, they need periods 201201 thru 201205 added back to the SalesHistory report. An append query is in order.

In order to get them what they need, follow these steps:

1. Create a query in the Query Design view that looks similar to the one shown in Figure 10.16.

   

2. Select the Design tab of the Ribbon, and then click the Append button. The Append dialog box (shown in Figure 10.17) appears.

   

3. In the Table Name field, enter the name of the table to which you would like to append your query results. In this example, enter SalesHistory.
4. Once you've entered your destination table's name, click OK. Your query grid has a new row called Append To under the Sort row (see Figure 10.18). The idea is to select the name of the field in your destination table where you want to append the information resulting from your query. For example, the Append To row under the Period field shows the word Period. This means that the data in the Period field of this query will be appended to the Period field in the SalesHistory table.

   

5. Run your query. Access throws up a message, as shown in Figure 10.19, telling you that you're about to append 2,781 rows of data and warning you that you won't be able to undo this action.

   

6. Click Yes to confirm and append the records.

Update queries

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 customers' zip codes. If the zip code 32750 has been changed to 32751, you can easily update your Customers table to replace 32750 with 32751.

Let's say you've just received word that the zip code for all customers in the 33605 zip code has been changed to 33606. In order to keep your database accurate, you'll have to update all the 33605 zip codes in your Dim_Customers table to 33606. Here's how:

1. Create a query in the Query Design view that looks similar to the one shown in Figure 10.20.

   

2. Perform a test by running the query.
3. Review the records that are returned, and take note that six records meet your criteria. You now know that six records will be updated if you run an update query based on these query definitions.
4. Return to the Design view.
5. Select the Design tab of the Ribbon, and click the Update button. Your query grid now has a new row called Update To. The idea is to enter the value to which you would like to update the current data. In this scenario, shown in Figure 10.21, you want to update the zip code for the records you're selecting to 33606.

   

6. Run the query. Access throws up the message shown in Figure 10.22, telling you that you're about to update six rows of data and warning you that you won't be able to undo this action. This is the number you were expecting to see, because the test you ran earlier returned six records.

   

7. Since everything checks out, click Yes to confirm and update the records.

Creating a crosstab query using the Crosstab Query Wizard

To use the Crosstab Query Wizard to create a crosstab query, follow these steps:

1. Select the Create tab of the Ribbon and then click the Query Wizard button. The New Query dialog box, shown in Figure 10.24, appears.

   

2. Select Crosstab Query Wizard from the selection list, and then click OK.

   The first step in the Crosstab Query Wizard is to identify the data source you'll be using. As you can see in Figure 10.25, you can choose either a query or a table as your data source. In this example, you'll be using the Dim_Transactions table as your data source.

   

3. Select Dim_Transactions and then click the Next button.

   The next step is to identify the fields you want to use as the row headings.

4. Select the ProductID field and click the button with the > symbol on it to move it to the Selected Items list. The dialog box should look like Figure 10.26. Notice that the ProductID field is shown in the sample diagram at the bottom of the dialog box.

   

   You can select up to three fields to include in your crosstab query as row headings. Remember that Access treats each combination of headings as a unique item. That is, each combination is grouped before the records in each group are aggregated.

   The next step is to identify the field you want to use as the column heading for your crosstab query. Keep in mind that there can be only one column heading in your crosstab.

5. Select the OrderDate field from the field list. Notice in Figure 10.27 that the sample diagram at the bottom of the dialog box updates to show the OrderDate.

   

   If your Column Heading is a date field, as the OrderDate is in this example, you'll see the step shown in Figure 10.28. In this step, you'll have the option of specifying an interval to group your dates by.

   

6. Select Quarter and notice that the sample diagram at the bottom of the dialog box updates accordingly.

   You're almost done. In the second-to-last step, shown in Figure 10.29, you'll identify the field you want to aggregate and the function you want to use.

   

7. Select the LineTotal field from the Fields list, and then select Sum from the Functions list. Notice the Yes, Include Row Sums check box. This box is checked by default to ensure that your crosstab query includes a Total column that contains the sum total for each row. If you don't want this column, simply remove the check from the check box.

   If you look at the sample diagram at the bottom of the dialog box, you will get a good sense of what your final crosstab query will do. In this example, your crosstab will calculate the sum of the LineTotal field for each ProductID by Quarter.

   The final step, shown in Figure 10.30, is to name your crosstab query.

   
   

   

8. In this example, name your crosstab Product Summary by Quarter. After you name your query, you have the option of viewing your query or modifying the design.
9. In this case, you want to view your query results so simply click the Finish button.

In just a few clicks, you've created a powerful look at the revenue performance of each product by quarter (see Figure 10.31).

Creating a crosstab query manually

Although the Crosstab Query Wizard makes it easy to create a crosstab in just a few clicks, it does have limitations that may inhibit your data analysis efforts:

• You can select only one data source on which to base your crosstab. This means that if you need to crosstab data residing across multiple tables, you'll need to take extra steps to create a temporary query to use as your data source.
• There is no way to filter or limit your crosstab query with criteria from the Crosstab Query Wizard.
• You're limited to only three row headings.
• You can't explicitly define the order of your column headings from the Crosstab Query Wizard.

The good news is that you can create a crosstab query manually through the query design grid. Manually creating your crosstab query allows you greater flexibility in your analysis.

Customizing your crosstab queries

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, we explain a few of the ways you can customize your crosstab queries to meet your needs.

Defining criteria in a crosstab query

The ability to filter or limit your crosstab query is another benefit of creating crosstab queries manually. To define a filter for your crosstab query, simply enter the criteria as you normally would for any other aggregate query. Figure 10.34 demonstrates this concept.

Changing the sort order of your crosstab query column headings

By default, crosstab queries sort their column headings in alphabetical order. For example, the crosstab query in Figure 10.35 will produce a dataset where the column headings read this order: Canada, Midwest, North, Northeast, South, Southeast, Southwest, and West.

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 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:

1. Open the query in Design view.
2. Right-click in the gray area above the white query grid and select Properties. The Query Properties dialog box, shown in Figure 10.36, appears.

   

3. Enter the order in which you want to see the column headings by changing the Column Headings attribute.

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

• You must enter each column name in quotes and separate each column with commas.
• Accidentally misspelling a column name will result in that column being excluded from the crosstab results and a dummy column with the misspelled name being included with no data in it.
• Column names you enter into the Column Headings attribute will show up in the final results even if no data exists for that column.
• You must enter every column you want to include in your crosstab report. Excluding a column from the Column Headings attribute will exclude that column from the crosstab results.
• Clearing the Column Headings attribute will ensure that all columns are displayed in alphabetical order.

Normalizing your database design

Many users who are new to Access build one large, flat table and call it a database. This structure seems attractive because you don't have to deal with joins and you only have to reference one table when you build your queries. However, as the volume of data grows in a structure such as this one, query performance will take a nosedive.

When you normalize your database to take on a relational structure, you break up your data into several smaller tables. This has two effects:

• You inherently remove redundant data, giving your query less data to scan.
• You can query only the tables that contain the information you need, preventing you from scanning your entire database each time you run a query.

Using indexes on appropriate fields

Imagine that you have a file cabinet that contains 1,000 records that aren't alphabetized. How long do you think it would take you to pull out all the records that start with S? You would definitely have an easier time pulling out records in an alphabetized filing system. Indexing fields in an Access table is analogous to alphabetizing records in a file cabinet.

When you run a query in which you're sorting and filtering on a field that hasn't been indexed, Access has to scan and read the entire dataset before returning any results. As you can imagine, on large datasets this can take a very long time. By contrast, queries that sort and filter on fields that have been indexed run much more quickly because Access uses the index to check positions and restrictions.

You can create an index on a field in a table by going into the table's Design view and adjusting the Indexed property.

Now, before you go out and start creating an index on every field in your database, there is one caveat to indexing: Although indexes do speed up select queries dramatically, they significantly slow down action queries such as update, delete, and append. This is because when you run an action query on indexed fields, Access has to update each index in addition to changing the actual table. To that end, it's important that you limit the fields that you index.

A best practice is to limit your indexes to the following types of fields:

• Fields in which you'll routinely filter values using criteria
• Fields that you anticipate using as joins on other tables
• Fields in which you anticipate sorting values regularly

Optimizing by improving query design

You'd be surprised how a few simple choices in query design can improve the performance of your queries. Take a moment to review some of the actions you can take to speed up your queries and optimize your analytical processes:

• Avoid sorting or filtering fields that aren't indexed.
• Avoid building queries that select * from a table. For example, SELECT * FROM MyTable forces Access to look up the field names from the system tables every time the query is run.
• When creating a totals query, include only the fields needed to achieve the query's goal. The more fields you include in the GROUP BY clause, the longer the query will take to execute.
• Sometimes you need to include fields in your query design only to set criteria against them. Fields that aren't needed in the final results should be set to “not shown.” In other words, remove the check from the check box in the Show row of the query design grid.
• Avoid using open-ended ranges such as > or <. Instead, use the Between…And statement.
• Use smaller temporary tables in your analytical processes instead of your large core tables. For example, instead of joining two large tables together, consider creating smaller temporary tables that are limited to only the relevant records and then joining those two. You'll often find that your processes will run faster even with the extra steps of creating and deleting temporary tables.
• Use fixed column headings in crosstab queries whenever possible. This way, Access doesn't have to take the extra step of establishing column headings in your crosstab queries.
• Avoid using calculated fields in subqueries or domain aggregate functions. Subqueries and domain aggregate functions already come with an inherent performance hit. Using calculated fields in them compounds your query's performance loss considerably.

Compacting and repairing your database regularly

Over time, your database will change due to the rigors of daily operation. The number of tables may have increased or decreased; you may have added and removed several temporary tables and queries; you may have abnormally closed the database once or twice; and the list goes on. All this action may change your table statistics, leaving your previously compiled queries with inaccurate query execution plans.

When you compact and repair your database, you force Access to regenerate table statistics and re-optimize your queries so that they'll be recompiled the next time the query is executed. This ensures that Access will run your queries using the most accurate and efficient query execution plans. To compact and repair your database, simply select the Database Tools tab on the Ribbon and choose the Compact and Repair Database command.

You can set your database to automatically compact and repair each time you close it by doing the following:

1. On the Ribbon, select File.
2. Click Options. The Access Options dialog box appears.
3. Select Current Database to display the configuration settings for the current database.
4. Place a check mark next to Compact on Close and click OK to confirm the change.