asked on

Multi-Dimension Dynamic Array to create a new table of data in Ms Access VBA

I have a table of data The table contains data that I need to calculate the distance between two points,. I have the Latitude, and Longitude of each location I have a formula that works well in excel but it is time consuming as there are thousands of records to check. the data looks like the following

table: tbldata
fields used: ID(datatype autonumber/integer), LS (datatype text) , Lat(datatype long), Log(datatype Long), status(datatype text),

What is needed is a way to create a new table, in the original table are three types of records.
active, pending , and closed records.

I need to know the distance between each active record and all other active, pending, and closed records.

the final table that would be created would look like this.

tblnewdata: TargetID, TargetLS, TargetLat, TargetLog, CheckID, CheckLS, CheckLat, CheckLog, Distance

the formula I used in excel was as follows.

=6371*ACOS(COS(RADIANS(90-D7))*COS(RADIANS(90-D8))+SIN(RADIANS(90-D7))*SIN(RADIANS(90-D8))*COS(RADIANS(E7-E8)))/1.609

D7 is the Target Latitude
D8 is the Check Latitude
E7 is the Target Longitude
E8 is the CheckLongitude

I had problems running that equation in VBA it seemed to have trouble with Radians, the other trig elements seem to be fine ie. Acos, Cos, Sin but I did not get it to work yet.

I tried to place the lats and log in the formula and created a button to run the equation but, it didn't work.

from what I researched it is a nested loop getting the first active record, then going back to the table and getting the next record and running the equation until all records have been done, then it loops back up to get the second record and repeat running the equation against all therecords in the database and recording or writing the results along with the field info needed in the new table until completed.

ie.

For x = 0 to (Dcount of main table where status is active - 1)
For Y = 0 to (Dcount of main table all records -1)[ minus 1 because of base 0]
now the SQL select to get the data needed ie. fields
now run the calculation to get mileage between
next Y
Next X

then a routine to write all data to a table that is created.

I did some programming years ago, I just do not have any resources to figure this out. I appreciate any help in getting things to work.

Thank you,

Bitsqueezer

Hi,

maybe you should upload an example database and Excel file to have a better understanding of what you try to reach. We are not all specialists in geographic calculations..;-)

But from what you have described I would saythat you don't need two loops in VBA and access each record of the table(s) because that would assumingly not be faster than your Excel file. In a database we work with SQL and if you want to process each record of the table with each other record of the same table you can simply create a cross join, that is done in Access by simply add the same table twice in a query definition. Like: "SELECT All,my,fields FROM MyTable, MyTable AS MyTable1" as example.

You can use SIN and COS as functions in SQL also, for calculating a degree value into radians and other mathematical functions not directly available you can always write a public function in VBA which can then be called in SQL. Here is an example of a radians function:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=719

Here you can find a function to calculate the ACOS:
https://www.access-programmers.co.uk/forums/showthread.php?t=33779

If you have the Public Functions in a standard module in VBA you can then use that in the cross join query like described above, of course replacing the field list with your formula.

Normally in a database you do not write the result into a target table, the job of a query is to calculate the result whenever needed. If the tables are designed in the right way (i.e. using indexes and so on) it's normally fast enough for a big amount of records.
But if you want, you can add an "INSERT INTO..." before the SELECT (if your target table already exists which should be the case) or if not using the "SELECT...INTO" syntax. You find all descriptions about these commands in the Access help or your favourite search engine in the Internet with tons of examples.

In all cases you should not use a nested For-Loop for the job and recordsets, does not make sense here.

Cheers,

Christian

crystal (strive4peace) - Microsoft MVP, Access

hi Daniel,

here is my function to get distance between 2 points:

Function GetDistance(pLat1 As Double, pLng1 As Double _
   , pLat2 As Double, pLng2 As Double _
   , Optional pWhich As Integer = 1 _
   ) As Double
'strive4peace 12-13-08, 12-22
   ' calculates distance between 2 points of Latitude and Longitude
   ' in Statute Miles, Kilometers, or Nautical Miles
   
   'PARAMETERS
   ' pLat1 is Latitude of the first point in decimal degrees
   ' pLng1 is Longitude of the first point in decimal degrees
   ' pLat2 is Latitude of the second point in decimal degrees
   ' pLng2 is Longitude of the second point in decimal degrees
   
   On Error Resume Next
   Dim EarthRadius As Double
   
   Select Case pWhich
   Case 2:
      EarthRadius = 6378.7
   Case 3:
      EarthRadius = 3437.74677
   Case Else
      EarthRadius = 3963
   End Select
   
   ' Radius of Earth:
   ' 1  3963.0 (statute miles)
   ' 2  6378.7 (kilometers)
   ' 3  3437.74677 (nautical miles)
   ' to convert degrees to radians, divide by 180/pi, which is 57.2958
   GetDistance = 0
   
   Dim X As Double
    
    X = (Sin(pLat1 / 57.2958) * Sin(pLat2 / 57.2958)) _
      + (Cos(pLat1 / 57.2958) * Cos(pLat2 / 57.2958) * Cos(pLng2 / 57.2958 - pLng1 / 57.2958))
      
   GetDistance = EarthRadius * Atn(Sqr(1 - X ^ 2) / X)
End Function

Open in new window

I see you were using 6371 for the Earth's radius, which is different than what I used -- both are ok since it is not consistent (using tenths is misleading!). If you have a lot of points, you probably want to use the logic in the function in an expression instead of calling the function so it is faster.

here is a video which shows how to makes a mileage chart between cities much like you would see in a map book (what it seems you want to do):

Convert VLOOKUP equations to Access
https://www.youtube.com/watch?v=ED_Wd4w9PLk
look in the video description for the link to download the samples

have an awesome day,
crystal

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.