NetLab3_Instructions_Final_Spring_2011

NetLab3_Instructions_Final_Spring_2011 - Net Lab #3 Office...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Net Lab #3 Office 2007 Integration Objective The purpose of this assignment is learn how two data management and analysis tools (ACCESS and EXCEL) can work together to help a manager or data analyst effectively collect, store, retrieve, process, and report on important information. At this point, you should have some thoughts about what tasks are particularly effective to do in Access versus Excel. In this lab you will learn how these packages can work together by learning how to import and export data to/from Excel and Access. In the previous two lab assignments we have worked on using both Excel and Access as relatively standalone applications. However, most MS Office business solutions require moving data between all Office applications most notably between Excel and Access. At the end of this assignment, you should be able to do the following: 1. Import an Excel worksheet into an Access database (in an existing or a new table). 2. Import Access data into Excel spreadsheets and apply custom formatting to the imported data along with custom analysis. 3. Link a PivotChart to an Access Query. In addition to learning how to import/export data, this lab will reinforce several of the key concepts (Excel conditional formatting, query building in Access, setting up relationships in Access, Excel web queries, etc) from the previous two assignments. Scenario Your boss liked your Access database and Excel workbook so much that she now wants you to integrate the two solutions together. In order to do this, she wants you to add additional tables to your Access database by importing some data that is currently stored statically on the web and/or in Excel workbooks. She additionally wants you to build a few more queries to answer some more in depth questions about the performance of her business. She also likes the idea of being able to build reports in Excel but doesn’t like the fact that the Excel reports are not dynamically linked to the central database. As such, she wants you to create a few reports for her sales and accounts receivable teams that will always be in sync with the centralized Access database. Getting Started Download the “NetLab3_Final_Starter_Spring_2011.xls” file from the resources section of Laulima under “Net Lab #3” to get started. Note that this file is an Excel 2003 (not 2007) file and you must save the file as an Excel 2003 file for the integration (tasks 1 & 2) to work. This is the case because the Excel file contains macros and the Access 2007 integration wizard does not like the “xlsm” file format (probably due to security concerns but that is just speculation on my Page 1 of 14
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Net Lab #3 Office 2007 Integration part). Half way through the activity, you will upgrade the file to a 2007 macro enabled workbook (“.xlsm” extension) (follow instructions prior to task 3). To save as an Excel 2003 file, open the starter file in Excel, click the Microsoft button, and then
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 14

NetLab3_Instructions_Final_Spring_2011 - Net Lab #3 Office...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online