Displays the table name for the active query.


Lists the tables and files in the selected data source.


Adds the selected table to the Table pane. The Add Tables dialog box remains open so you can add more tables.


Closes the dialog box. If you have added a table by clicking Add, click Close to return to the query to view the added table. If you have not added a table, click Close to return to the unchanged query.


Displays the Table Options dialog box. Controls which tables are listed in the Add Tables dialog box and the Query Wizard - Choose Columns dialog box, based on the options you select.


If you want the Tables box to display only those tables created by a specific owner, select the name in the Owner box. The label for this dialog box item may change, depending on the data source being used.


Select the database or location where the tables you want are stored. The label for this dialog box item may change, depending on the data source being used.


Contains a list of data sources you can query. You can also set up new data sources for queries.


Use the Query Wizard to step you through building a simple query that returns data to Microsoft Excel or another parent program. Clear the Use the Query Wizard to create/edit queries check box to use Microsoft Query rather than the Query Wizard to build your query.


Click OK to continue building your query or define a new data source. If you have an existing query selected, click Open to modify the query. To use the query Wizard to create or modify a database query, make certain the Use the Query Wizard to create/edit queries check box is selected. The Query Wizard is unavailable for OLAP cube data sources.


Click Cancel to close the dialog box and remain in Microsoft Query. If you started Query from another application, the Cancel command returns you to the application without returning data.


Click Browse to search for additional data sources or queries on your computer or network.


Opens the Data Source Options dialog box, which displays the default locations for data sources. You can also browse for data sources in other locations on your computer or network.


Starts the Microsoft Office Assistant. Displays tips and Help topics that assist you with steps in the wizard.


Removes the selected data source from the list of data sources you can connect to.


Click Best Fit to adjust the column width automatically to the largest value displayed in the selected column on the screen.


Type the number of characters you want the column to display based on the current font. Microsoft Query may actually display fewer characters in a field to compensate for uppercase letters or any letters that are not average size for that font. If you return data to Microsoft Excel or to another parent application, the formatting options you select will not be used in the result set in the parent program.


Select Standard width to apply the default width. The default column width varies, depending on the font used in the Data pane.


Applies a built-in function for a field in which you specify criteria. Select both the type of total you want to calculate on the field specified in the Field box and the criteria you want the totaled values to meet in the result set.


Select the field for which you want to specify criteria.


Select the criterion (operator) that must be true for the field you selected.


Type the value you want Microsoft Query to use when selecting records for the result set, or click Values and select from a list of values in the field to use for the criterion.


Adds the criteria you specify to the query. If Automatic Query is on, only the records that meet the criteria appear in the result set. The Add Criteria dialog box remains open so you can add additional criteria.


Closes the Add Criteria dialog box. Any criterion you specified by clicking Add is retained in the query.


Displays the Select Values dialog box. Select from a list of values in the field to use for the criterion.


Select And if you are specifying additional criteria for a query and both the existing and the new criteria must be true in the result set.


Select Or if you are specifying additional criteria for a query and either the existing or the new criteria can be true in the result set.


Select Include column headings to copy the selected data and the corresponding column headings from the Data pane.


Select Include row numbers to copy the selected data and the corresponding row numbers from the Data pane.


Type a name for your new table, or replace the name of the table you're using as the default table with the name you want for the new table. Options you select in this dialog box will not affect the table you are using as a template.


Type a name for a field that will be in the table. When Microsoft Query displays a result set in the Data pane, a field is represented as a column.


Select the data type for the field you entered in the Field name box. The data type indicates what kind of data can be entered into the field in the new table. Available data types depend on the data source you're using. For example, data types for fields using a dBASE data source include Date, Memo, and Numeric.


Microsoft Query displays the maximum number of characters allowed for the data type you selected, including decimal places for numeric types. You may be able to save disk space and help prevent data-entry errors by specifying the smallest possible number. For example, if postal codes are always 5 characters, specify a length of 5.


Enter a number to specify the number of digits displayed to right of the decimal point in numeric fields. The length of a numeric field, as specified in the Length box, includes decimal places.


Defines the field as a required entry field. For each completed record in the table, this field must have data.


Adds the field you defined to the table. Click Add each time you complete the entire Field description for a field you're adding to the table.

You can't edit a field once you've added it. If you need to make changes to a field, remove it from the Fields box with the Remove button, and then add it again with the changes you want.


Displays the names and field descriptions of all fields in the table.


Removes the field you select in the Fields box from the table and the query.


Creates the table with the fields you've defined. Microsoft Query displays a message confirming that the table was created successfully.


Closes the New Table Definition dialog box without adding a table, and redisplays the Select Table dialog box.


Click Connect to connect to the database. The ODBC driver will prompt you to enter driver-specific information required for the data source, including the name and location of the data source or database file.


Type a name for the data source you will base queries on. This name will be displayed in the box located on the Databases tab of the Choose Data Source dialog box.


Select a data source driver from the list. The drivers available depend on the OLE DB/ODBC driver options you chose during setup.


Select a table from the data source to use as the default table for building queries. If you use the Query Wizard to build your queries, only this default table will appear in the Choose Columns dialog box of the Query Wizard. Choosing a default table is useful if you will be creating multiple queries to the same table in a data source.


Select Save my user ID and password in the data source definition if you want to open the data source without typing your user name and password each time you query. This option may not be available with some data sources.


Include Registry DSNs in list of available databases
Select to include data sources created with versions of Microsoft Query earlier than Query 97 in the list of available data sources. Query displays an asterisk (*) beside each such data source in the Choose Data Source dialog box. Queries created with these data sources can be used only on your system and cannot be shared with other users.


