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

Required
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
Concatenation
Controls
Dynamic Report Creation with Controls, Dfunctions, a Graph, & Text Concatenation

Optional
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

Required
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

Optional
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
The GETPIVOTDATA Function


Assignment 3: Excel Automation

Required
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

Optional
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

Required
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

Optional
Data Modeling
Advanced Queries


Assignment 5: Intermediate & Advanced Access Topics

Required
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

Optional
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