Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

14.6 Data Analysis and Integration

Workplace Software and Skills14.6 Data Analysis and Integration

Table of contents
  1. Preface
  2. 1 Technology in Everyday Life and Business
    1. Chapter Scenario
    2. 1.1 Computing from Inception to Today
    3. 1.2 Computer Hardware and Networks
    4. 1.3 The Internet, Cloud Computing, and the Internet of Things
    5. 1.4 Safety, Security, Privacy, and the Ethical Use of Technology
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  3. 2 Essentials of Software Applications for Business
    1. Chapter Scenario
    2. 2.1 Software Basics
    3. 2.2 Files and Folders
    4. 2.3 Communication and Calendar Applications
    5. 2.4 Essentials of Microsoft 365
    6. 2.5 Essentials of Google Workspace
    7. 2.6 Collaboration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  4. 3 Creating and Working in Documents
    1. Chapter Scenario
    2. 3.1 Navigating Microsoft Word
    3. 3.2 Formatting Document Layout in Microsoft Word
    4. 3.3 Formatting Document Content in Microsoft Word
    5. 3.4 Collaborative Editing and Reviewing in Microsoft Word
    6. 3.5 Document Design
    7. 3.6 Navigating Google Docs
    8. 3.7 Formatting Layout and Content in Google Docs
    9. 3.8 Collaborative Editing and Reviewing in Google Docs
    10. 3.9 Versions and Version History
    11. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  5. 4 Document Preparation
    1. Chapter Scenario
    2. 4.1 Microsoft Word: Advanced Formatting Features
    3. 4.2 Working with Graphics and Text Tools in Microsoft Word
    4. 4.3 Managing Long Documents in Microsoft Word
    5. 4.4 Google Docs: Enhanced Formatting Features
    6. 4.5 Working with Graphics and Text Tools in Google Docs
    7. 4.6 Managing Long Documents in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  6. 5 Advanced Document Preparation
    1. Chapter Scenario
    2. 5.1 Creating Different Document Types in Microsoft Word
    3. 5.2 Mail Merge in Microsoft Word
    4. 5.3 Creating Forms in Microsoft Word
    5. 5.4 Creating Different Document Types in Google Docs
    6. 5.5 Creating Forms in Google Docs
    7. 5.6 Advanced Collaboration in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  7. 6 Preparing Presentations
    1. Chapter Scenario
    2. 6.1 Presentation and Design Essentials
    3. 6.2 Designing a Presentation in Microsoft PowerPoint
    4. 6.3 Formatting Microsoft PowerPoint Slides: Layout and Design Principles
    5. 6.4 Adding Visuals and Features to Microsoft PowerPoint Slides
    6. 6.5 Designing a Presentation in Google Slides
    7. 6.6 Creating Google Slides: Layout and Text
    8. 6.7 Adding Visuals and Features to Google Slides
    9. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  8. 7 Advanced Presentation Skills
    1. Chapter Scenario
    2. 7.1 Effective Presentation Skills
    3. 7.2 Finalizing a Slide Collection
    4. 7.3 Preparing a Microsoft PowerPoint Collection for Presentation
    5. 7.4 Preparing a Google Slides Collection for Presentation
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  9. 8 Content Management Systems and Social Media in Business
    1. Chapter Scenario
    2. 8.1 What Are Content Management Systems?
    3. 8.2 Common Content Management Systems
    4. 8.3 Creating Content with a Content Management System
    5. 8.4 Search Engine Optimization
    6. 8.5 Social Media in Business
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  10. 9 Working with Spreadsheets
    1. Chapter Scenario
    2. 9.1 Microsoft Excel Basics
    3. 9.2 Text and Numbers in Microsoft Excel
    4. 9.3 Calculations and Basic Formulas in Microsoft Excel
    5. 9.4 Formatting and Templates in Microsoft Excel
    6. 9.5 Google Sheets Basics
    7. 9.6 Text and Numbers in Google Sheets
    8. 9.7 Calculations and Basic Formulas in Google Sheets
    9. 9.8 Formatting and Templates in Google Sheets
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  11. 10 Advanced Excel Formulas, Functions, and Techniques
    1. Chapter Scenario
    2. 10.1 Data Tables and Ranges
    3. 10.2 More About Formulas
    4. 10.3 Using Arithmetic, Statistical, and Logical Functions
    5. 10.4 PivotTables
    6. 10.5 Auditing Formulas and Fixing Errors
    7. 10.6 Advanced Formatting Techniques
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  12. 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
    1. Chapter Scenario
    2. 11.1 Understanding Data, Data Validation, and Data Tables
    3. 11.2 Statistical Functions
    4. 11.3 What-If Analysis
    5. 11.4 PivotTables/Charts
    6. 11.5 Data Analysis Charts
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  13. 12 Using Excel in Accounting and Financial Reporting
    1. Chapter Scenario
    2. 12.1 Basic Accounting
    3. 12.2 Financial Functions in Microsoft Excel
    4. 12.3 Integrating Microsoft Excel and Accounting Programs
    5. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  14. 13 Understanding and Using Databases
    1. Chapter Scenario
    2. 13.1 What Is a Database?
    3. 13.2 Microsoft Access: Main Features and Navigation
    4. 13.3 Querying a Database
    5. 13.4 Maintaining Records in a Database
    6. 13.5 Creating Reports in Microsoft Access
    7. 13.6 Creating Forms in Microsoft Access
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  15. 14 Advanced Database Use
    1. Chapter Scenario
    2. 14.1 Advanced Queries in Microsoft Access
    3. 14.2 Multiple Table Forms
    4. 14.3 Customizing Forms
    5. 14.4 Customizing Reports
    6. 14.5 Using Macros
    7. 14.6 Data Analysis and Integration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  16. 15 Integrating Applications
    1. Chapter Scenario
    2. 15.1 Microsoft 365: Collaboration and Integration
    3. 15.2 Microsoft Word: Integration with Microsoft Excel and Microsoft Access
    4. 15.3 Microsoft Word and Microsoft PowerPoint Integration
    5. 15.4 Microsoft Excel and Microsoft PowerPoint Integration
    6. 15.5 Microsoft Excel and Microsoft Access Integration
    7. 15.6 Integrating Data from Other Programs into Google Workspace
    8. 15.7 New Developments: The Role of Artificial Intelligence
    9. 15.8 Mastering Workplace Software Skills: A Project
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
  17. Index