Type one or more paths where you want Microsoft Query to look for data sources, and click Add to add the location of the data source to the Folders to search for data sources box. If you are uncertain of the exact path information, click Browse to find the folder where data source files with a file name extension of *.dsn are located.


Adds the location of the data source to the Folders to search for data sources box.


Removes the selected path of the data source from the Folders to search for data sources box. Clicking Delete does not delete any data source files from the location.


Click Browse to find the folder where data source files are located.


Displays the name of your parameter. To change the name, enter the new text that you want to use to prompt for this parameter.


Displays the SQL data type associated with the column your parameter is applied to. Click the arrow to display other available data types you can apply to this parameter.


Displays the name for the parameter and SQL data type associated with the column your parameter is applied to. To change the parameter name or data type, select the name in the box, and click Edit to display the Edit Parameter dialog box.


Displays the Edit Parameter dialog box, where you can change the name and the data type of the selected parameter.


Closes this dialog box and applies any changes you've made.


The Cancel button closes this dialog box without applying any changes you have made. The Close button closes this dialog box and retains the changes you made.


Cancels the connection to the data source if the connection is not successful within the specified number of seconds. Enter the number of seconds you want to allow for the connection.

By default, Microsoft Query tries to connect to your database for an amount of time predetermined by the ODBC driver for the data source. If you want Microsoft Query to try to connect for an indefinite amount of time, clear the Cancel the connection if not connected within check box. If Microsoft Query can't connect to your database, you can cancel the attempt by pressing ESC.


Limits the number of records Microsoft Query will return to the result set. Select the Limit number of records returned to check box, and then enter in the Records box the maximum number of records you want Query to return.


Maintains the connection to the data source even if you close the query window. Once you exit Microsoft Query, the connection is closed. Selecting this option saves reconnection time if you open multiple queries to the same data source. However, you may not want to use this option if you pay for connection time to your data source or if you establish connections to multiple data sources.


Turns off the ability of Microsoft Query to edit query results. When this option is selected, the Allow Editing command on the Edit menu is unavailable. In some cases, you can't edit the data in a field even when this option is not selected. The types of fields you can't edit include fields in queries that have more than one table, fields in queries whose data source doesn't allow you to make changes, fields in queries that calculate totals, and fields that are locked or disabled by the database itself.


Select to run the query in Microsoft Query before saving the query or returning the data to Microsoft Excel. Clear this check box if you do not want to run the query when you save it or when you return the data to Excel.


Select to include fully qualified names for the tables in the query. Clear this check box when you are using file-based databases and do not want the paths to appear in your SQL statement.


Applies a built-in function for a field in which you specify criteria. Select both the type of total you want to calculate on the field specified in the Field box and the criteria you want the totaled values to meet in the result set.


Select the field for which you want to specify criteria.


Select the column you want to modify in the Data pane.


If you want to use a different name for the field, type the name in the Column heading box. Changing the column heading for the field in the Data pane does not affect the field name in the data source. If you return data to Microsoft Excel or to another parent program, the original field name from the data source will be used.


If you want the column to display a total of the values stored in the field, select the type of total you want displayed.


Select a name or type an expression for the field you want to add to the Data pane. You can use an expression to perform a calculation on field values (for example, Price*Quantity) in a new column.


Type the SQL statement you want to run. If you open an existing SQL file with  [image], Microsoft Query displays the statement in the SQL statement box.


Runs the SQL statement or procedure, and displays the query result set (if data is returned) in the Data pane.


Closes the dialog box without executing the SQL statement or procedure.


Displays the Open Query dialog box. Opens an existing SQL file, and display its contents in the SQL statement box.


Displays the Save As dialog box. You can save a new SQL statement, save changes to an existing statement, or save a copy of an existing statement or procedure under a new name.


Displays the Select Procedure dialog box. Select the procedure you want to execute. Available procedures depend on the data source you're using. Microsoft Query won't execute procedures that have output arguments and won't show a return value from a function. See your database administrator or database documentation for details.


Displays the Choose Data Source dialog box. Select the data source for the table on which you want to run the SQL statement or that contains the procedure you want to execute.


Displays different databases or tables in the same location as the selected data source, depending on the data source type. You can also select a different data source for the table on which you want to run the SQL statement or that contains the procedure you want to execute. Click Data Sources to choose a data source if there is no data source name displayed.


Displays the Edit Parameters dialog box. You can add or change parameters that prompt the data source for specific data to create the result set in the Data pane.


Type the number of the record you want to go to, and then click OK. If you've selected an entire row in the Data pane when you choose Go To, Microsoft Query moves to and selects the entire row for the record you specify.


Hides the selected columns.


Redisplays the selected columns.


Lists all columns in the Data pane alphabetically. You can select one or more columns in the box to hide or redisplay. Visible columns have a check mark next to them; hidden columns do not have a check mark. If columns are hidden when you save a query, they remain hidden when you reopen the query. If you return data to Microsoft Excel or another parent program, hidden columns will be visible in the result set.


Select the table and field you want on the left side of the join line.


Select the operator that determines the type of comparison Microsoft Query performs on the values in the joined fields.


Select the table and field you want on the right side of the join line.


Select the join option that specifies which records you want to retrieve and under what condition (based on the comparison operator). The option you select determines the join type — either an inner join (returns related records from both tables) or an outer join (returns all records from one table and related records from the other table).


Displays statements defining the existing join types in the active query. If you have more than one join, Microsoft Query displays a separate statement for each. You can remove a join by selecting its corresponding statement and then clicking Remove.


Adds the join line between two tables in the Table pane for the join you have specified in the Joins dialog box. The Joins dialog box remains open so you can add more joins, if necessary.


Removes the join statement you've selected in the Joins in query box. Removing the join also removes the join line between the two tables in the Table pane.


