Dynamic Crosstab Report

[BenRatty]

I need to create a dynamic crosstab report

I basically have the following data

employee name, job number, week number, work description.

I want these as follows

Page Header: employee name and week number

job number job number job number
job number

work description
work description
work description

There is a variable number of work descriptions and job numbers but I could
fix an absolute maximum if needed.

I have tried putting the data into a crosstab query and creating a pivot
report but I cannot get the totals I need or the ability to change header
names and titles. I also will need to add different sets of totals which in
Access 2007 the pivot form does not allow.

I have gone round in circles with this one, trying various VBA code from
other site which produces nothing.

 

[Maurice]

You say that you have created a cross tab query for this one. Assuming this
produces the right output my trick is to take this query as a source for an
"maketable" query - create a table with the output. This could be a tmp-table
that is filled everytime the report is opened.

Create the report and place dummy fields in the report (approx the amount
you think you need or can fit on your report). Name the fields txtData1,
txtData2, txtData3 etc.... If you want to sum any results also place txtSum1,
txtSum2 etc in your report. Just remeber the fields should be unbound because
you are using a crosstab and we never know how many columns it will use. In
VBA place something like the following code in the report. Set the table as
your reportsource.

---------------------------------
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection

intColCount = rst.Fields.Count
intControlCount = Me.Details.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

For i = 1 To intColCount
strName = rst.Fields(i - 1).Name
If Me.Controls("lblHeader" & i).Tag = "" Then
Me.Controls("lblHeader" & i).Caption = strName
Else
Me.Controls("lblHeader" & i).Caption = Me.Controls("lblHeader" &
i).Tag
End If
Me.Controls("txtData" & i).ControlSource = strName
Me.Controls("txtSum" & i).ControlSource = "=Sum([" & strName & "])"
Next i
rst.Close
set rst=nothing
---------------------

The sums in the code are being placed in the txtSum datacontrols and the
data is being placed in the txtData controls.

I've added the text I want to appear in the labels in the tag field (under
properties of the textfield)

Let me know if you can make any fish out if not i'll try to help you some
more on this one...

Maurice

 

[BenRatty]

Thanks. Does this code need to be in the Report_Open?

Where do I specify the source table/query?

You say that you have created a cross tab query for this one. Assuming this
produces the right output my trick is to take this query as a source for an
"maketable" query - create a table with the output. This could be a tmp-table
that is filled everytime the report is opened.

Create the report and place dummy fields in the report (approx the amount
you think you need or can fit on your report). Name the fields txtData1,
txtData2, txtData3 etc.... If you want to sum any results also place txtSum1,
txtSum2 etc in your report. Just remeber the fields should be unbound because
you are using a crosstab and we never know how many columns it will use. In
VBA place something like the following code in the report. Set the table as
your reportsource.

---------------------------------
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection

intColCount = rst.Fields.Count
intControlCount = Me.Details.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

For i = 1 To intColCount
strName = rst.Fields(i - 1).Name
If Me.Controls("lblHeader" & i).Tag = "" Then
Me.Controls("lblHeader" & i).Caption = strName
Else
Me.Controls("lblHeader" & i).Caption = Me.Controls("lblHeader" &
i).Tag
End If
Me.Controls("txtData" & i).ControlSource = strName
Me.Controls("txtSum" & i).ControlSource = "=Sum([" & strName & "])"
Next i
rst.Close
set rst=nothing
---------------------

The sums in the code are being placed in the txtSum datacontrols and the
data is being placed in the txtData controls.

I've added the text I want to appear in the labels in the tag field (under
properties of the textfield)

Let me know if you can make any fish out if not i'll try to help you some
more on this one...

Maurice

I need to create a dynamic crosstab report

I basically have the following data

employee name, job number, week number, work description.

I want these as follows

Page Header: employee name and week number

job number job number job number
job number

work description
work description
work description

There is a variable number of work descriptions and job numbers but I could
fix an absolute maximum if needed.

I have tried putting the data into a crosstab query and creating a pivot
report but I cannot get the totals I need or the ability to change header
names and titles. I also will need to add different sets of totals which in
Access 2007 the pivot form does not allow.

I have gone round in circles with this one, trying various VBA code from
other site which produces nothing.