Microsoft KB Archive/103262
ACC1x: How to Create a Dynamic Crosstab Report
The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
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.
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.
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).
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:
- Choose the Query button in the Database window, then choose the New button.
- Add the Employees, Orders, Order Details, and Products tables.
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;
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
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
Add a button to the form with the following properties:
Caption: "Print Report" OnPush: =XTabPrint()
Create a bound report with Page Header and Detail sections and the following RecordSource property:
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.
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.
NOTE: For Microsoft Access 95 or Microsoft Access 97, you can find information, instructions, and examples in the Developer Solutions sample application (Solutions.mdb) included with Microsoft Access. For more information about Creating a Dynamic Crosstab report in Microsoft Access 95 or Microsoft Access 97, open the Solutions.mdb database usually located in the ACCESS\SAMPLES directory. Select "Create advanced reports" in the Select A Category Of Examples box, then "Create a crosstab report with dynamic column headings" in the Select An Example box.
Microsoft Access "User's Guide," version 1.0, pages 154-159
Keywords : kbusage
Issue type : kbhowto
Last Reviewed: November 4, 2000