Fastest way to Aggregate?

[Guest]

Hi,

I often use forms to show large amounts of summary data. I'm wondering what
the "best practice" for this in terms of efficiency and speed of displaying
results. For example I'm building a form which needs to have 105 aggregated
fields, and my current method of using dcount will take forever (no, really
it deos...). There must be a better way!




Any help appreciated

 

[Guest]

Post the code you are using now.
What do you mean by Aggregating?
How do these 105 fields relate to each other?

 

[Guest]

Klatuu,

The form is based on a query with the following SQL:

SELECT tblOutcomeReview.fkContactID, tblOutcomeReview.NewRevEnd,
tblOutcomeReview.Date_comp, tblOutcomeReview.[1], tblOutcomeReview.[2],
tblOutcomeReview.[3], tblOutcomeReview.[4], tblOutcomeReview.[5],
tblOutcomeReview.[6], tblOutcomeReview.[7], tblOutcomeReview.[8],
tblOutcomeReview.[9], tblOutcomeReview.[10], tblOutcomeReview.[11],
tblOutcomeReview.[12], tblOutcomeReview.[13], tblOutcomeReview.[14],
tblOutcomeReview.[15], tblOutcomeReview.[16], tblOutcomeReview.[17],
tblOutcomeReview.[18], tblOutcomeReview.[19], tblOutcomeReview.[20],
tblOutcomeReview.[21], tblOutcomeReview.[22], tblOutcomeReview.[23],
tblOutcomeReview.[24], tblOutcomeReview.[25], tblOutcomeReview.[26]
FROM tblOutcomeReview;


the fields ([1], [2], etc) represent outcomes, and may each contain any
number between 1 and 5 for each record. I need the form to display how many
times each response (1 to 5) occurs for each of the 26 outcomes. The form
currently has the 26 outcomes listed on the left, and numbers 1-5 across the
top. For every possible combination of outcome and response there is a text
box with a source such as this:

=DCount("[1]","[qryRdistrict_outcomes]","[1]=1")
=DCount("[1]","[qryRdistrict_outcomes]","[1]=2") etc

This DOES work, but the calculations take forever.

I'd also like to have another text box per outcome-response possibility,
showing the number of instances as a % of total records:

=(DCount("[1]","[qryRdistrict_outcomes]","[1]=1"))/(Count(*))

So I actually have 210 dcount calculations happening here.

Thanks