Excel Annoyances

Chapter 6. Exhanging Data Annoyances

Excel usually plays nice with other programs—but when something goes wrong, it goes really wrong. One troublesome area is getting data from external sources, such as databases. Sure, it’s easy to cut-and-paste from Access to Excel, but that’s a sledgehammer approach where you might need a little finesse. For example, what if you only want to find the addresses of customers from Oregon who placed an order in the last month? That’s the sort of thing that’s difficult to do with Excel’s built-in tools, but it’s easy if you use Microsoft Query, an Office utility designed to get data out of databases, text files, and other data sources. You’ll learn how to use Query to solve this and other conundrums, tap into SQL to get data, and also make Excel make nice when exporting data to the Web.

MS QUERY AND DATABASE ANNOYANCES

BRING IN SELECTED DATABASE RECORDS

The Annoyance:

I’m pretty good at using Excel, but I’ve never had to do anything more than copy and paste rows from an Access database table into my worksheets. Now I’m faced with bringing thousands of database records into Excel. How do I get Excel to do the picking and choosing for me?

The Fix:

First off, don’t copy the entire table into Excel and then try to wrestle the data into some sort of shape! You want to narrow down the data to a manageable set, which is exactly what Microsoft Query was designed for. It lets you create and run queries in other database and spreadsheet programs, pull that information ...