Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to Create a Dynamic Crosstab Report
PRODUCT :Microsoft Access
PROD/VER:1.00 1.10
OPER/SYS:WINDOWS
KEYWORDS:kbusage
---------------------------------------------------------------------
The information in this article applies to:
- Microsoft Access versions 1.0 and 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.
REFERENCES
==========
Microsoft Access "User's Guide," version 1.0, pages 154-159
Additional reference words: 1.00 1.10
KBCategory: kbusage
KBSubcategory: RptOthr
================================================== ===========================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1994..