Learning Objectives

By the end of this section, you will be able to:

  • Integrate data from outside sources
  • Export data from Access to other applications
  • Build a form to serve as a dashboard that is updated automatically

Now that you are comfortable with building and maintaining databases, it is time to start talking about integration. Suppose you have one database that controls all accounting functions and another that simply focuses on operations. Integration allows you to share information between these two databases to provide comprehensive information about the company.

The simplest way to bring data from one Access database into another is by copying and pasting; however, this method does not support the updating of information, but for better control and flexibility, you may want to use importing and linking instead. When you import data from one database into another, Access creates a copy of the data in the destination database without altering the source. During the import operation, you can choose the objects you want to copy, control how tables and queries are imported, and specify whether relationships between tables should be imported. Relational databases have many layers and details baked into the cake. Making sure the details are captured is important.

You may want to import data to create a table or tables that are similar to tables that already exist in another database. You may want to copy the entire table or you may just need to import the table definitions so you can avoid the need to manually design each table. When you import only the table definition, the fields and field properties are copied to the destination database, but not the data in the table. The result is an empty table, which you can populate with your data.

If your goal is to add records from one database to an existing table in another database, you should consider importing the records to a new table and then creating an append query. You cannot append records to an existing table during an import operation.

Integrating Data from Outside Sources

Data stored outside the current database is called external data. External data may be data that you store in another Access database, or it may be data that you store in a multitude of other file formats. The first step in bringing outside data from Excel or other applications is to prepare the external data. An administrator may need to create an account and provide permissions to ensure that the right people have access to the data and that the data does not end up in the wrong hands. (For example, employees’ confidential information must be kept absolutely confidential.) In an external database, the administrator may also want to create specific tables, views, queries, and so on to limit the results to only what is needed.

Access provides a powerful and effective means of presenting data, even data from external sources. When you access external data, you can either import the data into an Access database or you can link to the data from an Access database. Importing the data, though optimal, is not always possible. If you can’t import external data, you should link to external files because Access maintains information about these linked files that will optimize performance when manipulating the external files.

Importing Data

The first step is to decide what you want to import and how it will join with your existing data. Normally, data is stored in various formats, files, and locations, which makes it hard to get and use it. If you have data in a spreadsheet, a SharePoint list, or some other format, you can import it into an Access database with just a few steps, making it much more easily available in Access. On the External Data tab, there are a number of tools for importing and linking to data in the Import & Link command group (Figure 14.71).

External Data tab is selected and Import & Link command group offers these options: New Data Source, Saved Imports, and Linked Table Manager.
Figure 14.71 Data may often be stored in other programs but can generally be imported into or linked to an Access database. (Used with permission from Microsoft)

