How to create a dynamic crosstab report in Access 2002 (328320)


The information in this article applies to:

  • Microsoft Access 2002
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

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:
  1. In the Database window, click Queries under 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.
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:
  1. In the Database window, click Queries under 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.
back to the top

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

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:
  1. Open the sample database Solutions9.mdb.
  2. In the Select a Category of Examples list, click Create advanced reports.
  3. In the Select an Example list, click Create a crosstab report with dynamic column headings, and then click OK.
back to the top
Modification Type:MinorLast Reviewed:8/3/2004
Keywords:kbReport kbhowto kbusage KB328320
©2004 Microsoft Corporation. All rights reserved.