SOLVING PROBLEMS WITH QUERIES
The following sections cover some common business questions and how they can be solved by using queries. You can adapt the queries to cover various questions common to developers. Some business questions answered are as follows:
How many times do I have to rent a tape before it pays for itself? The section “Grouping to Get Percentages” shows how to get the percentage of a rental cost to retail cost of a tape, thus allowing you to use it to get the number of times you need to rent it out.
In some cases, duplicate records slip in and need to be taken care of. The section “Finding and Deleting Duplicate Records” shows how to perform this task.
Sometimes it takes a few levels of queries to get to the desired results when analyzing data. This is discussed in the section “Nesting Groups to Get the Complete Solution.”
Additional answers are brought forth by using different methods with queries. Read on.
Grouping to Get Percentages
Sometimes you need to calculate percentages in a query. If the numbers required for the calculation are in the same record, the process is easy and can be accomplished in a formula, as shown by using the query named qryMovieTitlePercentOfRetail (see Figure 8.25). To see the percentage, use the query's Datasheet view (see Figure 8.26).
Figure 8.25. Creating a field giving the percentage of other fields is very straightforward.
Figure 8.26. Here, you can see the Percent field in the query in Datasheet view.
Note
The properties for the percentage column display the value as a percentage with a fixed decimal point.
Most often, percentages are calculated based on the sum or count of all records in the query. The number isn't available on each record and must be calculated by using a query. Create the query (as shown in Figure 8.27) and save it.
Figure 8.27. Here, the count of total phone numbers is displayed for the tblPhone table.
To create the query with percentages, create a new query from the same table and use the Query menu's Show Table command to add the previously created query to the query design grid. Don't create any join lines between the table containing the totals and the base table for the query.
Note
This query uses the Cartesian product of two tables. A Cartesian product results when a join line isn't used between two tables in a query. The resultset will contain one record for each combination of rows in the two tables. If each table has three rows, you get nine rows in the resultset (32=9). This can get really ugly and dangerous for large datasets.
Create the formula based on the Count() function to get the count of the number of records in the group and divide by the total from the summary query to get the percentage (see Figure 8.28).
Figure 8.28. This query presents the percentage of total phone numbers found in tblPhone.
Finding and Deleting Duplicate Records
Deleting duplicate records usually involves manually building a list of the records to delete and then going through them one by one to locate and delete the duplicates. You can use a summary query to identify the characteristics of a duplicated record, but summary queries don't provide a method for locating all but one record from the result list and then deleting them.
Note
The queries discussed in this section can be found on the accompanying CD-ROM in the Chap08.mdb database, in the ExamplesChap08 folder.
Use a unique identifier in the record and use nested queries to solve the problem. For this example, a query named qryMovieTitlesDuplicated begins coming up with a solution to the task with a list of duplicated titles.
By using the Select query qryMovieTitlesDuplicated as the basis, you can create a summary query that groups by titles and grabs the lowest (minimum) title number for each title. Join the list of duplicated titles so that the records returned by the query include only duplicated titles, as in the Totals query named qryMovieTitlesDuplicatedFirst (see Figure 8.29). Notice that the Total row is visible in the lower grid.
Figure 8.29. Create an ID of the first duplicated titles by using this totals query.
With the list of duplicated titles identified and the list of the first record in each duplicate group identified, you can build the Delete query. Perform this action by selecting all records that have duplicated titles but aren't the first record of the duplicate group. Figure 8.30 shows the Delete query that removes the duplicate records.
Figure 8.30. This Delete query will remove duplicated records.
Note
To adapt this procedure to work against tables that don't already have a unique record identifier, create a field in the table and give it a unique value for each record. You might need to use VBA and increment a value as you loop through each record and store the value in the record. When you have a unique identifier, the procedure described in this section should work to remove duplicate records.
Nesting Groups to Get the Complete Solution
Business, financial, and manufacturing analysts ask similar questions about statistics from databases. For example, the owner of the phone list wants to know how randomly scattered the people are that you have on the phone list. A count of the number of entries for each prefix isn't enough to solve the problem because it shows only what codes have the highest number of entries. You want to know how many prefix codes have that same number of records. The problem is solved by nesting two queries.
To solve the first part of the problem, build a summary query that groups and counts on the phone prefix. Figure 8.31 shows this query, named qryPhoneStatDetail.
Figure 8.31. This query, shown in Design view, counts the number of records with the same phone prefix.
With the detail query as a basis, create a new summary query. Copy the count field from the detail query into the query design grid twice. Set the first Total to Count and the second Total to Group By in the qryPhoneStatSummary query (see Figure 8.32). Running the query shows that two prefixes have one phone number, and one prefix code has three different phone numbers (see Figure 8.33).
Figure 8.32. This query gives the phone status.
Figure 8.33. Queries within queries can perform even obscure tasks, as shown with the qryPhoneStat-Summary query.
Distinguishing Between New and Old Records
In the example for this section, a user has two tables that list movie titles available in January and February. The user wants a single list showing the movies that were deleted from the list in January (Deleted) and the new movies added in February (Added).
To solve this problem, give the records a number, depending on which table they're selected from. If the title is in the Jan table, give it a 1. If the title is in the Feb table, give it a 2. The following union query results in records that follow this model:
SELECT DISTINCTROW MovieTitlesJan.TitleNo, 1 AS OldNew FROM MovieTitlesJan UNION SELECT DISTINCTROW MovieTitlesFeb.TitleNo, 2 AS OldNew FROM MovieTitlesFeb;
Save this query as qryMovieTitlesUnion and use it as the basis for a summary query. The summary query qryMovieTitlesSummary shows the summarized number and uses an IIf() statement to display the added/deleted text (see Figure 8.34).
Creating a Total Row for Crosstab Queries
When using Access's Crosstab queries, you can total up Columns by including additional Row Summary fields. But to calculate column totals at the bottom of the datasheet, you had to resort either to using Excel Pivot tables or a report. Now you can include a Total Row on all your crosstabs, if you want.
Creating row totals for crosstabs is one of those topics that, until you have a need for it, you'd never find it to be convenient. When you have a need and solve the problem, however, you'll find uses for it all over the place.
The solution isn't trivial, and requires the combination of a Totals query, two Crosstab queries, and a Union query. To start, let's first go through the steps that you need to do to combine all these queries into an intelligent answer:
1. |
Create a crosstab query to handle the detail. |
2. |
Create a Totals query to total the values for each rating. |
3. |
Create a crosstab query from the Totals query, giving it the same field layout as the first crosstab. This is to prepare for using the union query, which needs to have fields be in the same order. |
4. |
Create a union query to combine the two crosstabs, grouping by the DisplayOrder field. This makes sure that the total crosstab query values are displayed last. |
That's all there is to it. Piece of cake, right? It is, after you do it once.
Creating a Crosstab Query to Handle the Detail
First, you create a crosstab query that will handle the main detail of the data. This query will actually be the xtbCategoriesByRating crosstab query created for the report solution, with one added field. This field, DisplayOrder, is set to 0 in the column's Field property (see Figure 8.35).
Figure 8.35. The crosstab query has the DisplayOrder calculated field added.
Note
The Categories field is so named so that the word Categories will be displayed for the header instead of Description. You can also accomplish this by changing the field's Caption property.
Creating a Totals Query to Total the Values for Each Rating
This simple totals query will count the number of titles in the MovieTitles table that are each assigned a rating. The first field is an expression called Categories and is set to Rating Totals.
Note
Although it might sound strange to have a field named Categories containing the value Rating Totals, setting the value of the Categories field to Rating Totals works to your advantage when it comes to the next step.
Rating is the next field set to Group By on. Lastly, you'll want to set a Group By on the count of the titles for the given ratings. Figure 8.36 shows ttlRatingsTotals in Design view, and a copy called ttlRatingsTotalsRunning in Datasheet view.
Figure 8.36. This totals query will be used as a base for another crosstab query.
By looking at the datasheet in Figure 8.36, you can see that this is now set up to use in a crosstab query. The data is ready because you have the required fields for a crosstab query: a Row Headings field (Categories), a Column Headings field (Rating), and Value field (CountOfTitle).
Creating a Crosstab Query from the Totals Query with the Same Field Layout
Now that the Totals query is created, you need to place it in a format that will work with the original crosstab query that you created. To do so, you must create a new crosstab against the Totals query. This actually isn't as tough as it sounds because you can base queries off other queries as easily as tables.
Figure 8.37 shows what the new query will look like. Aptly named xtbRatingTotalsRow, it is, in fact, almost identical to the original crosstab created.
Figure 8.37. If you compare this crosstab query to the query in Figure 8.35, you'll see that they're very similar.
One main difference is that although both queries contain the calculated field DisplayOrder, the xtbCategoriesByRating crosstab has the DisplayOrder field set to 0, whereas in xtbRatingTotalsRow, it's set to 1.
Using a Union Query to Combine the Two Crosstabs
This is where the rubber hits the road. It's now time to take the two crosstabs and combine them by using a union query. Union queries combine two recordsets that have the same field structure—in this case, xtbCategoriesByRating and xtbRatingTotalsRow.
You can't create union queries by using the query design grid. Instead, create a new query in Design view, and then choose SQL from the View menu. Then type the following SQL string:
SELECT * FROM xtbCategoriesByRating UNION (SELECT * FROM xtbRatingTotalsRow) ORDER BY DisplayOrder;
Notice where the parentheses are placed. By having the ORDER BY where it is, the operation of ordering by DisplayOrder is performed on both recordsets, after they're unioned. Figure 8.38 shows the final recordset.
Figure 8.38. Here is the final product.
So there you have it. This method works great when you include it as a subform to display information. As with all these methods, the data Ratings table, notice that there are five ratings instead of the two showing, as you just saw in Figure 8.38. The other three ratings didn't show up because no movie titles currently in the MovieTitles table (the basis for these queries) have those ratings. Not having a column display is either a feature or flaw of using crosstabs, depending on your situation.
You can get the ratings to show in a couple of ways, of course. One way is to hard code them in the crosstab's Column Headings property. To see this property, choose Properties from the View menu while in the top half of the query design grid of the crosstab desired. You'll then see the property sheet for the query (see Figure 8.39).
Figure 8.39. You can set the query property Column Headings to display all columns in a given order.
The biggest problem with solving the problem this way is that if the columns change at all, you must go in and change the property setting. This means that, in this case, two crosstab queries must be updated. The alternative answer is to use code. For more information on creating dynamic columns in crosstab queries by using code, see Chapter 11.