crosstab

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

Guest

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
 
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");
 
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


KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
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.

geebee said:
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


KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UNION QUERY 1
runtime error 1
query error 12
display results in columns 1
day before 1
Crosstab Query to count number of items in value 4
How do I calculate times over multiple records? 7
format number 1

Back
Top