Using Microsoft Office 2011 as a relational database and querying other databases

by Jim Gordon, Microsoft Mac MVP, August 2012
Co-author Office 2011 for Mac All-in-One For Dummies

Part 1 - Introduction

You can use Microsoft Excel 2011 for Mac as a fully relational database. With Excel 2011 you have the ability to make data tables, data input forms, and reports. You can make Structured Query Language (SQL) queries in the Microsoft Query application or by using  Visual Basic for Applications (VBA). Microsoft Query and VBA are installed by default with Microsoft Office 2011 Home and Student edition, as well as with Home and Business.

Office 2011 supports these basic relational database elements:
Tables: Data arranged in rows and columns, arranged similar to a spreadsheet.
Forms: More exactly, input forms, for entering data into the tables.
Queries: Commands that specify what data to retrieve from the tables using Structured Query Language (SQL).
Reports: Query result sets can displayed on Excel worksheets in query tables, pivottables, and in Word mail merge documents.
This tutorial is a series of web pages designed to help intermediate users of Excel become familiar with Excel 2011's relational database features. The focus of this tutorial is to use an Excel workbook as a data source, but most aspects of the content can be applied to working any data source supported by ODBC drivers that work with Microsoft Office on the Mac.

Some pages in this tutorial have Visual Basic for Applications (VBA) code examples on them. These excerpts from the Office 2011 for Mac All-in-One For Dummies book may be of assistance regarding VBA on the Mac:
Here is the Table of Contents for this tutorial:
Part 1 - Introduction (this page)
Part 2 - Setup (next page in tutorial)
Part 3 - Organization
Part 4 - Tables
Part 5 - Forms
Part 6 - SQL and Queries
Part 7 - Simple step-by-step example
Part 8 - Refreshing a query table
Part 9 - Query table properties
Part 10 - Filtering records with SQL
Part 11 - Joining tables
Part 12 - Count and Group By Example
Part 13 - Matching strings using Like
Part 14 - Return unique records
Part 15 - Group records
Part 16 - Sort records
Part 17 - Making a calculated field
Part 18 - Use a database query to make a pivottable
Part 19 - Making a parameter query
Part 20 - Edit an existing query
Part 21 - Reserved words
Part 22 - Make a query using VBA and add calculated columns to the worksheet

My Excel Page
Part 2 (Next)