Enhance performance w/ running totals using DSUM or Select

11-10-2004, 05:30 PM

I need to calculate running totals using Access 2003 in query. I'll be using the running total against a grand total to calculate percent achievement over time. I've used both of the expressions below "within the qbe grid"...they work...but it takes an extremely long time to update a table with around 67,000 records. Note that I also need the additional field references to restart the running total for different groups. I have indexed the fields that these expressions are calling on. Is there a faster way?

Thank you. I look forward to your reply.


CumlDoors: (DSum("NbrDoors","Top15MktDistMT","[WeekChronoNbr]<=" & [AliasWeekChronoNbr] & "and [Acct]='" & [AliasAcct] & "' and [Market_Group]='" & [AliasMarketGroup] & "' and [Subtitle]='" & [AliasSubtitle] & "' and [Edition_Year]='" & [AliasEditionYear] & "'"))

CumlDoors: (Select sum(NbrDoors) from Top15MktDistMT where [WeekChronoNbr]<=[AliasTop15MktDistMT].[WeekChronoNbr] and [Acct]=[AliasTop15MktDistMT].[Acct] and [Market_Group]=[AliasTop15MktDistMT].[Market_Group] and [Subtitle]=[AliasTop15MktDistMT].[Subtitle] and [Edition_Year]=[AliasTop15MktDistMT].[Edition_Year])

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum