Search code examples
ms-access

Showing only highest AvgAmount per Location

I am new to Access Query Design. I have the following SQL and result:

SELECT Trip.Location, ExpPurpose.ExpPurposeID, ExpPurpose.ExpPurposeDescr, AVG(TripEmployeeExpPurpose.Amount) AS AvgAmount
FROM Trip, TripEmployeeExpPurpose, ExpPurpose
WHERE TripEmployeeExpPurpose.TripID = Trip.TripID AND TripEmployeeExpPurpose.ExpPurposeID = ExpPurpose.ExpPurposeID
GROUP BY Trip.Location, ExpPurpose.ExpPurposeID, ExpPurpose.ExpPurposeDescr
HAVING AVG(TripEmployeeExpPurpose.Amount) > 558.436888

enter image description here

As seen from the result, I would like to only show the highest amount of "AvgAmount" for "Abu Dhabi" for example, which in this case is "airfare". However, it is currently showing "airfare", "other_transport" and "lodging". How should i change my query? (I would like to do the same for the other locations). Thanks in advance!

Solution

  • Save the above query as QueryExp.

    Then use this as source in a final query using a subquery as filter:

    Select
    Location, 
    ExpPurposeID, 
    ExpPurposeDescr, 
    AvgAmount
From
    QueryExp
Where
    AvgAmount In
        (Select Top 1 Q.AvgAmount
        From QueryExp As Q
        Where Q.Location = QueryExp.Location
        Order By Q.AvgAmount Desc)