If more than nine query windows are open, Microsoft Query displays a More Windows command on the Window menu. Click the window you want, and then click OK.


Close (File menu)

Closes the active query when you no longer need it for the current working session.


Save As (File menu)

Displays the Save As dialog box, which you use to save a new query's design, an existing query's design under a different name, or a query's result set as a table. Save your query by selecting Database Queries in the Save As dialog box. Database queries with the extension.dqy can be run by selecting Run Database Query from the Get External Data command on the Data menu in Microsoft Excel.


Table Definition (File menu)

Displays the existing table definition for the selected table. You can create a new table or create an index on one or more fields in a table. When you create a new table from an existing table's definition, Microsoft Query doesn't add the data in the existing table to the new table. The new table is empty.


Execute SQL (File menu)

Runs a new or existing SQL statement or procedure. Use Execute SQL to run a query that you have created or modified in the SQL dialog box (available when you click SQL on the View menu). To change or replace the SQL statement, use the SQL syntax that Microsoft Query uses or the syntax your data source supports. See the SQL documentation or your database administrator for details.


Exit/Return Data

Quits Microsoft Query and closes any open query windows. If you started Microsoft Query from another program, the Exit command is replaced by a command that you use to return data from Microsoft Query to the original program. The name of the command varies depending on the program from which you started Microsoft Query. If you made any changes to a query's design and haven't saved the query, Microsoft Query prompts you to save your changes. If you click Yes but haven't previously named the query, the Save As dialog box is displayed.


Undo/Redo (Edit menu)

Reverses commands or deletes the last entry you typed. If you click Undo, the Undo command changes to Redo, which reverses the action of the Undo command.


Cut (Edit menu)

Removes the data you've selected. Use Cut to save time and avoid typing errors when you want to move data from one cell in the Criteria pane or Data pane to another cell in the same pane, or between the two panes.


Copy (Edit menu)

Copies the data you've selected. Use Copy to save time and avoid typing errors when you want to copy data from one cell in the Criteria pane or Data pane to another cell in the same pane, or between the two panes. You can also use Copy to copy data to another program. If you're copying data to another program, and you want to include row or column headings with your data, use Copy Special on the Edit menu instead of Copy.


Copy Special (Edit menu)

Copies data from the result set, including row numbers or column headings, to another program. After choosing this command, switch to the program into which you want to paste data. You can then paste the data as values only or as values that you can update when the source data changes. Choose Paste for values only and Paste Link for values that can be updated. Some programs may use a different Paste command to paste values that can be updated. Check the Paste commands in your program to determine which one to use.


Paste (Edit menu)

Inserts the cut or copied value into a cell in the Criteria pane or Data pane.


Delete (Edit menu)

Removes the selected item. You can delete a field list in the Table pane; a column, row, or cell that contains criteria in the Criteria pane; or a field or record in the Data pane.


Zoom Field (View menu)

Displays the Zoom Field dialog box. Use Zoom Field to edit the contents of a field's cell when you need more room to edit or enter data. Click OK to see edited data in the Data pane. You may need to click Allow Editing on the Records menu to edit the field; however, if you have added multiple tables to the query, you cannot edit records. This option is not available with all data sources. See your database administrator or database documentation for details.


Query Properties (View menu)

Sets the Unique values only property and the Group records property for a query.


Font (Format menu)

Select the options you want in the Font dialog box to change the appearance of field names and data (result set) in the Data pane. Font settings affect the entire result set, not individual cells, rows, or columns. If you return data to Microsoft Excel or to another parent program, the formatting options you select will not be used in the result set in the parent program.


Row Height (Format menu)

Changes the height of all rows in the Data pane. Row height changes are saved when you save the query. If you're using the standard (default) row height, Microsoft Query adjusts the row height automatically if you change the font. If you're using a custom row height and you change the font, you have to adjust row height manually to fit the font. If you return data to Microsoft Excel or to another parent program, the formatting options you select will not be used in the result set in the parent program.


Column Width (Format menu)

Changes the width of the selected column in the Data pane. Changing the font size can change the number of characters displayed in the column but doesn't change the column width. Changing the width of a column doesn't affect the field length defined in the table's definition. Column width changes are saved when you save the query. If you return data to Microsoft Excel or to another parent program, the formatting options you select will not be used in the result set in the parent program.


Hide Columns (Format menu)

Temporarily hides the selected column (field) in the Data pane but does not change the query result set. You can use values from a hidden column to set criteria. If columns are hidden when you save a query, they remain hidden when you reopen the query. Choose Show Columns on the Format menu to see a list of columns available in the Data pane. The hidden columns don't have a check mark next to them in the Show Columns dialog box. If you return data to Microsoft Excel or to another parent program, the formatting options you select will not be used in the result set in the parent program.


Show Columns (Format menu)

Displays an alphabetical list of columns (fields) available in the Data pane. Visible columns have a check mark next to them; hidden columns do not have a check mark. If columns are hidden when you save a query, they remain hidden when you reopen the query. If you return data to Microsoft Excel or to another parent program, the formatting options you select will not be used in the result set in the parent program.


Remove Table (Table menu)

Removes the selected table from the Table pane. When you remove a table, you're not deleting the data from the underlying table.


Joins (Table menu)

Creates, removes, or reorders joins between tables in the Table pane. Microsoft Query automatically joins tables if one of the tables has a primary key field and another table has a field with the same name and data type. If you add or delete a join in a query, it affects only that query. If you create a query that uses the same tables, Microsoft Query will create the join in the new query.


Add Criteria (Criteria menu)