Linking to external data is quite different from importing data. Linked data remains in its native format. By establishing a link to the external data, you can build queries, forms, and reports that present the data. Once you have created a link to external data, the link remains permanently established unless you explicitly remove it. The linked table appears in the Navigation Pane just like any other Access table, but with a different icon. In fact, if the data source permits multiuser access, the users of an application can modify the data, just as users of applications written in the data source’s native database format are able to do. The main difference between a linked table and a native table is that you cannot modify a linked table’s structure from within Access.

There are two circumstances in which you should import (rather than linking to) external data:

  1. If you are migrating an existing system into Access
  2. If you want to use external data to run a large volume of queries and reports, and you do not intend to update the data.

In either case, you would choose the import option because it gives you the added performance that native Access data provides.

Steps for Importing

Now you are ready to take the steps to import data from another source. Often, data analytics seems to grow in Excel. More and more, we see far too much data being stored in Excel. Luckily, the following steps can help move the data into Access for you.

Open the database into which you want to import objects. You will only be able to import tables and queries from other Access databases; you can’t import forms, reports, macros, or modules. The location of the Import Wizard will vary slightly depending on your version of Access. On the External Data tab, in the Import & Link command group, click New Data Source (Figure 14.72). Follow the wizard directions to add the imported records.

New Data Source button is selected and opens to these options: From File, From Database, From Online Servies, and From Other Sources.
Figure 14.72 Access can accommodate importing data from a variety of different sources and file types. (Used with permission from Microsoft)

As an example, suppose that once a month your local distributor sends you an updated list of wines to sell at your restaurant. The distributor emails you an Excel file that includes both the updated list of names of wines and their updated prices. The wines listed will not change often, but the prices will reflect supply-and-demand changes in the industry. Because the list is extensive, it will be helpful for you to know how to import the list into Access and then how to make changes as needed.

You will need a table that contains both its own product ID and a corresponding Stock Number—the number the distributor uses to identify each type of bottle. Figure 14.73 shows an example of the Excel sheet a wine distributor might send.

Data file with headers for Stock Number, Region, Wine, Vintage, and Price is visible. Information populates the cells. Green triangles are located at top left of all cells in the Vintage column.
Figure 14.73 Data is often contained in Excel spreadsheets that might contain formulas or links to other spreadsheets. (Used with permission from Microsoft)

In this example, start by adding a new data source from the saved file on your computer. The Import Wizard dialog box will appear, asking several questions to help you import the Excel data (Figure 14.74). Rather than just importing the Excel sheet, you can go ahead and link the sheet, knowing that the distributor will update the Excel sheet continuously.

Button for “Link to the data source by creating a linked table” is selected in response to Specify how and where you want to store the data in the current database.
Figure 14.74 You can import data into the database you have open as a new table currently or you can instead link it to an existing table. (Used with permission from Microsoft)

After the link is made, a permanent object is created in the All Access Objects list. The example shown in Figure 14.75 has the WineList table from Excel linked. It will automatically display any changes that have been made to the Excel file. The Excel table that is now visible in Access is easy to work with and functions much like a table.

A WineList file is selected from Table in All Access Objects. The file open at right displays column headers of Stock Number, Region, Wine, Vintage and Price and corresponding information in cells.
Figure 14.75 The imported data file looks very similar in format to the Excel spreadsheet using the column titles. (Used with permission from Microsoft)

Exporting Data

To export data from Access, first select the table or other database object to export in the Navigation Pane. Click the External Data tab in the ribbon, and then click the button in the Export button group for the file format to which you want to export the object. This will open the Export Wizard for the type of export you want to perform.

The specific steps shown in each wizard will vary slightly, depending on the file type to which you are exporting. In most cases, you must select a name and file location for the exported object. You also often need to set additional parameters for the export routine. You will be prompted to save your export routine, if desired.

The Export Wizard enables you to export data from an Access database to a file format that can be read by Excel. When you export data to Excel, Access creates a copy of the selected data and then stores the copied data in a file that can be opened in Excel. If you copy data from Access to Excel frequently, you can save the details of the export operation for future use and can even schedule the export operation to run automatically at set intervals.

Typically, your department or work group will use both Access and Excel to work with data. You store the data in Access databases, but you use Excel to analyze the data and to distribute the results of your analysis. As another example, you may be a longtime user of Access, but your manager or another colleague prefers to work with data in Excel.

Building a Dashboard with Navigation Forms

