Need Highest Value In Query

[GeeseWatcher]

I am trying to build a query that will return the highest value based on the
city, state, cost.

My query has over 300,000 records with Hub City, Hub State, Destination,
Rate, Fuel, Trucker, and Total Costs. For each Hub and Destination there
could be more than one cost depending on the trucker that is used. I need to
find a way to filter for one value (Hub, Destination, Cost) using the highest
cost regardless of the trucker.

I apologize if this isn't very clear, but any help would be appreciated.

 

[Ryan]

Why not just make a query with those 3 fields (Hub, Destination,Cost) and
sort Desending on the Cost?

 

[GeeseWatcher]

We did try that. However, for each location there can be more than one rate.
Sorting the rates descending isn't the issue, we just need to see ONE rate
for all the locations.

 

[KARL DEWEY]

It can can be done with one query if you know subqueries but here it is using
two and table named GeeseWatcher --

QRY GeeseWatcher_1 --
SELECT GeeseWatcher.City, GeeseWatcher.State, GeeseWatcher.Destination,
Max(GeeseWatcher.[Total Costs]) AS [MaxOfTotal Costs]
FROM GeeseWatcher
GROUP BY GeeseWatcher.City, GeeseWatcher.State, GeeseWatcher.Destination;

SELECT GeeseWatcher.*
FROM GeeseWatcher INNER JOIN GeeseWatcher_1 ON (GeeseWatcher.[Total Costs] =
GeeseWatcher_1.[MaxOfTotal Costs]) AND (GeeseWatcher.Destination =
GeeseWatcher_1.Destination) AND (GeeseWatcher.State = GeeseWatcher_1.State)
AND (GeeseWatcher.City = GeeseWatcher_1.City);

 

[GeeseWatcher]

Thanks Karl...that worked great!

It can can be done with one query if you know subqueries but here it is using
two and table named GeeseWatcher --

QRY GeeseWatcher_1 --
SELECT GeeseWatcher.City, GeeseWatcher.State, GeeseWatcher.Destination,
Max(GeeseWatcher.[Total Costs]) AS [MaxOfTotal Costs]
FROM GeeseWatcher
GROUP BY GeeseWatcher.City, GeeseWatcher.State, GeeseWatcher.Destination;

SELECT GeeseWatcher.*
FROM GeeseWatcher INNER JOIN GeeseWatcher_1 ON (GeeseWatcher.[Total Costs] =
GeeseWatcher_1.[MaxOfTotal Costs]) AND (GeeseWatcher.Destination =
GeeseWatcher_1.Destination) AND (GeeseWatcher.State = GeeseWatcher_1.State)
AND (GeeseWatcher.City = GeeseWatcher_1.City);

--
KARL DEWEY
Build a little - Test a little

We did try that. However, for each location there can be more than one rate.
Sorting the rates descending isn't the issue, we just need to see ONE rate
for all the locations.