DSUM problem

[Khalil Islamzada]

Hi,
I have an access database for customers, which I need to have running sum. I already used the DSUM formula but it is really slow, I need about 5 minutes to run this query.
the columns are as follow:


ID Year Month Customers Rsum
1 2012 1 10 10
2 2012 2 15 25


Can anyone help me in this regard?


Khalil

 

[CJ_London]

using domain functions in queries is slow.

you can replace with a sub query which should be quicker - something like

select D.*, (SELECT sum(customers) FROM myTable R WHERE ID<=D.ID) as Rsum
FROM myTable D

Note the use of aliasing

and should be quicker still, but not viewable in the query window

select D.ID, D.Year, D.Month, sum(R.customers) as RSum
FROM myTable R INNER JOIN myTable D ON R.PK<=D.PK
GROUP BY D.ID, D.Year, D.Month

this last one you can build in the query window with the usual = join, but then go into SQL and add the <

 

[arnelgp]

you may try using sub query:

select id, [Year], [month], Customers, (select sum("*") from tableToSum As T1 where Year(T1.dateField) & Month(T1.dateField) <= table1.[Year] & table1.[Month]) As RSum From table1

 

[CJ_London]

other ways to make your query faster if you are going to use year and month as the basis for summing is to combine in a single numeric field and index it.

You can still split out in your query if required

Be aware year and month are reserved words so should not be used as field names