Z
Zilla
Hi,
I'm trying to produce several aggregated reports based on data relating to
individuals using various services accross several geographical disticts. The
reports must contain a certain amount of information about these individuals
and their patterns of service use in an aggregated fashion. The problem I
have is finding the correct approach to doing this in a way which will not
end up with reports taking 30 min to load.
For example., for one section I need to display the following data: age band
by ethnicity by gender. So I had made something which resembles a table using
text boxes, with "Age Category" as row headings and "male", "female", "total"
field headings for each of the 6 Ethnicity categories I'm using. This set of
data then uses 108 seperate text boxes. In order to count distint values I'm
having to use the special Ecount function (...for example:
=ECount("*","qryRdistrict2","[ethnicity] = 'chinese' and [Ageband] = '0-4'
and [gender] = 'f'")...)
As you might imagine having this running 108 times is going to take a very
very long time. I have set up 24 so afr and it is taking about 1 min to
compute, and there are lots of other bits of data to display apart from this
set.
My question is: what is the correct approach to displaying a large amont of
aggregated data involving many criteria in an Access database? I'm looking
for a theoretical approach as much as anything at the moment, as my current
solution is bloated beyond what is reasonable before I have even barely begun.
Any help really appreciated
Jim
I'm trying to produce several aggregated reports based on data relating to
individuals using various services accross several geographical disticts. The
reports must contain a certain amount of information about these individuals
and their patterns of service use in an aggregated fashion. The problem I
have is finding the correct approach to doing this in a way which will not
end up with reports taking 30 min to load.
For example., for one section I need to display the following data: age band
by ethnicity by gender. So I had made something which resembles a table using
text boxes, with "Age Category" as row headings and "male", "female", "total"
field headings for each of the 6 Ethnicity categories I'm using. This set of
data then uses 108 seperate text boxes. In order to count distint values I'm
having to use the special Ecount function (...for example:
=ECount("*","qryRdistrict2","[ethnicity] = 'chinese' and [Ageband] = '0-4'
and [gender] = 'f'")...)
As you might imagine having this running 108 times is going to take a very
very long time. I have set up 24 so afr and it is taking about 1 min to
compute, and there are lots of other bits of data to display apart from this
set.
My question is: what is the correct approach to displaying a large amont of
aggregated data involving many criteria in an Access database? I'm looking
for a theoretical approach as much as anything at the moment, as my current
solution is bloated beyond what is reasonable before I have even barely begun.
Any help really appreciated
Jim