By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

Dynamic Crosstab Report

P: n/a
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?

I am having trouble converting the DAO references.
TITLE :INF: How to Create a Dynamic Crosstab Report
PRODUCT :Microsoft Access
PROD/VER:1.00 1.10
OPER/SYS:WINDOWS
KEYWORDS:kbusage

---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Access versions 1.0 and 1.1
---------------------------------------------------------------------

SUMMARY
=======

Using Microsoft Access version 1.x, you may want to create dynamic reports
based on parameter crosstab queries, or have reports to match a dynaset
returned by such a query. Dynamic reports allow your customized reports
to show only the most recently modified data and eliminate the need for
fixed column headings and empty columns.

The example below uses starting and ending dates entered on a form as
the parameters in a crosstab query. When a button on the form is
chosen, Access Basic functions run the crosstab query, which creates a
dynaset whose contents are displayed in a report.

In the following example, the report shows which employees had sales
for the current week, based on the dates entered on the form.

MORE INFORMATION
================

The steps below show how to create a dynamic crosstab report based on
tables in the sample database NWIND.MDB.

The following new objects must be added to the database: one table,
one query, one form, one report and two functions. Each item is
explained in a separate section below.

TABLE: XTabResult
-----------------

This table must contain enough columns to hold the maximum number of
fields that the crosstab query can possibly generate. The table is
later filled with data by Access Basic functions.

For this example, the maximum number of fields generated by the
crosstab query is 10, with no primary key necessary. Create a new
table with 10 columns (columns 0-9) with the Text data type (for
example, Column0, Column1, and so forth).

QUERY: CrossQry
---------------

Create a new crosstab query based on the Employees, Orders, Order
Details, and Products tables. (These tables are already joined, based
on previously created relationships in the NWIND database.) To do
this, use the following steps:

1. Choose the Query button in the Database window, then choose the New
button.

2. Add the Employees, Orders, Order Details, and Products tables.

3. Drag the following fields to the query grid and add the values
shown below:

NOTE: To display the crosstab field, choose Crosstab from the Query
menu.

Field: Order Amount
Table: Orders
Total: Sum
Crosstab: Value
Field: Product Name
Table: Products
Total: Group By
Crosstab: Row Heading
Field: RowTotal:Order Amount
Table: Orders
Total: Sum
Crosstab: Row Heading
Field: Last Name
Table: Employees
Total: Group By
Crosstab: Column Heading
Field: Order Date
Table: Orders
Total: Where
Crosstab:
Criteria: Between [Start Date] and [End Date]

If you choose SQL from the View menu, the SQL statement should look
like the following:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Sum(Orders.[Order Amount]) AS [SumOfOrder Amount]
SELECT Products.[Product Name], Sum(Orders.[Order Amount])
AS RowTotal
FROM Employees, Orders, [Order Details], Products, Orders
INNER JOIN [Order Details]
ON Orders.[Order ID] = [Order Details].[Order ID], Employees
INNER JOIN Orders
ON Employees.[Employee ID] = Orders.[Employee ID], Products
INNER JOIN [Order Details]
ON Products.[Product ID] = [Order Details].[Product ID]
WHERE ((Orders.[Order Date] Between [Start Date] And [End Date]))
GROUP BY Products.[Product Name]
PIVOT Employees.[Last Name]
WITH OWNERACCESS OPTION;

4. In the Query Parameters box, add two parameters with the same data
type, as follows:

Parameter DataType
------------------------
[Start Date] Date/Time
[End Date] Date/Time

FORM: XTabSample
----------------

1. Create an unbound form. Add two unbound text box controls with the
following properties:

(Text_Box_1) ControlName: Start Date
(Text_Box_2) ControlName: End Date

2. Add a button to the form with the following properties:

Caption: "Print Report"
OnPush: =XTabPrint()

REPORT: CrossReport
-------------------

1. Create a bound report with Page Header and Detail sections and the
following RecordSource property:

RecordSource: XTabResult

2. In the page header, create 10 tabular text box controls, without
labels, and change the ControlSource property of each to one of the
matching functions below:

