Need Access 2010 VBA code instead of sql query that is taking 30 minutes to run RRS feed

  • Question

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

    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)

    Table: T_SH_CumBal_Temp5 contains the transaction data for all owners (88,000 records)

    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.

    Any help is much appreciated.


    INSERT INTO T_SH_CumBal_AlloPeriod ( Ticker, PtnrNum, AlloDate, Yr, Mo, Dy, CumBal, Cum_NS, DatePct, Shares )

    SELECT T_AllocationPeriods.Ticker, T_SH_CumBal_Temp5.PtnrNum, T_AllocationPeriods.TradeDate AS AlloDate, T_AllocationPeriods.Trade_Dt_Yr AS Yr, T_AllocationPeriods.TradeMonthV AS Mo, T_AllocationPeriods.Trade_Dt_Dy AS Dy, Nz(DSum("TLDay","T_SH_CumBal_Temp5","[T_SH_CumBal_Temp5]![TradeDate]<=" & [T_AllocationPeriods]![TradeDate] & "And [PtnrNum]=" & [Ptnrnum]),0) AS CumBal, IIf(DSum("TLDay","T_SH_CumBal_Temp5","[T_SH_CumBal_Temp5]![TradeDate]<=" & [T_AllocationPeriods]![TradeDate] & "And [PtnrNum]=" & [Ptnrnum])<0,0,Nz(DSum("TLDay","T_SH_CumBal_Temp5","[T_SH_CumBal_Temp5]![TradeDate]<=" & [T_AllocationPeriods]![TradeDate] & "And [PtnrNum]=" & [Ptnrnum]),0)) AS Cum_NS, T_AllocationPeriods.DatePct, T_AllocationPeriods.Shares
    FROM T_AllocationPeriods INNER JOIN T_SH_CumBal_Temp5 ON (T_AllocationPeriods.Ticker = T_SH_CumBal_Temp5.Ticker) AND (T_AllocationPeriods.TradeDate = T_SH_CumBal_Temp5.TradeDate) AND (T_AllocationPeriods.TradeMonthV = T_SH_CumBal_Temp5.TradeMoV) AND (T_AllocationPeriods.Trade_Dt_Yr = T_SH_CumBal_Temp5.TradeYr)
    GROUP BY T_AllocationPeriods.Ticker, T_SH_CumBal_Temp5.PtnrNum, T_AllocationPeriods.TradeDate, T_AllocationPeriods.Trade_Dt_Yr, T_AllocationPeriods.TradeMonthV, T_AllocationPeriods.Trade_Dt_Dy, T_AllocationPeriods.DatePct, T_AllocationPeriods.Shares
    ORDER BY T_SH_CumBal_Temp5.PtnrNum, T_AllocationPeriods.TradeDate;

    Monday, March 6, 2017 6:25 AM

All replies

  • 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 is not going to buy you much (if anything) over the speed of SQL.

    peter n roth -, Maybe some useful stuff

    Monday, March 6, 2017 5:38 PM
  • To start with, remove the ORDER BY clause. You can always sort your results after. And using domain aggregates such as DSum are also a killer. Write another query that gets the sums you need and add it to your query.

    Bill Mosca

    Tuesday, March 7, 2017 2:59 PM