very slow report with dcount()

[Alex]

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’:

: 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

 

[Duane Hookom]

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.

 

[Alex]

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=

 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


[QUOTE="Duane Hookom"]
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


[QUOTE="Alex"]
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
[/QUOTE][/QUOTE]

 

[Alex]

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.

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=

 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


[QUOTE="Duane Hookom"]
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


[QUOTE="Alex"]
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
[/QUOTE][/QUOTE][/QUOTE]

 

[Duane Hookom]

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

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=

 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


[QUOTE="Duane Hookom"]
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


[QUOTE="Alex"]
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
[/QUOTE][/QUOTE][/QUOTE]

 

[Alex]

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

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

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=

 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


[QUOTE="Duane Hookom"]
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


:

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
[/QUOTE][/QUOTE][/QUOTE]

 

[Duane Hookom]

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

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

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

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=

 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


:

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


:

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
[/QUOTE][/QUOTE][/QUOTE]