DSum runs very slow....is there an alternative method?

[silv0920]

Hi,

I have a DSum statement in my access query to get a cumulative total at the end of each month by account number. I have over 200k records and it runs very slow. It can run for hours and still not give me any results. Is there an alternative way to get this to run faster or another way to get a running total? I am working in Access 2007.
Here is an example of what my table looks like:
Month/Year Account# Amount Cum Total
01-2015 123 $100 $100
02-2015 123 $200 $300
03-2015 123 $100 $400
01-2015 456 $200 $200
02-2015 456 $100 $300
03-2015 456 $50 $350

Any help will be greatly appreciated.
Thanks!

 

[sheetspread]

I expected to hear back from you about that, dsum (as Giacomo told me years ago) is much better for reports, not queries. It is impractically slow for so many records. Instead you can write a subquery or better still a summary query of the totals by month, then join that to a query of your current table.

 

[silv0920]

ok, i'm not familiar with subqueries but I'll try the second option you mentioned. Thanks!