Count only counts all results and ignores criteria

[Stephen]

Hello all

Run into a problem with a count query in access. It seems to count
either all the results or none depending on criteria even though

I have a query that selects the difference in seconds between two
times, I have managed to display these fine and even select ones below
500 seconds that show in a table.

When I group these by date and select all under 500 seconds again it
will count all the results or none.

Ie there would be 10 records in total 7 below 500s and 3 above 500s but
it would count the results as 10 even though without the aggregation it
would show 7 records in the result set.

Time in seconds is calulated by:
(DateDiff("s",[tblTimeRecords]![receivedTime],[tblTimeRecords]![repliedTime]))

any ideas?

[David Lloyd]

Stephen:

One approach would be to create a field to indicate whether the result is
above 500 or below and then have your query group on this field. You can
use an IIF statement to set the value for the field. For example:

IIF([Time]<=500, 1, 2)

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Stephen" <st***********@cwcom.co.uk> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hello all

Run into a problem with a count query in access. It seems to count
either all the results or none depending on criteria even though

I have a query that selects the difference in seconds between two
times, I have managed to display these fine and even select ones below
500 seconds that show in a table.

When I group these by date and select all under 500 seconds again it
will count all the results or none.

Ie there would be 10 records in total 7 below 500s and 3 above 500s but
it would count the results as 10 even though without the aggregation it
would show 7 records in the result set.

Time in seconds is calulated by:
(DateDiff("s",[tblTimeRecords]![receivedTime],[tblTimeRecords]![repliedTime]))

any ideas?

[fredg]

On 10 Jun 2005 04:42:25 -0700, Stephen wrote:

Hello all

Run into a problem with a count query in access. It seems to count
either all the results or none depending on criteria even though

I have a query that selects the difference in seconds between two
times, I have managed to display these fine and even select ones below
500 seconds that show in a table.

When I group these by date and select all under 500 seconds again it
will count all the results or none.

Ie there would be 10 records in total 7 below 500s and 3 above 500s but
it would count the results as 10 even though without the aggregation it
would show 7 records in the result set.

Time in seconds is calulated by:
(DateDiff("s",[tblTimeRecords]![receivedTime],[tblTimeRecords]![repliedTime]))

any ideas?

Try:
Sum(IIf([TimeField]<=500,1,0))
or
Sum(IIf([TimeField]>500,1,0))

I placed =500 in with the < 500 group.
Change it to the >500 group if you wish.

If the query is used as the recordsource for a report, I would place
the above expressions in unbound controls in the report, and not
bother in the query.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.