Group by Range in Crosstab Query

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

.