Unit 9: Database and Spreadsheet Integration In this unit, you will examine how the integration of database information and spreadsheets may be used to make an organization’s informational and communications materials more effective. This unit will start with an examination of some of the common features of the database and spreadsheet tools, and it will then go on to demonstrate how these features may be integrated to perform data analysis reports such as sales forecasts, product cost models, or fund raising projection. Data contained in a database sometimes need to be released into a form that allows greater flexibility and manipulation. Business organizations are often involved in what-if-analysis to understand the impact on past and future business actions. Your boss may ask you to retrieve all of the customer information for a certain spending level and do an analysis of how discounts impact their spending habits or perform an analysis on the seasonal spending habits.
This unit will show you how to combine both the database tool and the spreadsheet tool to produce useful analysis for your organization. You will conclude this unit with an examination of the key features and functions associated with the integration of database-tool information and spreadsheet documents, and how these features may aid decision-making in a business environment.
Unit 9 Time Advisory
This unit should take approximately 2.5 hours to complete.
☐ Subunits 9.1–9.2: 0.25 hours
☐ Subunit 9.3: 1 hour
☐ Subunit 9.4: 0.5 hours
☐ Subunit 9.5: 0.75 hours
Unit9 Learning Outcomes
Upon successful completion of this unit, the student will be able to: - identify the tool functions that allow for integration and content sharing across the database and spreadsheet tools; - describe how to design a database report using integrated spreadsheet data, as well as perform this task; - describe how to design a spreadsheet document using integrated data from retrieved database tables, as well as perform this task; - explain how database content can be integrated into a spreadsheet document. - identify key features for sharing content; and - explain how spreadsheet tool content can be integrated into a database table or report.
9.1 The Integration of Database Information and the Spreadsheet Tool Take approximately 15 minutes to study the information provided in subunits 9.1 and 9.2. The similarities between data contained in a database and a spreadsheet are great. These similarities often lead businesses to the conclusion that a spreadsheet can address most business needs. Although this is true in many cases, the best practice for handling transactional data is to use a database. This transactional data can be used to generate reports by using the database tool or by extracting the data into another form (spreadsheet) for reporting. One of the chief benefits of this practice is to allow other users to have access to the data without relinquishing control. The extracted data can be configured to update automatically from the database if needed or just used as a snap shot in time data set.
9.2 Why Integration Is Important for Business Decisions Integration of data from a database to a spreadsheet, or the reverse, is an important feature of data analysis that can assist the organization in making or evaluating business decisions. An organization that wishes to evaluate recent trends in the business transactions can extract data from a database to a spreadsheet and manipulate the data as part of the evaluation process. This extracted data can give the business analyst an opportunity to evaluate the effect that certain conditions have on the transactions and the resulting impacts. Organizations can also create data in a spreadsheet tool and integrate this data into the database to evaluate the potential results or to update the database with transactions that were executed outside of the database environment. Having the ability to move data between these two tools provides a business analyst with the capability and flexibility to evaluate data in multiple operational modes.
9.3 The Key Features for Sharing Content across the Database and Spreadsheet Tools 9.3.1 The Database Tool Take approximately 30 minutes to review the following information and practice the steps. The question many database users ask is “how do I convert database information into a spreadsheet?” To complete this process, you export the data from a database structure to a spreadsheet. One reason that many users want to do this function is to have the ability to distribute the data to someone else who may not have access to the database tool. As a system owner or steward of the data, you may also feel more comfortable sending someone a simple spreadsheet containing exactly the data that person requires rather than to send the person the whole database and then say, “run the individual query.”
In any case, it is very easy to export data from a database to a spreadsheet. You can either export the contents of a table, or the result of a query. Here is how you go about doing this.
Click on the table or query that contains the data you want to
From the “File” menu, click “Save As/Export.”
Select “To an External File or Database.”
Choose a location for your spreadsheet (or double click on an existing one).
You now have a spreadsheet containing the data from your query that you can email to anyone you like. Sub-subunit 9.5.1 will provide more detailed steps to achieve this goal.
9.3.2 The Spreadsheet Tool Take approximately 30 minutes to review the following information and practice the steps. Many business analysts are more comfortable using a spreadsheet tool to create and manipulate data and will use this tool to prepare data that will be later loaded into a database. Users who select this option should be aware of a few steps that are required to ensure that the process is executed smoothly. Here is how you go about doing this.
Create a new, empty database.
Prepare data for import.
Import the data.
Specify where you want to store the data.
You now have a database file containing the data from your spreadsheet file. Sub-subunit 9.4.1 will provide more detailed steps to achieve this goal.
9.4 Integrating Spreadsheet Tool Content into a Database Table or Report 9.4.1 How to Populate a Database Table from Spreadsheet Data - Web Media: YouTube: LearnAllYouCan’s “How to Import Excel Spreadsheets to Access Databases” Link: YouTube: LearnAllYouCan’s “How to Import Excel Spreadsheets to Access Databases” (YouTube)
9.4.2 How to Create a Database Report with Integrated Spreadsheet Data - Web Media: YouTube: LearnAllYouCan’s “How to Generate Reports in Access Databases” Link: YouTube: LearnAllYouCan’s “How to Generate Reports in Access Databases” (YouTube)
9.5 Integrating Database Content into a Spreadsheet Document 9.5.1 How to Populate a Spreadsheet from a Database Table - Web Media: YouTube: Malfunction9’s “Excel Advanced Tutorial 10 - Importing Data from MS Access” Link: YouTube: Malfunction9’s “Excel Advanced Tutorial 10 - Importing Data from MS Access” (YouTube)
9.5.2 How to Create a Spreadsheet Business Form with Integrated Database Data - Activity: The Saylor Foundation’s “Unit 9 Example” Instructions: Once data is in an excel spreadsheet form, it can be manipulated into any of the standard functions of a spreadsheet. To demonstrate this feature, we are going to create a spreadsheet business form using the integrated database data. We will use the same process as outline in sub-subunit 6.5.1.
To demonstrate this feature, perform the following steps. Enter the data in [this table](http://www.saylor.org/site/wp-content/uploads/2013/01/Unit9Example.png) (PNG) into a database. Export the table by using the process described in the sub-subunit 9.5.1 video. Select the data and create a pie chart. Save the exported data and pie chart file as “Unit9Example.” Using the information in the “Unit9Example” file, create a balance sheet for the members of an investment club called “Top Notch Investments.” Completing this activity should take approximately 30 minutes.