Better way to count records

[Guest]

Hello,
I am looking for a better way to do this. I have WAY too many queries with
this technique.

I have a value that I am throwing to an excel cell. I basically just need a
count of records that meet certain criteria.


Here is the SQL.
Dim tempValue as Integer

tempValue = DCount("*", "qry_One_of_Many")

'Then my code continues using this tempValue... value.

The SQL behind the "qry_One_of_Many" query, is small.
SELECT qry_Release_Open_FixTime.Severity
FROM qry_Release_Open_FixTime
WHERE (((qry_Release_Open_FixTime.Severity)=1));

How can I get the same result in the tempValue variable without having to
create an object (query)?

 

[Douglas J. Steele]

tempValue = DCount("*", "qry_Release_Open_FixTime", "Severity=1")

 

[Guest]

Douglas,
Thank you for the quick response, I don't think I explained it too well.

Is there a way to execute the SQL (of the query) and counting the records
that match the SQL, without having the query object?
SELECT qry_Release_Open_FixTime.Severity
FROM qry_Release_Open_FixTime
WHERE (((qry_Release_Open_FixTime.Severity)=1));



The current method seems to dramatically increase the amount of queries in
my database.

 

[John Vinson]

Douglas,
Thank you for the quick response, I don't think I explained it too well.

Is there a way to execute the SQL (of the query) and counting the records
that match the SQL, without having the query object?
SELECT qry_Release_Open_FixTime.Severity
FROM qry_Release_Open_FixTime
WHERE (((qry_Release_Open_FixTime.Severity)=1));



The current method seems to dramatically increase the amount of queries in
my database.

Why?

Are you creating separate queries for Severity 1, Severity 2, and so
on?

Are you creating separate queries for different fields?

Could you use a Totals query like

SELECT Severity, Count(*)
FROM qry_Release_Open_Fixtime
GROUP BY Severity;

or a Crosstab query?

John W. Vinson[MVP]