434,849 Members | 2,059 Online
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

# Find Nearest Sides (Polygon)

 100+ P: 493 I have a puzzle for all you sql fans. Imagine a polygon with 12 sides (SideA, SideB, SideC, etc) Within this polygon are 96 points (Pt1, Pt2, Pt3, etc). Now let's say I have a table that has the distances from each point to each side Point Side Distance Pt1 SideA 25.6683 Pt1 SideB 37.6778 Pt1 SideC 576.8383 Pt2 SideA 352.2652 Pt2 SideB 3.8953 Pt2 SideC 46.9563 etc. What I need to do is, for each point, find the two closest sides. So I need to sort on point name, find the two minimum values in Distance for that point, and report the side. How do I do this? We are having a hard time wrapping our brains around the correct syntax. Thanks for any help! Apr 28 '08 #1
41 Replies

 Expert 5K+ P: 8,636 I have a puzzle for all you sql fans. Imagine a polygon with 12 sides (SideA, SideB, SideC, etc) Within this polygon are 96 points (Pt1, Pt2, Pt3, etc). Now let's say I have a table that has the distances from each point to each side Point Side Distance Pt1 SideA 25.6683 Pt1 SideB 37.6778 Pt1 SideC 576.8383 Pt2 SideA 352.2652 Pt2 SideB 3.8953 Pt2 SideC 46.9563 etc. What I need to do is, for each point, find the two closest sides. So I need to sort on point name, find the two minimum values in Distance for that point, and report the side. How do I do this? We are having a hard time wrapping our brains around the correct syntax. Thanks for any help! I don't think that it would be much of a problem to derive a code based solution, but SQL is my weakness. I'm imagine that it would have to involve a Sub-Query, something along the lines of: Expand|Select|Wrap|Line Numbers SELECT tblPolygon.Point, tblPolygon.Side, tblPolygon.Distance FROM tblPolygon WHERE tblPolygon.Distance IN (SELECT TOP 2 Distance                        FROM tblPolygon AS Dupe                                  WHERE Dupe.ID = tblPolygon.ID            ORDER BY Dupe.Distance, Dupe.ID)  ORDER BY tblPolygon.Point; NeoPa is the expert in this area and I'll call him in on this. If you still want a code based solution, let me know and I'll see what I can do. Apr 29 '08 #2

 Expert 100+ P: 1,134 Try This Expand|Select|Wrap|Line Numbers SELECT Point,Side,Distance FROM (   SELECT a.Point,a.Side,a.Distance,              (   SELECT Count(Point)                   FROM tblPointDistances b                   WHERE a.Point=b.Point                     AND a.Distance>b.Distance              )+1 AS Pos     FROM tblPointDistances a )z WHERE pos<3     I dont know the name of your table so I used tblPointDistances Apr 29 '08 #3

 Expert Mod 15k+ P: 31,489 I'll have a look at this as soon as I get a spare 1/2 an hour or so. In the mean-time, let us know if any of the suggestions so far submitted work (may save me the effort ;)). Apr 29 '08 #4

 Expert 5K+ P: 8,636 I'll have a look at this as soon as I get a spare 1/2 an hour or so. In the mean-time, let us know if any of the suggestions so far submitted work (may save me the effort ;)). Save yourself the trouble, NeoPa, since Delerna's solution works quite well. Thanks again. Apr 29 '08 #5

 Expert 5K+ P: 8,636 Try This Expand|Select|Wrap|Line Numbers SELECT Point,Side,Distance FROM (   SELECT a.Point,a.Side,a.Distance,              (   SELECT Count(Point)                   FROM tblPointDistances b                   WHERE a.Point=b.Point                     AND a.Distance>b.Distance              )+1 AS Pos     FROM tblPointDistances a )z WHERE pos<3     I dont know the name of your table so I used tblPointDistances Very nice solution, Delerna! Apr 29 '08 #6

 Expert 2.5K+ P: 2,653 Try This... Bravo. An impressive solution. Perfect logic, elegant coding and nice styling. Apr 29 '08 #7

 100+ P: 493 Harrumph, so much for "instant email notifcation"! Thanks all for weighing in, I'm going to run this right now and see if it works! *edit* okay, I'm a SQL noob too - WHERE do I write this? Can I create a new query and write that into the SQL view? Or should I do it in VBA in a DoCmd.RunSQL statement? And how does the a. b. work? I know they're aliases but I don't know how that works. The table is named "Nn" (I didn't create it) and the fields are SOURCEUID (the points), TARGETUID (the polygon sides), and DISTANCE. This is the query I came up with in the SQL view of the query builder: Expand|Select|Wrap|Line Numbers SELECT SOUREUID, TARGETUID, DISTANCE FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE, (SELECT Count(SOURCEUID) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DISTANCE) +1 AS Pos FROM Nn a ]. AS z WHERE pos<3;   But that's not right and it just sort of hangs in a scary way. Thanks for any help! Apr 29 '08 #8

 Expert Mod 15k+ P: 31,489 Harrumph, so much for "instant email notifcation"! ... That's very dodgy at the moment. Every time you post to the thread this will be reset unless you explicitly set it again while editing the recently added post. ...*edit* okay, I'm a SQL noob too - WHERE do I write this? Can I create a new query and write that into the SQL view? Or should I do it in VBA in a DoCmd.RunSQL statement? ... Either will work. Typically, if wanting to design a QueryDef object do the former. This can then be tweaked as desired (Set timeout values etc etc. ...And how does the a. b. work? I know they're aliases but I don't know how that works. ... Everywhere the table name could normally be used, the alias can be used instead. This is useful for using shorter or more meaning ful names for recordsets. It's also very useful for assigning usable names to subqueries within your SQL. ... The table is named "Nn" (I didn't create it) and the fields are SOURCEUID (the points), TARGETUID (the polygon sides), and DISTANCE. This is the query I came up with in the SQL view of the query builder: Expand|Select|Wrap|Line Numbers SELECT SOUREUID, TARGETUID, DISTANCE FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE, (SELECT Count(SOURCEUID) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DISTANCE) +1 AS Pos FROM Nn a ]. AS z WHERE pos<3; But that's not right and it just sort of hangs in a scary way. Thanks for any help! Try (as a specific version of Delerna's code) : Expand|Select|Wrap|Line Numbers SELECT SourceUID, TargetUID, Distance FROM (SELECT a.SourceUID,              a.TargetUID,              a.Distance,              (SELECT Count(SourceUID)               FROM Nn AS b               WHERE a.SourceUID=b.SourceUID                 AND a.Distance>b.Distance)+1 AS Pos       FROM Nn AS a) AS z WHERE Pos<3; Apr 29 '08 #9

 Expert Mod 15k+ P: 31,489 Harrumph, so much for "instant email notifcation"! ... That's very dodgy at the moment. Every time you post to the thread this will be reset unless you explicitly set it again while editing the recently added post. ...*edit* okay, I'm a SQL noob too - WHERE do I write this? Can I create a new query and write that into the SQL view? Or should I do it in VBA in a DoCmd.RunSQL statement? ... Either will work. Typically, if wanting to design a QueryDef object do the former. This can then be tweaked as desired (Set timeout values etc etc). ...And how does the a. b. work? I know they're aliases but I don't know how that works. ... Everywhere the table name could normally be used, the alias can be used instead. This is useful for using shorter or more meaning ful names for recordsets. It's also very useful for assigning usable names to subqueries within your SQL. In this case, there is a subquery within a subquery. For the inner subquery to refer to the recordset (table) in the first subquery, it must refer to it by the alias (a - see lines 7 & 8 of the suggested version below). ... The table is named "Nn" (I didn't create it) and the fields are SOURCEUID (the points), TARGETUID (the polygon sides), and DISTANCE. This is the query I came up with in the SQL view of the query builder: Expand|Select|Wrap|Line Numbers SELECT SOUREUID, TARGETUID, DISTANCE FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE, (SELECT Count(SOURCEUID) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DISTANCE) +1 AS Pos FROM Nn a ]. AS z WHERE pos<3; But that's not right and it just sort of hangs in a scary way. Thanks for any help! Try (as a specific version of Delerna's code) : Expand|Select|Wrap|Line Numbers SELECT SourceUID, TargetUID, Distance FROM (SELECT a.SourceUID,              a.TargetUID,              a.Distance,              (SELECT Count(SourceUID)               FROM Nn AS b               WHERE a.SourceUID=b.SourceUID                 AND a.Distance>b.Distance)+1 AS Pos       FROM Nn AS a) AS z WHERE Pos<3; PS. The [...]. version (in your code) is a known bug/feature of MS Access SQL as provided by the Query design. See Access QueryDefs Mis-save Subquery SQL for more on this. Apr 29 '08 #10

 100+ P: 493 Yeah! Works great, though very very slow - even scrolling through the resulting table causes it to hang. But it's exactly what we're after - thanks!! Very complicated query and not something I could have come up with. Very elegant! Apr 29 '08 #11

 Expert 100+ P: 1,134 yes that is the most annoying bug/feature ever. Everytime you go back into Query view, access replaces the () of the subquery with []. Whats worse is the query won't work like that. If you replace the []. with the proper () and save and exit, everything works fine (assuming the query is written correctly of course). I find that while developing a query, if I highlight the query code and copy it before I save and exit, then if my change didn't work I can go back to Query view get rid of the access changed query and paste my copy back. Then I can make some changes, take another copy, save and exit, and test the query. Round and Round that goes until the query works as intended Apr 29 '08 #12

 Expert Mod 15k+ P: 31,489 I knocked up a version which is fundamentally the same as Delerna's (uses the same logic) but is slightly tweaked (added ORDER BY, named subqueries to reflect what they do, etc). Expand|Select|Wrap|Line Numbers SELECT SourceUID,        TargetUID,        Distance FROM (SELECT SourceUID,              TargetUID,              Distance,              (SELECT Count(*)               FROM Nn AS iNn               WHERE iNn.SourceUID=oNn.SourceUID                 AND iNn.Distance

 Expert Mod 15k+ P: 31,489 yes that is the most annoying bug/feature ever. ... Are you still using A2000 by any chance? I found that A2003, while still returning the [...]. SQL, nevertheless handles it ok when it tries to re-parse it. PS. I did try to use the following version without success, as the WHERE clause usage of the Pos field causes it to be tested before it's evaluated :( Otherwise that would save a level of subquery, but it was not to be :( Expand|Select|Wrap|Line Numbers SELECT oNn.SourceUID,        oNn.TargetUID,        oNn.Distance,        (SELECT Count(*)         FROM Nn AS iNn         iNn.SourceID=oNn.SourceID) AS Pos FROM Nn AS oNn WHERE Pos<2 Apr 29 '08 #14

 Expert 100+ P: 1,134 I like your idea NeoPa this would probably do what you were trying Expand|Select|Wrap|Line Numbers SELECT oNn.SourceUID,        oNn.TargetUID,        oNn.Distance FROM Nn AS oNn WHERE  (SELECT Count(*)         FROM Nn AS iNn         iNn.SourceID=oNn.SourceID)<2   I don't know how that would affect speed though, probably not by much if anything at all. If speed is a problem, I would add a field to the table to hold the pos and run this Expand|Select|Wrap|Line Numbers SELECT Count(*)         FROM Nn AS iNn         iNn.SourceID=oNn.SourceID   modified to act as an update query on the new field. The update query could then be run before the select query. That way, the position determining query would only be called once instead of once every row. That should improve performance considerably Yes I am still using 2000. I do have office 2007 but I hate it.I'll have to get used to it one day I suppose Apr 30 '08 #15

 Expert 5K+ P: 8,636 Yeah! Works great, though very very slow - even scrolling through the resulting table causes it to hang. But it's exactly what we're after - thanks!! Very complicated query and not something I could have come up with. Very elegant! When I get a chance, I'll work on a strictly code based solution which may/may not improve performance. It should be interesting to see how the Sub-Query vs VBA battle turns out. Stay tuned to this station! Apr 30 '08 #16

 Expert 100+ P: 1,134 In this case I vote that VBA will win Apr 30 '08 #17

 Expert Mod 15k+ P: 31,489 Likewise Delerna. Very neat thinking. I should have thought of that technique after determining what caused my earlier attempt to fail. Unfortunately, I couldn't get it to work :( I did change it slightly. I suspect to what you'd intended. Expand|Select|Wrap|Line Numbers SELECT SourceUID,        TargetUID,        Distance FROM Nn AS oNn WHERE (SELECT Count(*)        FROM Nn AS iNn        WHERE iNn.SourceID=oNn.SourceID          AND iNn.Distance>oNn.Distance)>2 It didn't seem to like the subquery in the WHERE clause at all. PS. VBA may win in Access but generally SQL would work faster (in my experience). In another engine (EG. MS SQL, Oracle, etc) which better supports subqueries (Access is notorious for throwing up its hands in defeat when trying to optimise SQL subqueries or UNION queries) it would almost certainly be more efficient in SQL.. Apr 30 '08 #18

 Expert Mod 15k+ P: 31,489 This is sort of a PPS, but as I noticed I was on 8,999 posts I thought "What the hell" and did it in a separate post :D Anyway, AccessIdiot (M), I'm interested in your data running so slowly as mine wasn't too bad. Can you let me have a copy of your data for me to load into my version of this. I can PM you e-mail details again if you like or, if you still have them, please send it on as either a text file or in Excel or even a whole Access database if you can get it to be small enough (compression etc). Apr 30 '08 #19

 Expert 100+ P: 1,134 I did change it slightly. I suspect to what you'd intended. Expand|Select|Wrap|Line Numbers SELECT SourceUID,        TargetUID,        Distance FROM Nn AS oNn WHERE (SELECT Count(*)        FROM Nn AS iNn        WHERE iNn.SourceID=oNn.SourceID          AND iNn.Distance>oNn.Distance)>2 Yes, that looks more like it. Yes I agree, in general SQL work faster than VB, for the same reason that record based cursors in SQL are slower than set based queries. Cursors have their place of course. By the way, when I say I vote VBA will win I mean against the first solution. Not sure about the second Apr 30 '08 #20

 Expert 5K+ P: 8,636 Yeah! Works great, though very very slow - even scrolling through the resulting table causes it to hang. But it's exactly what we're after - thanks!! Very complicated query and not something I could have come up with. Very elegant! I'm almost finished writing the code that will provide you the same functionality, but hopefully without the sluggishness that you are currently experiencing via the SQL route. There is one small detail that I would like to know. Can you be absolutely, positively sure that there will always be at least two distances for any given point. Should this be the case, code execution will be significantly faster for a large number of Records, because it does not have to traverse the Recordset (rst.MoveLast ==> rst.MoveFirst) in order to retrieve an accurate Record Count, and the dbOpenForwardOnly Option can also be used when opening the Recordset. Apr 30 '08 #21

 Expert 100+ P: 1,134 There is a thread in the SQLSever forum Here with a very similar requirement. This one wants the rows with the best 3 prices for models of cars. The technique is reported to be slow there also. I must say, most of my development is with SQLServer and I have used this technique myself on resonably large tables without too much of a speed issue. I do have indexes though. Apr 30 '08 #22

