CrossTab w/ Data Ranges

[mrmmickle1]

I have a table Called tblPendingSamples with Data that contains about 10 different categoris that are contained in the field HoldReason. I would like to display my data in a cross tab query that has PendingDays on the y axis and the HoldReason on the X Axis. At the current moment I have something that is very close to what I want....

The issue is that with this query all PendingDays Values are expressed. I would prefer to group them into ranges...

TRANSFORM Count(tblPendingSamples.[ID]) AS CountOfID
SELECT tblPendingSamples.[PENDINGDAYS], Count(tblPendingSamples.[ID]) AS [Total Of ID]
FROM tblPendingSamples
GROUP BY tblPendingSamples.[PENDINGDAYS]
PIVOT tblPendingSamples.[HoldReason];

My issue is that in this query the pending days appear in the left most column. The totals appear in the second from the left and the matrix of values appear to the right of that... I would really like to see something like this ....Where the furthest left column are simply words that describe the rest of the fields:

THe numbers on the y axis represent a count of PENDINGDAYS in that range or = to that #



DAYS | HOLDREASON1 | HOLDREASON2 | HOLDREASON3 | HOLDREASON4| TOTALS
10 + | 678 | 567 | 563 | 678 | 2466
9 | 123 | 896 | 456 | 899| 2374
8 | 4677 | 8799 | 394 | 345| 14215
7 | 753 | 5656 | 686 | 4354| 11449

etc....

If its easier to set this up in a report I am open to that too. I would just appreciate some guidance on how to display the data in this fashion if possible.

Thanks so much for the help.

[June7]

Create a field for range with a calculation and use that constructed field for the Row Heading criteria.

Something like:

Switch(PendingDays <= 5, "0-5", PendingDays <= 10, "6-10", PendingDays <= 15, "11-15", PendingDays > 15, "> 15")

What ranges do you want?

[mrmmickle1]

June7,

Thanks for the help. I was able to get the Query working good. I was able to do exactly what I wanted with your technique.

I added in a totals Row on the bottom and a 3 T0 10 days total row as well (How my company typically looks at the data) by using a UNION. I do have one additional question though.

Right now the Total Of ID column appears second from the left. Is there anyway to move this column to the far right? It just seems out of place in its current position...

Here is what I have now:

TRANSFORM Count(InnerQ.[ID]) AS CountOfID
SELECT InnerQ.[OVERALL], Count(InnerQ.[ID]) AS [Total Of ID]
FROM
(SELECT Switch(PendingDays=0,"DAY 0",PendingDays=1,"DAY 1",PendingDays=2,"DAY 2",PendingDays=3,"DAY 3",PendingDays=4,"DAY 4",PendingDays=5,"DAY 5",PendingDays=6,"DAY 6",PendingDays=7,"DAY 7",PendingDays=8,"DAY 8",PendingDays=9,"DAY 9",PendingDays>=10,"DAY TEN+") AS OVERALL, tblPendingSamples.HOLDREASON, tblPendingSamples.ID
FROM tblPendingSamples
WHERE (((Switch(PendingDays=0,"DAY 0",PendingDays=1,"DAY 1",PendingDays=2,"DAY 2",PendingDays=3,"DAY 3",PendingDays=4,"DAY 4",PendingDays=5,"DAY 5",PendingDays=6,"DAY 6",PendingDays=7,"DAY 7",PendingDays=8,"DAY 8",PendingDays=9,"DAY 9",PendingDays>=10,"DAY TEN+"))<>"DAY 0"))
UNION
SELECT Switch([PendingDays]=0,"ZERO",[PendingDays]>10,"OVER10",[PendingDays]>2,"TOTAL_3TO10",[PendingDays]>0,"1TO2") AS OVERALL, tblPendingSamples.HOLDREASON, 
tblPendingSamples.ID
FROM tblPendingSamples
WHERE (((Switch([PendingDays]=0,"ZERO",[PendingDays]>10,"OVER10",[PendingDays]>2,"TOTAL_3TO10",[PendingDays]>0,"1TO2"))="TOTAL_3TO10"))
UNION
SELECT Switch([PendingDays]>=0,"TOTALS") AS OVERALL, tblPendingSamples.HOLDREASON, tblPendingSamples.ID
FROM tblPendingSamples) AS InnerQ
GROUP BY InnerQ.[OVERALL]
PIVOT InnerQ.[HOLDREASON];

Anything you could do to help me finish this up would be great!

[CJ_London]

I would really like to see something like this

With crosstabs you will always get the rowheadings to the left and column headings to the right.

However make the crosstab the recordsource to a form and you can put the columns in any order you like

[mrmmickle1]

Ajax,

Thanks for the tip. I appreciate it.