Building and using a relational database in Excel (with a little help from MS Query)

Five management queries

Using Excel's Get External Data command, one can use MS Query to create a query against the relational database. The result of this query can be a new table in an Excel spreadsheet, or a PivotTable, which in itself is a powerful tool for data analysis!  MS Query provides the necessary infrastructure to link the tables using the specified keys.  Before discussing how these queries are built, here are the results of three queries:

A list of customers and their accounts

A list of account types and the average balance in each type

A cross-table of the number of accounts by customer city and account type

A parameterized query to list of customers in a zip code that is specified dynamically

A parameterized query to list of customers in a zip code selected from a drop down list of valid zip codes