I need a non-example-specific description of how to do dynamic crosstab reports

[Richard Hollenbeck]

Hello Newsgroup. You have all been very helpful in the past and I thank
you. I try to ask relevant questions so that they don't just benefit me,
but also benefit the group. I'm currently overwhelmed by useless examples
across the web on how to make "dynamic crosstab reports" without myself
having a basic understanding about how to retrieve and assign recordsources,
etc., from fields in a query to fields in the report. I see all these
examples and I don't know what I can modify and what is essential. I need a
stripped-down, down to basics, tutorial (without a bunch of examples.) or
just simple explanation.

Looking all over the Internet I find numerous examples of "dynamic crosstab
reports" based on a crosstab query. Unfortunately, everything I found,
including Microsoft's stuff, is usually based on a specific example and
doesn't explain the basics of what's really going on. Usually it is based
on a form that prompts the user to enter a beginning and ending date. I
don't care about dates. That's not what my query is about. I just need the
bare-bones code to get the field name and data from a column and plug it
into the form. I do need to filter the results, but that's not my problem
right now. I'm not asking anybody to do my work for me. I just need a
clue. I'm asking, in general terms, how to define and open a datasource
(recordsource?) in a report, search through the column names and assign them
to the labels or text boxes in the header, and search through the fields
(columns)each record, and assign them to textboxes for each student (row).
I think the query will supply the rest of the data with little other
formatting. I wonder why there isn't an easily understood description "out
there" on how to do this without having to revise an example that isn't in
any way applicable to my situation. I know about inserting unbounded text
boxes and all that, but then the examples are so example-specific, that I
don't get the general idea.

Thanks! Rich Hollenbeck

my situation:
I will have 40+plus columns to print, so I will want to display
10-at-a-time, print them, get the next 10, etc. while keeping the same page
formatting for each set of ten columns.

The columns will be activities in a course. The rows will be students
within a course. The data intersecting the rows and columns will be the
score (or grade).

Title of Report: Table of Grades

DataSource: qryTableOfGrades (cross-tab query)

Grouped by: [courses].[courseDescription]
(or the query's representation of it)

Sorted (within each course) by: [students].[student name]
(or the query's representation of it)

Each course will perhaps have different activities, so I could filter the
report by the course displayed in the form from which the report is called.

[Richard Hollenbeck]

Thank you. Here's how I got it to work:

Option Compare Database
Option Explicit

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from qryTableOfGrades")

rst.MoveFirst
j = -1
i = 0

For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*ID" Then GoTo skip_it
j = j + 1

Select Case j
Case 0
Me.Label0.Caption = rst.Fields(i).Name
Case 1
Me.Label1.Caption = rst.Fields(i).Name
Case 2
Me.Label2.Caption = rst.Fields(i).Name
Case 3
Me.Label3.Caption = rst.Fields(i).Name
Case 4
Me.Label4.Caption = rst.Fields(i).Name
Case 5
Me.Label5.Caption = rst.Fields(i).Name
Case 6
Me.Label6.Caption = rst.Fields(i).Name
Case 7
Me.Label7.Caption = rst.Fields(i).Name
Case 8
Me.Label8.Caption = rst.Fields(i).Name
Case 9
Me.Label9.Caption = rst.Fields(i).Name
Case 10
Me.Label10.Caption = rst.Fields(i).Name
Case 11
Me.Label11.Caption = rst.Fields(i).Name
Case 12
Me.Label12.Caption = rst.Fields(i).Name
End Select

skip_it:
Next
rst.Clone
Set rst = Nothing
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from qryTableOfGrades")
rst.MoveFirst

j = -1
i = 0

For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*ID" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.field0.ControlSource = rst.Fields(i).Name
Case 1
Me.field1.ControlSource = rst.Fields(i).Name
Case 2
Me.field2.ControlSource = rst.Fields(i).Name
Case 3
Me.field3.ControlSource = rst.Fields(i).Name
Case 4
Me.field4.ControlSource = rst.Fields(i).Name
Case 5
Me.field5.ControlSource = rst.Fields(i).Name
Case 6
Me.field6.ControlSource = rst.Fields(i).Name
Case 7
Me.field7.ControlSource = rst.Fields(i).Name
Case 8
Me.field8.ControlSource = rst.Fields(i).Name
Case 9
Me.field9.ControlSource = rst.Fields(i).Name
Case 10
Me.field10.ControlSource = rst.Fields(i).Name
Case 11
Me.field11.ControlSource = rst.Fields(i).Name
Case 12
Me.field12.ControlSource = rst.Fields(i).Name
End Select

skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:jN*******************@nwrddc01.gnilink.net...

Hello Newsgroup. You have all been very helpful in the past and I thank
you. I try to ask relevant questions so that they don't just benefit me,
but also benefit the group. I'm currently overwhelmed by useless examples
across the web on how to make "dynamic crosstab reports" without myself
having a basic understanding about how to retrieve and assign recordsources, etc., from fields in a query to fields in the report. I see all these
examples and I don't know what I can modify and what is essential. I need a stripped-down, down to basics, tutorial (without a bunch of examples.) or
just simple explanation.

Looking all over the Internet I find numerous examples of "dynamic crosstab reports" based on a crosstab query. Unfortunately, everything I found,
including Microsoft's stuff, is usually based on a specific example and
doesn't explain the basics of what's really going on. Usually it is based
on a form that prompts the user to enter a beginning and ending date. I
don't care about dates. That's not what my query is about. I just need the bare-bones code to get the field name and data from a column and plug it
into the form. I do need to filter the results, but that's not my problem
right now. I'm not asking anybody to do my work for me. I just need a
clue. I'm asking, in general terms, how to define and open a datasource
(recordsource?) in a report, search through the column names and assign them to the labels or text boxes in the header, and search through the fields
(columns)each record, and assign them to textboxes for each student (row).
I think the query will supply the rest of the data with little other
formatting. I wonder why there isn't an easily understood description "out there" on how to do this without having to revise an example that isn't in
any way applicable to my situation. I know about inserting unbounded text
boxes and all that, but then the examples are so example-specific, that I
don't get the general idea.

Thanks! Rich Hollenbeck

my situation:
I will have 40+plus columns to print, so I will want to display
10-at-a-time, print them, get the next 10, etc. while keeping the same page formatting for each set of ten columns.

The columns will be activities in a course. The rows will be students
within a course. The data intersecting the rows and columns will be the
score (or grade).

Title of Report: Table of Grades

DataSource: qryTableOfGrades (cross-tab query)

Grouped by: [courses].[courseDescription]
(or the query's representation of it)

Sorted (within each course) by: [students].[student name]
(or the query's representation of it)

Each course will perhaps have different activities, so I could filter the
report by the course displayed in the form from which the report is called.