Using Microsoft Office for Mac as a Relational Database

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

Part 7 - Queries - A step by step how-to example

On this page, we will generate a report based on a simple query. This flowchart explains visually how the data will flow:


You're going to need some data. If have not yet already done so, make a folder called DatabaseExample in your Documents folder. Click here and save the linked content ExampleData.xls file into the DatabaseExample folder you made. Exampledata.xls is an Excel workbook that has the data used for the examples in this tutorial. For your own purposes in the future, when you make additional databases, you should make a new DatabaseWhatever folder for each new database.

Now that you have data to work with, you're ready to make a report. It's not required, but I suggest saving reports associated with a data source in the same folder as the data source unless your data source is located on a network share. The next step is to make a folder called Reports in the DatabaseExample folder we made earlier.

In this example you will make a report on a worksheet in an Excel workbook.

1. In Applications > Utilities open ODBC Manager application.
2. A data source can be made available to all users on a Mac (SYSTEM DSN) or just the Mac OS current user (USER DSN). In ODBC Manager, choose either USER DSN or SYSTEM DSN tab depending upon which Mac OS users will have access to this data source. When adding new data source names, do not use the same data source name on USER and SYSTEM tabs. Do not use reserved words as data source names.
3. Click the Add button
4. Choose the Actual Access driver if using an Excel workbook as the data source (or other driver as appropriate), and then click the Finish button to configure the driver.

5. In the Introduction panel of the DSN Configuration wizard click CONTINUE
6. In the Enter Data Source Name panel, type a name and description for the new data source. For this tutorial the name of ExampleData is used for both the name and the description. Then, click the Choose button and navigate in Finder to the saved ExampleData.xls file in the DatabaseExample folder and choose ExampleData.xls. Then click the CONTINUE button.
7. In the Database Information panel click the CONTINUE button.
8. In the Conclusion panel click the DONE button. If you were successful, you will see your new data source in the ODBC Manager dialog.
9. Quit ODBC manager. Your data source is now ready to be used and will remain available for repeated use until you delete it.
10. Open a new, blank Excel workbook.
11. Use File > Save As and save the workbook in the Reports folder you made. A name suggestion is EmployeeReport.xlsx.
12. Excel 2016: On the Data menu choose Get External Data > From Database
      Excel 2011; On the Data tab of the Ribbon choose New Database Query > From Database
      The Data Source Chooser dialog opens:

Select Data Source

13. Select a data source on either the DSN or System tab.
14. Click the OK button.
Microsoft Query will open.

STEPS 15 through 19 are different for Excel 2016 and Office 2011. Instructions for 2011 follow instructions for 2016, below.

The Microsoft Query application window is divided into 3 panels.
15. Click the disclosure triangle in the left panel to display the names of the database tables.
16. Click a table name to generate a Select All (Select * From TableName) query in the Query editing panel.
17. Click the Run button to see the first few records of your query. The full result set does not display when you click the Run button.
18. Click the Return Data button to bring the result set into Excel and close Microsoft Query.
The Microsoft Query window divided into 3 panels. There is a smaller window that lists the tables in ExampleData.xls. We're going to select all the records in the Employees table for our report.
15. In the Tables window, Select EMPLOYEES. In MS Query 2016 a Select * from Employees query will automatically display. In MS 2011, click the Add Table button or double-click on EMPLOYEES. EMPLOYEES will appear in the tables panel in the Microsoft Query window.
16. Click on the Field: pop-up to select columns for your report. Choosing the asterisk * selects the entire table. Columns can be dragged within the interface to change the order of the fields.
17. Click Test! to display the query result records in the bottom (result set) panel.
18. [Optional] Click the SQL View button to see the syntax of your SQL query. In this example the query is SELECT Employees.* FROM Employees
19. Click the Return Data button (lower right corner of Microsoft Query window) to bring the result set into Excel.

Microsoft Query

Excel will offer a variety of options that let you control how where Excel puts the data once the import has been completed. For this example, choose to return the data to the active worksheet in cell A1 by clicking the OK button as shown here:

Data location

Note that you can return data from Microsoft Query to an existing worksheet, a new worksheet, or a PivotTable report.
The Properties button of the Returning External Data dialog is discussed in Part 9.

You have now successfully created a report. Use Command-S or click the Save button to save your finished report as an Excel workbook.

Data from Microsoft query is in a special range called a query table, which by default has Table formatting turned on so you can format it using the Table tab of the Ribbon.
You have completed the basic steps for generating a report. The basic procedure is summarized here:
  1. In an Excel workbook click the Data tab of the Ribbon and choose a Database
  2. Add tables and criteria to your query
  3. Return Data
When Microsoft Query is open you may be able to see the your worksheet or other things in the background. In Excel 2011, if you move the cursor from Microsoft Query you will see a spinning rainbow. This is normal. The rainbow is to advise you to not modify the Excel workbook while you have Microsoft Query open.

You can make an unlimited number of reports based on the same database. You only have to maintain one Data Source Name for a given database.

Reports that depend upon data can be kept current by clicking the Refresh button on the Data tab of the Ribbon.

Use ODBC to add, remove and configure data sources in all versions of Mac OS.

Users of Lion and Mountain Lion: When you display the Data Source Chooser dialog (see step 12 above) don't click the Add button to add a new data source. If you do, you will crash Microsoft Query because the configuration routine has not been updated and requires Rosetta, which is not in Lion or Mountain Lion.
Part 1

Part 6 (Previous)

Part 8 (Next)