Multiple Query Counts

[Jeff]

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

 

[John Spencer]

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

 

[raskew via AccessMonster.com]

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]

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

 

[Jeff]

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

 

[raskew via AccessMonster.com]

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]

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

 

[raskew via AccessMonster.com]

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]

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

 

[Jeff]

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

 

[raskew via AccessMonster.com]

Jeff -
Love doing this without sample data!?! Nonetheless, it appears that you need
to display the number of clients that were in their 1st, 2nd, or 3rd
trimester at the time they were first seen (0 doesn't count because that
indicates they were 'nonpregnant')

Believe you need to be grouping on [Weeks Pregnant at Intake] and change your
HAVING to WHERE, which will filter-out the non-countable clients.

Couple of potential syntax errors, at least as they appeared in the posting.
Look at 'Between' in your code and ensure there's a space following it.

Please post back. Sample data would be wonderful.

Bob

 

[Jeff]

Bob

Thanks for your patience with this. You're right, the data is looking
at weeks pregnant at the time of the first visit.

There was a space with between, and I changed the Having to Where.

The SQL now looks like:

SELECT 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, Demographics.[Weeks Pregnant at Intake]
FROM Demographics
WHERE (((Demographics.[Program Type])="OIMRI") AND
((Demographics.[Active Home Visit Client])=-1))
GROUP BY Demographics.[Weeks Pregnant at Intake];

Sample Data


Trimester1 Trimester2 Trimester3 Weeks Pregnant at Intake
1 0 0 6.00
1 0 0 15.00
3 0 0 23.00
1 0 0 37.00

 

[raskew via AccessMonster.com]

Jeff –

Based on your last post:

Created an example table, tblDem. similar to what you posted but with much
abbreviated field names.
Fields:
ID – Autonumber – PK
ClientID – Number – No dups
Weeks – Number
Program – Text
Active – Yes/No

Populated this with client data.

Created two queries.
The first (Query3) is a cross-tab that uses the Partition() function to
categorize the number of weeks for each client. This returns as many rows as
there are eligible clients.

TRANSFORM Count(tblDem.ClientID) AS CountOfClientID
SELECT tblDem.ClientID
FROM tblDem
WHERE (((tblDem.Program)="xyz") AND ((tblDem.Active)=True))
GROUP BY tblDem.ClientID
PIVOT Partition(Int([Weeks]),1,41,12);

The second (Query4) calls on Query3 and returns a one-line recap displaying
total clients in each of the three trimesters:

SELECT
Sum(Query3.[1:12]) AS Tri1
, Sum(Query3.[13:24]) AS Tri2
, Sum(Query3.[25:36]) AS Tri3
FROM
Query3;

HTH - Bob

Bob

Thanks for your patience with this. You're right, the data is looking
at weeks pregnant at the time of the first visit.

There was a space with between, and I changed the Having to Where.

The SQL now looks like:

SELECT 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, Demographics.[Weeks Pregnant at Intake]
FROM Demographics
WHERE (((Demographics.[Program Type])="OIMRI") AND
((Demographics.[Active Home Visit Client])=-1))
GROUP BY Demographics.[Weeks Pregnant at Intake];

Sample Data

Trimester1 Trimester2 Trimester3 Weeks Pregnant at Intake
1 0 0 6.00
1 0 0 15.00
3 0 0 23.00
1 0 0 37.00

 

[Jeff]

Thanks, thanks!! It worked like a champ!

 

[raskew via AccessMonster.com]

You're most welcome! Love it when a plan finally comes together.

Bob