Solved

Access 2010 Maximum Crosstab Columns

Posted on 2013-01-29
4
942 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
[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
4 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

617 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