Getting Data from Another Program

Information you want to analyze may not always exist in an Excel workbook; you might have to retrieve it from another Office program, such as Access. Access table data can be easily converted into Excel worksheet data. Before you can analyze Access data in a workbook, you must convert it to an Excel file. You can either use the Analyze It With Microsoft Office Excel command in Access to export data as an Excel table file, or use the PivotTable And PivotChart Report wizard in Excel to use the Access data as a PivotTable , a table you can use to perform calculations with or rearrange large amounts of data. Once you determine what fields and criteria you want to use to summarize the data and how you want the resulting table to look, the wizard does the rest.

Export an Access Database Table into an Excel Workbook

Click Start on the taskbar, point to All Programs, click Microsoft Office, and then click Microsoft Office Access 2003.

Open the database you want, and then click Tables on the Objects bar.

Click the table you want to analyze.

Click the OfficeLinks button list arrow on the Database toolbar.

Click Analyze It With Microsoft Office Excel to save the table as an Excel file, start Excel, and open the workbook.

Use Excel tools to edit and analyze the data.

Create an Excel Workbook PivotTable from an Access Database

Click the Data menu, and then click PivotTable And PivotChart Report.

Click the External Data Source option.

Click Next to continue.

Click Get Data.

On the Databases tab, click MS Access Database, or click Browse, and then locate and open the Access data source you want.

Click OK, and then if necessary follow the wizard to select the data you want to use.

Click Next to continue.

Click a location option for the new PivotTable.

If you want, click Layout or Options to change the way the PivotTable looks or functions, and then click OK.

Click Finish.