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
|
|||
|
|||
very slow report with dcount()
Hi All,
I have a very slow report because I’m using dcount() for every individual item on this report. I would appreciate if somebody could advise some other way of doing this report. I have a base query with a ‘to do list’: [code]: To Do item Code [Responsible]: Name [Completion Date]: dates such as 1/31/2009 [Completed]: Yes, NULL (Empty), ‘N/A’ [ToDoGroup]: ToDoList groups I need to build a report that would show a % of completion per month and for the year for items, groups, and totals. On a report I’m trying to use the following text boxes for each month and totals to show the result: On the Code Header (I’m not using details at all): AugNA: =DCount("Completed","qryQuery","Completed='N/A' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugNo: =DCount("Completed","qryQuery","Completed='No' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugAll: =DCount("*","qryQuery ","Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugYes: =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") Aug: =IIf(([AugAll]-[AugNA])=0,"",[AugYes]/([AugAll]-[AugNA])) The same in the ToDoGroup footer but adding And [ToDoGroup]=[txtToDoGroup] to a condition in dcount() The same formula used for Totals but without conditions for [code] and [ToDoGroup]. All numbers on a report are correct but it’s just extremely slow. Thanks |
#2
|
|||
|
|||
very slow report with dcount()
What is the record source of your report? If it is "qryQuery", you will
probably be able to use simple Count() or Sum() or other. Do you have your report grouped by month? How about txtCode? You can usually count the number of records in a footer with an expression like: =Sum(Abs(Completed="N/A")) The above expression would count the number of records in the group where the Completed field is equal to N/A. -- Duane Hookom Microsoft Access MVP "Alex" wrote: Hi All, I have a very slow report because I’m using dcount() for every individual item on this report. I would appreciate if somebody could advise some other way of doing this report. I have a base query with a ‘to do list’: [code]: To Do item Code [Responsible]: Name [Completion Date]: dates such as 1/31/2009 [Completed]: Yes, NULL (Empty), ‘N/A’ [ToDoGroup]: ToDoList groups I need to build a report that would show a % of completion per month and for the year for items, groups, and totals. On a report I’m trying to use the following text boxes for each month and totals to show the result: On the Code Header (I’m not using details at all): AugNA: =DCount("Completed","qryQuery","Completed='N/A' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugNo: =DCount("Completed","qryQuery","Completed='No' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugAll: =DCount("*","qryQuery ","Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugYes: =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") Aug: =IIf(([AugAll]-[AugNA])=0,"",[AugYes]/([AugAll]-[AugNA])) The same in the ToDoGroup footer but adding And [ToDoGroup]=[txtToDoGroup] to a condition in dcount() The same formula used for Totals but without conditions for [code] and [ToDoGroup]. All numbers on a report are correct but it’s just extremely slow. Thanks |
#3
|
|||
|
|||
very slow report with dcount()
Thanks, Duane, for your response. I already thought it’s so complicated that
nobody would respond on it. Yes, the record source is based on “qryQuery” and it would be wonderful to make all job in the query using Sum() and Count() I just don’t know how to do it. The report grouped by ToDoGroup then by Code. On a query we have Code= different codes; [Completion Date] = 8/1/2008, etc.; Completed = Yes, Empty, ‘N/A’; ToDoGroup – it’s groups for codes, several codes belong to a one ToDoGroup. The Report grouped by ToDoGroup (highest level) and then by Code. I need to show on a report % = [Number of Codes=Yes]/([Number Of All Codes]- [Number Of Codes=’N/A’]) per month (12 months). How I did it I described in my initial post. I’m using those dcount() with txtCode=[code] that is a textbox on a report on a Code group header, [txtToDoGroup]= [ToDoGroup] – textbox on a ToDoGroup group header. All calculations (textboxes) I put on a Code group header. It shows correct numbers only in that way. I’m not using details area at all. For each month I’m calculating =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") etc. as non-visible textboxes and then % as a visible textbox. Now, I’m thinking to change “[Completion Date] between #08/1/2008# AND #08/31/2008#” to “[CompletionDate]=8”, etc. if it could speed myreport. It would be very nice to make all calculations in a query. Please, advise if there is any way to do it. Thanks "Duane Hookom" wrote: What is the record source of your report? If it is "qryQuery", you will probably be able to use simple Count() or Sum() or other. Do you have your report grouped by month? How about txtCode? You can usually count the number of records in a footer with an expression like: =Sum(Abs(Completed="N/A")) The above expression would count the number of records in the group where the Completed field is equal to N/A. -- Duane Hookom Microsoft Access MVP "Alex" wrote: Hi All, I have a very slow report because I’m using dcount() for every individual item on this report. I would appreciate if somebody could advise some other way of doing this report. I have a base query with a ‘to do list’: [code]: To Do item Code [Responsible]: Name [Completion Date]: dates such as 1/31/2009 [Completed]: Yes, NULL (Empty), ‘N/A’ [ToDoGroup]: ToDoList groups I need to build a report that would show a % of completion per month and for the year for items, groups, and totals. On a report I’m trying to use the following text boxes for each month and totals to show the result: On the Code Header (I’m not using details at all): AugNA: =DCount("Completed","qryQuery","Completed='N/A' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugNo: =DCount("Completed","qryQuery","Completed='No' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugAll: =DCount("*","qryQuery ","Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugYes: =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") Aug: =IIf(([AugAll]-[AugNA])=0,"",[AugYes]/([AugAll]-[AugNA])) The same in the ToDoGroup footer but adding And [ToDoGroup]=[txtToDoGroup] to a condition in dcount() The same formula used for Totals but without conditions for [code] and [ToDoGroup]. All numbers on a report are correct but it’s just extremely slow. Thanks |
#4
|
|||
|
|||
very slow report with dcount()
Duane, thanks again. It looks as I'm on a right track now using your advice
about Count and Sum in a query. I'll advise shortly if I resolve it. "Alex" wrote: Thanks, Duane, for your response. I already thought it’s so complicated that nobody would respond on it. Yes, the record source is based on “qryQuery” and it would be wonderful to make all job in the query using Sum() and Count() I just don’t know how to do it. The report grouped by ToDoGroup then by Code. On a query we have Code= different codes; [Completion Date] = 8/1/2008, etc.; Completed = Yes, Empty, ‘N/A’; ToDoGroup – it’s groups for codes, several codes belong to a one ToDoGroup. The Report grouped by ToDoGroup (highest level) and then by Code. I need to show on a report % = [Number of Codes=Yes]/([Number Of All Codes]- [Number Of Codes=’N/A’]) per month (12 months). How I did it I described in my initial post. I’m using those dcount() with txtCode=[code] that is a textbox on a report on a Code group header, [txtToDoGroup]= [ToDoGroup] – textbox on a ToDoGroup group header. All calculations (textboxes) I put on a Code group header. It shows correct numbers only in that way. I’m not using details area at all. For each month I’m calculating =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") etc. as non-visible textboxes and then % as a visible textbox. Now, I’m thinking to change “[Completion Date] between #08/1/2008# AND #08/31/2008#” to “[CompletionDate]=8”, etc. if it could speed myreport. It would be very nice to make all calculations in a query. Please, advise if there is any way to do it. Thanks "Duane Hookom" wrote: What is the record source of your report? If it is "qryQuery", you will probably be able to use simple Count() or Sum() or other. Do you have your report grouped by month? How about txtCode? You can usually count the number of records in a footer with an expression like: =Sum(Abs(Completed="N/A")) The above expression would count the number of records in the group where the Completed field is equal to N/A. -- Duane Hookom Microsoft Access MVP "Alex" wrote: Hi All, I have a very slow report because I’m using dcount() for every individual item on this report. I would appreciate if somebody could advise some other way of doing this report. I have a base query with a ‘to do list’: [code]: To Do item Code [Responsible]: Name [Completion Date]: dates such as 1/31/2009 [Completed]: Yes, NULL (Empty), ‘N/A’ [ToDoGroup]: ToDoList groups I need to build a report that would show a % of completion per month and for the year for items, groups, and totals. On a report I’m trying to use the following text boxes for each month and totals to show the result: On the Code Header (I’m not using details at all): AugNA: =DCount("Completed","qryQuery","Completed='N/A' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugNo: =DCount("Completed","qryQuery","Completed='No' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugAll: =DCount("*","qryQuery ","Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugYes: =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") Aug: =IIf(([AugAll]-[AugNA])=0,"",[AugYes]/([AugAll]-[AugNA])) The same in the ToDoGroup footer but adding And [ToDoGroup]=[txtToDoGroup] to a condition in dcount() The same formula used for Totals but without conditions for [code] and [ToDoGroup]. All numbers on a report are correct but it’s just extremely slow. Thanks |
#5
|
|||
|
|||
very slow report with dcount()
If you want values by month then I would create a month grouping level in the
sorting and grouping dialog. Then in the Month Group Footer (or header), use a text box with a control source of: =Sum(Abs([Completed]="Yes"))/(Count(*) - Sum(Abs([Completed]="N/A"))) You could probably use a crosstab as the Record Source of your report. The Row Headings would be ToDoGroup, Code, and Format([Completion Date],"yyyymm") as YYYYMM. The Column Heading would be Nz([Completed],"No") and the value would be a count of Code. You might want to set the Column Headings property to "Yes", "No","N/A". This should create a record set with fields of: ToDoGroup, Code, YYYYMM, Yes, No, and N/A -- Duane Hookom Microsoft Access MVP "Alex" wrote: Thanks, Duane, for your response. I already thought it’s so complicated that nobody would respond on it. Yes, the record source is based on “qryQuery” and it would be wonderful to make all job in the query using Sum() and Count() I just don’t know how to do it. The report grouped by ToDoGroup then by Code. On a query we have Code= different codes; [Completion Date] = 8/1/2008, etc.; Completed = Yes, Empty, ‘N/A’; ToDoGroup – it’s groups for codes, several codes belong to a one ToDoGroup. The Report grouped by ToDoGroup (highest level) and then by Code. I need to show on a report % = [Number of Codes=Yes]/([Number Of All Codes]- [Number Of Codes=’N/A’]) per month (12 months). How I did it I described in my initial post. I’m using those dcount() with txtCode=[code] that is a textbox on a report on a Code group header, [txtToDoGroup]= [ToDoGroup] – textbox on a ToDoGroup group header. All calculations (textboxes) I put on a Code group header. It shows correct numbers only in that way. I’m not using details area at all. For each month I’m calculating =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") etc. as non-visible textboxes and then % as a visible textbox. Now, I’m thinking to change “[Completion Date] between #08/1/2008# AND #08/31/2008#” to “[CompletionDate]=8”, etc. if it could speed myreport. It would be very nice to make all calculations in a query. Please, advise if there is any way to do it. Thanks "Duane Hookom" wrote: What is the record source of your report? If it is "qryQuery", you will probably be able to use simple Count() or Sum() or other. Do you have your report grouped by month? How about txtCode? You can usually count the number of records in a footer with an expression like: =Sum(Abs(Completed="N/A")) The above expression would count the number of records in the group where the Completed field is equal to N/A. -- Duane Hookom Microsoft Access MVP "Alex" wrote: Hi All, I have a very slow report because I’m using dcount() for every individual item on this report. I would appreciate if somebody could advise some other way of doing this report. I have a base query with a ‘to do list’: [code]: To Do item Code [Responsible]: Name [Completion Date]: dates such as 1/31/2009 [Completed]: Yes, NULL (Empty), ‘N/A’ [ToDoGroup]: ToDoList groups I need to build a report that would show a % of completion per month and for the year for items, groups, and totals. On a report I’m trying to use the following text boxes for each month and totals to show the result: On the Code Header (I’m not using details at all): AugNA: =DCount("Completed","qryQuery","Completed='N/A' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugNo: =DCount("Completed","qryQuery","Completed='No' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugAll: =DCount("*","qryQuery ","Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugYes: =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") Aug: =IIf(([AugAll]-[AugNA])=0,"",[AugYes]/([AugAll]-[AugNA])) The same in the ToDoGroup footer but adding And [ToDoGroup]=[txtToDoGroup] to a condition in dcount() The same formula used for Totals but without conditions for [code] and [ToDoGroup]. All numbers on a report are correct but it’s just extremely slow. Thanks |
#6
|
|||
|
|||
very slow report with dcount()
Thanks, Duane. It's starting to look better. How could I show months as
columns on a Report and % of completion in details grouping it by ToDoGroup and Code with total calculations in a final column (for all months) and totals in a footer for groups and Report. If I'm using crosstab query you advised as a recordsource I can see only ToDoGroup, Code, Months, TotalOfCode, N/A,No, and Yes fields. I cannot use =Sum(Abs([Completed]="Yes"))/(Count(*) - Sum(Abs([Completed]="N/A"))) As [Completed] field is not even there. Thanks "Duane Hookom" wrote: If you want values by month then I would create a month grouping level in the sorting and grouping dialog. Then in the Month Group Footer (or header), use a text box with a control source of: =Sum(Abs([Completed]="Yes"))/(Count(*) - Sum(Abs([Completed]="N/A"))) You could probably use a crosstab as the Record Source of your report. The Row Headings would be ToDoGroup, Code, and Format([Completion Date],"yyyymm") as YYYYMM. The Column Heading would be Nz([Completed],"No") and the value would be a count of Code. You might want to set the Column Headings property to "Yes", "No","N/A". This should create a record set with fields of: ToDoGroup, Code, YYYYMM, Yes, No, and N/A -- Duane Hookom Microsoft Access MVP "Alex" wrote: Thanks, Duane, for your response. I already thought it’s so complicated that nobody would respond on it. Yes, the record source is based on “qryQuery” and it would be wonderful to make all job in the query using Sum() and Count() I just don’t know how to do it. The report grouped by ToDoGroup then by Code. On a query we have Code= different codes; [Completion Date] = 8/1/2008, etc.; Completed = Yes, Empty, ‘N/A’; ToDoGroup – it’s groups for codes, several codes belong to a one ToDoGroup. The Report grouped by ToDoGroup (highest level) and then by Code. I need to show on a report % = [Number of Codes=Yes]/([Number Of All Codes]- [Number Of Codes=’N/A’]) per month (12 months). How I did it I described in my initial post. I’m using those dcount() with txtCode=[code] that is a textbox on a report on a Code group header, [txtToDoGroup]= [ToDoGroup] – textbox on a ToDoGroup group header. All calculations (textboxes) I put on a Code group header. It shows correct numbers only in that way. I’m not using details area at all. For each month I’m calculating =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") etc. as non-visible textboxes and then % as a visible textbox. Now, I’m thinking to change “[Completion Date] between #08/1/2008# AND #08/31/2008#” to “[CompletionDate]=8”, etc. if it could speed myreport. It would be very nice to make all calculations in a query. Please, advise if there is any way to do it. Thanks "Duane Hookom" wrote: What is the record source of your report? If it is "qryQuery", you will probably be able to use simple Count() or Sum() or other. Do you have your report grouped by month? How about txtCode? You can usually count the number of records in a footer with an expression like: =Sum(Abs(Completed="N/A")) The above expression would count the number of records in the group where the Completed field is equal to N/A. -- Duane Hookom Microsoft Access MVP "Alex" wrote: Hi All, I have a very slow report because I’m using dcount() for every individual item on this report. I would appreciate if somebody could advise some other way of doing this report. I have a base query with a ‘to do list’: [code]: To Do item Code [Responsible]: Name [Completion Date]: dates such as 1/31/2009 [Completed]: Yes, NULL (Empty), ‘N/A’ [ToDoGroup]: ToDoList groups I need to build a report that would show a % of completion per month and for the year for items, groups, and totals. On a report I’m trying to use the following text boxes for each month and totals to show the result: On the Code Header (I’m not using details at all): AugNA: =DCount("Completed","qryQuery","Completed='N/A' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugNo: =DCount("Completed","qryQuery","Completed='No' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugAll: =DCount("*","qryQuery ","Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugYes: =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") Aug: =IIf(([AugAll]-[AugNA])=0,"",[AugYes]/([AugAll]-[AugNA])) The same in the ToDoGroup footer but adding And [ToDoGroup]=[txtToDoGroup] to a condition in dcount() The same formula used for Totals but without conditions for [code] and [ToDoGroup]. All numbers on a report are correct but it’s just extremely slow. Thanks |
#7
|
|||
|
|||
very slow report with dcount()
The expressions I gave you were not the same solution as the crosstab. They
are exclusive of each other. There is a solution at http://www.tek-tips.com/faqs.cfm?fid=4524 which shows how to create more than a single value in a crosstab. There is another solution at http://www.tek-tips.com/faqs.cfm?fid=5466 showing how to create a monthly crosstab report. -- Duane Hookom Microsoft Access MVP "Alex" wrote: Thanks, Duane. It's starting to look better. How could I show months as columns on a Report and % of completion in details grouping it by ToDoGroup and Code with total calculations in a final column (for all months) and totals in a footer for groups and Report. If I'm using crosstab query you advised as a recordsource I can see only ToDoGroup, Code, Months, TotalOfCode, N/A,No, and Yes fields. I cannot use =Sum(Abs([Completed]="Yes"))/(Count(*) - Sum(Abs([Completed]="N/A"))) As [Completed] field is not even there. Thanks "Duane Hookom" wrote: If you want values by month then I would create a month grouping level in the sorting and grouping dialog. Then in the Month Group Footer (or header), use a text box with a control source of: =Sum(Abs([Completed]="Yes"))/(Count(*) - Sum(Abs([Completed]="N/A"))) You could probably use a crosstab as the Record Source of your report. The Row Headings would be ToDoGroup, Code, and Format([Completion Date],"yyyymm") as YYYYMM. The Column Heading would be Nz([Completed],"No") and the value would be a count of Code. You might want to set the Column Headings property to "Yes", "No","N/A". This should create a record set with fields of: ToDoGroup, Code, YYYYMM, Yes, No, and N/A -- Duane Hookom Microsoft Access MVP "Alex" wrote: Thanks, Duane, for your response. I already thought it’s so complicated that nobody would respond on it. Yes, the record source is based on “qryQuery” and it would be wonderful to make all job in the query using Sum() and Count() I just don’t know how to do it. The report grouped by ToDoGroup then by Code. On a query we have Code= different codes; [Completion Date] = 8/1/2008, etc.; Completed = Yes, Empty, ‘N/A’; ToDoGroup – it’s groups for codes, several codes belong to a one ToDoGroup. The Report grouped by ToDoGroup (highest level) and then by Code. I need to show on a report % = [Number of Codes=Yes]/([Number Of All Codes]- [Number Of Codes=’N/A’]) per month (12 months). How I did it I described in my initial post. I’m using those dcount() with txtCode=[code] that is a textbox on a report on a Code group header, [txtToDoGroup]= [ToDoGroup] – textbox on a ToDoGroup group header. All calculations (textboxes) I put on a Code group header. It shows correct numbers only in that way. I’m not using details area at all. For each month I’m calculating =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") etc. as non-visible textboxes and then % as a visible textbox. Now, I’m thinking to change “[Completion Date] between #08/1/2008# AND #08/31/2008#” to “[CompletionDate]=8”, etc. if it could speed myreport. It would be very nice to make all calculations in a query. Please, advise if there is any way to do it. Thanks "Duane Hookom" wrote: What is the record source of your report? If it is "qryQuery", you will probably be able to use simple Count() or Sum() or other. Do you have your report grouped by month? How about txtCode? You can usually count the number of records in a footer with an expression like: =Sum(Abs(Completed="N/A")) The above expression would count the number of records in the group where the Completed field is equal to N/A. -- Duane Hookom Microsoft Access MVP "Alex" wrote: Hi All, I have a very slow report because I’m using dcount() for every individual item on this report. I would appreciate if somebody could advise some other way of doing this report. I have a base query with a ‘to do list’: [code]: To Do item Code [Responsible]: Name [Completion Date]: dates such as 1/31/2009 [Completed]: Yes, NULL (Empty), ‘N/A’ [ToDoGroup]: ToDoList groups I need to build a report that would show a % of completion per month and for the year for items, groups, and totals. On a report I’m trying to use the following text boxes for each month and totals to show the result: On the Code Header (I’m not using details at all): AugNA: =DCount("Completed","qryQuery","Completed='N/A' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugNo: =DCount("Completed","qryQuery","Completed='No' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugAll: =DCount("*","qryQuery ","Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") AugYes: =DCount("Completed"," qryQuery ","Completed='Yes' And Code=[txtCode] And [Completion Date] between #08/1/2008# AND #08/31/2008#") Aug: =IIf(([AugAll]-[AugNA])=0,"",[AugYes]/([AugAll]-[AugNA])) The same in the ToDoGroup footer but adding And [ToDoGroup]=[txtToDoGroup] to a condition in dcount() The same formula used for Totals but without conditions for [code] and [ToDoGroup]. All numbers on a report are correct but it’s just extremely slow. Thanks |
Thread Tools | |
Display Modes | |
|
|