Display Fields Related to a Totals Query
This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
Display Fields Related to a Totals Query
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.*Your article regarding the totals query in the January 1999 issue was timely for me. I have another totals problem that's been challenging me for months. Using your real estate example, suppose you want to find the maximum price by ZIP code, but you want additional information about the property. When you use the Max function in the totals query, you can't view other descriptive information about the record without also grouping by those fields. Can you help me with a solution?
As with many challenges in Access, there are several solutions to this problem. Before we get into the details, let's set up a simple example that illustrates the problem.
Set up the sample
We'll assume that we're working with real estate data from a table called tblSale, as shown in Figure A. Initially, we want to identify which properties sold for the highest amount in each ZIP code. To do so, we'll create a totals query. Open a new query in Design view and drag the ZIP and Price fields to the query design grid. Then, choose View/Totals from the menu bar to display the Total row. By default, the values in the Total row appear as Group By. Open the dropdown list in the Price field's Total row and select Max. Finally, save your query as qryMaxSales.
Figure A: We'll use this data to create a query that displays the highest sale in each ZIP code.
At this point, run the query. As you can see in Figure B, Access correctly displays the highest selling price from each ZIP code. However, chances are you want to know more information about the locations. Getting Access to display that information isn't as easy as you might expect.
Figure B: The Max function in a totals query identifies the highest price from each region.
A look at the problem
Let's see what happens when you add fields to the query. Switch to Design view and drag the remaining fields to the query design grid. By default, Access automatically sets the Total selection to Group By for each additional field. This is unacceptable, as the query will display a result row for each unique combination of fields that have a Group By setting--essentially guaranteeing that every record will be displayed. Unfortunately, you have no choice but to make a selection in the Total row.
As you investigate the choices in the Total row, you might logically assume that the Last or First choices would allow you to display fields related to the records that have the highest Price value. Unfortunately, the solution isn't that simple.
Figure C shows the result you'll get if you apply the Last choice to the additional fields you want displayed. Although the Max function still returns the correct price, the additional data that's displayed doesn't correspond to the record containing that price value. Contrary to what you might expect, the Max function doesn't act as query criteria. If you compare Figures A and C, you'll see that the Last function returns data from the last record Access evaluates in each ZIP code group from the underlying source data.
Figure C: You may find that the Last function produces unexpected results.
Now that we've examined the problem, let's look at some solutions. Close qryMaxSales without saving any of the changes you've made.
Solution 1: joined queries
When you save your totals query, as we have, you can use it to control what records are displayed in a second query. The totals query returns a unique row for each group, so we'll use all of the query's fields to set up a relationship with the original source data. First, create a new query in Design view. Add tblSale and qryMaxSales to the query, and then close the Show Table dialog box. Now, drag the ZIP and Price fields from tblSale to the appropriate fields in qryMaxSales. Finally, add the fields you want displayed, as shown in Figure D.
Figure D: By joining to a saved query, we can display the fields from the record associated with the highest sale prices.
At this point, run your query. The new query only returns the records where all the joined values from both sides of the relationship are equal. As you can see in Figure E, Access now returns the correct data. Save your query as qryJoined,**and then close it.
Figure E: After setting up our relationship, Access displayed only tblSale records where the price equals the maximum calculated by qryMaxSales.
Solution 2: using a subquery
If you want to keep the number of saved queries in your database to a minimum, you can accomplish our previous results with a single saved query. In reality, Access will still perform two selects against your data; however, one query will be nested as a subquery within the other. Although you can build this query in the query design grid, it's probably easier to just enter the SQL statement. First, open a new query in Design view. Close the Show Table dialog box without adding any tables. Then, choose View/SQL View from the menu bar and replace the existing query with the following:
SELECT * FROM tblSale WHERE Price= (SELECT Max(MaxSale.Price) AS MaxOfPrice FROM tblSale As MaxSale WHERE MaxSale.ZIP = tblSale.ZIP);
This solution is similar to our first solution, although there are subtle differences. The subquery uses an alias of tblSale called MaxSale to determine the maximum price when the alias's ZIP value equals the ZIP value in the current tblSale record. If the MaxOfPrice field equals the Price field in tblSale, Access returns the record.
Hide division-by-zero errors in printed reports
*I have an Access report with a calculated field that sometimes ends up with a division by zero. How can I prevent #Div/0 from printing?
Fortunately, eliminating the #Div/0 error message is easy. Let's say that you have a report that displays the cost for an event by person. The report displays two fields, Cost and Attending, and a calculation called PerPerson. If the formula used to generate PerPerson's value is simply
the report will display #Div/0 if Attending equals zero. There are a number of ways to prevent the error from being displayed. The easiest is to use the IIf function to conditionally display what is displayed in the PerPerson text box. Change the formula to
The IIf function evaluates the contents of Attending and displays a zero-length string if Attending equals zero. Otherwise, PerPerson displays the result of Cost divided by Attending. Depending on the purpose of your report, you may want to consider substituting the zero-length string with something more meaningful. For instance, in our example, we might substitute N/A or No one has registered for this event.
Declaring multiple variables in one line of code
I was reading through the code in the article "Present kiosk data interactively with Access and PowerPoint" in the February 1999 issue, and I noticed a line declaring a few recordset objects in a single line
I was under the impression that if variables were declared in this manner, only the final variable would be dimmed as a Recordset *object, while the first two would be dimmed as variants. I've done a little VB programming, but not much in VBA. Is this the case, or do things work differently in Access?
Dim rst, rstAddress, rstInventory As Recordset
Shame on us! You're right; only the last variable is declared as a recordset. Although variables declared this way generally won't trip up your code, Variant types take up more memory. To declare multiple variables on one line, you should specify the type for each variable, such as
Dim rst As Recordset, rstAddress As Recordset, rstInventory As Recordset
Change data by altering either side of an equation
*I have a form that has fields for a start date, number of days, and an end date. I'd like the form setup to automatically calculate and store the end date after I input the start date and number of days. However, I also want to be able to bypass the number field, enter the end date, and have the number of days calculated. How do I do this?
To incorporate the flexibility you're looking for, you should make use of the BeforeUpdate event. Let's say that your form's three fields are named dtmStartDate, intDays, and dtmEndDate. Assuming that the value in dtmStartDate won't change, you initially want to calculate the value for dtmEndDate by changing the value stored in intDays. To do so, you need to run code when intDays changes, so the formula will be attached to intDays's BeforeUpdate event.
The following procedure updates the value stored in dtmEndDate when intDays changes:
Private Sub intDays_BeforeUpdate(Cancel As Integer) dtmEndDate = dtmStartDate + intDays End Sub
If the value in dtmStartDate can change, you'll want to attach the same formula to that field's BeforeUpdate event. Using BeforeUpdate, you can recalculate and change the dependent data when you manually change the final result of the calculation. Simply add the formula
intDays = dtmEndDate - dtmStartDate
to the dtmEndDate's BeforeUpdate event. Keep in mind that you may not actually want to store the number of days in your table, since you can easily calculate the number and simply display the result in an unbound text box on your form. If you replace the intDays field with an unbound text box, attach code to your form's Current event to populate the text box with the appropriate value when a record receives focus. For instance, if your text box is named intDays, you'd use the code
Private Sub Form_Current() intDays = dtmEndDate - dtmStartDate End Sub
Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.