Solved

Ms Access Crosstab Report with variable number of columns

Posted on 2014-02-21
6
5,714 Views
Last Modified: 2014-03-04
I have a crosstab query that I want to place in a report.  

The issue is that the query result will   have a variable number of columns .. from 3 to 15.  I have seen the code MS has provided for crosstab reports, but for my case either I have a large amount of blank space in the report or I get a runtime error beacuse I have more columns than textboxes.


Anyone have a better approach for this, or a better solution than the MS code?

Thanks in advance.
0
Comment
Question by:envirospatial
6 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 39876427
The MSFT knowledgebase has an article on this:

http://support.microsoft.com/kb/328320

If you have only one or two "options" - i.e. you have a crosstab with 5, 10 or 15 columns - you may find it easier to just create the 3 crosstabs and be done with it. If you think you'll have more down the road, of if the number of columns is very dynamic, then the article should show you how to get started.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 200 total points
ID: 39876442
I generally do mine dynamically, but if 15 columns won't fit on a page (have you tried legal paper), your best bet might be export it to Excel.

I'm on my iPad, and don't have access to a sample database, but I generally create the report with all of the columns that I need.

I the report Open event, I open a recordset:

SELECT Top 1 * from rptQuery

Then I use the recordset fields collection to loop through the fields in the recordset and set the label captions and the field ControlSource properties, something like

Dim rs as dao.recordset
Dim intLoop as integer

set rs = db.openrecordset("SELECT TOP 1 * from rptQuery",, dbfailonerror)
For intLoop = 0 to rs.fields.count - 1
  if intLoop > 14 then exit for        'the number of controls on your form 
  me.controls("lbl_" & intLoop).Caption = rs.fields(intloop).name
  me.controls("txt_" & intLoop).ControlSource = rs.Fields(intLoop).name
Next

Open in new window

0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 150 total points
ID: 39879256
The use of the IN clause lets you predefine the columns that the crosstab generates. That lets you reference specific fields in your report. You can then determine the name of the column to display in your report -- or none.

I wrote a paper on how to create a 12 month crosstab report for any date range (including missing months) that can be done without writing any VBA code. A sample database is also included. Hope this helps: http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html

It's part of our Microsoft Access Query Help Center: http://www.fmsinc.com/MicrosoftAccess/query/help-center.html
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:envirospatial
ID: 39882166
Thank you for the comments.

To answers questions posed


Scott
- The crosstab resultschange. One time it could generate 3 columns, another 7, another 14, another 15.  The number of columns generated is highly variable.  Ex.. One survey team could find three tree species while another could find 15 tree species, or anywhere in between.  Iuse the routines provided to load the information in the textboxes.

-Fyed
Thanks for the routine.
I am using something similar to populate the textboxes (headers and values) in my report.   May have to use legal.

-Luke
I want to show all the columns generated.  

Seems like there are no other approaches that would facilitate this?
0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 150 total points
ID: 39882485
There will be a maximum number of columns you can show in the report based on space. Refer to the columns by number and have them all in your IN clause -- that's used for values that don't exist too. I show that in my example since the report always refers to 12 columns whether those months exist or not.
0
 

Author Closing Comment

by:envirospatial
ID: 39903060
Thanks.  I just added a bunch of textboxes and made fonts smaller to accomodate the variable number of columns.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
subtract 1 in Access 2003 query 7 37
Search Form with Run Time Error 3075 1 22
Importing and Dropping Table in Access 11 23
I need to be able to get MAX(date)-1 from table. 4 25
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now