Crosstab report repeats last record for all values

[Jason Kearns]

Hello,

I've run into a rather strange problem. I have a crosstab query that
shows me exactly what I want (all the assignments for all students in
all classes with their given mark). My problem is that when I make a
dynamic Crosstab Report out of this crosstab query (by following the
example in the Solutions.mdb db provided by MSDN), every record in the
report displays the same data - which is always the last record in the
crosstab query.

For example, if in my crosstab query I have:

Class Student Quiz Homework Exam
English John Doe 5 7 55
English Jane Doe 8 11 68
English Mary Joe 9 8 59

Then in my crosstab report, it would show:

Class Student Quiz Homework Exam
English John Doe 9 8 59
English Jane Doe 9 8 59
English Mary Joe 9 8 59

My crosstab query is correct. My code for generating the report looks
like:

<code>
Option Compare Database ' Use database order for string
comparisons.
Option Explicit
' Constant for maximum number of columns FinalCheckMarks query would
create
Const conTotalColumns = 12
' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset
' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long

Dim lngReportTotal As Long
Private Sub InitVars()
Dim intX As Integer
' Initialize lngReportTotal variable.
lngReportTotal = 0
' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Place values in text boxes and hide unused text boxes.
Dim intX As Integer
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into text
boxes
' in detail section.
If Me.FormatCount = 1 Then
For intX = 7 To intColumnCount 'I start at 7 because
there are 6 row headers before the data starts
Me("Col" + Format$(intX)) = rstReport(intX - 1)
Next intX
' Hide unused text boxes in detail section.
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
End If
End Sub

Private Sub Detail_Retreat()
' Always back up to previous record when detail section retreats.
rstReport.MovePrevious
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Dim intX As Integer
' Put column headings into text boxes in page header.
For intX = 7 To intColumnCount 'I start at 7 because there are 6
row headers before the data starts
Me("Head" + Format$(intX)) = rstReport(intX - 1).Name
Next intX
' Hide unused text boxes in page header.
For intX = (intColumnCount + 1) To conTotalColumns
Me("Head" + Format$(intX)).Visible = False
Next intX
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim qdf As DAO.QueryDef
' Set database variable to current database.
Set dbsReport = CurrentDb
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qryFinalCheckMarks_Crosstab2") 'this
is the crosstab query
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
End Sub

Private Sub Report_Close()
On Error Resume Next
' Close recordset.
rstReport.Close
Set rstReport = Nothing
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation,
"No Records Found"
rstReport.Close
Cancel = True
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
' Move to first record in recordset at beginning of report
' or when report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
If Not rstReport.EOF And Not rstReport.BOF Then rstReport.MoveFirst
'Initialize variables.
InitVars
End Sub
</code>

I've went through this code line by line as it has run and watched
every piece of data (i.e. every mark) come into the report via the
Detail_Format subroutine. For some reason though, when it finishes
looping through every student in every class and then goes to print
preview, it displays the marks in the last record for every student in
every class. Anyone out there have any ideas?

Thanks!
JK

 

[Duane Hookom]

Might not address your exact question but there are other (and I think
better) solutions at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
The crosstab.mdb has a solution that is much more flexible and efficient
than the "solutions.mdb".

 

[Jason Kearns]

Duane,

I have looked at that database and have tried implementing its way of
doing a crosstab report. Unfortunately, I have not had any success in
replicating what they did. My data structure is somewhat complex in
that I have Students Classes and Assignments all joined together with a
many to many to many table. Because of this complexity, I was unable
to find a way to make it work. In the crosstab reports provided in the
Invisible Inc. database, they are querying all of their information
from one table. I had read many previous threads where you and others
mentioned using this database. Do you have any other suggestions?

Please let me know.

Thanks!
JK

 

[Duane Hookom]

I actually created all of those samples about 5 years ago when I worked for
Invisible, Inc.

There is really not much difference between a query and a report when it
comes to creating reports.

 

[Jason Kearns]

Duane,

The difference between what you did and what I'm trying to do (I
think), is that you are grouping on only one variable (the date went
with only a user), where I'm trying to group on two (the mark goes with
a student in a class).

For example, in your CrossTabWithMultiColumn (the same thing I'm trying
to do), you are just finding all the dates that go with a certain user.
I was able to do this but every mark for each student (i.e. their
marks in every class) was put into each class. My subreport in the
detail section was linked to the student, but since all the marks for
every class are in that subreport, they are all listed. Any suggestion
on how to link a subreport to two fields (i.e. have two parents and two
children)?

Thanks for your help!
JK

 

[Duane Hookom]

You can enter two field names into your Link Master/Child properties.

 

[Jason Kearns]

Damn. You're right. And even worked! Thanks Duane!

JK

 

[Duane Hookom]

Don't act so surprised!

 

[Jason Kearns]

Duane,

I don't know if you're still watching this thread, but I was hoping you
might know if I can improve the performance on opening this report.
Currently, the report is drawing its information from a query that
joins 7 tables and results with a little over 5000 records. The
subreports (one in the detail and one in the page header to show the
labels), are drawing from a query that joins 4 tables and holds over
25000 records.

Currently, this report takes about 45 seconds to load. I'm using a
dual P4 machine running at 3.0GHz and the data is coming from a
datasource on our server across a 10MBit network.

Any advice on how I could improve the load time would be appreciated.

Thanks!
JK

 

[Duane Hookom]

There are times when it makes sense to create temporary tables for reporting
purposes. If you have exhausted creating efficient queries with properly set
indexes, my next step would be to create a temporary, local mdb with your
tables.

 

[Jason Kearns]

OK, I'll try that. Thanks Duane!

JK