Formatting MS Access report with subreports and subforms in pivot views

I've always had trouble with Access' pivot table functionality, back when I had to use it. I found that the best strategy was to create pivot tables in Excel. They're easier to manage, you can easily set print margins, etc. I'd just take the underlying data table, export it to Excel, and create pivot tables there. As for getting some of your colleagues to do these reports themselves--depends on their computer literacy, honestly. Access is not especially user-friendly, even though it claims to be able to create "reports" that can be generated merely by pressing a button.

When I needed reports formatted just so in OpenOffice Base, whose inbuilt report generator is totally sucky, I ended up writing StarBasic code to generate them by automating OpenOffice Writer and attaching that code to buttons in one of the Base forms. The same approach should work with Access, Excel, and VBA.

"You might find it's actually easier to use Excel's native ability to pull data from Access and just update your spreadsheet weekly[.]" This works pretty darn well, but not entirely seamlessly. Once I understood the way Excel and Access interact, it became pretty easy. The book, http://www.worldcat.org/oclc/85622971, was very helpful getting me there. Basically, I put a table in an Excel worksheet that draws from a http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx of an Access database. The Query parameters (thus far, just date ranges) are http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP005199548.aspx. In turn, I created several worksheets that contain PivotCharts and PivotTables linked to the main Table linked to the Access DB. THEN, I put a hyperlink on the Access form that opens the excel file and the main worksheet. The main worksheet has a brief description that tells the user to adjust the dates and hit a button that triggers a macro that refreshes all the data and prints the entire workbook to a PDF. So, it works *almost* like a report in Access (there's an extra step for clicking the link to open the excel file), with much, much nicer formatting. The presence of macros and data connections, however, means that Microsoft security settings prevent it from opening initially on some machines. There aren't so many people that need to get at it that it presents much of a hurdle. Thanks for the suggestions.

It shouldn't be terribly hard to work out how to write a tiny scrap of VBA to open the spreadsheet for you, and attach that to a button in an Access form; that way your users wouldn't necessarily even notice you were using Excel.

I'm glad you've got it sorted. It sounds like you've gotten it much more manageable than before. You can probably sort out a macro or a little VBA to auto-calculate your dates when the worksheet is opened (as long as they're any sort of pattern), and refresh the data for you so the end user won't have to, if it's worth the extra effort to you.