Solved

Access 2010 Maximum Crosstab Columns

Posted on 2013-01-29
4
755 Views
Last Modified: 2016-02-11
I have been asked to create an Excel spreadsheet from an Access database with about 365 columns of data. The Crosstab query that I used for this purpose was previously for 60 months of data plus some static information. This worked via the command:
DoCmd.TransferSpreadsheet acExport, , "qrySpec_FundFutCashFlow_Crosstab", strExportFile

I need a technique using VBA code to create an Excel spreadsheet with about 365 months of data (in columns) from an Access database.
0
Comment
Question by:RCUllrich
  • 2
4 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 200 total points
ID: 38833103
that may not be feasible, queries and tables are limited to 255 columns.

perhaps using VBA codes can do that.
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 300 total points
ID: 38833143
You could build text file, comma separated, with a '.csv' extension, which would open nicely in Excel.
What is your table structure (just fields you want in spreadsheet)?
Are there entries for each month?
0
 
LVL 3

Author Comment

by:RCUllrich
ID: 38836845
The data only includes a monthly value if there is one. Some assets have a single cash flow record at maturity so I did NOT write 359 "zero" records when only 1 record was needed (with a date 30 years from today).

My issue is that I can't use the crosstab query, therefore I will need to code this in VBA and write out the delimited text file.

Thanks
0
 
LVL 3

Author Comment

by:RCUllrich
ID: 40995506
Does anyone know if Access 2013 will allow more than 255 columns in a query (or table)?
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

746 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

9 Experts available now in Live!

Get 1:1 Help Now