Define the criteria that records must meet to be included in the result set. For example, you can specify criteria to select only records that contain a certain value (such as records for suppliers in Japan) or records that fall within a range (such as employees with start dates between 1-Jan-2001 and 1-Jan-2002).


Remove All Criteria (Criteria menu)

Removes all criteria specified in a query.


Add Column (Records menu)

Adds a column (field) to the Data pane. If there's already a result set in the Data pane, Microsoft Query adds the new column to the end of the result set. If you have a column in the result set selected, this command changes to Insert Column. If you add a column while Automatic Query is turned off, only the column heading is displayed in the Data pane. You must click Query Now to see the result set.


Remove Column (Records menu)

Removes the selected column from the Data pane. When you remove a column, you're not deleting the data from the underlying table; you're removing only the column and its data from your query. If you want a column and its data to remain in the query but not be displayed in the result set, choose Hide Columns on the Format menu instead. You can't delete the blank column in the Data pane.


Edit Column (Records menu)

Modifies the selected column with the changes you specify. Use this command to change column headings and to specify the type of total you want to calculate on specific fields. Changing column headings in the result set in Microsoft Query does not affect field names in the original data source. If you return data to Microsoft Excel or to another parent program, the original field name from the data source will be used.


Sort (Records menu)

Sorts the records in a query by the values in the column that you select. You can sort the records in ascending order (A-Z, 0-9) or descending order (Z-A, 9-0).


Go To (Records menu)

Moves to the value in the selected field in the record you specify. If you've selected an entire row in the Data pane when you choose the Go To command, Microsoft Query moves to and selects the entire row for the record you specify.


Allow Editing (Records menu)

Controls whether you can make changes to the data in a query's result set, thereby changing the data in the underlying table. When the command has a check mark beside it, you can edit the data. If you choose the command again, you remove the check mark and won't be able to edit the data. This option is not available with all data sources. See your database administrator or database documentation for details.


Tile (Window menu)

Arranges query windows in Microsoft Query side by side, so that all windows are visible without overlapping.


Cascade (Window menu)

Overlaps query windows in Microsoft Query so that each window's title bar is visible.


Office Assistant

The Office Assistant provides Help topics and tips to help you accomplish your tasks.


About Microsoft Query (Help menu)

Displays the version number of this Microsoft program; copyright, legal, and licensing notices; the user and organization name; and the software serial number.


Cancel (File menu)

Quits Microsoft Query and closes any open Query windows. If you started Microsoft Query from another application, the Cancel command returns you to the application without returning data.


Options (Edit menu)

Select the data source connection options you want in the Options dialog box.


Select the data source you want to use or remove from the box. When you click Save As to display the Select Data Source dialog box, the Save As dialog box includes the file type *.dqy. Select this item to save the query's design. Select a specific data source to save the query's result set as a table.


Uses the data source you select in the Available data sources box to create a new query. Depending on the data source you're using, some data sources display an additional dialog box that requests more information, such as a user identification, password, database, or directory or folder.


Click Other if the data source you want isn't listed in the Select Data Source dialog box. Microsoft Query displays the ODBC Data Sources dialog box, which displays all the ODBC drivers installed on your system.


Removes the selected data source from the Available data sources box, but does not remove the ODBC driver from your system. Microsoft Query won't remove a data source if an open query is using the data source.


Select the specific column you want to sort records in the result set by. To sort records by using a field from the data source that isn't in the result set, type the field's name in the Column box, using this syntax:

(table.fieldname)

Click Add to add the sort order to the Sorts in query box.


Sorts records in ascending order, from the beginning of the alphabet, the lowest number, or the earliest date, by using the selected column.


Sorts records in descending order, from the end of the alphabet, the highest number, or the latest date, by using the selected column.


Displays the sort order for each column you sorted on. The position in which the columns are listed in the Sorts in query box is the order in which Microsoft Query performs each sort. Rearranging the order of columns in the result set after you sort the records doesn't affect the sort order.


Sorts on the column selected in the Column box. When you sort on a column, the column is removed from the Column box and placed in the Sorts in query box. The Sort dialog box remains open so you can sort on other columns.


Removes the sort order for a column you've selected in the Sorts in query box. When you remove a column from the Sorts in query box, it is redisplayed in the Column box.


Type a parameter value for a field for which parameters have been specified in the Criteria pane. Only records that include this value will be returned in the result set.


Displays a list of procedures from the current data source that you can execute. Select the procedure you want to execute. The procedures available depend on the data source or database you are using. See your database administrator or database documentation for details.


Pastes the selected procedure into the SQL statement box in the Execute SQL dialog box, where you can execute it.


Redisplays the Execute SQL dialog box. No procedure is pasted into it.


Hides duplicate values in your query's result set.


Groups together all the like values in a field.


Q+E data types

Value	Type
1	SQL_CHAR
2	SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQL_VARCHAR
3	SQL_NUMERIC, SQL_DECIMAL
4	SQL_INTEGER, SQL_BIGINT
5	SQL_BIT, SQL_TINYINT, SQL_SMALLINT
6	SQL_FLOAT
7	SQL_REAL, SQL_DOUBLE
8	SQL_TIME, SQL_DATE, SQL_TIMESTAMP


SQL data types

Value	Type
0	SQL_UNKNOWN_TYPE
1	SQL_CHAR
2	SQL_NUMERIC
3	SQL_DECIMAL
4	SQL_INTEGER
5	SQL_SMALLINT
6	SQL_FLOAT
7	SQL_REAL
8	SQL_DOUBLE
9	SQL_DATETIME (ODBC 3.0 only)
12	SQL_VARCHAR


system channel

You use a system channel in a DDE conversation between the destination application and Microsoft Query to get general and query-related information. In addition to providing information about queries, the system channel also enables you to retrieve information about the system, such as the current connections, open queries, and the status of the destination application.


