Group by Range in Crosstab Query

[Chris]

I have created a crosstab query which displays the number of days from
referral to consult. I want to groups the days into ranges (0-14 days);
(15-21 days);(>21 days), etc. and show total unit numbers based on that
range. The sql statement currently reads as follows:

TRANSFORM Count(qryWaitTimesMOReferralToConsult.Unit) AS CountOfUnit
SELECT qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)],
Count(qryWaitTimesMOReferralToConsult.Unit) AS [Total Of Unit]
FROM qryWaitTimesMOReferralToConsult
GROUP BY qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)]
PIVOT Format([Reg Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks.
Christine

 

[KARL DEWEY]

Try this --
TRANSFORM Count(qryWaitTimesMOReferralToConsult.Unit) AS CountOfUnit
SELECT IIF(qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)] Between 0
AND 14, "0-14", IIF(qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)]
Between 15 AND 21, "15-21", ">21")) AS [Days from Referal to Consult],
Count(qryWaitTimesMOReferralToConsult.Unit) AS [Total Of Unit]
FROM qryWaitTimesMOReferralToConsult
GROUP BY IIF(qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)] Between
0 AND 14, "0-14", IIF(qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)]
Between 15 AND 21, "15-21", ">21"))
PIVOT Format([Reg Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[John Spencer]

Use an expression like the following:
IIF([Ref To Consult (Days)]<15,"0-14 Days"
, IIF([Ref To Consult (Days)]<=21,"14-21 Days",">21 Days"))

For safety you could use a more complex expression and check to see if days
were between 0 and 14, 15 to 21, over 21, or none of the preceding.

IIF([Ref To Consult (Days)]>=0 and [Ref To Consult (Days)]<15,"0-14 Days"
,IIF([Ref To Consult (Days)]>=15 and [Ref To Consult (Days)]<22 ,"14-21 Days"
,IIF([Ref To Consult (Days)]>21,">21 Days","Out of Range")))


TRANSFORM Count(qryWaitTimesMOReferralToConsult.Unit) AS CountOfUnit
SELECT IIF([Ref To Consult (Days)]>14,"0-14 Days", IIF([Ref To Consult
(Days)]<=21,"14-21 Days",">21 Days")) as Period
Count(qryWaitTimesMOReferralToConsult.Unit) AS [Total Of Unit]
FROM qryWaitTimesMOReferralToConsult
GROUP BY IIF([Ref To Consult (Days)]>14,"0-14 Days", IIF([Ref To Consult
(Days)]<=21,"14-21 Days",">21 Days"))
PIVOT Format([Reg Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

I would not hard-code the ranges into an expression. Consider creating a
table of day ranges:
tblDayRange
FromDays ToDays RangeTitle
0 14 0-14 days
15 21 15-21 days
22 999999 22+ days

You can then add this table to your crosstab and set the criteria under the
column [Ref To Consult(Days)]. Use the RangeTitle as the Row Heading.

WHEN you need to change the day ranges, do it in the table, not in query
design.

 

[KARL DEWEY]

So far as I know partition segments are always the same size whereas what is
wanted is three different size groups -- 15 in first group, 7 in second, and
all else in the third.

--
Build a little, test a little.

Hi -

Consider employing the Partition() function. Here's an example, based
on Northwind's Orders table. It displays, by Customer, the number of
orders grouped by OrderDate / ShippedDate.

TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Orders.CustomerID
FROM Orders
WHERE ((Not (Orders.ShippedDate) Is Null))
GROUP BY Orders.CustomerID
PIVOT Partition(nz([ShippedDate]-[OrderDate],0),1,100,5);

Give it a try. It'll make more sense when viewed in Design View.

Bob

I would not hard-code the ranges into an expression. Consider creating a
table of day ranges:
tblDayRange
FromDays ToDays RangeTitle
0 14 0-14 days
15 21 15-21 days
22 999999 22+ days

You can then add this table to your crosstab and set the criteria under the
column [Ref To Consult(Days)]. Use the RangeTitle as the Row Heading.

WHEN you need to change the day ranges, do it in the table, not in query
design.

I have created a crosstab query which displays the number of days from
referral to consult. I want to groups the days into ranges (0-14 days);

[quoted text clipped - 11 lines]

Thanks.
Christine