Dynamic Report based on a Crosstab query
14649 Downloads
Create a crosstab report with dynamic column headings. This example is based on this Microsoft KB article: How to create a dynamic crosstab report in Access 2002
You can use Microsoft Access 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 download includes databases in Microsoft Access 97 and Microsoft Access 2000. formats This will allow anyone with Access 97, Access 2000, Access 2002, Access 2003, and Access 2007 to use this example.
| Category: | Example Reports |
| File Size: | 227.67 KB |
| File Type: | application/zip |
| Hits: | 57273 Hits |
| Download: | 14649 times |
| Created Date: | 03-12-2016 |
| Last Updated Date: | 03-09-2022 |
Related documents
This example shows how to print a varying amount of multiple labels per record.
It also shows how to have 1 of X, 2 of X, etc. print for each item in a group.
This is an example based on the NorthWind example using the Invoice report.
I created a form to select the Invoices you want to print. There is a "Select All", "Unselect All", "Print Invoices", and "Save as Snapshot files" buttons.
To make it work I had to added one field to the "Orders" table. I added this field to the query form the Invoice report.
*** Updated 9/28/2010 ******
Added the ability to export the invoices to individual Snapshot (.snp) files.
Hope you find this useful.
*** The download includes a 97 and 2000 version.
Will convert to Access 2007/2010
For 2007/2010 see: Batch Printing and Save As PDF
By Candace Tripp (utter:angel) Access 2000 format
Demonstrates a method of displaying a picture/image file in a report without storing the image in a table.
This example is in Access 2000 format. It should work fine with Access 2000 and later.
This example Access database shows how to print the Barcode 39 (also called 3 of 9) on an Access Report.
**Note: The barcode font is stored in a table as a BLOB. It is installed and removed automatically for you.
This is an Acess 2007 format database.
This is an example based on the NorthWind example using the Invoice report.
I created a form to select the Invoices you want to print. There is a "Select All", "Unselect All", "Print Invoices" , and "Save Invoices as separate PDFs" buttons.
To make it work I had to added one field to the "Orders" table. I added this field to the query form the Invoice report.
Hope you find this useful.
*** The download uses the built-in ability to create PDFs available in Access 2007 and later.
Note: with Access 2007 you will need to be sure that you have downloaded and installed the add-in for creatng PDFs..
This example shows several different methods for selecting and running reports.