How to create a dynamic crosstab report in Access 2002

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.

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:

1. In the Database window, click Queriesunder Objects and then double-click Create query in Design view.
2. Add the Order Details table and the Products table.
3. 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)         
    

4. Save the query as
   OrderDetailsExtended and then close the query.

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:

1. In the Database window, click Queriesunder Objects and then double-click Create query in Design view.
2. Add the Employees table, the Orders table, the OrderDetailsExtended Query, and the Products table.
3. On the Query menu, click Crosstab Query.
4. 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] 

5. From the Query menu, click
   Parameters.
6. 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
    

7. Close the Parameters dialog box.
8. Save the query as EmployeeSales and then close the query.

Create a Form That Is Named EmployeeSalesDialogBox

1. In the Database window, click Forms, and then click New.
2. In the New Form dialog box, click
   Design View, and then click OK.
3. Add two unbound text box controls with the following properties:

   
      Text Box 1: ControlName: BeginningDate
      Text Box 2: ControlName: EndingDate
    

4. 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"
    

5. 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(stDocName)
     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
   
   

6. Close the Microsoft Visual Basic Editor.
7. Save the form as
   EmployeeSalesDialogBox and then close the form.

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.

1. In the Database window, click Reports, and then click New.
2. 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.
3. To add a report footer section, click Report Header/Footer on the View menu.
4. 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".
5. 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.
6. 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.
7. 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!BeginningDate") _
        = frm!BeginningDate
      qdf.Parameters("Forms!EmployeeSalesDialogBox!EndingDate") _
        = 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
   

8. 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]
    

9. 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.