Share this Page URL

Chapter 15. Integration with Office 2003... > Analyzing Access Data Through Excel - Pg. 553

Integration with Office 2003 and SharePoint Services 3. 4. 5. 6. 1. 2. 3. 4. 5. 6. 553 In the first screen of the Link To Windows SharePoint Services Wizard, enter the URL for the SharePoint site to which you want to connect. In the wizard's next screen, select the list or lists you want to view. Click Next. Select the views to which you want to link, and click Next. Click Finish. Click the Lists item in your SharePoint team site. At the top of the Lists section, click Create List. In the Custom Lists section, click Custom List In Datasheet View. On the New List page, type a name for your list. Click Create. Click Task Pane at the top of the list object. Click the Create Linked Table In Access link in the task pane. Select a database, and click Open. Link a list to an Access table Analyzing Access Data Through Excel One of the most important computer-based business tools used in information management these days is the spreadsheet. Microsoft Excel is a longtime leader in the world of spreadsheet tools, and Excel 2003 builds on that leadership with stronger XML support, smart tag enhancements, and updated features. Like the other Office tools we've looked at, Excel 2003 is all about data collection, display, and distribution and functions as an integrated partner with Microsoft Access. If you're at all familiar with spreadsheets, you know that the data in a spreadsheet is organized in rows and columns and that the cells in a spreadsheet "store" the data that will be presented and organized. Similarly, a relational database such as Access stores data in rows and columns and allows you to relate the fields in one table with those in another. In many ways, a spreadsheet is a nonrelational database, and each worksheet can be viewed as a table. Because of this, using Excel to represent data in Access tables is very straightforward. At this point, you might be wondering, "If Excel spreadsheets and Access tables are so similar, why use Access with Excel or Excel with Access? Why not just use one or the other?" Excel's strength lies in its ability to aggregate, or summarize, data and make it easier to use. In many ways, Excel complements the work that an Access form or report does. For example, in a database table, typi- cally a field is not a calculation of other fields. So if you had a table that tracked the cost of materials for producing a given product in your company and you wanted to find out the total cost of the materials using just the table, you'd have to write a small program that performed the calculation and embed that in a form or do the calculation in a report. Using Excel with Access can accomplish the same tasks. But because Excel is designed specifically for data analysis, doing calculations can be a little less labor intensive and can provide a more rapid set of calculations of your data. Excel allows you to create cells (or fields) that strictly are not a part of the data set itself but are aggregations of that data. You can create a cell in the spreadsheet that calculates the cost of the items and displays that sum. The cell lives right with the data and performs the calculation auto- matically. In addition, the calculation is dynamic, so as the information in the data cells changes, the sum changes to reflect the updates. At the risk of oversimplifying, one way Excel complements Access is by taking the raw data from the database and providing tools that help analyze that data. Whereas SharePoint is about sharing data and InfoPath is about capturing data, Excel excels at manipulating data and making it easier to understand and work with. In this section, we'll explore ways to use Access in conjunction with Excel to provide better, more detailed views into Access data.