query channel

You use a query channel in a DDE conversation between the destination application and a specific query (for example, Query1) in Microsoft Query. To use a query channel, you must have already opened the query window using a system channel.


DDE conversation

The interaction between two applications that are communicating and exchanging data through dynamic data exchange (DDE). In a DDE conversation, the destination application initiates the interaction about a particular topic, and the source application responds.


address

The path to an object, document, file, page, or other destination. An address can be a URL (address to an Internet site) or a UNC network path (address to a file on a local area network). An address may also contain more specific address information, such as a Microsoft Excel cell range in the file that the main address points to.


calculated field

A field in the result set of a query that displays the result of an expression rather than data from a database.


column heading

In Microsoft Query, the shaded area at the top of each Data pane column that contains the field name, or if you changed it, another descriptive name for the field. Click the column heading to select an entire column. To increase or decrease the width of a column, drag the line to the right of the column heading. To adjust the column width quickly to the width of the largest value in the column, double-click the right border of the column heading.


constant

A value that is not calculated. For example, the date 10/9/1999, the number 210, and the text "Quarterly Earnings" are all constants. An expression, or a value resulting from an expression, is not a constant.


criteria

Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000:

Order Amount > 30000


Criteria pane

The area of the Microsoft Query window that displays the criteria you created to limit the records included in the result set of your query.

The Criteria Field row displays the fields for which you specify criteria. Microsoft Query retrieves only those records in which the values in the specified fields meet the criteria. The Value row displays the expressions for the criteria you set. The or row displays alternative criteria the records must meet to be included in the result set.

The Criteria pane is not displayed automatically when you create a new query. You can hide or display the Criteria pane by clicking Show/Hide Criteria  [image]  on the toolbar.


Data pane

The area of the Microsoft Query window that displays the result set of your query. You can add a field to the data pane by double-clicking the field name in the table that contains the field.


data source

A stored set of information that identifies the database a user wants to gain access to and includes the information needed to connect to that data. A data source has an identifying name and can include the name and location of the database server, the name of the driver program used to connect to the database, and information that the database needs when you log on. Data sources can connect to relational databases such as Microsoft Access and Microsoft SQL Server, On-Line Analytical Processing (OLAP) databases such as Microsoft SQL Server OLAP Services, text file databases, and lists on Microsoft Excel worksheets.


database

A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.

Database programs such as Microsoft Access, dBASE, and SQL Server make it possible to store large volumes of data and then sort and retrieve the data later.


expression

A combination of operators, field names, functions, and constants that evaluates to a single value. In Microsoft Query, you can use an expression to specify criteria (for example, Order Amount > 10000) or to perform a calculation on field values (for example, Price * Quantity).


external data

Data that is stored in a database, such as Microsoft Access, dBASE, or SQL Server, that is separate from Microsoft Query and the program from which you started Microsoft Query.


external data range

Data from an external database, text file, or Web page that has been brought into Microsoft Excel. You can format an external data range or use the data in calculations as you would any other data in Excel. If the data in the underlying database, file, or page changes, you can update the external data range in Excel by using the Refresh Data command on the Data menu.


field

A category of information, such as last name or order amount, that is stored in a table. When Microsoft Query displays a result set in its Data pane, a field is represented as a column.


function

A prewritten expression that returns a value based on the results of a calculation or other operation. For example, the Sum function returns the sum of two or more values. Microsoft Query assumes that your data sources support the following five functions:

Function	Description
Avg	The average of values in a field
Count	The number of values in a field
Max	The maximum value in a field
Min	The minimum value in a field
Sum	The sum of values in a field
Note   Some data sources may not support all of these functions, or may support additional functions. To find out which functions your data source supports, see the Help for the ODBC driver for your data source, or contact the driver vendor.


identifier

A field name used in an expression. For example, Order Amount is the identifier (field name) for a field that contains order amounts. You can use an expression (such as Price*Quantity) in place of an identifier.


index

A database component that speeds up searching for data. When a table has an index, data in the table can be found by looking it up in the index.


inner join

A connection between fields in two tables that selects only the records that have the same value in the joined fields. When values are the same in both fields, Microsoft Query combines the two matching records from each table and displays them as one record in the result set. If a record from one table doesn't have a matching record in the other table, neither record appears in the result set.

The inner join is the default join type that Microsoft Query uses when it joins tables automatically.


join

A connection between the information in two tables. A join tells Microsoft Query how to display the information from the two tables, by combining records that have matching data in a field that is joined. Microsoft Query then either includes or excludes records that don't match, depending on the type of join.


join line

In Microsoft Query, a line that connects fields between two tables and shows how the data is related. The type of join indicates which records are selected for the query's result set.


locked field or record

The condition of a record, field, or other object in a database that permits it to be viewed but not changed (makes it read-only) in Microsoft Query.


name

A word or string of characters in Microsoft Excel that represents a cell, range of cells, formula, or constant value.


ODBC driver

A dynamic-link library (DLL) file that Microsoft Query and Microsoft Excel can use to connect to a particular database. Each database program, such as Microsoft Access or dBASE, or database management system, such as Microsoft SQL Server, requires a different driver. ODBC drivers make Microsoft Query flexible; so you can connect to new databases as they become available by installing new drivers.


operator

A sign or symbol that specifies the type of calculation — such as addition, subtraction, or multiplication — to perform within an expression. Operators include mathematical operators and comparison operators (also known as logical operators) that produce the value TRUE or FALSE.


outer join

A connection between fields in two tables that selects all the records from one table, whether or not there are matching records in the other table. When values in the two tables are the same, Microsoft Query combines the matching records and then displays them as one record in the query result set. When a record from the table that's contributing all of its records can't be matched with a record from the other table, the record still appears in the result set. However, the record has empty cells wherever there is no matching record from the other table.


