在Microsoft Access查询中使用数组

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.

我在Microsoft Access中有一个查询,用于更新表(I_Postcodes)中的zipcodes / postcodes,其中包含从基本原始邮政编码(在I_BasePostcode中保存)到相关邮政编码所需的距离和时间。

My Access query for determining distance only is:

仅用于确定距离的My Access查询是:

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:

I_BasePostcode表如下所示:

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

and an extract from the I_Postcodes table looks like:

并且I_Postcodes表的摘录如下所示:

+----------+------------------+--------------+
| 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:

GetDistance函数对于这个问题的目的有点复杂,但它可以正常工作,并返回一个由两个值组成的数组:距离整数和时间整数。我可以通过以下方式访问:

' 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?

我想更新我的查询以包括前往目的地所花费的时间,但我不确定如何在不调用GetDistance函数两次的情况下执行此操作?

1 个解决方案

#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.

使用静态变量在GetDistance函数中返回数组和先前的参数值。如果参数相同且数组已具有有效值,则从静态变量返回数组,否则计算时间/距离。