Count If or DCount -SLOW!!


S

Schwimms

I am creating a formula in a query in access and I have used the dcount
formula on a field that I am trying to find the count of how many times a Job
number shows up.
This bogs down my computer tremendously and the database is only 10,000
records. The formula is as follows:

CountOfJobNumber: DCount("*","Vert Duplicates","[col_CC_UT] = '" &
[col_CC_UT] & "'")

I know that this also takes a while in excel with the countif function but
there is a shortcut if I sort the job numbers in ascending and I only do a
count on 30 records at a time. The excel formula looks like this:

=countif(d1:d30,d15) or if you move it down it would be like this =countif(
d80:d119,d95)

Is there a way I can build this into Access, or maybe I have the wrong
formula?
 
Ad

Advertisements

J

John W. Vinson

I am creating a formula in a query in access and I have used the dcount
formula on a field that I am trying to find the count of how many times a Job
number shows up.
This bogs down my computer tremendously and the database is only 10,000
records. The formula is as follows:

CountOfJobNumber: DCount("*","Vert Duplicates","[col_CC_UT] = '" &
[col_CC_UT] & "'")
Check the table design for the col_CC_UT field. Is there an Index on the
field? If not, create one; that should make a dramatic difference.

If that doesn't help please post the entire SQL of the query.
 
S

Schwimms

The field is not indexed and I can't change it because it is a linked table
created from a database by another user.

Here is the SQL

SELECT [Vert Duplicates].col_CC_UT, DCount("*","Vert
Duplicates","[col_CC_UT] = '" & [col_CC_UT] & "'") AS CountOfJobNumber
FROM [Vert Duplicates]
WHERE ((([Vert Duplicates].col_CC_UT) Is Not Null));


John W. Vinson said:
I am creating a formula in a query in access and I have used the dcount
formula on a field that I am trying to find the count of how many times a Job
number shows up.
This bogs down my computer tremendously and the database is only 10,000
records. The formula is as follows:

CountOfJobNumber: DCount("*","Vert Duplicates","[col_CC_UT] = '" &
[col_CC_UT] & "'")
Check the table design for the col_CC_UT field. Is there an Index on the
field? If not, create one; that should make a dramatic difference.

If that doesn't help please post the entire SQL of the query.
 
Ad

Advertisements

J

John W. Vinson

The field is not indexed and I can't change it because it is a linked table
created from a database by another user.
I guess what I could suggest is that rather than using a dcount in a query you
could simply use a totals query:

SELECT [Vert Duplicates].col_CC_UT, Count(*)
FROM [Vert Duplicates]
WHERE ((([Vert Duplicates].col_CC_UT) Is Not Null))
GROUP BY col_CC_UT;

As it is you'll see each value of col_CC_UT repeatedly, with each repitition
showing the same count.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count or DCount 0
DCOUNT, SUM, or COUNT???? 2
Count or DCount function 2
Count/DCount Solution 1
Dcount help - counting by month 4
Dcount makes the report run slow! 9
DCount () 0
dCount 5

Top