=GetPageHdr(0) =GetPageHdr(1) =GetPageHdr(2) =GetPageHdr(3)
=GetPageHdr(4) =GetPageHdr(5) =GetPageHdr(6) =GetPageHdr(7)
=GetPageHdr(8) =GetPageHdr(9)

For example, the ControlSource property of the first text box should
be set to =GetPageHdr(0), the second to =GetPageHdr(1), and so
forth.

3. In the Detail section, create 10 tabular text box controls without
labels. Bind each of these controls to one of each of the fields in
the XTabResult table:

[Column0] [Column1] [Column2] [Column3] [Column4] [Column5]
[Column6] [Column7] [Column8] [Column9]

MODULE: <Any Name>
------------------

Create a new module with the following Access Basic code and call it
any name you choose:

NOTE: In the following sample code, an underscore (_) is used as a line-
continuation character. Remove the underscore when re-creating this code in
Access Basic.

Option Compare Database 'Use database order for string comparisons.
Option Explicit

'==================
' Global variables
'==================

Dim MyFields() ' For holding field names.
Dim nColumns As Integer ' For holding the number of columns.

'=====================
' GetPageHdr Function
'=====================

Function GetPageHdr (col)
If (col < nColumns) Then
GetPageHdr = MyFields(col)
Else
GetPageHdr = ""
End If
End Function

'====================
' XTabPrint Function
'====================

Function XTabPrint ()
Dim MyDB As Database, MyTable As Table
Dim MyDyna As Dynaset, MyQueryDef As QueryDef
Dim MySnap As Snapshot, i As Integer

' Create a dynaset from the query.
Set MyDB = CurrentDB()
Set MyQueryDef = MyDB.OpenQueryDef("CrossQry")
MyQueryDef![Start Date] = Forms![XTabSample]![Start Date]
MyQueryDef![End Date] = Forms![XTabSample]![End Date]
Set MyDyna = MyQueryDef.CreateDynaset()
MyQueryDef.Close

' Get field information and store the field names.
Set MySnap = MyDyna.ListFields()
MySnap.MoveLast
MySnap.MoveFirst
nColumns = MySnap.RecordCount
ReDim MyFields(nColumns)
i = 0
While Not MySnap.EOF
MyFields(i) = MySnap!Name
i = i + 1
MySnap.MoveNext
Wend
MySnap.Close

' Delete the contents of the XTabResult table.
Set MyTable = MyDB.OpenTable("XTabResult")
While Not MyTable.EOF
MyTable.Delete
MyTable.MoveNext
Wend

' Dump the dynaset into the XTabResult table.
' Table should have a column called COLUMN# for each column in
' the crosstab dynaset.
While Not MyDyna.EOF
MyTable.AddNew
For i = 0 To nColumns - 1
MyTable("Column" & i) = MyDyna(MyFields(i))
Next
MyTable.Update
MyDyna.MoveNext
Wend
MyTable.Close
MyDyna.Close

' Print the report.
DoCmd OpenReport "CrossReport", A_PREVIEW
End Function

Choosing a Date Range
---------------------

After you create the new database objects specified above, you will be
able to open the XTabSample form and enter starting and ending dates
on the form. A recommended data range is 1/1/91 through 12/21/92.
However, if you alternate short date ranges with long date ranges, you
will see how the report dynamically changes to fit the data.

After entering the date range, choose the Print Report button on the
form to display your dynamic report.

REFERENCES
==========

Microsoft Access "User's Guide," version 1.0, pages 154-159

Additional reference words: 1.00 1.10
KBCategory: kbusage
KBSubcategory: RptOthr

================================================== ===========================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1994..
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Found it... Access 2k version:

HOW TO: Create a Dynamic Crosstab Report in Access 2002
Applies To
This article was previously published under Q328320
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a
Microsoft Access project (.adp).
IN THIS TASK
SUMMARY

