Course Content Summary for 2009

Below is a list of assignment topics and tasks drawn from the five assignments that constituted the core work in the Information Management course for the 2008 academic year. This list may be helpful as you decide whether this course is right for you and - if you do enroll - to help you plan your work during the term.

Assignment 1: Tools and Techniques for Managing Excel Lists

Excel Data Formats
Data Manipulation
Error Trapping in Excel 2007
New Data Visualization Options in Excel 2007
Excel's Filters (Autofilter, Advanced Filter)
Database Functions
Dynamic Report Creation with Controls, Dfunctions, a Graph, & Text Concatenation

Variable Data & Flexible Lookup Formulas
Use INDEX and MATCH for Lookups
Find Unmatched Records from Different Data Sources
Find Duplicate Entries in Indexed Records
Find Duplicate Entries in Un-Indexed Records
Excel Data Validation
Excel 2007's New Conditional Functions

Optional appendices include:
Notes on Transitioning from Excel 2003 Lists to Excel 2007 Tables
Notes on Form Controls vs. ActiveX Controls
How to Construct a Dynamic Chart Title for the L1 Task 8 Task
Vector/Matrix Manipulation in a Finance Context

Assignment 2: Excel's Pivot Table and Data Analysis Tools

Create Four Simple Pivot Tables from Excel Data
Use an Excel External Data Source and an Internal Copy to Create Two Pivot Tables
Analyze External MS Access Data with a Pivot Table
Explore the New Pivot Table "Compact Layout"
Pivot Table Calculated Fields and Items
Practice with Pivot Table Filter Options
Create a Pivot Table Interface with Simple Recorded Macros

Apply a Conditional Format to Each of Three Pivot Tables
Use a Custom List in a Pivot Table Sort
Create a Genuine Pivot Chart
Create a Revenue by Line of Business Report as a Pivot Table and Excel Chart
Manage the View of Structured Data with a Chart
Automatically Include New Data in an Existing Chart
Coordinate Chart Scales Across Charts
Create Sparklines in Excel

Optional appendices include:
Excel 2007 Macro Security and How to Turn on the Developer Tab
How to Determine the Source Range Name for a Pivot Table

Assignment 3: Excel Automation

Record a Relative Excel Macro
Copy an Excel Function
Devise Your Own Excel Function
Use a Looping Subroutine and a UserForm
Create an Automated Reporting System

Retrieve Web Data Into Excel (direct, Web Query)
Run a Microsoft-supplied Web Query
Create a System Based on a Dynamic Web Query
Create a Real-Time Exchange Rate Reporting System

Optional appendices include:
Excel 2007 Macro Security & How to Turn on the Developer Tab
Notes on Building a User-Defined Function (UDF) in Excel
Common VBA Code Lines
How to Publish Excel Content to the Web (using your Duke account)
How to Save a Web Page as a File on Your PC

Assignment 4: Introduction to MS Access

Review a Sample Access Database (Quiz 4)
Relational Database Design Theory
Introduction to Access Tables
Access to Excel Links
Introduction to and Pratice with Access Queries

Data Modeling
Advanced Queries

Assignment 5: Intermediate & Advanced Access Topics

Action, Union, and Crosstab Queries
Find Duplicates and Find Unmatched Queries
The Crosstab Query
Automated Reporting
How to Create an Access Switchboard (using Access macro language)
How to Create a Tabbed Form with Calculated Fields

A User-Friendly Access Form-Query Combination (using Access VBA)
Access Decision-Making Queries (IIF and SWITCH)
Access Pivot Tables and Charts
Custom Access Reporting

Optional appendices include:
Notes on Null Values and Zero Length Strings
How to Build a Simple Access Report (report components)

Last updated 12/18/2008