parameter query

A type of query that, when you run it, prompts for values to use to select the records for the result set so that the same query can be used to retrieve different result sets. To describe the parameter that the query requests, you can enter your own custom text for the prompt that Microsoft Query displays when you run the query. For example, you can create a parameter query that selects records by city. When you run the query, you can display a custom prompt "Type the name of the city," and the query then selects only the records for the city entered by the user.


password

A word, phrase, or string of characters used to prevent unauthorized access to a database. To gain access to the database, you must enter the password correctly either when you create the data source for the database or when you connect to the database to create a query.


PivotTable report

An interactive table in Microsoft Excel that summarizes and analyzes data from various sources, including queries and On-Line Analytical Processing (OLAP) data sources created in Microsoft Query. Use the Excel PivotTable and PivotChart Wizard to specify the source data you want to use and to define how you want to arrange the data in the PivotTable report. Data from OLAP data sources can be returned to Excel only as a PivotTable report, not as an external data range.


primary key

One or more fields that uniquely identify each record in a table. In the same way that a license plate number identifies a car, the primary key uniquely identifies a record.


query

In Microsoft Query or Microsoft Access, a means of finding the records that answer a particular question you ask about the data stored in a database.


query design

All of the elements you include in the Query window, including the tables, criteria you add to the Criteria pane, the order in which you add fields to the Data pane, and the font you use in the result set. In addition, the design specifies whether Auto Query  [image]  is turned on or off and whether you can edit the source data.


range

Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.


read-only

An option you can set so that a file can be opened and read, but not changed or saved. If you edit the read-only file, you can save your changes only if you give the file a new name.


record

A collection of information about a particular person, place, event, or thing. When Microsoft Query displays a result set in the Data pane, a record is represented as a row.


refresh

To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.


report template

A Microsoft Excel template that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel doesn't store the queried data in the template; instead, it refreshes the data whenever the report template is opened. A report template has an .xlt file name extension.


result set

The set of records that results when you run a query. You can see the result set of a query in Microsoft Query, or you can return a result set to a Microsoft Excel worksheet for further analysis. Microsoft Query displays the result set in row-and-column format in the Data pane.


row heading

In Microsoft Query, the shaded area to the left of each row. Click the row heading to select an entire row. To increase or decrease the height of all the rows in the result set, drag the line below a row heading.


sort order

A way to arrange data based on value or data type. You can sort data numerically or alphabetically. Sort orders use an ascending (1 to 9, A to Z) or descending (9 to 1, Z to A) order.


Structured Query Language (SQL)

A language used to retrieve, update, and manage data. When you create a query, Microsoft Query uses SQL to build the corresponding SQL SELECT statement. If you know SQL, you can view or change the SQL SELECT statement by clicking SQL on the View menu.


table

A collection of data about a particular subject that is stored in records (rows) and fields (columns).


Table pane

The area of the Microsoft Query window that displays the tables in a query. Each table displays the fields from which you can retrieve data. To view the data stored in a particular field, add that field to the Data pane.

You can hide or display the Table pane by clicking Show/Hide Tables  [image]  on the toolbar.


totals

One of the five calculation types Microsoft Query defines for you: Sum, Avg, Count, Min, and Max.


Web query

A query that retrieves data stored on your intranet, the Internet, or the World Wide Web.


World Wide Web

A system for navigating the Internet by using hyperlinks. When you use a Web browser, the Web appears as a collection of text, pictures, sounds, and digital movies.


cube

A data structure containing On-Line Analytical Processing (OLAP) data. The data in a cube consists of dimensions and data fields. Dimensions organize the types of data into hierarchies with levels of detail, and data fields measure the quantities being tracked in the database. For example, a cube could have a geographic dimension that has levels Country, Region, and City; a product dimension that has levels Product Category and Product Name; and a data field Sales Amount, which has values for the intersection of each geographic and product level.


cube definition

Information, stored by the OLAP Cube Wizard in an .oqy file, that defines how to construct an OLAP (On-Line Analytical Processing) cube from data retrieved from a relational database. A cube definition allows the cube to be created in memory whenever you open a report that is based on the .oqy file. It also allows you to use the wizard to make changes to the structure of a cube or offline cube file.


data source driver

A dynamic-link library (DLL) file that is used to connect to a particular database. Each database program, such as Microsoft Access or dBASE, or database management system, such as SQL Server, requires a different driver. Data source drivers can be ODBC (Open Database Connectivity), OLE-DB, or OLE-DB for OLAP (On-Line Analytical Processing) drivers.


dimension

In an On-Line Analytical Processing (OLAP) data cube, a dimension organizes a type or category of data into levels of detail. For example, a geographic dimension could have levels Country, Region, and City, and a time dimension could have levels Year, Quarter, Month, and Day.

In a Microsoft Excel PivotTable report that is based on OLAP data, dimensions become row, column, or page fields. In a PivotChart report, dimensions become series, category, and page fields. Each dimension becomes a set of fields in which you can display greater levels of detail.


level

In an On-Line Analytical Processing (OLAP) dimension, data is organized into lower and higher levels of detail. For example, a geographic dimension could have levels Country, Region, and City; and a time dimension could have levels Year, Quarter, Month, and Day. In a PivotTable or PivotChart report that is based on source data from an OLAP database, an item at a particular level corresponds to a member, in OLAP terminology.


list

A series of rows in a Microsoft Excel worksheet that contain related data, such as an invoice database or a set of client names and phone numbers. A list can be used as a database, in which rows are records and columns are fields. The first row of the list has labels for the columns.


