ACC1x: How to Create a Dynamic Crosstab Report

• Microsoft Access versions 1.0, 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.

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.

REFERENCES

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

Keywords          : RptOthr kbusage
Version           : 1.0 1.1
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto