Column in queries to row in report (1 Viewer)

[ALAN]

I got a query to count grades for my students like below:
StudentID CountofGrades
114 2A
114 1B
114 3C
116 1A
116 3B
116 2C
…… ….
…… …..
My problem is how to change the appearance of this query in a form or a report so that it appears in rows as below:

114 2A1B3C
115 1A3B2C
…… ……….
…… ……….

Thanks in advance….

 

[KevinM]

Use a CrossTab Query.

 

[ALAN]

Thanks Kevin.I've successfully build the crosstab query and I got like this:

StudentID A B C
411.....2..3..
But I got 2 problems:
1. How to change the null value to zero. So that it appears like this:
StudentID A B C
411.....2..3..0

2. When I take the crosstab queries as a subform, I receive this message:
"You can't use pass-through query or non-fixed-column crosstab query as a record source for a subform or report. - Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

Thanks again...Hope to hear from you soon..

[This message has been edited by ALAN (edited 06-08-2001).]

[This message has been edited by ALAN (edited 06-08-2001).]

 

[KevinM]

1. Use an iif expression in the VALUE column to return ZERO if Null.

iif([MyField] Is Null,"0",[MyField])

2.
You have to enter the values in the Column headings box in the query's properties (a real pain).

I wouldn't use it in a form as the column headings aren't dynamic.
Same goes for a report.

Try a pivot table form instead of a crosstab qry if you want to add formatting to it.

HTH

 

[ALAN]

I got my pivot table...but the problem is..it appears in list...how to make it appears individually and syncronised with the main form? (StudentID) Somebody plz help me solve this problem.....

[This message has been edited by ALAN (edited 06-13-2001).]