Solved

Access 2010 Maximum Crosstab Columns

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

831 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