Multiple Query Counts

I had a question about a count query. I have parent records (literally
about parents) that include a date when the parent came in for the
first appt. Based on the number (which is defined as an integer), it
goes into 1 of 3 buckets -- greater than or equal to 0 and less than or
equal to 12 in the first, 13 and 23 in the second, and 24 in the
3rd.

Is there a way to do this? I'm not sure how to set the criteria, etc to
do this in a single query. I know I can do 3 queries, but it seems like
overkill.

Thanks

I'll take a stab at it

In a query,
SELECT
Abs(SUM(SomeIntegerField between 0 and 12)) as Bucket1,
Abs(SUM(SomeIntegerField between 13 and 23)) as Bucket2,
Abs(Sum(SomeIntegerField 23)) as Bucket3
FROM SomeParentTable


In the query grid
Field: Bucket1: Abs(SUM(SomeIntegerField between 0 and 12))
Total: Expression


Jeff wrote:

I had a question about a count query. I have parent records (literally
about parents) that include a date when the parent came in for the
first appt. Based on the number (which is defined as an integer), it
goes into 1 of 3 buckets -- greater than or equal to 0 and less than or
equal to 12 in the first, 13 and 23 in the second, and 24 in the
3rd.

Is there a way to do this? I'm not sure how to set the criteria, etc to
do this in a single query. I know I can do 3 queries, but it seems like
overkill.

Thanks

Hi -
Here's a sample query based on Northwind's Orders table that groups by
CustomerID and returns both a count of orders over a specified period and
uses the Switch() function to categorize the number of orders.

SELECT
Orders3.CustomerID
, Count(Orders3.OrderID) AS TheCount
, Switch([thecount]=12,"a",[thecount]=23,"b",True,"c") AS Type
FROM
Orders3
WHERE
(((Orders3.OrderDate) Between #9/1/1994#
AND
#8/30/1996#))
GROUP BY
Orders3.CustomerID;

Try copying/pasting to a new query, modifying table name, field names and
dates as appropriate.

HTH - Bob
Jeff wrote:
I had a question about a count query. I have parent records (literally
about parents) that include a date when the parent came in for the
first appt. Based on the number (which is defined as an integer), it
goes into 1 of 3 buckets -- greater than or equal to 0 and less than or
equal to 12 in the first, 13 and 23 in the second, and 24 in the
3rd.

Is there a way to do this? I'm not sure how to set the criteria, etc to
do this in a single query. I know I can do 3 queries, but it seems like
overkill.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200611/1

I just tried this and came up with the same number in all 3 buckets,
even though the data isn't like that. Am I missing something?

Thanks

Hi Bob

That works great, but it's now giving me all of my data in the 1st
bucket, even though I changed a few values to ensure that it's not just
the data.

I'm using a field that's defined as an integer with 0 decimal places.
Shouldn't that work??

Thanks

Jeff

Jeff -

Give this a try:

SELECT
Orders3.CustomerID
, IIf(Count([OrderID])12,Count([OrderID]),0) AS Bucket1
, IIf(Count([OrderID]) Between 13
AND
23,Count([OrderID]),0) AS Bucket2
, IIf(Count([OrderID])23,Count([OrderID]),0) AS Bucket3
FROM
Orders3
WHERE
(((Orders3.OrderDate) Between #9/1/1994#
AND
#8/30/1996#))
GROUP BY
Orders3.CustomerID;

Bob

Jeff wrote:
Hi Bob

That works great, but it's now giving me all of my data in the 1st
bucket, even though I changed a few values to ensure that it's not just
the data.

I'm using a field that's defined as an integer with 0 decimal places.
Shouldn't that work??

Thanks

Jeff

--
Message posted via http://www.accessmonster.com

Still having the same problems with the query. Everything ends up in
the 1st bucket.. How should the field be defined? Could it just be
recognizing everything as 0 or ??

Jeff -
That's puzzling. Both queries I provided were tested and appear to work as
advertised. Are you saying that even though someone has 15 visits, their
count of 15 ends up in Bucket1? Or, conversely, is it a case that no one has
a count greater than 12?

Bob

Jeff wrote:
Still having the same problems with the query. Everything ends up in
the 1st bucket.. How should the field be defined? Could it just be
recognizing everything as 0 or ??

--
Message posted via http://www.accessmonster.com

No matter how many visits there are, the count is added to Bucket1. I'm
puzzled too!


Here's my SQL for the query.

SELECT Demographics.[Program Type], Demographics.[Active Home Visit
Client], IIf(Count(Demographics.[Weeks Pregnant at
Intake])12,Count(Demographics.[Weeks Pregnant at Intake]),0) AS
Trimester1, IIf(Count(Demographics.[Weeks Pregnant at Intake]) Between
13 And 23,Count(Demographics.[Weeks Pregnant at Intake]),0) AS
Trimester2, IIf(Count(Demographics.[Weeks Pregnant at
Intake])23,Count(Demographics.[Weeks Pregnant at Intake]),0) AS
Trimester3
FROM Demographics
GROUP BY Demographics.[Program Type], Demographics.[Active Home Visit
Client]
HAVING (((Demographics.[Program Type])="OIMRI") AND
((Demographics.[Active Home Visit Client])=-1));

Weels Pregnant at Intake is defined as an Integer, 0 decimal places,
standard format, 0 is the default value, and validation rule is = 40

In looking at this, I think I'm saying that if the count of records is
less than 12, then add 1 more to Trimester1.. Is that right?

However, if I try taking out that count, I get the old "does not
include expression in the aggregate function" error.

Jeff