Understanding

   

Understanding "Query"

The previous chapter compared using the Query Wizard to using the Microsoft Query window. You saw that to choose the Query Wizard over Microsoft Query is to choose a friendlier, less powerful tool over a moderately demanding tool that offers greater functionality.

There's a similar choice involved between Microsoft Query and a database manager. Microsoft Query offers you a few ways to structure a query the selection of fields and records and sort orders, for example but does not provide the rich array of tools you have when you use a database directly.

Still, Microsoft Query is the only means available for bringing data into an Excel worksheet automatically and without programming. And one of the best ways to move data from a database into a worksheet is to use the database's tools to structure a query's data, and to use Microsoft Query to arrange the transfer of that data into the worksheet.

One difficulty in learning about queries is the word query itself. It's used, somewhat casually, to mean anything from the data returned by a query, to a set of instructions that define how to handle a set of data, to the application that helps create the query. It makes things more difficult that queries do more than simply return data from a data source: They can also add or remove data in tables, edit data, and even create new tables.

This book uses the term query to mean a set of instructions. For example, this is a simple query:

 SELECT Tiles.TileID, Tiles.SpaceID, Tiles.Floor, Tiles.SmokeZone FROM Tiles; 

It is written in Structured Query Language SQL for short, and pronounced "sequel." It says to select the fields named TileID, SpaceID, Floor, and SmokeZone from something named Tiles, which could be a table or it could be another query. (You'll frequently find yourself constructing a query based on another query.) Most of the queries that this book discusses are Select queries; that is, queries that get data from a database and present it to another application. Here the application of interest is Excel, but the principles apply no matter what the receiving application might be.

If a Select query changes the data in any way (for instance, if Sex equals 1 show "Male" and if Sex equals 2 show "Female") that change takes place after the query has obtained the data. So, unless otherwise specified, you can assume that a query discussed in this book is a Select query. A Delete query (one that removes records from a table), an Append query (one that adds records), or an Update query (one that modifies a record's values) will be identified by its type.

NOTE

SQL is a standard language. A SQL statement that works in one database management system is very likely to work in the same way when used by a different one. That weaselly very likely is due to the fact that there are variations in SQL. Transact-SQL, for example, differs from SQL in important ways. But they are virtually identical in basic querying syntax.


It's very seldom necessary to write a query using SQL. Most popular applications that use SQL give the user a graphic interface to help design the query. The application then interprets the graphic information to write the query itself in SQL. Figure 5.1 shows an example. The table pane contains the table with its fields, the data pane shows the records and their field values, and the SQL window shows the structured query language statement.

Figure 5.1. This is how Microsoft Query visually represents the structured query language.

graphics/05fig01_alt.jpg

graphics/05fig01.jpg


You can view the SQL of a query that you have built, or that you're building, by clicking Microsoft Query's SQL toolbar button.

TIP

Few applications that help you write SQL, such as Microsoft Query and Access, have a text search-and-replace capability. Suppose that you needed to change the table reference in the sample query shown earlier from Tiles to Tiles2004. You could add a table named Tiles2004 to the table pane and change each field reference in the query from Tiles to Tiles2004. It's often quicker, though, to display the SQL window, copy its text, paste it into another application like Notepad or Word, replace Tiles with Tiles2004, and then copy and paste the result back into the SQL window.


The database management system, whether it's Access or Oracle or SQL Server or some other, sees the SQL statement, interprets it, and returns the data accordingly.



Managing Data with Microsoft Excel
Managing Data with Microsoft Excel
ISBN: 789731002
EAN: N/A
Year: 2003
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net