Group by Query using Min and showing corresponding value in field X

[cyork]

I have a table with 3 fields. The first is an address, the second is a destinatino address and the third field is the distance between the two. I want to group by the first address and take the Minimun for the distance column and simply display the destination address that corresponds with this minimum distance... I can't seem to be able to just display the destination field (it has to be Min, Max, Where, Expression...)

I hope this is understandable.

Thanks,

Chris

 

[RickSpr]

In a query of this sort, where you're grouping rows, there's no guarantee that multiple rows won't have the minimum value. In this case, that would be like the distance to two or more destinations happening to be exactly the same. In principle, you couldn't select a summary row with three different destinations in the same column. That's why you can't get what you want with just a grouping query.

What you need to do is leave just the address and minimum distance in this query, and save it. Then build another query that joins this underlying query with the original table, on the address and (minimum) distance fields. In that query, which you don't make a totals query, you can select the destination field.

Just be aware that some day you might wind up with two (or more) destinations having the minimum distance; the combination of address and minimum distance may not be unique all the time. Rick Sprague

 

[cyork]

I didn't think of that possibility but you are absolutely right.

Thank you very much that will work perfectly.

Chris