Pivot tables and charts - Access or Excel

[kkrishna]

I am using the 2007 versions of Access and Excel. I have an Access database set up to record the metrics for several indexes on a monthly basis starting from Jan 2009. This is an ongoing exercise and every month new records will be added to the database.

The indexes are calculated fields from the input data in a table and a query is set up for the same. I am working out a form to filter the records depending on the month, entity name, index name etc.



Now we have two options - either display the results as another query in Access or export the results to Excel. The ultimate goal is to create charts for the indexes for the selections made.

I find that Excel has great features to display results in a pivot table and create charts for the indexes. I am not conversant with Pivot table and Pivot chart features of Access. However the menu buttons are different from Excel.

Has Access the same capabilities as Excel in creating the Pivot table and Charts? I am already finding problems as can be seen by my post on AutoCalc in this forum. Are there any limitations? What is preferable? If we could create tables and charts in Access as well as we do in Excel, I would keep working on these features. Otherwise we would export the query to Excel and work on a pivot table in Excel.

I'll appreciate any suggestion from the experienced users of Access.
Thanks in advance.
Krishna

[Datagopherdan]

Hi Krishna,

I've done a number of metrics reports that were generated from Access and involved charts, graphs, and pivot tables. I've found it better to produce these in Excel. One of the biggest reasons is that in Excel, the users have the flexibility of creating additional charts and pivot tables if needed.

I am using the 2007 versions of Access and Excel. I have an Access database set up to record the metrics for several indexes on a monthly basis starting from Jan 2009. This is an ongoing exercise and every month new records will be added to the database.

The indexes are calculated fields from the input data in a table and a query is set up for the same. I am working out a form to filter the records depending on the month, entity name, index name etc.

Now we have two options - either display the results as another query in Access or export the results to Excel. The ultimate goal is to create charts for the indexes for the selections made.

I find that Excel has great features to display results in a pivot table and create charts for the indexes. I am not conversant with Pivot table and Pivot chart features of Access. However the menu buttons are different from Excel.

Has Access the same capabilities as Excel in creating the Pivot table and Charts? I am already finding problems as can be seen by my post on AutoCalc in this forum. Are there any limitations? What is preferable? If we could create tables and charts in Access as well as we do in Excel, I would keep working on these features. Otherwise we would export the query to Excel and work on a pivot table in Excel.

I'll appreciate any suggestion from the experienced users of Access.
Thanks in advance.
Krishna