Adam and Mike, Sorry to be adding to this thread so late--but I still think it's worthwhile so I'm suggesting a third way, in SQL alone. It's the same basic method as used in the "extend lines to nearest lines" thread (see query [4 Closest end with nearest 3 segments] here), in this case using a parameter to find the TOP N rows. Query 1 is almost identical to Adam's first query (Q). -- Query 1 SELECT [D1].[ID] AS [Road segment ID], [D2].[ID] AS [Address line ID], [D2].[Adjusted length] FROM [Rural] AS [D1] LEFT JOIN [Tie lines 5280'] AS [D2] ON Touches([D1].[ID], [D2].[ID]) -- with e ; Query 2 calls two instances of query 1 in a self join, matching rows on the left to each row on the right for the same road segment but having a longer tie line. (So in each matched pair we get the shorter line on the left.) It can then group by road segment and use HAVING to leave only the rows where there are fewer than N matched rows on the right, i.e. where the match on the left is one of the N shortest lengths for the group (the road segment). The count N is entered as a user parameter (with a default of 10). The last wrap in query 2 groups again, summing the lengths for the filtered rows (the N shortest lengths, or N closest houses, on this road segment). -- Query 2 PARAMETERS [Limit (shortest N lines)] LONG; -- Default is the 10 shortest lines SELECT [Road segment ID], COUNT([Address line ID]) AS [Address lines], -- MAX([Rank]) AS [Check], -- NB always at least 1 (even if there are no incident lines) SUM(Coalesce([Adjusted length], 0)) AS [Total length] FROM (SELECT [T1].[Road segment ID], [T1].[Address line ID], -- COUNT([T2].[Address line ID]) + 1 AS [Rank], -- one-based index [T1].[Adjusted length] FROM [Query 1] AS [T1] LEFT JOIN [Query 1] AS [T2] ON [T1].[Road segment ID] = [T2].[Road segment ID] -- Same section of target line AND [T1].[Adjusted length] > [T2].[Adjusted length] -- The left hand address line is longer than the right -- (giving shorter lines a lower count) GROUP BY [T1].[Road segment ID], [T1].[Address line ID], [T1].[Adjusted length] HAVING COUNT([T2].[Address line ID]) < Coalesce([Limit (shortest N lines)], 10) ) GROUP BY [Road segment ID] ; Query 3 is an UPDATE version of query 2. (Like query 2 it just calls query 1.) -- Query 3 PARAMETERS [Limit (shortest N lines)] LONG; -- Default is the 10 shortest lines UPDATE (SELECT [D].[Sum], [T].[Total length] FROM [Rural] AS [D] INNER JOIN (SELECT [Road segment ID], SUM(Coalesce([Adjusted length], 0)) AS [Total length] FROM (SELECT [T1].[Road segment ID], [T1].[Address line ID], [T1].[Adjusted length] FROM [Query 1] AS [T1] LEFT JOIN [Query 1] AS [T2] ON [T1].[Road segment ID] = [T2].[Road segment ID] -- Same section of target line AND [T1].[Adjusted length] > [T2].[Adjusted length] -- The left hand address line is longer than the right -- (giving shorter lines a lower count) GROUP BY [T1].[Road segment ID], [T1].[Address line ID], [T1].[Adjusted length] HAVING COUNT([T2].[Address line ID]) < Coalesce([Limit (shortest N lines)], 10) ) GROUP BY [Road segment ID] ) AS [T] ON [D].[ID] = [T].[Road segment ID] ) SET [Sum] = [Total length] ; Attachments: Query 1.txt Query 2.txt Query 3.txt
|