Crosstab Query - Horiz and Veritcal Buckets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank - 13 Jul 2006 15:23 GMT

Hi Folks,
I'm just wondering if there is a way to create a crosstab query to create
date ranges from today as column headings and quantity buckets on row
headings.

CRITERIA    <6 Days    Count    6-30 days    Count    30-60 days    Count
(a) Less 5K$0    0    ($8,060)    165    $4,366    98
(b) 5-50K    $0.00    0    ($422)    334    ($1,862)
(c) 50-100K$0.00    0    $194,307    118    $7,627
(d) 100K-1MM$0.00    0    $9,761    430    ($11,656)
(e) >1MM    $0.00    0    $30,822.    292    ($29,623)

I tried doing this in the crosstyab wizxard but could not sum on the row
totals and could not get anything except dates grouped by month on the column
headings. I know I can insert a field via another query to group the rows but
am still wondering how to get the right column groups.
Thanks

Reply to this Message

Duane Hookom - 13 Jul 2006 16:17 GMT

You could create a crosstab if you had a table of day buckets.
tblDayBuckets
==================
DayFrom DayTo DayTitle
0 6 "Less than 6 Days"
6 30 "6 to 30 Days"
-- etc --
Do the same for the quantity buckets.

You can add these tables to your query and set a criteria under the
appropriate columns to:

>=[DayFrom] AND < [DayTo]

>=[QtyFrom] AND < [QtyTo]

Then build your crosstab with the QtyTitle as the Row Heading and DayTitle
as the Column Heading.

Signature

Duane Hookom
MS Access MVP

> Hi Folks,
> I'm just wondering if there is a way to create a crosstab query to create
[quoted text clipped - 15 lines]
> am still wondering how to get the right column groups.
> Thanks

Reply to this Message

KARL DEWEY - 13 Jul 2006 17:41 GMT

I did a count of temperature in ranges like this --
PIVOT IIf([Temp]<90,"Cool",IIf([Temp] Between 90 And 239.999,"Warm","Hot"));

> You could create a crosstab if you had a table of day buckets.
> tblDayBuckets
[quoted text clipped - 34 lines]
> > am still wondering how to get the right column groups.
> > Thanks

Reply to this Message