Chapter 11 - Importing and Exporting

I would like to use Access for my department’s inception to date reports because Access, unlike Excel, has so many wonderful reporting features. One would think this would be a fairly simple process, right?

The best source of my inception to date data is an SQL based program called QMENU. When I save the QMENU files as text documents I am not able to import them into Access because there doesn’t seem to be a set delimiter to the data. I am also not able to import them into Excel as text files. When I save the QMENU files as html documents I am able to import them into Excel, but not into Access. Access shows the html file as 11 different tables, none of which has the complete data I need. So I seem to be stuck with using Excel to manipulate the QMENU data.

Another of the obstacles I've encountered when trying to produce inception to date reports in Access is in the sub-totaling of data. I need three levels of sub-totaling for these reports, at the account group org and fund levels. The Microsoft Office pivot tables normally work extremely well for sub-totaling on multiple levels. However, because the pivot table command doesn't work well in Access I need to create pivot tables in Excel. Unfortunately, these Excel pivot tables do not export into Access well. The column headings which are text in Excel, i.e. Sum of BUDGET, show up as error messages when I try to create an Access link to the Excel pivot tables. So I wind up spending more time manipulating the data in Excel so that I can just copy it into Access.

This would be very time-consuming but an okay solution if I were just creating a one-time report. However, I was hoping to be able to somehow have my Access report link to the Excel/VistaPlus/QMENU data which changes weekly.

I was able to export the Access data back into Excel and Word successfully once I remembered to save the Access 2007 databases as Access 2003, because that's the version of the Excel and Word software I have on my computer now.