ACCESS QUERY: Get Values after Grouping and finding Minimum

[Barnaby Burman]

Hi,

I have a simple table in Access with four cols, as an output from my
GIS:

BUILDING_ID , ROAD_ID , ROUTE_ID , DISTANCE

The table shows the distance of each building from each road, and many
roads make up a route. I just want a query to:

1. Group by Building_ID where the Routes are the same
2. Find the Minimum Distance
3. Get the Road_ID from which the Minimum Distance refers to.

I've written some lovely VBA code for this, but it takes a very long
time, especially when there are 100k's of records. Is there a way in
the query builder this can be done more quickly?

If anyone has any ideas, I'd be very grateful,

Regards,

Barnaby Burman

 

[Guest]

Try these two queries - first one named Barnaby_1.
Barnaby_1 --
SELECT Barnaby.ROUTE_ID, Min(Barnaby.DISTANCE) AS MinOfDISTANCE
FROM Barnaby
GROUP BY Barnaby.ROUTE_ID;

SELECT Barnaby.*
FROM Barnaby INNER JOIN Barnaby_1 ON (Barnaby.DISTANCE =
Barnaby_1.MinOfDISTANCE) AND (Barnaby.ROUTE_ID = Barnaby_1.ROUTE_ID);

 

[Neil Sunderland]

I have a simple table in Access with four cols, as an output from my
GIS:

BUILDING_ID , ROAD_ID , ROUTE_ID , DISTANCE

The table shows the distance of each building from each road, and many
roads make up a route. I just want a query to:

1. Group by Building_ID where the Routes are the same
2. Find the Minimum Distance
3. Get the Road_ID from which the Minimum Distance refers to.

Create a query called qryShortestDistance (change 'Distance' to the
name of your table):
SELECT BUILDING_ID, ROUTE_ID, Min(DISTANCE) AS ShortestRoute
FROM Distances
GROUP BY BUILDING_ID, ROUTE_ID

Then a second query to get the shortest distance:
SELECT BUILDING_ID, ROAD_ID, ROUTE_ID, DISTANCE
FROM Distances AS D
INNER JOIN qryShortestDistance AS S
ON D.BUILDING_ID = S.BUILDING_ID
AND D.ROUTE_ID = S.ROUTE_ID
AND D.DISTANCE = S.ShortestRoute

 

[Neil Sunderland]

Then a second query to get the shortest distance:
SELECT BUILDING_ID, ROAD_ID, ROUTE_ID, DISTANCE
FROM Distances AS D
INNER JOIN qryShortestDistance AS S
ON D.BUILDING_ID = S.BUILDING_ID
AND D.ROUTE_ID = S.ROUTE_ID
AND D.DISTANCE = S.ShortestRoute

Whoops, must specify the table when the fields are the same...

SELECT D.BUILDING_ID, D.ROAD_ID, D.ROUTE_ID, D.DISTANCE
FROM Distances AS D
INNER JOIN qryShortestDistance AS S
ON D.BUILDING_ID = S.BUILDING_ID
AND D.ROUTE_ID = S.ROUTE_ID
AND D.DISTANCE = S.ShortestRoute