Create a Query That Is Named OrderDetailsExtended
Create a Query That Is Named EmployeeSales
Create a Form That Is Named EmployeeSalesDialogBox
Create a Report That Is Named EmployeeSales
REFERENCES
SUMMARY
You can use Microsoft Access 2002 to create dynamic reports that are
based on parameter crosstab queries. You can also create reports to
match a dynaset that is returned by such a query. Dynamic reports allow
your customized reports to show only the most recently modified data.
This gets rid of the need for fixed column headings and empty columns.

The following example uses starting dates and ending dates that are
entered on a form as the parameters in a crosstab query. When a button
on the form is chosen, Microsoft Visual Basic for Applications (VBA)
functions run the crosstab query that creates a dynaset. The contents of
the dynaset are then presented in a report.

In the following example, the report shows the employees that have sales
for a certain period of time. The employees that appear in the report
are based on the dates that are entered on the form. The steps below
show how to create a dynamic crosstab report based on tables in the
sample database Northwind.mdb.

The following new objects must be added to the database:
two queries
one form
one report
two functions
Each item is explained in a separate section that follows.

back to the top
Create a Query That Is Named OrderDetailsExtended
You can create a new select query that is based on the Order Details
table and the Products table. These tables are already joined based on
previously created relationships in the Northwind database. To create a
new select query, follow these steps:
In the Database window, click Queries under Objects and then
double-click Create query in Design view.
Add the Order Details table and the Products table.
Drag the following fields to the query grid and then add the following
values: Field: OrderID
Table: Order Details
Field: ProductName
Table: Products
Field: ProductID
Table: Order Details
Field: UnitPrice
Table: Order Details
Field: Quantity
Table: Order Details
Field: Discount
Table: Order Details
Field: ExtendedPrice: CCur(CLng([Order
Details].[UnitPrice]*[Quantity]*(1-[Discount])*100)/100)

Save the query as OrderDetailsExtended and then close the query.
back to the top
Create a Query That Is Named EmployeeSales
You can create a new crosstab query that is based on the Employees
table, the Orders table, the OrderDetailsExtended Query, and the
Products table. These tables are already joined based on previously
created relationships in the Northwind database. To create a new
crosstab query, follow these steps:
In the Database window, click Queries under Objects and then
double-click Create query in Design view.
Add the Employees table, the Orders table, the OrderDetailsExtended
Query, and the Products table.
On the Query menu, click Crosstab Query.
Drag the following fields to the query grid and then add the following
values: Field: LastName
Table: Employees
Total: Group By
Crosstab: Column Heading
Field: ProductName
Table: Products
Total: Group By
Crosstab: Row Heading
Field: Order Amount: ExtendedPrice
Table: OrderDetailsExtended
Total: Sum
Crosstab: Value
Field: ShippedDate
Table: Orders
Total: Where
Crosstab:
Criteria: Between
[Forms]![EmployeeSalesDialogBox]![BeginningDate] And
[Forms]![EmployeeSalesDialogBox]![EndingDate]
From the Query menu, click Parameters.
In the Parameters dialog box, add the following entries: Parameter:
[Forms]![EmployeeSalesDialogBox]![BeginningDate]
Data Type: Date/Time

Parameter: [Forms]![EmployeeSalesDialogBox]![EndingDate]
Data Type: Date/Time

Close the Parameters dialog box.
Save the query as EmployeeSales and then close the query.
back to the top
Create a Form That Is Named EmployeeSalesDialogBox
In the Database window, click Forms, and then click New.
In the New Form dialog box, click Design View, and then click OK.
Add two unbound text box controls with the following properties: Text
Box 1: ControlName: BeginningDate
Text Box 2: ControlName: EndingDate

Add a command button to the form with the following properties. If the
Command Button Wizard starts, click Cancel. Name: Command4
Caption: "Employee Sales Crosstab"

Set the OnClick property of the command button to the following event
procedure:Private Sub Command4_Click()
Dim stDocName As String
Dim accobj As AccessObject

On Error GoTo Err_Command4_Click

