Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP Query Question (AccessXp)

    Hi Rich...

    All you have to do is create a second query.... Keep the totals query as is (with just the Min and Max of Cost)...
    Then create a second query that joins the Table to the totals query on the Cost field... The inner join will return only the records where those amounts match (ie the Min and Max Cost records)... In this query, select the Description field from the table and you should get the results you are looking for...

    HTH

  2. #2
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AccessXP Query Question (AccessXp)

    We're trying to design a Query that returns the Highest Costing Item, and the Lowest Costing Item. Our table includes a field name of Description, and another field name of Cost.
    We ran the Query successfully using totals to find at the same time the Max and the Min for the "Cost" field. But we want to know the Description of the Highest and the Lowest! When we include the field "Description", the Query returns all the records, not simpply max and min.

    Does anybody know how we could show the "Description" and the Max and Min?

    Thanks,
    Rich
    see http://www.mrwteaches.net/d221

    (URL corrected and made clickable by HansV - see <!help=19>Help 19<!/help>)

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP Query Question (AccessXp)

    Hi Trudi,

    Thank you so much for your quick reply!

    I tried your suggestion, but I must not be doing something correct. Maybe I'm not as familiar with inner joins. I still can get it to work the way we envisioned! I get no results. Do I run the new query to see all the info we're after?

    Any suggestions?

    Thanks a bunch,
    G'Day,
    Rich

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

    Re: AccessXP Query Question (AccessXp)

    Let's say that you have a table tblCosts with fields ID (AutoNymber), Description (Text) and Cost (Currency).

    1. Create a query qryMinMax that returns the lowest and highest costs:

    SELECT Min([Cost]) AS MinCost, Max([Cost]) AS MaxCost FROM tblCosts

    2. Create a query based in qryMinMax and on two instances of tblCosts. Join one instance to qryMinMax on Cost vs MinCost, and the other instance on Cost vs MaxCost. Return the Description field from each:

    SELECT tblCosts.Description AS [Least Expensive], qryMinMax.MinCost, tblCosts_1.Description AS [Most expensive], qryMinMax.MaxCost
    FROM (qryMinMax INNER JOIN tblCosts ON qryMinMax.MinCost = tblCosts.Cost) INNER JOIN tblCosts AS tblCosts_1 ON qryMinMax.MaxCost = tblCosts_1.Cost
    GROUP BY tblCosts.Description, qryMinMax.MinCost, tblCosts_1.Description, qryMinMax.MaxCost;

    See screenshot.
    Attached Images Attached Images
    • File Type: png x.PNG (11.1 KB, 0 views)

Posting Permissions

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