Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access report to mirror Excel format

    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?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  3. #3
    New Lounger
    Join Date
    Feb 2010
    Location
    Florida
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •