Exploring Advanced Microsoft Office Access and Excel 2003

DR. PHILIP A. KONEMAN Dr. Koneman has over ten years of experience in computers and education. He is an Associate Professor of Computer Information Systems and Advanced Computer Applications at Colorado Christian University. His teaching responsibilities include Introduction to Computer Applications, where students learn the primary applications in Microsoft Office 2000 Premium Edition.   Dr. Koneman has a broad professional portfolio that includes a variety of activities outside the university. He assisted Nivo International in defining the MOUS Certification objectives for Office 2000. In addition to being an author on numerous Select modules; he is also the Series Consulting Editor for the entire Select series. Dr. Koneman is President of Instructional Design Consultants, Inc., a company that develops educational multimedia and provides consulting for curriculum design and educational measurement.   Dr. Koneman received his Ph.D. from the University of Colorado at Denver, School of Education, Division of Instructional Technology. His research interests include computer-based learning, educational testing and measurement, and ethical issues in computers and technology. He is a certified Microsoft Office User Specialist for Office 2000.  

Chapter 1 - Using Excel to Model Data and Make Decisions

Using Excel for What-If Analysis

Using Function to Calculate Consulting Revenue and Expenses

Analyzing Utilization Data

Chapter 2 - Using Queries and SQL to Manage and Analyze Data

Access Tables, Relationships, and Query Tables

Using Structured Query Language (SQL)

Linking an Excel Workbook and Creating a Table with a Make-Table Query

Importing Records from Excel and Creating an Archive Table

Using Access Queries to Analyze Consulting Profitability

Summarize Data on Multiple Fields Using Crosstab Queries

Chapter 3 - XML in Microsoft Office 2003

XML and Markup Languages

Using XML

XML and Microsoft Excel 2003

XML and Microsoft Access 2003

Creating a Query to Export Data as XML

Put It All Together: Using XML to Display Consulting Revenue Summaries on the Web

Chapter 4 - Learning to Program with Visual Basic for Applications (VBA)

Visual Basic for Applications

VBA in Excel and Access

Modules and Procedures

Display Messages Using VBA

Variables and Constants

User Input

Perform Calculations with Variables and Constants and Format Results

Chapter 5 - Using Programming Structures in Visual Basic for Applications (VBA)

Programming Structures

Repetition Structures

Anticipate and Trap Errors

Chapter 6 - Customizing Excel with Visual Basic for Applications (VBA)

Develop Custom Applications in Excel Using a Software Development Cycle

Objects and the Excel Object Model

Visual Basic Forms

Plan and Design a Data Entry Form

Use Functions

Initialize, Display and Close Forms

Chapter 7 - Customizing Access Databases Using Visual Basic for Applications (VBA)

Create Custom Forms in Access

Access Modules

Use the Active Data Object (ADO) and Recordset Objects

Perform Calculations with Procedures

Use Procedures to Calculate Federal and State Payroll Withholding Taxes

Create Additional Procedures to Add Functionality to the Application

Export Data as XML

Chapter 8 - Summarizing and Reporting Data in Excel and Access

Excel Lists, PivotTable Reports, and PivotChart Reports

Group Data in Excel PivotTable and PivotChart Reports

Summarize and Report Data in Microsoft Access

Chapter 9 - Use Smart Tags in Office 2003

Use HTML Data in Excel and Access

Publish Excel Worksheets and Workbooks to the Web

Data Access Pages

Appendix -- Data Normalization

Database Design

Normalization and the Normal Forms