Solved

Access 2010 Maximum Crosstab Columns

Posted on 2013-01-29
4
773 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

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)

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

911 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

22 Experts available now in Live!

Get 1:1 Help Now