Row and Column Averages in Dynamic Crosstab Report?



Using Access 2002:

I have created a dynamic crosstab report based on one from a KB article
( which is working great for me.
However I have come up against a problem...

Currently the row totals column and the column totals in the report footer
do exactly that - display TOTALS. I would like them to display averages
instead. This causes me various problems: 1. The column count is not always
the same - it could be anywhere between 3 and 9 (including row headers and
row totals, i.e. showing 1-7 days of data). 2. Sometimes the vaules will be 0
- I want to exclude these values from the average calculation. 3. This all
goes slightly beyond my VBA abilities!

I've attached the code for the report, which is adapted slightly from the
code in the KB article:

Option Compare Database

' Constant for maximum number of columns
Const conTotalColumns = 9

' 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 Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Detail_Format
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.

If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
End If
End If

Exit Sub

MsgBox "Error " & Err.Number & " " & Err.Description
Resume Exit_Detail_Format
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" +
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.

lngReportTotal = lngReportTotal + lngRowTotal
End If

End Sub

Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.

End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX

End Sub

Private Sub Report_Close()

On Error Resume Next

' Close recordset.

End Sub

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", vbExclamation, "No
Records Found"
Cancel = True

End Sub

Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!SelectDate
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Query1")
' Set parameters for query based on values entered

qdf.Parameters("Forms!SelectDate!StartDate") = frm!StartDate
qdf.Parameters("Forms!SelectDate!EndDate") = frm!EndDate

' 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 ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Put grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX

End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at the beginning of the report
' or when the 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.)

'Initialize variables.

End Sub

Can anyone help with this please?


Hi Duane,

At the moment I'm working with Daily reporting and yes, the column headings
are date intervals (at the moment days, but I will be working on monthly
reports soon).

I did have a look at that tek-tips article a few days ago, but found it
difficult to understand and I also need a solution which will work with
columns which are not date intervals as I will be running a variety of
crosstab reports from the db.

I've downloaded the other example you've posted but don't have time to look
in detail now.

Thanks for your help,


Duane Hookom

The date interval crosstab report solution is easy as pie compared with the
code monster solution in the MS KB article. My solution has no code and is
much more efficient.

If you have questions, come on back with them.

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