macro

A program you write or record to store a series of Microsoft Excel commands that you can later use as a single command. Macros can automate complex tasks and reduce the number of steps required to complete tasks you perform frequently. Excel uses the Visual Basic for Applications programming language to record macros. You can also write macros directly by using the Visual Basic Editor.


On-Line Analytical Processing (OLAP)

A database technology optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables. Microsoft SQL Server OLAP Services is an example of a product that provides OLAP data services.


offline cube file

A file on your hard disk or a network share that contains On-Line Analytical Processing (OLAP) data and provides source data for a Microsoft Excel PivotTable or PivotChart report. In Microsoft Query, you can use the OLAP Cube Wizard to create an offline cube file from data retrieved from a relational database, such as Microsoft SQL Server. Offline cube files allow you to continue working with a report when you are disconnected from the network or when the original OLAP server is unavailable.


OLAP provider

A set of software that provides access to a particular type of On-Line Analytical Processing (OLAP) database. This software can include a data source driver and other client software that is necessary to connect to a database. Third-party OLAP providers may or may not be compatible with Microsoft Query features, depending on whether the provider is Microsoft Office-compatible and includes support for these features.


PivotChart report

An interactive Microsoft Excel chart that provides a graphical analysis of data from existing lists, databases, and PivotTable reports. After you create a PivotChart report, you can view different levels of detail or reorganize the layout of the chart by dragging its fields and items.


user profile folder

A folder that contains settings that define customization preferences and options for a particular user, including data sources and query files. The location of this folder depends on the operating system you use and the way your system administrator has configured your user profile on a server. For queries, cube definition files, and data source files, the folder is Root\OS\Profiles\User_name\Application Data\Microsoft\Queries, where Root is a server location or a folder on a drive from which all other folders branch (for example, C:\), OS is the operating system folder (for example, Windows or Winnt), and User_name is your user name.


Visual Basic Help

To install Visual Basic Help, run the installation program that you used to install Microsoft Office or Microsoft Excel. Click Add or Remove Features, and then click  [image]  next to Office Tools. Click the icon next to Visual Basic Help, click Run from My Computer, and then click Update Now.

To get Help for Microsoft Visual Basic while in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. Click Microsoft Visual Basic Help  [image]; type the method, property, function, statement, or object you want information about; click Search, and then click the topic you want.


Type the height for the rows in points based on the font size used to format the Data pane. Row height changes are saved when you save the query. If you're using a custom row height and you change the font, you have to adjust row height manually to fit the font. If you return data to Microsoft Excel or to another parent program, the formatting options you select will not be used in the result set in the parent program.


Select to apply the default height. Row height changes are saved when you save the query. If you're using the standard (default) row height, Microsoft Query adjusts the row height automatically if you change the font.


Displays the name of the table for which you are creating an index.


Type the name you want to assign to the index. (When you want to remove the index, you reference this name in a DROP INDEX SQL statement.) Requirements for index names vary depending on the data source. See the documentation on your ODBC data source for more information.


Indicate the field or fields on which you want to create an index using one of these methods: selecting one of the table's field names from the list; entering one or more of the field names separated by commas (such as LAST_NAME,FIRST_NAME); or entering an expression for a calculated field (such as PRICE*QUANTITY).


Adds the index on the field or fields you specified from the table.


When checked, indicates that the values in the field or fields on which you created the index uniquely identify each record in the table.


Type a new data source to execute your SQL statement for the query, or use an existing data source displayed in the box.


Click New to select a new ODBC driver you want to use to execute your SQL statement.


Removes the selected data source, but does not remove the ODBC driver from your system. Microsoft Query won't remove an ODBC driver if an open query is using the data source.


Displays options that can change the list of tables and columns displayed in the Available tables and columns list. Options you select become the default for the next query you create by using the same data source.


Displays a list of all tables in the underlying database, or database equivalent, in the Available tables and columns list.


Adds the names of alternative views in the database to the Available tables and columns list. In an SQL database, a view is an alternative way of looking at data from one or more tables. In a Microsoft Access database, it's a query.


Adds the names of system tables to the Available tables and columns list. System tables contain information about the structure of a database. Clear this check box if you are using the Microsoft Excel driver.


Adds synonyms that were created for the tables in the database to the Available tables and columns list. Synonyms are alternative names given to tables and are stored with the database.


Lists tables and columns in alphabetical order in the Available tables and columns list. Clear this check box to list tables and columns in the order received from the underlying database.


Click a name in the list to display only the tables created by that owner in the Available tables and columns list.


Displays options that can change the list of tables displayed in the Add Tables dialog box when you're using tables (not files used as tables). The options you select become the default for the next query you create using the same data source.


Displays a list of all the tables in the underlying database (or database equivalent) in the Add Tables dialog box. By default, the Tables check box is selected.


Adds the names of alternate views of any of the database tables to the Add Tables dialog box. The Views option isn't available if alternate views do not exist in the database. In SQL, a view is an alternate way of looking at data from one or more tables. In Microsoft Access, a view is a query in the database.


Adds the names of system tables (tables that contain information about the structure of a database) to the Add Tables dialog box.


Adds the synonyms (alternate names given to tables and stored with the database) that were created for the tables in the database to the Add Tables dialog box. The Synonyms option isn't available if synonyms do not exist in the database.


Updates the query to display the most current list of tables in the Add Tables dialog box.


New Query

Creates a new query. Displays the Choose Data Source dialog box, where you either choose the data source you want to use for the new query or select an existing query to change.


Open Query

Opens an existing query. Displays the Open Query dialog box, from which you open an existing query for viewing or editing. The Open Query dialog box is also displayed when you click Open in the Execute SQL dialog box. From the dialog box, you can open an SQL file whose SQL statement you want to view, edit, or run.