Dashboards provide a visual interface for users to interact with when using forms. The Dashboard provides a listing of buttons that direct the user to specific forms or reports. The first thing you see on opening many Access databases is a dashboard, a form populated with navigation forms that will be accessed through clicking on the specific command button. Using these provides the means for the user to navigate the database, opening forms, reports, and so on. The key for the user is a way to navigate. Traditionally, a dashboard is a graphical interface that allows a quick view of visualized data, as shown in Figure 14.76. In Access, however, we can build a dashboard from a form that will let us easily run queries and pull up reports. This section will demonstrate how to upgrade a Main Menu form that has only buttons and labels into a full dashboard of information for your company to use.

A Main Menu window displays white buttons for New Shipment Received, Current Customer History, and New Customer Order in the middle of the screen.
Figure 14.76 A dashboard is a menu of buttons that allows users to access other forms and subforms that you have created. (Used with permission from Microsoft)

Planning Your Dashboard

There are four general subtypes of dashboards:

  1. Strategic dashboard: focused on long-term strategies and high-level metrics
  2. Operational dashboard: shows shorter time frames and operational processes
  3. Analytical dashboard: contains vast amounts of data created by analysts
  4. Tactical dashboard: used by middle management to track performance

In the following example, imagine that you are a mid-level sales manager who wants to track a basic sales summary. A tactical dashboard seems like the best fit for your situation. Business intelligence has evolved into smart solutions that provide effective data management—from extracting, monitoring, analyzing, and deriving actionable insights needed to stay competitive in the market, to powerful visualizations created with a dashboard builder, which enable business users to interact with the specific bits and pieces of information they may need at any time. In this simple example, you want to record and track total orders and number of customers. Adding these elements to your Main Menu is a good place to start.

Building Your Dashboard

It is often helpful to first make a listing of the forms that will need to be regularly accessed by users. The forms could vary by department but there might be a group of forms that are routinely used company-wide. At WorldCorp, key performance indicators (KPIs) are critical to monitor for the success of their strategies. A dashboard that contains navigation options, as well as KPIs, can help manage the business. Figure 14.77 shows an example of this.

Main Menu window displays buttons for New Shipment Received, Current Customer History, and New Customer Order, a Sales Summary with Orders and # of Customers, and Bar graph with Date and Sales.
Figure 14.77 Dashboards can include not only navigation buttons but also summary information for tracking purposes. (Used with permission from Microsoft)

DSum and DCount expressions can add both a sales total and a customer count to your dashboard. These have been added to text boxes that were unbound to the form. Each text box was then formatted to fit the theme of the form. Under form design, Insert Modern Form was selected. This is a simple yet powerful way to add context to a sale. Additionally, your team can review the form and see the results as they are updated after each launch of the form. Note that there is an option to Refresh All on the Home tab, which will update the text box and charts.

Real-World Application

Relational Databases in the Cultural World

Many industries use relational databases. They are often hard at work in the background compiling data, gathering customer information, optimizing inventory, or organizing content for decision making. Database usage is more evident in industries such as banking, airlines and transportation, manufacturing, and retail.

In recent years, the usage of relational databases in unexpected industries has surged. One example is in libraries and historical preservation. Libraries and museums have utilized new technologies to make their collections of archives and artifacts available digitally for a much larger audience to enjoy, appreciate, and use for their own research. Wilmington College, a four-year liberal arts college in Ohio that was founded by the Religious Society of Friends (Quakers), has done just that. Through the generosity of a significant peace activist and donor to the institution, the college has extensive collection of artifacts related to Hiroshima and the impacts of nuclear weapons held at its Peace Resource Center (PRC).

The PRC archives is the most extensive collection of interdisciplinary materials related to the legacy of nuclear war in the United States. Each year, the PRC BRMA receives between 400-450 visitors, including researchers, students, and members of the public. The archives holds documents; creative responses to the atomic bombings, such as poetry, plays, and artistic works; historic poster collections; historic photos; slides; scrapbooks; photo albums; 16-mm films; reel-to-reel audio; cassette tapes; and artifacts among others.

—the Barbara Reynolds Memorial Archivese

Through the use of digital archiving and cataloging in an extensive database, this collection is available online to the broader public. The searchable, relational database is linked to several research libraries and contains digital images of the archives and artifacts held in the college’s collection. Many other such databases of items of historical significance exist across the world such as through the Smithsonian and the United States National Archives. Historians and archivists have taken advantage of these technological tools to make history more accessible without compromising the integrity of the artifacts.

Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.