Use array in Microsoft Access query

Question

I have a query in Microsoft Access which updates zipcodes/postcodes in a table (I_Postcodes) with the distance and time it takes to travel from a base origin zipcode (held in I_BasePostcode) to the zipcode concerned.

My Access query for determining distance only is:

UPDATE I_Postcodes, I_BasePostcode
SET I_Postcodes.DistanceFromBase = GetDistance(I_BasePostcode.Postcode,I_Postcodes.Postcode)
WHERE (I_Postcodes.DistanceFromBase Is Null);

The I_BasePostcode table looks like:

+----------+
| Postcode |
+----------+
| LS1 3EX  |
+----------+

and an extract from the I_Postcodes table looks like:

+----------+------------------+--------------+
| Postcode | DistanceFromBase | TimeFromBase |
+----------+------------------+--------------+
| SW13 9EE |               50 |          200 |
| SW13 9EF |               50 |          201 |
| SW13 9EG |               52 |          210 |
+----------+------------------+--------------+

The GetDistance function is somewhat complicated for the purposes of this question but it works as I hope and returns an array consisting of two values: A distance integer and a time integer. I can access both by calling:

' Distance
GetDistance(Origin, Destination)(0)

' Time
GetDistance(Origin, Destination)(1)

I would like to update my query to include the time taken to travel to a destination but am unsure how I can do this without calling the GetDistance function twice?

Is the GetDistance function expensive, and is it called frequently for the same values? If so, it might be worthwhile to build a dictionary of previous calls and the result; this would not only fix your issue of calling the same function twice (you would have two functions that call the same dictionary), but it could potentially speed up duplicate calls for the same from/to locations — Hambone
– Hambone, Commented Dec 24, 2015 at 20:55

Sergey S. · Accepted Answer · 2015-12-24 16:42:13Z

1

Use static variables for returning array and previous argument values in GetDistance function. If arguments are the same and array already has valid values, return array from static variable, otherwise calculate time/distance.

answered Dec 24, 2015 at 16:42

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

noobmaster69 Over a year ago

What would that look like?

Gord Thompson Over a year ago

"Use static variables ..." - Yup, that's what I had envisioned as well. Another alternative would be to use a Recordset update instead of a SQL query.

Sergey S. Over a year ago

It looks nothing special, just remember that if you use Static instead of Dim in declarations, the variable keeps current value between function calls, so you don't need to recalculate result if parameters are the same.

Collectives™ on Stack Overflow

Use array in Microsoft Access query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related