Results 1 to 14 of 14
  1. #1
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    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
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    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.
    Attached Images Attached Images
    Regards
    John



  3. #3
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    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
    Attached Images Attached Images
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    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.
    Regards
    John



  5. #5
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    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
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    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.
    Regards
    John



  8. #8
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Highest AND lowest values (2007)

    Um, excuse me??
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  9. #9
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Highest AND lowest values (2007)

    Just so I'm not TOTALLY losing my place here, these are the table fields:
    Attached Images Attached Images
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  12. #12
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    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.
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #14
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    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
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

Posting Permissions

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