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

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");

-- 
Build a little, test a little.


"Chris" wrote:

> 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

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

Chris wrote:
> 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

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.
-- 
Duane Hookom
Microsoft Access MVP


"Chris" wrote:

> 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

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


Duane Hookom wrote:
>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

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

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.


"raskew via AccessMonster.com" wrote:

> 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
> 
> 
> Duane Hookom wrote:
> >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
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
>