Tutorial: Query Microsoft Access Tables using Excel for Mac
Part 4 - Get Your Data - Excel 2011 (The way it ought to be in 2016, but isn't)

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

Now that you have set up your data sources you can use them in Excel. There are big differences between how this is done in Excel 2011 and Excel 2016. Excel 2011 has real Microsoft Query. Excel 2016 has nothing like it. This page is for Excel 2011. If you are using Excel 2016, click here.

Using Microsoft Query

Microsoft Query is an application that comes with Microsoft Office 2011. MS Query lets you create Structured Query Language (SQL) queries using a Graphical User Interface (GUI) or by typing queries in a SQL Query text editor. The syntax for SQL queries in MS Query is slightly different from the syntax in Microsoft Access.

Example #1 – Import an entire table from Access into Excel.

1. Open Excel to a blank workbook
2. Switch to Normal view
3. From the DATA tab of the Ribbon click the Database button
4. The ODBC Administrator will open.

You must click on a data source from the list then click OK. Even if there is only one data source, you must first click on it to select it in order to make a connection.
Microsoft Query will open (see Figure 1). There is a main window divided into 3 panels. Also, there is a window that lists the tables in the Access database.

1.    Select a table name and click the Add Table button or double-click a table name and it will appear in the upper part of the panel.
2.    Change the FIELD selector to the table name with the asterisk * to select the entire table.
3.    Click TEST to display the records in the bottom panel.
4.    Click the RETURN DATA button to bring the table information into Excel. Excel will offer a variety of options that control how Excel will treat the data once the import has been completed.

Figure 1

Screen shot of Microsoft Query
Microsoft Query interface tips

In Microsoft Query columns and fields can be dragged in the interface to change their order.
Click FIELD pop-up menus to display and choose filed names fields.
Click SORT pop-up menus to add sort criteria for the chosen field.
Type CRITERIA to filter data.
Right-click a Table to delete it from your query.
Click the TEST button to run your query and see the result set in Microsoft Query.

Example #2 – Filter Records

The middle panel of MS Query panel is controls for which fields will be selected and what criteria will be used for the selections. This example shows Query View how to filter to find records that contain the word “Brochure” from the source-Name table of tblSource. See Figure 2.

Figure 2
Query View of
      Microsoft Query

Click the SQL View button to display the query in the text editor of MS Query. Switch between Query View and SQL View to discover syntax for SQL queries. Query View is able to display only simple queries. Complex SQL queries can be made only in SQL View. Here is the syntax for query Example 2. Pay special attention to the use of parentheses:
SELECT tblSource.source_ID, tblSource.source_name FROM tblSource WHERE (tblSource.source_name = 'Brochure')

Example #3 – Multiple Table Query

By joining two tables together, you can limit the result set that is returned to eliminate duplicate records.

To join two tables, first put two tables into the query. Then, drag a field that both tables have in common from one table into the other. MSQuery may prompt you for the field names if it can not determine matched fields automatically. See Figure 3

Figure 3

Joining two tables together.

A line joining the two tables will appear. You can double-click the line to edit the join.

You can perform any SELECT query that Microsoft Access supports. The
query shown in Figure 2 lists the names of everyone who registered for any workshop and found the workshop information from our brochure. Here is the syntax for the query in Example 3. Again, note use of single quotation marks and parentheses:

SELECT tblRegistration.reg_name_first, tblRegistration.reg_name_last, tblSource.source_name FROM tblRegistration, tblSource WHERE (tblSource.source_name = 'Brochure') AND (tblSource.source_ID=tblRegistration.reg_source)

Be advised that Microsoft Query is a farily simple tool. It is possible to make queries that produce very large number of records in the result set or a "runaway query" that will never end. You will have to force quit Microsoft Query to interrupt a query that is taking too long. If you wish to make a request for a more robust version of Microsoft Query, use the Help menu in Microsoft Query and choose the Send Feedback option.

Example #4 – Count number of records

This query has a result set but can not be displayed in Query View, so you must type it in SQL View.

SELECT count(Reg_Event) FROM tblRegistration

Example #5 – Match a text string

There is a SQL operator named LIKE that will search through the text within a field and return records that match the text string. This example shows how to search for the word final in a field called tblSoftware.Title.

To use the LIKE command, in the Criteria field type the word like followed by a space then surround the search characters in single quotes and percent signs as shown. Notice the use of single quotation marks and percent signs. Query View is shown in Figure 5:

SELECT tblSoftware.Title FROM tblSoftware WHERE (tblSoftware.Title like '%quicktime%')

Figure 5

                    the Like operator

Example #6 – Return only unique records in the result set

Sometimes a column will have repetitive (identical or duplicate) records within it, like the example above.  To return a set of records that contains only unique rows, add DISTINCT to the SQL command. You will need to switch to SQL view in order to do this. Example adding DISTINCT to your query as shown the example #5, then click the TEST button. You can’t use DISTINCT in Query View.

SELECT distinct tblSoftware.Title FROM tblSoftware WHERE (tblSoftware.Title like '%Quicktime%')

Example #7 – Group query results

The GROUP operator works only in SQL View. Example 7 returns records from the name table grouped by name

SELECT tblRegistration.name FROM tblRegistration group by name

Example #8 – Make a PivotTable in Excel using data from an Access database

On a worksheet in Excel, from the DATA tab of the Ribbon, in the Analysis group, click the PivotTable button’s triangle. From the pop-up menu choose Create Manual PivotTable. See Figure 8a

Figure 8a
Use the
                    Ribbon to make a manual PivotTable

The Create a PivotTable dialog opens. In the Choose the data you want to analyze section, choose Use an External Data source. Then click the Get Data button to open the ODBC manager and Microsoft Query. See Figure 8b.

Figure 8b
Linking a PivotTable to a DSN


Jim Gordon's MVP Home