stDocName = "EmployeeSales"
'This function closes the report if the report is open and then
re-opens the report.
Set accobj = Application.CurrentProject.AllReports.Item(stDocNa me)
If accobj.IsLoaded Then
If accobj.CurrentView = acCurViewPreview Then
DoCmd.Close acReport, stDocName
DoCmd.OpenReport stDocName, acPreview
End If
Else
DoCmd.OpenReport stDocName, acPreview
End If
Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
Close the Microsoft Visual Basic Editor.
Save the form as EmployeeSalesDialogBox and then close the form.
back to the top
Create a Report That Is Named EmployeeSales
Note When you put the text boxes on the report for steps 4, 5, and 6,
put them horizontally.
In the Database window, click Reports, and then click New.
In the New Report dialog box, click Design View, click to select the
EmployeesSales query in the Choose the table or query where the object's
data comes from check box, and then click OK.

Note When you receive an Enter Parameter Value dialog box, click Cancel.
To add a report footer section, click Report Header/Footer on the View
menu.
Assume that there are nine records in the Employees table. Then, in the
page header, create 11 unbounded text box controls without labels. Set
the Name property of the left text box to "Head1" and the Name property
of the next text box to "Head2". Continue until you complete "Head11".
In the "Detail" section, create 11 unbounded text box controls without
labels (one text box for the row heading, nine text boxes for the
maximum number of employees in the Northwind database Employees table,
and one text box for the row total). Set the Name property of the left
text box to "Col1" and the Name property of the next text box to "Col2".
Continue until you complete "Col11". Set the Format property of the text
boxes "Col2" through "Col11" to Standard.
In the report footer, create 11 unbounded text box controls without
labels. Set the Name property of the left text box to "Tot1". In the
Control Source property of Tot1, type ="Totals:". Set the Name property
of the remaining text boxes that display the column totals to "Tot2"
through "Tot11". Set the Format property of text boxes "Tot2" through
"Tot11" to Standard.
On the View menu, click Code.

You see the VBA code window for the report.

Type or paste the following code to the code window:NOTE: The sample
code in this article uses Microsoft Data Access Objects. For this code
to run properly, you must reference the Microsoft DAO 3.6 Object
Library. To do so, click References on the Tools menu in the Visual
Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library
check box is selected.

' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11

' 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
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' 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.
rstReport.MoveNext
End If
End If

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" +
Format(intX))
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.
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 = 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.
rstReport.Close

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 Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

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

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!EmployeeSalesDialogBox
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("EmployeeSales")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!EmployeeSalesDialogBox!Begin ningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!EmployeeSalesDialogBox!Endin gDate") _
= frm!EndingDate

' 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.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub

The following event procedures are set for the report. Report/Section
Property Setting
------------------------------------------------------------
Report OnOpen [Event Procedure]
OnClose [Event Procedure]
OnNoData [Event Procedure]
Report Header OnFormat [Event Procedure]
Page Header OnFormat [Event Procedure]
Detail Section OnFormat [Event Procedure]
OnPrint [Event Procedure]
OnRetreat [Event Procedure]
Report footer OnPrint [Event procedure]

Save the Report as EmployeeSales. When you are prompted to enter the
parameter values, click Cancel and then close the report.
After you create the new database objects that are specified earlier,
you can open the EmployeeSalesDialogBox form. You can enter starting
dates and ending dates on the form. Use a date range from 7/10/1996
through 05/06/1998.

After you enter the date range, click Employee Sales Crosstab on the
form to preview your dynamic report.

back to the top

REFERENCES
For additional information about downloading a working copy of a sample
database, click the following article number to view the article in the
Microsoft Knowledge Base:
248674 ACC2000: Orders and Developer Solutions Sample Databases
Available on the Microsoft Developer Network (MSDN)

Follow these steps to find the sample:
Open the sample database Solutions9.mdb.
In the Select a Category of Examples list, click Create advanced
reports.
In the Select an Example list, click Create a crosstab report with
dynamic column headings, and then click OK.
back to the top
The information in this article applies to:
Microsoft Access 2002
Last Reviewed: 10/23/2003 (3.0)
Keywords: kbhowto kbHOWTOmaster kbusage KB328320
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.