Sunday, March 15, 2015

Beware of MS Access 2007 crosstab

My case here was,  MS Access was used to import live data from an IBM database using ODBC drivers and there are no data corruption happened. Triple checked that.

I was working a report for my company sales manager where the report was about putting all the type of sales rebates in a crosstab/pivot style report (Sales transactions as row, rebates type as column and values of each rebates for each sales made up the cells). Then the sales manager will use Excel to import the data from the Access database and work on his analysis in Excel. However, MS Access 2007 crosstab has failed to show some of the expected value when there are more than 40+ columns as rebate types.

After some investigation, removing 10 columns seems to help restore those values back, however, all the columns are deemed to be crucial for the sales manager. In the end, I finally figured out by export the data to MS SQL Server Express and run a stored procedure to generate a pivot view there. Due to the MS SQL sat at my computer, where I might turn it off occasionally, the result is exported into an XML file so the sales manager able link Excel to the XML file instead.

Therefore, beware if you are using crosstab because someday you may find some of the value  missing due to too many columns created.