Don't try to imitate Excel's powerful analysis functions in Access. Instead, export your data into Excel and run your analysis there. In short, don't ever work harder than you need tolet Office do its job!
Sending data to Excel is one of the easiest Office collaborations. You just select a table or query, select Analyze It with MS Excel from the OfficeLinks button, and you're done. After your data is in Excel, you can use any Excel featureExcel doesn't care where the data came from.
Let's export the Plantings table from your gardening database to Excel. To do so, follow these steps:
Figure 16.1 shows the new spreadsheet in Excel. As you can see, all your data retains its original formatting as a result of selecting the Save Formatted option. Notice also that Excel uses the field names as column headings.
Figure 16.1. Access easily exports data to Excel.
You can't see all the data onscreen, so just scroll to the right and you'll see that Excel even respects the hyperlink data in the More Info field. Only your pictures didn't make the trip. You're ready to start analyzing!
Now, let's export the Catalogs table and see how Excel handles it. Repeat steps 14 from the previous procedure. But this time, specify the Catalogs table and don't select the Save Formatted option. Open the new Excel spreadsheet based on the Catalogs table. This time, there's no formatting and Excel doesn't know how to interpret all the data. Can you see the small triangles in the upper-left corner of several of the ZIP entries?
Select one of those fields, and Excel displays an error icon. Click the drop-down arrow to see the possible types of errors shown in Figure 16.2. In this case, there really isn't a problem because ZIP codes don't need to be stored as numbers . If you want, you can select the fields and select Ignore Error, or you can do nothing. Save this file in Excel using the name CatalogsInExcel.xls ; you'll use it in the next section.
Figure 16.2. Excel lets you know when it can't interpret Access data.