Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Vancouver, Br. Columbia, Canada
    Thanked 0 Times in 0 Posts

    Use SQL instead of VBA loop (AXP)

    There is a method for calculating the area of a closed polygon with known vertices that involves looping thru all the records ( ). As a curiosity, is it possible to duplicate this functionality in SQL? The problems that I foresee is that the calculations require referencing two records simultaneously and that the first vertex must be referenced to the 2nd vertex and the Nth vertex (using the MOD function in the referenced algorithm).

    My current project has a table of vertices for the polygon vertices structured like this:
    PolygonID (100's of polygons)
    OrientationID ( with 5 different orientations each)
    VertexID (and a variable number of vertices on each polygon)
    Northing (the Y-coordinate)
    Easting (the X-coordinate)

    I plan to loop thru all the polygons, with all the orientations, and all the vertices, and calculate the sums as per the algorithm. Results to be stored in another table. I would be curious if someone could suggest a way to do it via SQL. It's not vital to the success of project to convert to SQL -- more of an exploration of what is feasible.
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts

    Re: Use SQL instead of VBA loop (AXP)

    I haven't tried to work out a solution, but here is a reference to an article on how to refer to the next record in a query.;en-us;101081

    I imagine you could incorporate Mod into the this to loop back to the start.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Use SQL instead of VBA loop (AXP)

    Assuming that vertices are always numbered from 1 to the number of vertices for each polygon, something like this would return the vertex and next vertex:

    SELECT t1.PolygonID, t1.OrientationID, t1.VertexID t1.Northing, t1.Easting, t2.Northing, t2.Easting
    FROM tblVertices AS t1 INNER JOIN tblVerticeses AS t2 ON t1.PolygonID = t2.PolygonID AND t1.VertexID = (t2.VertexID Mod DCount("*","tblEmployees", "PolygonID = " & t1.PolygonID)) + 1;

    Note: such a query won't be displayed correctly in design view; you must use SQL view to see/modify the design.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts