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

QUERY: CrossQry

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>

   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

REFERENCES

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

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