Once you have an external data connection, you can use the connection properties to point to another database table or query. You can even write your own SQL statements. SQL (Structured Query Language) is the language that relational database systems (such as Microsoft Access) use to perform various tasks. You can pass instructions right from Excel by using SQL statements. This can give you more control over the data you pull into your Excel model.
Although a detailed discussion of SQL isn't possible, let's step a bit outside our comfort zone and edit our external data connection using a simple SQL statement to pull in a different set of data.
- Go to the Data tab on the Ribbon and select Connections. This activates the Workbook Connections dialog box shown here.
Choose the Properties button for the connection you want to change. - Choose the connection you want to edit and then click the Properties button.
- The Connection Properties dialog box opens. Here, you can click the Definition tab.
On the Definitions tab, select the SQL command type and enter your SQL statement. - Change the Command Type property to SQL and then enter your SQL statement. In this case, you can enter:
SELECT * FROM [Sales_By_Employee>WHERE ([Market> = 'Tulsa')This statement tells Excel to pull in all records from the Sales_By_Employee table where the Market equals Tulsa. - Click OK to confirm your changes and close the Connection Properties dialog box. Excel immediately triggers a refresh of your external connection, bringing in your new data.
