Reverse Axis in Crosstab Query

[Guest]

I have a crosstab query I use to show the counts of escalation types. The
data is returned like this

Open Date 1st Escalation 2nd Escalation
4/1/06 10 5
4/2/06 8 3
4/3/06 12 6

I need the data to show like this

Escalation Type 4/1 4/2 4/3
1st Escalation 10 8 12
2nd Escalation 5 3 6

Here is my sql

PARAMETERS [Forms]![frmEscReportsa]![Dstart] DateTime,
[Forms]![frmEscReportsa]![DEnd] DateTime;
TRANSFORM Count(tblTemp.[1st or 2nd Time]) AS [CountOf1st or 2nd Time]
SELECT tblTemp.[Open Date]
FROM tblTemp
WHERE (((tblTemp.[Open Date])>([Forms]![frmEscReportsa]![Dstart]-1) And
(tblTemp.[Open Date])<([forms]![frmEscReportsa]![DEnd]+1) And (tblTemp.[Open
Date]) Not Like "0") AND ((tblTemp.TL) Is Not Null) AND ((tblTemp.Status)
Like
IIf([forms]![frmEscReportsa]![cbxStat]="Combined","*",[forms]![frmEscReportsa]![cbxStat])))
GROUP BY tblTemp.[Open Date]
PIVOT tblTemp.[1st or 2nd Time] In ("1st Escalation","2nd Escalation");

Thanks for your help.

 

[Duane Hookom]

Please give us the original table/query structure, not the crosstab. What
you are doing is like giving us a Lego car and asking us to make a dragon.
Why not just give us the blocks to start with rather than asking us to take
apart the car first?

 

[Guest]

Sorry....here is the sql in SELECT query mode.

PARAMETERS [Forms]![frmEscReportsa]![Dstart] DateTime,
[Forms]![frmEscReportsa]![DEnd] DateTime;
SELECT tblTemp.[Open Date], tblTemp.[1st or 2nd Time], Count(tblTemp.[1st or
2nd Time]) AS [CountOf1st or 2nd Time]
FROM tblTemp
WHERE (((tblTemp.[Open Date])>([Forms]![frmEscReportsa]![Dstart]-1) And
(tblTemp.[Open Date])<([forms]![frmEscReportsa]![DEnd]+1) And (tblTemp.[Open
Date]) Not Like "0") AND ((tblTemp.TL) Is Not Null) AND ((tblTemp.Status)
Like
IIf([forms]![frmEscReportsa]![cbxStat]="Combined","*",[forms]![frmEscReportsa]![cbxStat])))
GROUP BY tblTemp.[Open Date], tblTemp.[1st or 2nd Time];

I have a form that allow you to enter 2 dates (Dstart,DEnd) and another
control box (cbxStat) that allows you to choose the status of the record
(open,closed,combined). I hope this helps if i need to provide more
information please let me know. Thanks.

Please give us the original table/query structure, not the crosstab. What
you are doing is like giving us a Lego car and asking us to make a dragon.
Why not just give us the blocks to start with rather than asking us to take
apart the car first?
--
Duane Hookom
MS Access MVP

I have a crosstab query I use to show the counts of escalation types. The
data is returned like this

Open Date 1st Escalation 2nd Escalation
4/1/06 10 5
4/2/06 8 3
4/3/06 12 6

I need the data to show like this

Escalation Type 4/1 4/2 4/3
1st Escalation 10 8 12
2nd Escalation 5 3 6

Here is my sql

PARAMETERS [Forms]![frmEscReportsa]![Dstart] DateTime,
[Forms]![frmEscReportsa]![DEnd] DateTime;
TRANSFORM Count(tblTemp.[1st or 2nd Time]) AS [CountOf1st or 2nd Time]
SELECT tblTemp.[Open Date]
FROM tblTemp
WHERE (((tblTemp.[Open Date])>([Forms]![frmEscReportsa]![Dstart]-1) And
(tblTemp.[Open Date])<([forms]![frmEscReportsa]![DEnd]+1) And
(tblTemp.[Open
Date]) Not Like "0") AND ((tblTemp.TL) Is Not Null) AND ((tblTemp.Status)
Like
IIf([forms]![frmEscReportsa]![cbxStat]="Combined","*",[forms]![frmEscReportsa]![cbxStat])))
GROUP BY tblTemp.[Open Date]
PIVOT tblTemp.[1st or 2nd Time] In ("1st Escalation","2nd Escalation");

Thanks for your help.

 

[Duane Hookom]

I would create a crosstab based on your select query that has [1st or 2nd
Time] as the Row Heading, [Open Date] as the Column Heading, and Sum of
[CountOf1st or 2nd Time] as the value.

I'm not sure why you need:
tblTemp.[Open Date] Not Like "0"

If Open Date is between the other values, it should not be "0". Also, if
Open Date is a date field, its value will never be "0".
--
Duane Hookom
MS Access MVP

Sorry....here is the sql in SELECT query mode.

PARAMETERS [Forms]![frmEscReportsa]![Dstart] DateTime,
[Forms]![frmEscReportsa]![DEnd] DateTime;
SELECT tblTemp.[Open Date], tblTemp.[1st or 2nd Time], Count(tblTemp.[1st
or
2nd Time]) AS [CountOf1st or 2nd Time]
FROM tblTemp
WHERE (((tblTemp.[Open Date])>([Forms]![frmEscReportsa]![Dstart]-1) And
(tblTemp.[Open Date])<([forms]![frmEscReportsa]![DEnd]+1) And
(tblTemp.[Open
Date]) Not Like "0") AND ((tblTemp.TL) Is Not Null) AND ((tblTemp.Status)
Like
IIf([forms]![frmEscReportsa]![cbxStat]="Combined","*",[forms]![frmEscReportsa]![cbxStat])))
GROUP BY tblTemp.[Open Date], tblTemp.[1st or 2nd Time];

I have a form that allow you to enter 2 dates (Dstart,DEnd) and another
control box (cbxStat) that allows you to choose the status of the record
(open,closed,combined). I hope this helps if i need to provide more
information please let me know. Thanks.

Please give us the original table/query structure, not the crosstab. What
you are doing is like giving us a Lego car and asking us to make a
dragon.
Why not just give us the blocks to start with rather than asking us to
take
apart the car first?
--
Duane Hookom
MS Access MVP

I have a crosstab query I use to show the counts of escalation types.
The
data is returned like this

Open Date 1st Escalation 2nd Escalation
4/1/06 10 5
4/2/06 8 3
4/3/06 12 6

I need the data to show like this

Escalation Type 4/1 4/2 4/3
1st Escalation 10 8 12
2nd Escalation 5 3 6

Here is my sql

PARAMETERS [Forms]![frmEscReportsa]![Dstart] DateTime,
[Forms]![frmEscReportsa]![DEnd] DateTime;
TRANSFORM Count(tblTemp.[1st or 2nd Time]) AS [CountOf1st or 2nd Time]
SELECT tblTemp.[Open Date]
FROM tblTemp
WHERE (((tblTemp.[Open Date])>([Forms]![frmEscReportsa]![Dstart]-1) And
(tblTemp.[Open Date])<([forms]![frmEscReportsa]![DEnd]+1) And
(tblTemp.[Open
Date]) Not Like "0") AND ((tblTemp.TL) Is Not Null) AND
((tblTemp.Status)
Like
IIf([forms]![frmEscReportsa]![cbxStat]="Combined","*",[forms]![frmEscReportsa]![cbxStat])))
GROUP BY tblTemp.[Open Date]
PIVOT tblTemp.[1st or 2nd Time] In ("1st Escalation","2nd Escalation");

Thanks for your help.