Crosstab query report

[Souris]

I wanted to use Crosstab query to generate my report.
Since the fiedl names depend on the row heading which is vary, I need link
fields at run time.

Please let me know if I am wrong,

If I need link the field at run time, can you please give me any example code?


Your help is great appreciated,

 

[Allen Browne]

One way to do this is to alias the field names so they don't change.
Duane Hookom has an example:
dynamic monthly crosstab reports
at:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

If you want to do it programmatically, you will need to be familiar with VBA
code. Set up the report with the maximum number of text boxes you could
need. Name them txt0, txt1, etc, and leave them unbound. In the Open event
of the report, OpenRecordset on a Totals query that will let you determine
the field names you need, and assign them to the ControlSource of the
unbound text boxes. At the same time, generate the list to include in the
PIVOT clause, and assign the entire SQL statement to the RecordSource of the
report.

 

[Souris]

Thanks millions,

One way to do this is to alias the field names so they don't change.
Duane Hookom has an example:
dynamic monthly crosstab reports
at:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

If you want to do it programmatically, you will need to be familiar with VBA
code. Set up the report with the maximum number of text boxes you could
need. Name them txt0, txt1, etc, and leave them unbound. In the Open event
of the report, OpenRecordset on a Totals query that will let you determine
the field names you need, and assign them to the ControlSource of the
unbound text boxes. At the same time, generate the list to include in the
PIVOT clause, and assign the entire SQL statement to the RecordSource of the
report.

 

[Souris]

Can you please let me know if I am on the right track using following code?

dim rs as DAO.recordset

set rs = Report.RecordSource.openRecordset


If rs.Field("MyField")<> null then
txtMyField.ControlSource = "MyField"
else
txtMyField.ControlSource = null
end if


Thanks millions,

 

[Allen Browne]

FWIW, I've uploaded an example here:
http://allenbrowne.com/temp/CrosstabExample.txt

The report has:
- 20 unbound text boxes (named txt0 to txt19) in the Detail section;
- 20 labels (named lbl0 to lbl19) in the Page Header section;
- 20 unbount text boxes (txtFoot0 to txtFoot10) in the Report Footer.

 

[Souris]

Thanks millions

FWIW, I've uploaded an example here:
http://allenbrowne.com/temp/CrosstabExample.txt

The report has:
- 20 unbound text boxes (named txt0 to txt19) in the Detail section;
- 20 labels (named lbl0 to lbl19) in the Page Header section;
- 20 unbount text boxes (txtFoot0 to txtFoot10) in the Report Footer.

 

[Cydney]

This example and information helped with my report immensly. However, I have
one problem. The totals are not coming in correctly. They are increasing like
the first to fields are added together to equal the 3rd column, and then the
2nd and 3rd are being added together to equal the 4th.. but at that point it
all breaks loose and doesn't make any sense at all. Here is my code:

Private Sub Report_Open(Cancel As Integer)
'On Error GoTo Err_Handler
'Purpose: Build the crosstab query statmennt dynamically, to assign as
RecordSource.
' Bind the text boxes to the fields, and set the captions of
the labels.
Dim test
Dim rs As DAO.Recordset 'Possible
Dim strSql As String 'SQL statements.
Dim strWhere As String 'WHERE clause
Dim strDescrip As String 'Description of the WHERE clause.
Dim strPivot As String 'PIVOT clause
Dim strDoc As String 'Names of forms to look for.
Dim lngLen As Long 'Length ot string
Dim varArray 'PIVOT clause items as array.
Dim i As Integer 'Loop controller.
Dim iUBound As Integer 'Number of columns in the PIVOT clause
(zero based.)
Const icMaxBoxes = 11 'Maximum text box/label number.
Const strcSep = ". " 'Separator for description string.
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Format JET expects for dates in
SQL strings.
Const strcStub = "TRANSFORM sum(qEmplHrsCURR.Hours) as hrs " & vbCrLf & _
"SELECT qEmplHrsCURR.ProjNo, qEmplHrsCURR.Empl,
sum(qEmplHrsCURR.CTO) as SumCTO1 " & vbCrLf & _
"FROM qEmplHrsCURR " & vbCrLf
Const strcTail = "GROUP BY qEmplHrsCURR.ProjNo, qEmplHrsCURR.Empl " &
vbCrLf & _
"ORDER BY qEmplHrsCURR.Empl, qEmplHrsCURR.CTO " & vbCrLf

