Access report to mirror Excel format

Topic

New Reply

I have a customer who is currently using over 100 linked spreadsheets to display Income Statement information. I’ve been asked to consolidate the data into Access (done – no problem), and then re-create the Income Statements as reports. I can generate reports for a single period, but they want the information spread out across 5 years in a format similar to a Crosstab report. I’m not sure how to transform the data into a format that LOOKS like an Excel spreadsheet.

Here’s what they want:
January February March… for 2011 and 2012, Qtr1, Qtr2, Qtr3, Qtr4 for 2013 and Total year values for 2014 and 2015. These would be the column headings.
Below each heading would be listed the sales and cost-of-sales values for each General Ledger grouping.

This would be a table-type format similar to what you’d see in an Excel spreadsheet. How do I make an Access Report do this?

Oh, and this has to be flexible for ANY period. The column headings cannot be static since they could decide to only report for a six month period.

Suggestions?

Reply | Quote

Replies

Because of the different interval types, this gets pretty complicated. You could use a crosstab query to get the basic data into a column format similar to what Excel would give you, but the different interval types is a challenge. One option would be to modify the querydef for the crosstab and define column headings dynamically – I’ve never tried to do that, but in theory is should be possible. Another option might be to use Automation to create an Excel workbook on the fly from your data. Excel has some advantages, in particular the ability to shrink a worksheet to fit on a single page if you wish. That would involve a fair bit of VBA, but is the option I would prefer.

Reply | Quote

I agree with Wendell, but I had another idea. I do not like working with the report writer in Access. You may not want to work with VBA. (Many users I deal with don’t know VBA and I don’t have the time to maintain the db for them if something goes wrong.) A way to get around it is to combine the power of Access with the report flexibility of Excel. Not seeing the data or reports they already have, I would write a set of queries and macros that filter the data to what they desire. You could give them a form(s) where they could select the periods to be displayed. Here’s the key, the final step in the process is to write this data to set of tables in Access. Then I would link or import with a macro an Excel workbook to the data. If you need to do any fine tuning with Pivot Table, Dashboard, you can in Excel. You can even write a macro that asks if they want to update the data everytime the workbook is opened.

Just a thought.

Reply | Quote

Good suggestion, kreaves. What I’m doing is creating two tables: One with the headings required in the report; another with the data organized in the required columns. I’ve bound the report to the table with the headings and added a subreport which shows the data contained in the data table. I’ve also created a form to display this information in the same format. Both tables are created using VBA (SQL commands, actually). This has the added bonus of resulting in a data table that can be exported to Excel. That functionality is actually built into the system, of course, even before you suggested it! The difficulty was ensuring the data was added to the table in the proper order, but that was accomplished with a series of For-Next and DO loops.
The down-side to manipulating the data like this is that the report layouts need to be pre-defined (for example, I need to know that for the first 2 years of any selected period, they want individual months; after that Quarters for 2 years and any additional data will be summarized by year). They can’t decide to change this up unless they want to pay me to change the VBA code! They can still use standard Access Crosstab reports, but they’re less elegant and not as flexible. I always tell my customers to create queries in Access and Pivots in Excel because data should always be stored in Access, but analysis should always occur in Excel.

Thanks for the feedback.

Reply | Quote