Count Efficiency

[Guest]

I am running a query to return a count based on a specific condition. The
name of the query that the information is being pulled from is "raw
extraction" (raw extraction is a query which I use to pull numeric values
from text fields in the initial table). I have changed the alias of my query
to "RVZ" for the purposes of my subquery. I am using the following line in an
attempt to retrieve the count of records from the "ERP Raw" field which
contain a value of 8 or higher...

ERP: (Select Count([ERP Raw]) from [Raw Extraction] Where [ERP Raw]>=8 and
[Call Center]=[RVZ].[Call Center])/[Surveys]

While this subquery returns the information that I am looking for, it is
running much slower than I anticipated and I would like to know if there is a
more efficient way to query this information. Thanks in advance for any
assistance on the issue!

 

[Jeff Boyce]

Jahlu

We aren't there. We can't see your database.

There are a number of potential factors in a "slow" query:
* indexing in the underlying tables (on each join, sort and selection
field)
* number of tables joined in the query
* "split" vs single-file design
* network speed
* network interface card speed
* PC speed
.... and more.

If this were mine, I'd start at the top of the list above and work down.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Thank you for the advice. I had to make some changes to initial table that
the information is pulled from but am very pleased with the result.

Jahlu

We aren't there. We can't see your database.

There are a number of potential factors in a "slow" query:
* indexing in the underlying tables (on each join, sort and selection
field)
* number of tables joined in the query
* "split" vs single-file design
* network speed
* network interface card speed
* PC speed
.... and more.

If this were mine, I'd start at the top of the list above and work down.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am running a query to return a count based on a specific condition. The
name of the query that the information is being pulled from is "raw
extraction" (raw extraction is a query which I use to pull numeric values
from text fields in the initial table). I have changed the alias of my
query
to "RVZ" for the purposes of my subquery. I am using the following line in
an
attempt to retrieve the count of records from the "ERP Raw" field which
contain a value of 8 or higher...

ERP: (Select Count([ERP Raw]) from [Raw Extraction] Where [ERP Raw]>=8 and
[Call Center]=[RVZ].[Call Center])/[Surveys]

While this subquery returns the information that I am looking for, it is
running much slower than I anticipated and I would like to know if there
is a
more efficient way to query this information. Thanks in advance for any
assistance on the issue!