Expert 2.5K+
P: 2,653
Hello, all.

I've coded hydride SQL/VBA query.

Code Module
Expand|Select|Wrap|Line Numbers
1. Public Function Enumerate(varPoint As Variant) As Long
2.
3.     Static varCurPoint As Variant
4.     Static lngNum As Variant
5.
6.     If varCurPoint <> varPoint Then
7.         lngNum = 0
8.         varCurPoint = varPoint
9.     End If
10.     lngNum = lngNum + 1
11.     Enumerate = lngNum
12.
13. End Function
14.
Query
Expand|Select|Wrap|Line Numbers
1. SELECT tbl.*
2. FROM tbl
3. WHERE Enumerate(tbl.Point)<3
4. ORDER BY tbl.Point, tbl.Distance;
5.
Seems to run faster, though Delerna's solution with subqueries runs reasonably fast too on 96x12=1152 records table (Access2003, Win XP SP2, Celeron 3.33GHz, RAM 512M).

Regards,
Fish
Attached Files
 PolygonPoints.zip (23.1 KB, 92 views)
Apr 30 '08 #23

 100+ P: 493 Wow this post has generated a lot of interest! :-) This whole thing actually stems from a GIS field project with testing wells at a project site location. The data was stored in a geodatabase which is a fancy access database so yes, I have all the data stored in an mdb and am happy to send it to anyone who wants to see it (NeoPa I'll need your email again I think, please PM me). It's pretty big, about 9 MB, and was really more of an exercise in how it could be done than any kind of project deadline, so I wouldn't spend too much time on it, though it would be useful for future projects. cheers! Apr 30 '08 #24

 Expert Mod 15k+ P: 31,489 I would think the easiest and lightest way to do it (all I require is the bare data) would be to open the table (Nn) and open up a new spreadsheet in Excel. When both are open, select the whole table's data (Square at top-left) and use Ctrl-C to copy it to the clipboard. When that process has completed, use Ctrl-V to paste it into the Excel spreadsheet. That should be a smaller file to e-mail. This thread has generated interest for two reasons :It's a tricky / fiddly problem which are naturally interesting anyway. ADezii, PMed me (and possibly others too) to draw my attention to it as he's a naturally helpful chap and SQL is his weaker area (He's a VBA code guru). PS. I'll PM you with my details later. Apr 30 '08 #25

 100+ P: 493 Will do. There are 162,000 records. Do you want the whole thing or just a subset? Let me know! Apr 30 '08 #26

 Expert 5K+ P: 8,636 Here's a drastically different approach. Download the Attachment and see what you think. Expand|Select|Wrap|Line Numbers Dim strSQL_1 As String Dim strSQL_2 As String Dim strSQLInsert As String Dim MyDB As DAO.Database Dim rstUniquePoints As DAO.Recordset Dim rstNn As DAO.Recordset Dim intCounter As Integer   DoCmd.SetWarnings False   DoCmd.RunSQL "DELETE * FROM tblFinalResults;" DoCmd.SetWarnings True   Set MyDB = CurrentDb()   'Needed in order to return Unique Points in Ascending Sort Order strSQL_1 = "SELECT DISTINCT Nn.SOURCEUID FROM Nn ORDER BY Nn.SOURCEUID;" Set rstUniquePoints = MyDB.OpenRecordset(strSQL_1, dbOpenForwardOnly)   If rstUniquePoints.RecordCount = 0 Then Exit Sub   With rstUniquePoints   Do While Not .EOF     strSQL_2 = "SELECT * FROM Nn WHERE Nn.[SOURCEUID] = '" & ![SOURCEUID] & _                "' ORDER BY Nn.DISTANCE ASC;"     Set rstNn = MyDB.OpenRecordset(strSQL_2, dbOpenSnapshot)       If rstNn.RecordCount >= 2 Then      'need at least 2 Points         For intCounter = 1 To 2           'return 2 shortest Distances for the Point           strSQLInsert = "INSERT INTO tblFinalResults (SOURCEUID, TARGETUID, DISTANCE)" & _                          "Values ('" & rstNn![SOURCEUID] & "', '" & rstNn![TARGETUID] & "', " & _                          rstNn![DISTANCE] & ")"           MyDB.Execute strSQLInsert, dbFailOnError             rstNn.MoveNext         Next             rstNn.MoveFirst       End If     .MoveNext   Loop End With   rstNn.Close rstUniquePoints.Close Set rstNn = Nothing Set rstUniquePoints = Nothing   'After all that work, let's see the results DoCmd.OpenTable "tblFinalResults", acViewNormal, acReadOnly Apr 30 '08 #27

 Expert Mod 15k+ P: 31,489 Will do. There are 162,000 records. Do you want the whole thing or just a subset? Let me know! I'll have the whole lot if you can get them. Compress the file if it's that big please. At that number of records it will be easiest to send the whole database. Apr 30 '08 #28

 Expert Mod 15k+ P: 31,489 Update : Expand|Select|Wrap|Line Numbers Table Name=[Nn] Field;      Type;        IndexInfo ID;         AutoNumber;  PK SourceUID;  String TargetUID;  String Distance;   Numeric There are various measurements (Distances) for each Point/Line as each line is considered to have multiple measurement positions (nodes). As this may possibly yield a top 2 which both point to the same side (:() this will actually complicate the problem somewhat further. A fun intellectual exercise for us all I'm sure ;) BTW This explains why there are more than the 1,152 records that would otherwise have been expected. Apr 30 '08 #29

 100+ P: 493 Yep, sorry about that all. The problem was explained to me verbally and then I was given the db and realized it was much bigger than originally explained. Looks like the guys that handed me the puzzle took the polygon and busted each side up into equally spaced nodes, the theory being that the shortest distance will be the most perpendicular, which is what they were after in the first place. My apologies for not providing enough info. Apr 30 '08 #30

 Expert Mod 15k+ P: 31,489 I'm just busy at the moment getting timing info for the three states I'm interested in : Expand|Select|Wrap|Line Numbers Main1 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID] Main2 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID] + Ix4=[SourceUID]&[TargetUID] Main3 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID] + Ix4=[SourceUID]&[TargetUID]&[Distance] When I received the database, the table Nn was set up as (approx) Main1. My expectation is that Main3 will provide the quickest results. I'm using the SQL from post #18 as the query, but saved into a pre-optimised QueryDef for consistency. I'll post when the results arrive. May 1 '08 #31

 Expert Mod 15k+ P: 31,489 Well, that'll teach me. I ran the tests only once, but my results were : Expand|Select|Wrap|Line Numbers Main1: 01/05/2008 01:54:39 - 01/05/2008 02:02:03 = 7'24" Main2: 01/05/2008 01:34:21 - 01/05/2008 01:42:32 = 8'11" Main3: 01/05/2008 01:45:27 - 01/05/2008 01:53:01 = 7'34" I may have to run them again after fiddling the SQL to return the correct results, but these results certainly indicate that the added index didn't help (as I would have anticipated). Anyway, enough for tonight. I'm for bed :) PS. My code was : Expand|Select|Wrap|Line Numbers ?"Main1: " & Now(); : Call DoCmd.OpenQuery("qryTop2",acViewNormal) : ?" - " & Now() I redesigned the table and changed the value in the first string between each run of course (and calculated the resultant time manually and added it afterwards). May 1 '08 #32

 Expert Mod 15k+ P: 31,489 My curiosity got the better of me so I tried running with only the ID index set, and using the following (more refined) code : Expand|Select|Wrap|Line Numbers datBeg=Now() : _   ?"Main0: " & datBeg; : _   Call DoCmd.OpenQuery("qryTop2",acViewNormal) : _   datEnd=Now() : _   ?" - " & datEnd & " = " & Format(datEnd-datBeg,"h\'n\""") I gave up after over 20 mins had elapsed with the output still saying : Expand|Select|Wrap|Line Numbers Main0: 01/05/2008 02:23:33 :( May 1 '08 #33

 Expert Mod 15k+ P: 31,489 I produced some more SQL to handle this situation. It is a considerably more complicated situation than the previous one I'm afraid, and you can expect quite severely slowed performance. Expand|Select|Wrap|Line Numbers SELECT SourceUID,        TargetUID,        Distance FROM (SELECT SourceUID,              TargetUID,              Distance,              (SELECT Count(*)               FROM (SELECT SourceUID,                            TargetUID,                            Min(Nn.Distance) As Distance                     FROM Nn                     GROUP BY SourceUID,                              TargetUID) AS iNn               WHERE iNn.SourceUID=oNn.SourceUID                 AND iNn.Distance

 Expert Mod 15k+ P: 31,489 I set up the time trial with my best indexing and it still took over 1:30 (when I aborted). I will have to think of a practicable way to handle this. It will almost certainly involve adding an ordinal field to the table and an update query being run first. Disappointed -NeoPa. May 2 '08 #35

 Expert Mod 15k+ P: 31,489 As a last attempt before looking at a table update method, I've noticed that both SourceUID & TargetUID are text fields of 255 allowable characters. None of the values is longer than 7 characters so I will see what setting the field lengths to ten does. I also changed the index I created (SourceUID; TargetUID; Distance) to specify Unique. I understand that the space taken up in the data is no more if the field is specified as 255 than if it's done as ten, but it may effect the indexing performance. I don't know. I'll see. May 2 '08 #36

 Expert Mod 15k+ P: 31,489 2.5 hours later there are still no results. Regardless of whether or not this is more efficient, it's clearly not a solution that is workable in the real world. Next: Add a field into the table which is reset for every run. Essentially a scratch field which reflects the ordinal position of the Point/Side record by Distance. Maybe even a simple flag to indicate whether or not it's the closest. That way a SELECT query can be run along similar lines to the earlier versions, but which ignores all but the nearest Distances from the start. A much more manageable set of records to deal with. I'll post progress. May 3 '08 #37

 Expert Mod 15k+ P: 31,489 Step 1: The procedure (that works) is first of all to add a new Yes/No field called [Nearest] to the table. This is not added into any indexes. The recently added Main index (SourceUID; TargetUID; Distance) should still be in place. The overall work is done by three queries (QueryDefs in Access). Remember, the optimisation of the QueryDef is done the first time it's run, so don't rely on timings of that run. Run normally, these QueryDefs in sequence, take very little more than 8 minutes in total. Considering the number of records that are to be processed this is not a long time at all. May 3 '08 #38

 Expert Mod 15k+ P: 31,489 Step 2: [Nearest] must start off as set (to True) in all records. To do this we have a QueryDef called qryTopSet set up with the following SQL : Expand|Select|Wrap|Line Numbers UPDATE Nn SET [Nearest]=True I used the following code in the Immediate pane to run this and log the time taken : Expand|Select|Wrap|Line Numbers Call DoCmd.SetWarnings(False) : _   datBeg=Now() : _   ?"Main1: " & datBeg; : _   Call DoCmd.OpenQuery("qryTopSet",acViewNormal) : _   datEnd=Now() : _   Call DoCmd.SetWarnings(True) : _   ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""") The results of this step were : Expand|Select|Wrap|Line Numbers Main1: 03/05/2008 02:01:21 - 03/05/2008 02:01:28 = 0'7" May 3 '08 #39

 Expert Mod 15k+ P: 31,489 Step 3: Next, the Point/Side records which have [Distance]s which are larger than ANY of the other matching Point/Side [Distance]s, must have [Nearest] reset (to False). To do this we have a QueryDef called qryTopReset set up with the following SQL : Expand|Select|Wrap|Line Numbers UPDATE Nn AS Nn1 INNER JOIN Nn AS Nn2     ON (Nn1.TargetUID=Nn2.TargetUID)    AND (Nn1.SourceUID=Nn2.SourceUID) SET Nn1.Nearest=False WHERE (Nn1.Nearest)   AND (Nn1.Distance>Nn2.Distance) I used the following code in the Immediate pane to run this and log the time taken : Expand|Select|Wrap|Line Numbers Call DoCmd.SetWarnings(False) : _   datBeg=Now() : _   ?"Main2: " & datBeg; : _   Call DoCmd.OpenQuery("qryTopReset",acViewNormal) : _   datEnd=Now() : _   Call DoCmd.SetWarnings(True) : _   ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""") The results of this step were : Expand|Select|Wrap|Line Numbers Main2: 03/05/2008 02:02:15 - 03/05/2008 02:10:11 = 7'56" May 3 '08 #40

 Expert Mod 15k+ P: 31,489 Step 4: Now all the records are prepared. Only the nearest Point/Side records are flagged as [Nearest]. Now, and only now, are we in a position to execute the logic (code) that was originally considered for this problem in the knowledge that we're only processing through 1,152 records rather than 161,880. To do this we have a QueryDef called qryTop2 set up with the following SQL : Expand|Select|Wrap|Line Numbers SELECT sNn.SourceUID,        sNn.TargetUID,        sNn.Distance FROM (SELECT SourceUID,              TargetUID,              Distance,              (SELECT Count(*)               FROM Nn AS iNn               WHERE iNn.Nearest                 AND iNn.SourceUID=oNn.SourceUID                 AND iNn.Distance

 Expert Mod 15k+ P: 31,489 I'm curious to know how you got on with this. I received your e-mail, but I don't know if 8 minutes was within the sort of time-frame you'd envisaged as acceptable. May 13 '08 #42