If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Fastest way to Aggregate?
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 |
#2
|
|||
|
|||
Fastest way to Aggregate?
Post the code you are using now.
What do you mean by Aggregating? How do these 105 fields relate to each other? "Zilla" wrote: 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 |
#3
|
|||
|
|||
Fastest way to Aggregate?
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 "Klatuu" wrote: Post the code you are using now. What do you mean by Aggregating? How do these 105 fields relate to each other? "Zilla" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|