'***************************************************
'Build the PIVOT clause.
'***************************************************
strSql = "SELECT qEmplHrsCURR.CTO FROM qEmplHrsCURR GROUP BY
qEmplHrsCURR.CTO ORDER BY qEmplHrsCURR.CTO;"
' strSql = "SELECT DISTINCT tblNotes.NoteCode FROM tblNotes WHERE
tblNotes.NoteCode Is Not Null ORDER BY tblNotes.NoteCode;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
iUBound = -1
Do While (iUBound <= icMaxBoxes) And Not rs.EOF
strPivot = strPivot & """" & rs!CTO & ""","
rs.MoveNext
iUBound = iUBound + 1
Loop
rs.close

lngLen = Len(strPivot) - 1& 'Without trailing comma
If lngLen > 0& Then
strPivot = Left$(strPivot, lngLen)
varArray = Split(Replace(strPivot, """", vbNullString), ",")
strPivot = "PIVOT qEmplHrsCURR.CTO In (" & strPivot & ");"
End If

'***************************************************
'Set up the report.
'***************************************************
'Give up if there were no column headings.
If Not IsArray(varArray) Then
Cancel = True
MsgBox "There are no Note Codes for this report.", vbExclamation,
"No data for report " & Me.Caption & "."
Else
'Assign the RecordSource to the report.
Me.RecordSource = strcStub & strWhere & strcTail & strPivot
test = strcStub & strWhere & strcTail & strPivot

'Assign text box ControlSource to text boxes, and Caption to labels,
and show them.
iUBound = UBound(varArray)
If iUBound > icMaxBoxes Then
iUBound = icMaxBoxes
End If
For i = 0 To iUBound
With Me("txt" & i)
.ControlSource = varArray(i)
If Not .Visible Then
.Visible = True
End If
End With
With Me("lbl" & i)
.Caption = varArray(i)
If Not .Visible Then
.Visible = True
End If
End With
With Me("txtFoot" & i)
.ControlSource = "=sum(" & varArray(i) & ")"
If Not .Visible Then
.Visible = True
End If
End With
Next

'Hide the unused boxes and labels.
For i = i To icMaxBoxes
With Me("txt" & i)
If .ControlSource <> vbNullString Then
.ControlSource = vbNullString
End If
If .Visible Then
.Visible = False
End If
End With
With Me("lbl" & i)
If .Visible Then
.Visible = False
End If
End With
With Me("txtFoot" & i)
If .ControlSource <> vbNullString Then
.ControlSource = vbNullString
End If
If .Visible Then
.Visible = False
End If
End With
Next
End If

Exit_Handler:
Set rs = Nothing
Exit Sub

Err_Handler:
Debug.Print conMod & ".Report_Open Error " & Err.Number & " at " & Now()
& " - " & Err.DESCRIPTION
Resume Exit_Handler
End Sub


--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[Cydney]

why do you say "20 unbound text boxes (txtFoot0 to txtFoot10) in the Report
Footer." and then only number from 0 to 10? Is this a typo?

Also, how would I add a total by row?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[Cydney]

I've solved my own issues.
My field titles were "05", "06", "11", "18"... etc.
I needed to include BRACKETS around those fields names in my code for those
fields that are in the Total Row.
Now my totals are correct.
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[Cydney]

This one was resolved by adding a total row in the SQL of the Pivot.
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson