| In one way at least, after you've established an outer join using Microsoft Query, you have pushed that application to its limit. You're allowed no more than two tables in Microsoft Query's table pane if you've used an outer join (see Figure 5.12). Figure 5.12. To establish an outer join with more than two tables, you need a more sophisticated query manager. 
Therefore, if you have even a slightly more complicated situation, you need to involve a database management system directly. These applications, such as Access and SQL Server and Oracle, do not place such restrictive limits on designing queries. Building a Query in Access Because Access generally accompanies Excel in the Office software editions, it makes a convenient platform to discuss creating and managing queries in a database. But the concepts covered here extend well beyond Access and are employed in every relational database system. NOTE Later chapters show you how to use Excel to build queries that are executed by the database. This book is titled Managing Data with Excel, after all, not Managing Data with Access. But it's helpful to know how to build the query using the database before building it, one step removed, from the workbook.
Suppose that the data set that this chapter has discussed so far is found in an Access database. To extend the query on a building's doors beyond Microsoft Query's capabilities, you might use Access to build a more complicated query. Then, to get the data into an Excel external data range or pivot table, you use Microsoft Query to treat the Access query exactly as though it were a table. You would begin by opening the database in Access. After the database has been opened, you see the main Access window, as shown in Figure 5.13. Figure 5.13. The placement of the objects (tables, queries, forms, and so on) depends on the version of Access you're using. 
You can see the tables named Doors, DoorLocations, and DoorDetails in the Database window. To build a query using these tables, begin by clicking the Queries button (or tab, in Access 97). Then click New to establish a new query. The New Query window shown in Figure 5.14 appears. Figure 5.14. The wizards are occasionally useful, but you'll build most of your queries in Design view. 
Make sure that Design View is selected in the list box, and click OK. The Show Table box appears: It's similar to the Add Tables box you saw earlier in Figure 4.12 of Chapter 4, "Importing Data: An Overview," which is part of Microsoft Query. The Show Table box remains open until you click the Close button. This enables you to continue adding tables and queries until you're finished. TIP If you have only a few tables and queries in the database, use the Both tab to show both tables and queries. If you have so many that you need to use the scrollbar to find them all, it's easier to select by using first the Tables tab and then the Queries tab.
After you've selected the tables and queries you want for your new query, click Close. Access's Query window appears as shown in Figure 5.15. Figure 5.15. The tables appear in the table pane from left to right, in the order that you select them from the Show Table box. 
There are a few important differences between the Access query window and the Microsoft Query window (compare with, for example, Figure 5.11). The Access Query window has no data pane. To see what your query returns, you need to click the Run button, or click the View button and choose Datasheet View from the list. There's no difference between Run and View for Select queries. With other types of queries Delete, Update or Append queries, for example there's a reason to use the View instead of the Run button. The View button can present a preview of what the query will do, and the Run button actually carries out the action. You can add a field to the Design Grid and choose not to show it in the query's results. Suppose, for example, that you want to sort records on some field, but you don't want to return that field from the query. Put the field in the Design Grid, and choose Ascending or Descending in the grid's Sort row. Lastly, to prevent the query from displaying that field, clear the field's check box in the grid's Show row. Access does not limit you to two tables in a query when you're using an outer join. Figure 5.16 shows how you might structure a query involving the tables named Doors, DoorLocations, and DoorDetails. The query returns all records from Doors that are matched by records in DoorLocations, whether or not they're matched by records in DoorDetails, and uses the DoorLocations table to show a door's location. You would not be able to do this using Microsoft Query alone. Figure 5.16. In Access, the Query window specifies which table a field comes from. 
You can summarize data in the query. For example, you might merely want to know the number of times that a door has been inspected. With the DoorID field in the query grid twice once from the Doors table and once from the DoorDetails table, you would click the Totals button. In the new Totals row, choose Group By for the Doors table and Count for the DoorDetails table. The resulting design is shown in Figure 5.17. When you click the Run button, you get the result shown in Figure 5.18. Figure 5.17. Besides Count, other summary statistics available include average, sum, and standard deviation (shown as StDev). 
Figure 5.18. Note that DoorID 1A0A109 has no records in the DoorDetails table. Therefore this query uses an outer join (see Figure 5.17). 
This list of features isn't even close to exhaustive: There are many ways to tailor a Select query in Access that are not available in Microsoft Query. After you've designed the query, save it by choosing Save from the File menu or just close it. You'll be asked whether you want to save your changes, and you'll have a chance to give the query a name that's more useful than a default name such as Query1, Query2, and so on. Using Microsoft Query to Return the Results of a Database Query After the query has been saved, you get its results into an Excel worksheet or pivot table in the usual way via Microsoft Query, although in this case you work with the query instead of tables. Suppose that you create a query shown in Figure 5.16, and named it Doors Query. You could return its data to Excel using these steps: - With an Excel worksheet active, choose Data, Import External Data. If you've already established the database as a data source, select it in the Choose Data Source window (bear in mind that adding a new query to a data source changes nothing about the data source itself). Otherwise, you'll need to create it by means of the New Data Source item.
- Using the Add Tables window, add Doors Query to Microsoft Query's table pane. If you don't see Doors Query in the Tables list, click the Options button, fill the Views check box (and clear the Tables check box if you want), and click OK (see Figure 5.19).
Figure 5.19. A view is very similar to a Select query. In Access, the two terms are close to synonymous. 
- Drag each field that you want to return into the data pane. The Microsoft Query window now appears as shown in Figure 5.20.
Figure 5.20. When fields from two or more tables have the same name, they are qualified by the name of the table (for example, Doors.DoorID). 
- Click the Return Data button, or choose File, Return Data to Microsoft Excel.
NOTE As noted previously, views are very similar to Select queries. System tables are used by the database to keep track of information about tables, queries, and other structures you've defined in the database itself. Synonymns are the intersection of a table and the user of that table; you're unlikely to need to see them unless you're querying an Oracle data source.
Figure 5.21 shows the data returned from Doors Query to the worksheet. Figure 5.21. Note the empty cells in some records from DoorDetails: The outer join allows the query to return those records. 
|