Save File

Saves a query, replacing any previous version with the current version. If you haven't saved the query before, Microsoft Query displays the Save As dialog box.


View SQL

Displays the corresponding SQL SELECT statement for the query in the query window so that you can view or edit the statement. See the SQL documentation or your database administrator for details about the syntax your data source supports.


Show/Hide Tables

Displays or hides the Table pane.


Show/Hide Criteria

Displays or hides the Criteria pane. Microsoft Query doesn't display the Criteria pane automatically when you create a query. The Criteria pane appears the first time you specify criteria by clicking Add Criteria on the Criteria menu or by clicking the Show/Hide Criteria button.


Add Tables

Displays the Add Tables dialog box, which you can use to add one or more tables to the Table pane in your query. The Table pane must be displayed to see a table's field list. Click Add Tables to display the Table pane if you can't see a table you've added.


Criteria Equals

Specifies a criterion that selects only those records containing the same value as the active cell. The cell you select in the Query result set becomes the value for the criterion in the Criteria pane.


Cycle Through Totals

Calculates values, depending on the type of data stored in the field. To change the type of calculation, double-click the field heading in the Data pane, and select the type of total you want displayed for the selected field in the Edit Column dialog box.


Sort Ascending

Sorts records in ascending order, from the beginning of the alphabet, the lowest number, or the earliest date, by using the selected field. To sort on multiple fields, select the next field you want to sort on, and hold down CTRL and click a sort button for each additional sort.


Sort Descending

Sorts records in descending order, from the end of the alphabet, the highest number, or the latest date, by using the selected field. To sort on multiple fields, select the next field you want to sort on, and hold down CTRL and click a sort button for each additional sort.


Query Now

Runs the query and displays the most current result set in the Data pane. When Automatic Query is on, Microsoft Query runs the query each time you add a field, specify criteria, or otherwise change the query design. When Automatic Query is off, you can run the query only when you're ready by clicking Query Now.


Automatic Query

Turns Automatic Query off or on. Controls whether the query is run every time you change it, such as when you add fields or specify new criteria that records must meet. By default, Automatic Query is on.


Displays the Select Values dialog box. Select from a list of values in the field to use for the criteria.


Displays the names of the available tables.


Select the data source that contains the name of the table whose definition you want to work with.


Displays the View Table Definition dialog box, which has the definition of the table you selected.


Displays the New Table Definition dialog box, which has blank fields where you can define a new table.


Removes the table you selected in the Table box.

Caution   When you click Remove, Microsoft Query prompts you to confirm the deletion. If you click Yes, Microsoft Query deletes the table and all the data stored in the underlying database. Once this happens, you can't undo your deletion from within Microsoft Query.


Displays the Create Index dialog box, where you create an index on one or more fields in a table.


Displays the Table Options dialog box, where you can specify which tables are listed in the Select Table dialog box.


Type the name of the table whose definition you want to work with, or select a name from the list.


Use Zoom Field to edit the contents of a field's cell when you need more room for editing or entering data. Click OK to see edited data in the Data pane. You may need to choose Allow Editing on the Records menu to edit the field. If you have added multiple tables to the query, you cannot edit records. This option is not available with all data sources. See your database administrator or database documentation for details.


Click Preview Now to preview the data in the column selected in the Columns in your query box.


Moves an entire table or individual columns in a table to the Columns in your query box for inclusion in your query. If all the columns are not displayed in the Available tables and columns box, click the plus sign to the left of a table name to display all the columns in the table.


Removes the selected columns from the Columns in your query box.


Use the scroll buttons to view more tables and columns in the Columns in your query box.


Removes all columns from the Columns in your query box.


Double-click a column in the Available tables and columns box to include it in your query. Click the plus sign to the left of a table name to display all the columns in the table.


Click a column in the Columns in your query box, and then click the scroll buttons to the right of the box to change the order of columns as they are displayed in the result set.


Select a column in the Column to filter box to filter or refine your query, and then select an operator and the specific criteria you want to filter in the Only include rows where box. Click And or Or to further filter the data you want your query to return.

Click Next without making changes in this step of the Query Wizard to return all of the data unfiltered from the data source.


Use the boxes beneath Only include rows where to select your filtering criteria. You can create a more complex filter by using the And or the Or button to link multiple criteria. Click And to combine two criteria to filter the data you want your query to return. Click Or to use either of two criteria.


Click Save Query to run the query and add this query to the list of available queries. Use this list when you choose a query to run from Microsoft Excel or Microsoft Query or when you choose a data source. Saves the query's design as the file type *.dqy.


Runs the query you finished and returns the result set to Microsoft Excel or the program you started the Query Wizard from.


Runs the query you finished and returns the result set to Microsoft Query. You can view the data and make additional changes to your query in Microsoft Query before returning the data to the program you started the Query Wizard from.


Opens the OLAP Cube Wizard where you can create an OLAP cube from the data in your query. An OLAP cube organizes the data so that reports are easier to create and may allow you to work with more data than you could otherwise. You can return data in an OLAP cube to Microsoft Excel only as a PivotTable report and not as an external data range.


Select the specific column you want to sort rows by. This sort column is identified as the first sort key or primary sort column, if you are sorting a result set by several columns.

Click Next without making changes in this step of the Query Wizard to keep the data in the order it's received from the data source.


Click Ascending to sort from the lowest number, the beginning of the alphabet, or the earliest date first in the sorted list.

Click Descending to sort from the highest number, the end of the alphabet, or the latest date first in the sorted list. Blank fields are always sorted last.

Click Next without making changes in this step of the Query Wizard to keep the data in the order it's received from the data source.