Report based on crosstab query

[Richard Hollenbeck]

I'm building a table of grades report based on a crosstab query where
students are in the rows and the activities are in the columns. The grades
are in the body where the rows and columns intersect.

The problem is that the activities will change from semester to semester.
Will I need to redesign a new report every semester to reflect the changes?
Maybe I should just export the results of the query to a spreadsheet. ??

[Rich P]

Add an additional column to your data table for semester. Then you
query on the semester. Student Name, Activity, Semester. It won't
matter what the activities are for any semester. The only catch is if
in Spring you have 10 activities but summer or fall has a different
number of activities and your report is set for 10 activities well you
could create your report with say 20 columns. If the query yields less
than 20 columns, you hide the columns/fields-textboxes that don't get
used. Name your textboxes like

txt1, txt2, txt3,...
Then you can loop through the controls collection of your report

Dim ctl As Control, i As Integer, j As Integer
Dim QD As Querydef
Set QD = CurrentDB.Querydefs("yourQuery")
j = QD.Fields.Count
For Each ctl in Me.Controls
If i > j And ctl.Name = "txt" & i Then
ctl.Visible = False
End If
i = i + 1
Next

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!