Analyzing Records with Microsoft Excel

Access is capable of performing calculations on groups of records, but it can't hold a candle to its Microsoft Office 2003 counterpart, Microsoft Excel, when it comes to number crunching. An Excel spreadsheet is similar to an Access datasheet in many ways, with one important difference. Not only can you enter information in an Excel spreadsheet, but you can also easily add formulas directly into the spreadsheet grid something you can't do in Access. It makes sense, then, that more people use Excel together with Access than any other program.

In this lesson you will learn how to create a Microsoft Excel spreadsheet based on an Access query.

1. Make sure that the Database window is displayed.

   We will be using a query to create an Excel spreadsheet in this exercise, but you can also create spreadsheets based on tables and reports.

2. Click the Queries icon in the Objects bar and select the qrySales query.

   Here's how to export the selected database object to Microsoft Excel.

3. Click the OfficeLinks button list arrow on the toolbar and select Analyze It with Microsoft Office Excel from the list.

   Another way to export an object to Excel is to select Tools » Office Links » Analyze It with Microsoft Excel from the menu.

   Microsoft Access sends the results of the qrySales query to Microsoft Excel, as shown in Figure. We won't spend much time working in Excel this is Access tutorial but let's at least try adding a formula to the spreadsheet.

4. Scroll to the right if necessary, click in cell H2, type =G2*.9 and press Enter.

   Excel multiplies the value in cell G2 by 0.9 (90 percent) and displays the results.

5. Close Microsoft Excel without saving any changes.

TO EXPORT ACCESS DATA TO MICROSOFT EXCEL:

1. OPEN OR SELECT THE TABLE, QUERY, OR REPORT YOU WANT TO EXPORT.

2. CLICK THE OFFICELINKS BUTTON LIST ARROW ON THE TOOLBAR AND SELECT ANALYZE IT WITH MICROSOFT OFFICE EXCEL FROM THE LIST.