Dcount makes the report run slow!

[Guest]

I have a report, which was just a basic one, and as soon as I put the dcount
on the report, the report started running slow, does anyone know how I can
take of this?

I have about 9000 records but without the dcount, it runs fast.

 

[Dirk Goldgar]

I have a report, which was just a basic one, and as soon as I put the
dcount on the report, the report started running slow, does anyone
know how I can take of this?

I have about 9000 records but without the dcount, it runs fast.

I'm not at all surprised that making 9000 calls to DCount would slow
things down. Where are you calling DCount, in the report's RecordSource
query or in the controlsource of a text box on the report, or in a
Sorting and Grouping expression? What is the SQL of the report's
RecordSource query? What is the DCount expression?

 

[Guest]

** I am using the Dcount in the controlsource of an unbound text box on the
report

**the SQL of the report's RecordSource query is ---- SELECT
[qryRqst].EmpID, [qryRqst].Date, [qryRqst].DComplete, [qryRqst].DFollowup,
[qryRqst].Status
FROM [qryRqst]

**What is the DCount expression ---- =DCount("*","qryRqst","[EmpID]= " &
[EmpID] & " and [DComplete]=#" & [Date] & "#")

 

[Dirk Goldgar]

** I am using the Dcount in the controlsource of an unbound text box
on the report

**the SQL of the report's RecordSource query is ---- SELECT
[qryRqst].EmpID, [qryRqst].Date, [qryRqst].DComplete,
[qryRqst].DFollowup, [qryRqst].Status
FROM [qryRqst]

**What is the DCount expression ---- =DCount("*","qryRqst","[EmpID]=
" & [EmpID] & " and [DComplete]=#" & [Date] & "#")

Is your report grouped by EmpID? In what section of the report is the
text box with the DCount expression?

 

[Guest]

Yes, the reports is grouped by EmpID and the Dcount expression is in the
EmpID Header

** I am using the Dcount in the controlsource of an unbound text box
on the report

**the SQL of the report's RecordSource query is ---- SELECT
[qryRqst].EmpID, [qryRqst].Date, [qryRqst].DComplete,
[qryRqst].DFollowup, [qryRqst].Status
FROM [qryRqst]

**What is the DCount expression ---- =DCount("*","qryRqst","[EmpID]=
" & [EmpID] & " and [DComplete]=#" & [Date] & "#")

Is your report grouped by EmpID? In what section of the report is the
text box with the DCount expression?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Try this as the controlsource for the text box:

=Abs(Sum([DComplete]=[qryRqst].[Date]))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Yes, the reports is grouped by EmpID and the Dcount expression is in
the EmpID Header

** I am using the Dcount in the controlsource of an unbound text box
on the report

**the SQL of the report's RecordSource query is ---- SELECT
[qryRqst].EmpID, [qryRqst].Date, [qryRqst].DComplete,
[qryRqst].DFollowup, [qryRqst].Status
FROM [qryRqst]

**What is the DCount expression ----
=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DComplete]=#"
& [Date] & "#")

Is your report grouped by EmpID? In what section of the report is
the text box with the DCount expression?

 

[Guest]

Ohh it even became worse, too slow....

Try this as the controlsource for the text box:

=Abs(Sum([DComplete]=[qryRqst].[Date]))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Yes, the reports is grouped by EmpID and the Dcount expression is in
the EmpID Header

** I am using the Dcount in the controlsource of an unbound text box
on the report

**the SQL of the report's RecordSource query is ---- SELECT
[qryRqst].EmpID, [qryRqst].Date, [qryRqst].DComplete,
[qryRqst].DFollowup, [qryRqst].Status
FROM [qryRqst]

**What is the DCount expression ----
=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DComplete]=#"
& [Date] & "#")

Is your report grouped by EmpID? In what section of the report is
the text box with the DCount expression?

 

[Dirk Goldgar]

Ohh it even became worse, too slow....

That surprises me, but I'll have to think about another alternative and
get back to you later. I'm a little busy right now.

I'm thinking vaguely about a static recordset in the report's module,
and a function that returns the value from that recordset for the
current EmpID.

Another possibility would be some tinkering with the query qryRqst and
the report's recordsource query. Please post the SQL of qryRqst.

 

[Guest]

**the SQL of the report's RecordSource query is ---- SELECT
[tblrqst].EmpID, [tblRqst].Date, [tblRqst].DComplete,
[tblRqst].DFollowup, [tblRqst].Status FROM [tblRqst]

Actually while still in this topic, I do have another problem, when you are
not busy, please help me.

I am trying to count requests for each employee that have been completed in a
a given day e.g., on

07/15 I rcvd 3 requests, 2 were completed on 7/15 and 1 is still pending but
is completed 7/16
07/16 I rcvd 4 requests, 1 was completed on 7/16 and 4 are pending
07/17 I rcvd 0 requests, but completed 4 that were pending

So my report kinda should look like this


Date rcvd Completed pending
07/15 3 2 1
07/16 4 1 4
07/17 0 4 0

The Dcount expression I had before was as follows
=DCount("*","qryRqst","[EmpID]= " & [EmpID] & " and [DComplete]=#" & [Date]
& "#")

but the problem is that it with the dcount that I had before, only counts
the rqst received on e.g., 07/15 and 07/16 but not the 07/17

My report is grouped by date and EmpID. Its true that my report does not
recognize the 07/17 bcoz nothing was received but how do put there/group my
report?

 

[Dirk Goldgar]

**the SQL of the report's RecordSource query is ---- SELECT
[tblrqst].EmpID, [tblRqst].Date, [tblRqst].DComplete,
[tblRqst].DFollowup, [tblRqst].Status FROM [tblRqst]

Wait a minute. Before, you said the the SQL of the report's
recordsource query was

SELECT
[qryRqst].EmpID, [qryRqst].Date, [qryRqst].DComplete,
[qryRqst].DFollowup, [qryRqst].Status
FROM [qryRqst]

Is it the SQL of qryRqst that you gave above?

The more I think about it, the more I find it hard to believe that the
controlsource I posted made the report slower than the DCount expression
you originally had. I think there must be something about this report
or your setup that I don't understand. I think it would be a lot easier
to answer your questions, in this case, if I could look at the database
itself.

If you'd like to send me a cut-down copy of your database, containing
only the tables, queries, and report necessary to demonstrate the
problem, compacted and then zipped to less than 1MB in size (preferably
much smaller) -- I'll have a look at it, time permitting. Delete most
of the records from the tables so that the sample is small, and then
compact the sample database. Don't send your whole database, nor the
send it without compacting it and zipping it. You can send it to the
address derived by removing NO SPAM from the reply address of this
message. If that address isn't visible to you, you can get it from my
web site, which is listed in my sig. Do *not* post my real address in
the newsgroup -- I don't want to be buried in spam and viruses.