Count If or DCount -SLOW!!

[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?

 

[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.

 

[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));

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.

 

[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.