crosstab

[geebee]

hi,

i have a table, and here is some of the data:

status statuscount dated activitycd party code
#ofcalls
CHGOFF 1 6/30/2006 RV 2
CHGOFF 2 6/30/2006 RV 3
CHGOFF 108 6/30/2006 SA 1
CHGOFF 109 6/30/2006 SC 1
CHGOFF 2 6/30/2006 SI 1
CURR_AMD/DFR 3 6/3/2006 BP 1
CURR_AMD/DFR 4 6/3/2006 DE 1
CURR_AMD/DFR 1 6/3/2006 IC A 1
CURR_AMD/DFR 2 6/3/2006 IC A 2
CURR_AMD/DFR 5 6/3/2006 MS 1
CURR_AMD/DFR 2 6/3/2006 OC A 1
CURR_AMD/DFR 1 6/3/2006 OC I 1
CURR_AMD/DFR 1 6/3/2006 OC N 1
CURR_AMD/DFR 2 6/3/2006 OC O 1
CURR_AMD/DFR 2 6/3/2006 PD 1
CURR_AMD/DFR 5 6/3/2006 PY 1
CURR_AMD/DFR 1 6/3/2006 RV 1
CURR_AMD/DFR 1 6/3/2006 RV A 2
CURR_AMD/DFR 8 6/3/2006 SA 1

I need to be able to show the data in a report, crosstab style, with the
dated columns up at top, then the rows with the different status types AND
also the rows for activitycd and party code as groups.

the question is...

HOW?

Thanks in advance,
geebee

 

[KARL DEWEY]

Is this what you are looking for --
TRANSFORM Sum(geebee.statuscount) AS SumOfstatuscount
SELECT geebee.status, geebee.activitycd, geebee.[party code],
Sum(geebee.statuscount) AS [Total Of statuscount]
FROM geebee
GROUP BY geebee.status, geebee.activitycd, geebee.[party code]
PIVOT Format([dated],"Short Date");

 

[geebee]

Yes, the query results look good and formatted good... Now I just have to get
the results in a report. So I would like to create a table from the crosstab
query... Is this possible? HOW?

Thanks in advance,
geebee

Is this what you are looking for --
TRANSFORM Sum(geebee.statuscount) AS SumOfstatuscount
SELECT geebee.status, geebee.activitycd, geebee.[party code],
Sum(geebee.statuscount) AS [Total Of statuscount]
FROM geebee
GROUP BY geebee.status, geebee.activitycd, geebee.[party code]
PIVOT Format([dated],"Short Date");

hi,

i have a table, and here is some of the data:

status statuscount dated activitycd party code
#ofcalls
CHGOFF 1 6/30/2006 RV 2
CHGOFF 2 6/30/2006 RV 3
CHGOFF 108 6/30/2006 SA 1
CHGOFF 109 6/30/2006 SC 1
CHGOFF 2 6/30/2006 SI 1
CURR_AMD/DFR 3 6/3/2006 BP 1
CURR_AMD/DFR 4 6/3/2006 DE 1
CURR_AMD/DFR 1 6/3/2006 IC A 1
CURR_AMD/DFR 2 6/3/2006 IC A 2
CURR_AMD/DFR 5 6/3/2006 MS 1
CURR_AMD/DFR 2 6/3/2006 OC A 1
CURR_AMD/DFR 1 6/3/2006 OC I 1
CURR_AMD/DFR 1 6/3/2006 OC N 1
CURR_AMD/DFR 2 6/3/2006 OC O 1
CURR_AMD/DFR 2 6/3/2006 PD 1
CURR_AMD/DFR 5 6/3/2006 PY 1
CURR_AMD/DFR 1 6/3/2006 RV 1
CURR_AMD/DFR 1 6/3/2006 RV A 2
CURR_AMD/DFR 8 6/3/2006 SA 1

I need to be able to show the data in a report, crosstab style, with the
dated columns up at top, then the rows with the different status types AND
also the rows for activitycd and party code as groups.

the question is...

HOW?

Thanks in advance,
geebee

 

[KARL DEWEY]

Why make a table? Just use the query as your record source for the report.

You can open the query in design view and change it from Select-Query to
Make-Table query by clicking on the icon bar and changing the query type.

Yes, the query results look good and formatted good... Now I just have to get
the results in a report. So I would like to create a table from the crosstab
query... Is this possible? HOW?

Thanks in advance,
geebee

Is this what you are looking for --
TRANSFORM Sum(geebee.statuscount) AS SumOfstatuscount
SELECT geebee.status, geebee.activitycd, geebee.[party code],
Sum(geebee.statuscount) AS [Total Of statuscount]
FROM geebee
GROUP BY geebee.status, geebee.activitycd, geebee.[party code]
PIVOT Format([dated],"Short Date");

hi,

i have a table, and here is some of the data:

status statuscount dated activitycd party code
#ofcalls
CHGOFF 1 6/30/2006 RV 2
CHGOFF 2 6/30/2006 RV 3
CHGOFF 108 6/30/2006 SA 1
CHGOFF 109 6/30/2006 SC 1
CHGOFF 2 6/30/2006 SI 1
CURR_AMD/DFR 3 6/3/2006 BP 1
CURR_AMD/DFR 4 6/3/2006 DE 1
CURR_AMD/DFR 1 6/3/2006 IC A 1
CURR_AMD/DFR 2 6/3/2006 IC A 2
CURR_AMD/DFR 5 6/3/2006 MS 1
CURR_AMD/DFR 2 6/3/2006 OC A 1
CURR_AMD/DFR 1 6/3/2006 OC I 1
CURR_AMD/DFR 1 6/3/2006 OC N 1
CURR_AMD/DFR 2 6/3/2006 OC O 1
CURR_AMD/DFR 2 6/3/2006 PD 1
CURR_AMD/DFR 5 6/3/2006 PY 1
CURR_AMD/DFR 1 6/3/2006 RV 1
CURR_AMD/DFR 1 6/3/2006 RV A 2
CURR_AMD/DFR 8 6/3/2006 SA 1

I need to be able to show the data in a report, crosstab style, with the
dated columns up at top, then the rows with the different status types AND
also the rows for activitycd and party code as groups.

the question is...

HOW?

Thanks in advance,
geebee