Crosstab Query - Horiz and Veritcal Buckets

[Frank]

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

 

[Duane Hookom]

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.

 

[KARL DEWEY]

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
==================
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.

--
Duane Hookom
MS Access MVP

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