Integrating Excel and Access

Using Database Queries

Database Queries are valuable when you need more control over the data that is returned. Here is a quick example scenario where you can use a Database Query; this example uses the Northwind Database that comes with Access. Let's assume that your job is to review orders where the freight cost is over $100. There is a Query already designed in the Northwind Database called Orders Qry where the freight column is defined. You could bring in the entire table and search for records where the freight is over $100, but that would be time-consuming and error prone. It's simpler to make the computer do the work.

Choose New Database Query from the Import External Data submenu of the Data menu to get the screen shown in Figure 2-4. Since we want to use the Northwind Database, select MS Access Database from this dialog box and press OK, making sure that the box at the bottom of Figure 2-4 is checked to have the Query Wizard write the queries. The Northwind Database is in the Microsoft Office Samples Folder, as shown in Figure 2-5. After you select the Northwind Database, you get a list of all the tables and queries available in the database. For this example, you want to select the query called Orders Qry. You can expand a table or query to see all of the available fields, enabling you to select only the fields that you want. For this example, we want all of the fields, so click once on the name of the query and press the > button to place all of the fields in the query ...