需要Access 2010 VBA代码而不是运行30分钟的SQL查询 [英] Need Access 2010 VBA code instead of sql query that is taking 30 minutes to run
问题描述
我有一个至少需要20分钟才能运行的Access 2010查询。 我知道有更好的方法可以通过VBA使用记录集来做到这一点，但是我是新手用户，之前从未使用过它们。
I have an Access 2010 query that takes at least 20 minutes to run. I know there is a better way to do this through VBA using record sets, however I am a novice user and have never used them before.
关于这个问题的简短介绍：
The short-story about this issue:
我需要在一年中的特定日期计算每个基金所有者的每日总数和百分比。
I need to calculate daily totals and percentages for each owner of a fund on given dates in a year.
表：T_SH_CumBal_AlloPeriod包含日期，我需要为每个所有者计算的每个日期的百分比和总份额值。 （18条记录）
Table: T_SH_CumBal_AlloPeriod contains the dates, percentages and total share values for each date I need to calculate for each owner. (18 records)
表：T_SH_CumBal_Temp5包含所有所有者的交易数据（88,000条记录）
Table: T_SH_CumBal_Temp5 contains the transaction data for all owners (88,000 records)
该查询使用DSUM公式计算每个所有者的总数每个日期的所有者，并在分配表中的每个日期计算每个所有者的所有权百分比。
The query uses DSUM formulas to calculate totals for each owner on each date and calculates the percent of ownership for each owner on each date in the Allocation table.
非常感谢任何帮助。
< span style ="font-size：0.75em"> _________________________________________________________________________________________________________________________________
_________________________________________________________________________________________________________________________________
INSERT INTO T_SH_CumBal_AlloPeriod（Ticker，PtnrNum，AlloDate，Yr，Mo，Dy，CumBal，Cum_NS，DatePct，Shares）
INSERT INTO T_SH_CumBal_AlloPeriod ( Ticker, PtnrNum, AlloDate, Yr, Mo, Dy, CumBal, Cum_NS, DatePct, Shares )
推荐答案
我建议您先重写查询以进行选择，然后将计算推迟到您在结果上运行的第二个查询第一个。
I suggest you rewrite the query to do the selection first, and postpone the calculations to a second query which you run on the results of the first.
VBA不会比SQL的速度更快（如果有的话）。
VBA is not going to buy you much (if anything) over the speed of SQL.
这篇关于需要Access 2010 VBA代码而不是运行30分钟的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
