CODEWITHSUNDEEP
sqlms-access-2010
Michael B
Michael B

Reputation: 1

Creating a list by Top N by Category and Subcategory in MSACCESS

I have a list of 50 offices and a large list of counties for which I am trying to determine which are the top 3 office closest to those counties. I have used the Lat Long for the county center and the the office to calculate the distance from each.

My data now is down to 3 columns:

Zip, OfficeName, and Miles (the distance between the two)

Zip     OfficeName   Miles
20601   Park Potomac    32.1344
20601   Clarksville 39.6714
20601   Cambridge   43.1868
20601   Ellicott City   44.4464
20601   Lutherville 55.8513
20601   Perry Hall  56.0435
20602   Park Potomac    33.3036
20602   Clarksville 41.9749
20602   Cambridge   45.3606
20602   Ellicott City   47.0838
20602   Lutherville 58.8546
20602   Perry Hall  59.2286
20603   Park Potomac    30.0754
20603   Clarksville 39.6311
20603   Ellicott City   45.1373
20603   Cambridge   48.3889

I have the miles for all 50 offices, but what to reduce the output to the offices that are the closest 3.

I have tried the solution found at the following : Access 2002 - how to Group By and Top N records in same query

SELECT TopDistance.Zip, TopDistance.OfficeName, TopDistance.Miles
FROM TopDistance
WHERE TopDistance.Miles In 
      (SELECT TOP 3 TopDistance.Miles
      FROM TopDistance as Dupe
      WHERE Dupe.Zip = TopDistance.Zip and Dupe.OfficeName=TopDistance.OfficeName
      ORDER BY TopDistance.Miles ASC)

Based on the use of the SELECT TOP 3 statement, I should be getting only 3 line of data per Zip, showing the closed three offices and how far away the offices are.

However, the results are still showing the distance for all 50 offices.

Why is the use of the "Select Top 3" not working?

Upvotes: 0

Views: 93

Answers (1)

M. Kanarkowski
M. Kanarkowski

Reputation: 2205

I believe you should change the select output from TopDistance.Miles to Dupe.Miles in the subquery. Secondly, your condintion in the subquery is to detailed. This condition Dupe.Zip = TopDistance.Zip and Dupe.OfficeName=TopDistance.OfficeName can be fulfiled only by one row, that's why you got all the rows.

SELECT TopDistance.Zip, TopDistance.OfficeName, TopDistance.Miles
FROM TopDistance as TopDistance
WHERE TopDistance.Miles In 
      (SELECT TOP 3 Dupe.Miles
      FROM TopDistance as Dupe
      WHERE Dupe.Zip = TopDistance.Zip
      ORDER BY Dupe.Miles ASC)

Upvotes: 1

Related Questions