We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
7 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Ms Access Crosstab Report with variable number of columns
Posted on 2014-02-21
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
- +1
6 Comments
Active today
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.
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.
Active today
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
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
Active 3 days ago
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
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
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?
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?
Active 3 days ago
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.
Featured Post
June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks.
Specify a start-up form through options:
Specify an Autoexec macro:
Us…
Suggested Courses
Course of the Month7 days, 9 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.