counting takes too long

  • Thread starter Thread starter Jean-Paul De Winter
  • Start date Start date
J

Jean-Paul De Winter

Hi,

Out of a table with about 60000 records I want to count every record form
Januari (Februari-March-...) over the last 3 years (2004-2003-2002)
I use following formula:
=IIf(IsNull(DSum("[reeks]";"voorschr";"Year([datvoor])=StartYear And
Month([datvoor])=1"));0;(DSum("[reeks]";"voorschr";"Year([datvoor])=StartYea
r And Month([datvoor])=1")))

This works but takes ages to finally show a form with 36 fields.

There must be a faster way.
Any idea??

Thanks
 
What about a Summation query? Something along the lines of:

SELECT Format([datvoor], "yyyymm"), Sum([reeks]) AS MonthTotal
FROM MyTable
GROUP BY Format([datvoor], "yyyymm")
 
I tried to modify this like:

SELECT Format([datvoor],"yyyymm") AS Expr1, Sum([reeks]) AS MonthTotal
FROM VOORSCHR
GROUP BY Format([datvoor],"yyyymm")
HAVING (((Format([datvoor],"yyyymm"))=Year(Date()-1)+"01"));

But this doesn't seem to work... what am I doing wrong?
JP
Douglas J. Steele said:
What about a Summation query? Something along the lines of:

SELECT Format([datvoor], "yyyymm"), Sum([reeks]) AS MonthTotal
FROM MyTable
GROUP BY Format([datvoor], "yyyymm")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jean-Paul De Winter said:
Hi,

Out of a table with about 60000 records I want to count every record form
Januari (Februari-March-...) over the last 3 years (2004-2003-2002)
I use following formula:
=IIf(IsNull(DSum("[reeks]";"voorschr";"Year([datvoor])=StartYear And
Click to expand...
Month([datvoor])=1"));0;(DSum("[reeks]";"voorschr";"Year([datvoor])=StartYea
r And Month([datvoor])=1")))

This works but takes ages to finally show a form with 36 fields.

There must be a faster way.
Any idea??

Thanks
Click to expand...
Click to expand...
 
I tried to modify this like:

SELECT Format([datvoor],"yyyymm") AS Expr1, Sum([reeks]) AS MonthTotal
FROM VOORSCHR
GROUP BY Format([datvoor],"yyyymm")
HAVING (((Format([datvoor],"yyyymm"))=Year(Date()-1)+"01"));

But this doesn't seem to work... what am I doing wrong?
Click to expand...

Try simplifying it a bit: you're jumping back and forth from date/time
to Text fields, and it's not necessary to do so.

SELECT Format([datvoor],"yyyymm") AS Expr1, Sum([reeks]) AS MonthTotal
FROM VOORSCHR
GROUP BY Year([datvoor]), Format([datvoor], "yyyymm")
WHERE Month([datvoor]) = 1;


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top