Crosstab Query / Column Headers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that logs issues. The crosstab query shows the volume of
issues by age (in days). Sample result looks like this:

IssueType 1 2 4 6 9 12(Days)
IssueA 4 4 2 1 1
IssueB 5 3 1 1

A query that pulls from the crosstab shows volumes by age ranges: 0-2 days,
3-5 days, 6-10 days, and >10 days. The expression to get the range looks like
"0-2 Days: Nz([0])+Nz([1])+Nz([2])". (note: [0] would be issues logged today)

Sample result looks like:

IssueType 0-2 3-5 6-10 10+
IssueA 8 0 3 1
IssueB 5 3 2 0

The problem is if there are no issues of any type for a particular day, the
expression cannot calculate correctly becuse the column doesn't exist. I
can't use the Column Headings property because I'd have to list out every day
as a column header and some issues are over 400 days old.

Is there a better way to go about this?
 
The most flexible method is to create a table of ranges:
tblDayRanges
==============
MinDays MaxDays Title
0 2 From 0 to 2
3 5 From 3 to 5
6 10 From 6 to 10
11 50 From 11 to 50
51 9999 Over 50

Add this table to your crosstab and set the criteria under the "days" column
to:
Between MinDays And MaxDays
Add the [Title] field to the grid and use this as your column heading. You
can also set the column headings property to all possible titles.
 
Nice! I like that very much. Thank you.

Duane Hookom said:
The most flexible method is to create a table of ranges:
tblDayRanges
==============
MinDays MaxDays Title
0 2 From 0 to 2
3 5 From 3 to 5
6 10 From 6 to 10
11 50 From 11 to 50
51 9999 Over 50

Add this table to your crosstab and set the criteria under the "days" column
to:
Between MinDays And MaxDays
Add the [Title] field to the grid and use this as your column heading. You
can also set the column headings property to all possible titles.
--
Duane Hookom
MS Access MVP
--

Jeff Schneider said:
I have a database that logs issues. The crosstab query shows the volume of
issues by age (in days). Sample result looks like this:

IssueType 1 2 4 6 9 12(Days)
IssueA 4 4 2 1 1
IssueB 5 3 1 1

A query that pulls from the crosstab shows volumes by age ranges: 0-2
days,
3-5 days, 6-10 days, and >10 days. The expression to get the range looks
like
"0-2 Days: Nz([0])+Nz([1])+Nz([2])". (note: [0] would be issues logged
today)

Sample result looks like:

IssueType 0-2 3-5 6-10 10+
IssueA 8 0 3 1
IssueB 5 3 2 0

The problem is if there are no issues of any type for a particular day,
the
expression cannot calculate correctly becuse the column doesn't exist. I
can't use the Column Headings property because I'd have to list out every
day
as a column header and some issues are over 400 days old.

Is there a better way to go about this?
Click to expand...
Click to expand...
 
Back
Top