# Thread: Highest AND lowest values (2007)

1. ## Highest AND lowest values (2007)

How do I structure a query in the "Design" window that will extract the highest and lowest amounts from a Sales field (\$)?

Regards,
Chuck Billow

2. ## Re: Highest AND lowest values (2007)

Include two copies of the field that holds the Value of the Sale, then in the Design tab of the Ribbon, click the Totals button.
This adds a Total line to the query grid, which defaults to GroupBy.
Change one of these to Max, the other to Min.

3. ## Re: Highest AND lowest values (2007)

John, it runs but it looks like what it's giving me is the min and the max for *each* item, rather than the highest and lowest of the items.

Regards,
Chuck Billow

4. ## Re: Highest AND lowest values (2007)

Take out the UnitNumber field.
Because you are Grouping by that field, you are getting a separate answer for each Unit.

5. ## Re: Highest AND lowest values (2007)

But then how would I tell which unit was actually the highest/lowest?

I'd have an amount, but "indentifier"?

Regards,
Chuck

6. ## Re: Highest AND lowest values (2007)

You have to save the query that calculates the highest and lowest values.
Then create a new query based on the first query and two copies of the table.
Add the MinOfWeeklyRate and MaxOfWeeklyRate fields to the query grid.
Link one instance of the table to the query on WeeklyRate vs MinOfWeeklyRate, and add the UnitNumber from this instance to the query grid.
Link the other instance of the table to the query on WeeklyRate vs MaxOfWeeklyRate, and add the UnitNumber from this instance to the query grid.

7. ## Re: Highest AND lowest values (2007)

You did not say you wanted to know that when you asked the question!

<hr>But then how would I tell which unit was actually the highest/lowest?<hr>

You need to do this in several steps. First step is just to extract the Max.

Save this query, then create a new query that uses this query, and the original table.
Join the query and table on the Price field. Display the UnitNumber and the Price.

Then repeat all this for the Min.

8. ## Re: Highest AND lowest values (2007)

Um, excuse me??

9. ## Re: Highest AND lowest values (2007)

Just so I'm not TOTALLY losing my place here, these are the table fields:

10. ## Re: Highest AND lowest values (2007)

Have you created the totals query that computes the Min and Max of WeeklyRate?
If so, under what name have you saved it?

11. ## Re: Highest AND lowest values (2007)

Attached is a simplified version of what Hans has described.

qryMnMax identifies the minimum and maximum values
qryFinal links qryMinMax back to the original table on both the min and max values to show which Units are corresponding to the min and max values.

12. ## Re: Highest AND lowest values (2007)

Hans, as of this moment, no, I haven't got any query (Totals).

I have the Total line available in the quesry; I have the WeekltyRate in two adjacent columns, one Min, one Max; so when I view the query, I get the amounts, but, obviously, no unit #. And, as you are aware, if I add the UnitNumber filed, it just gives me min/max for all units.

And I'm totally lost at this particular moment.

For the sake of clarity: what I want to be able to see is "this is the highest priced unit AND this is the cheapest" all in a query result.

Are you saying I search for each separately, and then *join* those two queries into one? 'Cause I'm not getting that step at all.

13. ## Re: Highest AND lowest values (2007)

OK, so you have a query that returns the MinOfWeeklyRate in one column, and the MaxOfWeeklyRate in another column.
Save and close this query; let's say that you name it qryTotal.
Now create a new query in design view.
Add the RentalUnits table, then the qryTotal query, and finally add the RentalUnits table again (this is intentional, not a mistake).

Drag the MinOfWeeklyRate field from qryTotal to the WeeklyRate field in the first instance of RentalUnits; this will create a join between the query and table on these fields.
Add the MinOfWeeklyRate field from qryTotal to the query grid.
Add the UnitNumber field from the first instance of RentalUnits to the query grid.

Drag the MaxOfWeeklyRate field from qryTotal to the WeeklyRate field in the second instance of RentalUnits; this will create a join between the query and table on these fields.
Add the MaxOfWeeklyRate field from qryTotal to the query grid.
Add the UnitNumber field from the second instance of RentalUnits to the query grid.

You now have a query with 4 fields: MinOfWeeklyRate and the UnitNumber that has the minimum rate, and MaxOfWeeklyRate and the UnitNumber that has the maximum rate.

14. ## Re: Highest AND lowest values (2007)

Hans, THAT is too totally cool!

I see it working, but I'm not sure I totally get the "why" yet.

Maybe I'll set up a "test database" and go through the steps till I got it.

I really appreciate the help Hans.

John and Mike, you guys as well. I'm just slow I suppose.

Regards,
Chuck

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•