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