VBA代码循环表和匹配条件的计数记录 [英] VBA Code to Loop Through Table and Count Records That Match Criteria

问题描述

大家好，

我以前从未在这样的网站上发布过但很遗憾有点卡在这个问题上......希望比我更聪明的人可以帮助我！

我在MS Access 2013中有一张表，其中包含纬度和经度值。我想使用距离公式（如下所示）来循环遍历同一个表中的所有记录，并计算用户定义的距离彼此之间的条目。理想情况下，我可以同时运行更新查询，将值转储回表中，以便在可视化软件中使用。该表有大约200,000个条目。无法想象这足以引起问题。


说我甚至是VBA的业余爱好者都会有所帮助，但我通常可以解决最基本的问题。这个有点超出我的能力水平，所以任何帮助将不胜感激！提前感谢您的帮助！

展开 | 选择 | Wrap | 行号

解决方案

校长非常简单，但有一个潜在的速度问题。


所以一些基础知识。

你需要一张TblPlaces的地方表来说

PlaceID自动编号PK

放置文字

Lat Double

Lon Double


你需要第二张桌子TblJoinPlacePlace带

PlaceID1长关节PK

PlaceID2长关节PK

距离Double


与2份TblPlace和1份TblJoinPlacePlace建立关系，并将PlaceID从第一个TblPlace加入PlaceID1和PlaceID来自第二个TblPlace到PlaceID2。


这个安排将每个地方都链接到每个地方。


问题没有人是n个地方，有需要n *（n-1）/ 2个连接，因此对于200,000个位置，将有大约20,000,000,000个记录。这是很多计算，我没有时间来运行这个计算。


再次。我会给你VBA基础知识，但是如果你需要进一步的帮助，请回来。


在代码中，你需要创建3个查询。

我将称之为OuterQuery的第一个以PlaceID顺序读取TblPaces中的每个记录。

第二个查询，InnerQuery以PlaceID顺序读取TblPlaces中的每个记录WHERE PlaceID> PlaceID在OuterQuery中。

第三个查询是创建TblJoinPlacePlace，所以你将PlaceID从OuterQuery添加到PlaceID1，从InnerQuery到PlaceID2的PlaceID，通过调用你的函数DistanceFeet进行计算（ lat1 As Double，lon1 As Double，lat2 As Double，lon2 As Double）基于Lat&很长一段时间来自OuterQuery（你的Lat1& Lon1）和Lat&很久就从InnerQuery（你的Lat2& Lon2）获得距离。


我不知道你的函数DistanceFeet（lat1 As Double，lon1 As Double，lat2 As Double，lon2正如Double）可以工作，但需要更改为

展开 | 选择 | Wrap | 行号

您是否拥有Latitude和已经定义了经度？


如果是这样，您应该能够创建一个调用VBA函数并返回结果的查询。如果您要提供表格结构，我们可以为您提供查询。

非常感谢您的快速回复！您有一个调用VBA函数并返回结果的查询的想法是我希望完成的。我对VBA的了解非常有限，无法确定使其工作的确切语法。


我已经定义了一个表。该表的名称为[AC_PROPERTY]，其中包含大约50-60列数据，大约有200,000个条目。出于此功能的目的，我认为唯一相关的列将是唯一标识符[PROPNUM]，[LATITUDE]和[LONGITUDE]。


再次感谢您对此问题的持续帮助！

Hi everyone,
I have never posted on a site like this before but am unfortunately a bit stuck on this problem... Hopefully someone much smarter than myself can help me out!

I have a table in MS Access 2013 full of latitude and longitude values. I am wanting to use a distance formula (something like one shown below) to loop through all the records in this same table and count entries that are within a user defined distance of one-another. Ideally I could run an update query at the same time to dump the values back into the table for use in visualization software. The table has approximately 200,000 entries. Can''t imagine that is enough to cause a problem.

Saying that I am even an amateur in VBA would be a stretch but I can usually figure most basic problems out. This one is a bit outside of my ability level though so any help would be greatly appreciated! Thanks in advance for any help!

Expand|Select|Wrap|Line Numbers

解决方案

The principal is very simple, but there is a potential problem of speed.

So a few basics.
You need a table of places TblPlaces with say
PlaceID AutoNumber PK
Place Text
Lat Double
Lon Double

You need a second table TblJoinPlacePlace with
PlaceID1 Long Joint PK
PlaceID2 Long Joint PK
Distance Double

Set up a relationship with 2 copies of TblPlace and 1 copy of TblJoinPlacePlace and join PlaceID from the first TblPlace to PlaceID1 and the PlaceID from the second TblPlace to PlaceID2.

This arrangement links every place to every place.

Problem no one is that for n locations, there are n*(n-1)/2 joins required, so with 200,000 locations, there will be approximately 20,000,000,000 records. That''s a lot of calculation, and I have no concept of the time to run this calculation.

Again. I''ll give you the VBA basics, but if you need further help, please come back.

In code, you need to create 3 queries.
The first one which I shall call OuterQuery reads every record in the TblPaces in PlaceID Order.
The second query, InnerQuery reads every record in the TblPlaces in PlaceID Order WHERE PlaceID > PlaceID in the OuterQuery.
The third query is to create the TblJoinPlacePlace, so you add the PlaceID from the OuterQuery to PlaceID1, the PlaceID from the InnerQuery to PlaceID2, Do your calculation by calling your Function DistanceFeet(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) based on the Lat & Long from OuterQuery (Your Lat1 & Lon1) and the Lat & Long from the InnerQuery (Your Lat2 & Lon2) to get the distance.

I have no idea if your Function DistanceFeet(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) will work, but it needs changing to

Expand|Select|Wrap|Line Numbers

Do you have the tables with Latitude and Longitude already defined?

If so, you should be able to create a single Query that calls your VBA function and return your results. If you were to provide your table structure, we could provide you a query.

Thank you so much for the quick response! Your idea of having one query that calls the VBA function and returns the results is sort of what I was hoping to accomplish. My knowledge of VBA is just too limited to figure out the exact syntax to make it work.

I do already have a table defined. The name of the table is [AC_PROPERTY] and has approximately 50-60 columns of data in it with about 200,000 entries. For purposes of this function, the only relevant columns I believe would be the unique identifier [PROPNUM], [LATITUDE] and [LONGITUDE].

Thanks again for the continued help with this problem!

这篇关于VBA代码循环表和匹配条件的计数记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！