mapped to 90572 (SQL.DOC - OK)


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 further define a data source. If you have an existing query selected, click Open to modify the query. Make certain the Use the Query Wizard to create/edit queries check box is selected, if you want to use the Query Wizard to modify your query and return a different result set to Microsoft Excel.


Click Cancel to close the dialog box and remain in Query.


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


Opens the Data Source Options dialog box, which displays where the default locations for data sources can be located. 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.


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. For more information about using built-in functions in your criteria and result set, click  [image].


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 View 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. For more information about specifying an external data source, click  [image].


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 an ODBC driver from the list. The drivers available depend on the ODBC driver options you chose during setup. For more information about installing ODBC drivers, click  [image].


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 user ID and password in connection string 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.


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 Remove 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.


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. For more information about using built-in functions in your criteria and result set, click  [image].


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 execute. If you open an existing SQL file with  [image], Microsoft Query displays the statement in the SQL statement box. For more information about creating, editing, or viewing SQL statements in Microsoft Query, click  [image].


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.


Hide or show the Office Assistant

•	To show the Office Assistant, click Office Assistant  [image].

•	To hide the Assistant, click the Close button in the upper-right corner of the Assistant.


Move the Office Assistant and its balloon

•	Drag the Office Assistant to any location on the screen.

Note   To display the Assistant after you display a Help topic, click anywhere in the Microsoft Query window, or close the Help topic.


Choose a different Office Assistant

1	If the Office Assistant is not showing, click Office Assistant  [image].

2	Click Options.

If the Options button is not visible, click the Assistant, and then click Options.

3	Click the Gallery tab, and then click Back or Next until the Assistant you want appears.

Notes

•	The Mother Nature and Genius Assistants (not available if you installed your Microsoft Office program from floppy disks) require that your video monitor and adapter support at least 256 colors. For information about how to change the number of colors supported by your monitor, see Windows Help.

•	If you have access to the Internet, you can download additional Assistants from the Microsoft Office Web site. On the Help menu, point to Microsoft on the Web, and then click Microsoft OfficeHome Page.


Troubleshoot the Office Assistant

What do you need help with?

The Office Assistant is distracting.

I can't turn off the Office Assistant.

I don't have an Office Assistant.

The Office Assistant doesn't show the Help topic I want.

What happened to my Office Assistant balloon?


The Office Assistant is distracting.

•	Some Office Assistants are more active than others. To choose a different Assistant, right-click the Assistant, and then click Choose Assistant on the shortcut menu. On the Gallery tab, click Back or Next to see the available Assistants. The Office Logo Assistant is the least active. The Assistants that are the most active and have the most frequent sounds are The Dot and PowerPup (not available if you installed your Microsoft Office program from floppy disks).

•	To limit Office Assistant movements around the screen, click the Assistant, and then click Options. On the Options tab, clear the Move when in the way check box.

•	To turn off Office Assistant sounds, click the Assistant, and then click Options. On the Options tab, clear the Make sounds check box.

•	The Office Assistant has two sizes. To change the size, point to the Office Assistant border until the pointer changes to a double-headed arrow. Drag the border to the size you want.


I can't turn off the Office Assistant.

•	To hide the Assistant, right-click the Assistant, and then click Hide Assistant on the shortcut menu. If the Assistant shows a message or Help topic, close it, and then hide the Assistant.

•	You can remove the Office Assistant from your Office programs by running Setup for your Microsoft Office program. For more information about how to remove the Assistant, click  [image].


I don't have an Office Assistant.

If the Office Assistant is not showing, click Office Assistant  [image]. If the Assistant does not appear, the Assistant probably wasn't installed during setup. For information about how to install the Office Assistant, click  [image].


The Office Assistant doesn't show the Help topic I want.

If the Office Assistant is not showing, click Office Assistant  [image].

•	For best results during a search for a Help topic, type a complete sentence or question, rather than a single word or a phrase, in the Assistant. For example, to find Help on how to limit the data displayed in the result set, type Limit the data displayed by using criteria, not limit data.

•	If you prefer not to type complete sentences, or prefer to browse an index of words and phrases from the Help topics, click Contents and Index on the Help menu, and then click the Index tab.

•	To determine whether a word or phrase appears in any of the Help topics, click Contents and Index on the Help menu, and then click the Find tab. If this is the first time you've clicked the Find tab, follow the directions to set up the search capability.

•	To look for Help topics in a table of contents, click Contents and Index on the Help menu, and then click the Contents tab.


Get Help without using the Office Assistant

•	On the Help menu, click Contents and Index.

Tip   To display Help without the Office Assistant when you press F1, click Options. (If the Options button is not visible, click the Assistant, and then click Options.) On the Options tab, clear the Respond to F1 key check box.


Display messages without using the Office Assistant

1	If the Office Assistant is not showing, click Office Assistant  [image].

2	Click Options.

If the Options button is not visible, click the Assistant, and then click Options.

3	On the Options tab, clear the Display alerts check box.


Change the size of the Office Assistant

1	Point to the border of the Office Assistant until the pointer changes to a double-headed arrow.

2	Drag the border to either the large or the small size.

Note   To display the Assistant after you display a Help topic, click anywhere in the Microsoft Query window, or close the Help topic.

Tip   To have the Assistant automatically change from the large to the small size after 5 minutes not in use, click Options. (If the Options button is not visible, click the Assistant, and then click Options.) On the Options tab, select the Move when in the way check box.


Turn the Office Assistant sound on or off

To hear sounds with the Office Assistant, you must have a sound card installed on your computer.

1	In the Office Assistant, click Options.

If the Options button is not visible, click the Assistant, and then click Options.

2	On the Options tab, select or clear the Make sounds check box.

Note   To display the Assistant after you display a Help topic, click anywhere in the Microsoft Query window, or close the Help topic.


What happened to my Office Assistant balloon?

•	One balloon may appear in front of another. Close the balloon in front to display the balloon behind it.

•	Messages and reminders disappear when you display Help and appear again automatically as soon as you close the Help window or click anywhere in the Microsoft Query window.

•	If you just switched to a different Office program, the balloon displayed in the first program no longer appears. Messages are specific to the program you are in and do not apply when you switch to another program.

•	Some Office Assistant balloons close automatically when you click in the Microsoft Query window. These informative messages don't require a response from you.


Why can't I continue to work in Microsoft Query?

When some messages are displayed, either by the Office Assistant or without the Assistant, you may not be able to work in Microsoft Query until you close the message. These are important messages that require a response from you. To continue to work in Microsoft Query, click an option button in the message that appears. If Microsoft Query no longer displays the message but does display a Help topic, click anywhere in the Microsoft Query window to display the message again.


Keys for using the Office Assistant

To	Press
Select the Assistant balloon	ALT+F6 (press repeatedly until a dotted line appears around the balloon)
Select a Help topic from the topics displayed by the Office Assistant	ALT+topic number (where 1 is the first topic, 2 is the second, and so on)
See more Help topics	ALT+DOWN ARROW
See previous Help topics	ALT+UP ARROW
Close an Office Assistant message	ESC
Get Help from the Office Assistant	F1
Show or hide the Office Assistant in a wizard	TAB to select the Office Assistant button; SPACEBAR to show or hide the Assistant


Install or remove individual components of Microsoft Office

1	Close all open programs.

2	Click the Windows Start button, point to Settings, and then click Control Panel.

3	Double-click Add/Remove Programs.

4	On the Install/Uninstall tab, click Microsoft Office or the name of the program you installed, and then click Add/Remove.

5	When the Microsoft Office Setup dialog box is displayed, click Add/Remove.

6	Select the category of options you want to add or remove.

To install components shared with other Office programs, such as the Office Assistant, click Office Tools, and then click Change Option.

7	Select the check box of the item you want to install, or clear the check box of an item you want to remove, and then click OK.

8	When you've changed the options you want, click Continue.

Notes

•	If you originally installed the program from a network file server or a shared folder, run that copy of the Setup program.

•	To add or remove components of Office on Windows NT, start Office Setup as when you first installed Office, and follow the instructions on the screen.


Get program information about Microsoft Query

•	On the Help menu in Microsoft Query, click About Microsoft Query.

Program information, including the version number, appears in the About Microsoft Query dialog box.


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. For more information about joining tables in a query, click  [image].


Select the operator that determines the type of comparison Microsoft Query performs on the values in the joined fields. For more information about joining tables in a query, click  [image].


Select the table and field you want on the right side of the join line. For more information about joining tables in a query, click  [image].


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 or an outer join. For more information about join types, click  [image].


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.


Use keyboard shortcut keys in Microsoft Query

What do you want to do?

Move in the Microsoft Query panes by using shortcut keys

Select data and criteria by using shortcut keys

Edit data and criteria by using shortcut keys

Use function keys in Microsoft Query

Use the Office Assistant


Move in the Microsoft Query panes by using shortcut keys

Move in the Table pane

To	Press
Move between tables	TAB
Move to and select field names in a table	UP ARROW or DOWN ARROW. To select contiguous fields in a table or to cancel selection, press SHIFT+UP ARROW or SHIFT+DOWN ARROW
Move between columns and rows in the Data pane or the Criteria pane

To	Press
Move to a specific record (row) in the Data pane	F5, type the number of the record in the Record box, and then press ENTER
Move to the next column in the Data pane	TAB, ENTER, or RIGHT ARROW (from a selected cell)
Move to the previous column in the current row or the last field in the previous record	SHIFT+TAB, SHIFT+ENTER, or LEFT ARROW (from a selected cell)
Move to the last column in the current row	END
Move to the last column in the last row	CTRL+END
Move to the first column in the current row	HOME
Move to the first column in the first row	CTRL+HOME
Move to the current column in the next row	DOWN ARROW
Move to the current column in the last row	CTRL+DOWN ARROW
Move to the current column in the previous row	UP ARROW
Move to the current column in the first row	CTRL+UP ARROW
Move the insertion point within a value in a cell in the Data pane or the Criteria pane

To	Press
Cancel selection and display the insertion point in a selected cell	F2
Move one character to the right	RIGHT ARROW
Move one word to the right	CTRL+RIGHT ARROW
Move one character to the left	LEFT ARROW
Move one word to the left	CTRL+LEFT ARROW
Move to the end of the current line in single-line or multiple-line values	END
Move to the end of the value in multiple-line values	CTRL+END
Move to the beginning of the current line in single-line or multiple-line values	HOME
Move to the beginning of the value in multiple-line values	CTRL+HOME
Move between screens in the Data pane or the Criteria pane

To	Press
Move down one screen in a pane	PAGE DOWN
Move up one screen in a pane	PAGE UP
Move right one screen in a pane	CTRL+PAGE DOWN
Move left one screen in a pane	CTRL+PAGE UP


Select data and criteria by using shortcut keys

Select values within a column

To	Press
Select the current value, or cancel selection and display the insertion point	F2
Select the value in the next column	TAB, ENTER, or RIGHT ARROW (from a selected cell)
Make a selection in a value from the insertion point to the beginning of the column	SHIFT+HOME
Make a selection in a value from the insertion point to the end of the column	SHIFT+END
Extend selection or cancel selection of one character to the right	SHIFT+RIGHT ARROW
Extend selection or cancel selection of one word to the right	CTRL+SHIFT+RIGHT ARROW
Extend selection or cancel selection of one character to the left	SHIFT+LEFT ARROW
Extend selection or cancel selection of one word to the left	CTRL+SHIFT+LEFT ARROW
Select a row

To	Press
Select the current row	SHIFT+SPACEBAR
Select the next row, if the current row is selected	DOWN ARROW
Select the previous row, if the current row is selected	UP ARROW
Extend or cancel selection down one row	SHIFT+DOWN ARROW
Extend or cancel selection up one row	SHIFT+UP ARROW
Extend selection down one screen	SHIFT+PAGE DOWN
Extend selection up one screen	SHIFT+PAGE UP
Select the entire result set	CTRL+SHIFT+SPACEBAR
Select a column

To	Press
Select the current column	CTRL+SPACEBAR
Select the column to the right, if the current column is selected	RIGHT ARROW
Select the column to the left, if the current column is selected	LEFT ARROW
Extend or cancel selection left one column	SHIFT+LEFT ARROW
Extend or cancel selection right one column	SHIFT+RIGHT ARROW
Extend selection right one screen	CTRL+SHIFT+PAGE DOWN
Extend selection left one screen	CTRL+SHIFT+PAGE UP


Edit data and criteria by using shortcut keys

Copy, cut, paste, or delete

To	Press
Copy the current selection onto the Clipboard	CTRL+C
Paste the contents of the Clipboard at the insertion point	CTRL+V
Cut the current selection and copy it onto the Clipboard	CTRL+X
Delete the current selection or the character to the left of the insertion point	BACKSPACE
Delete the current selection or the character to the right of the insertion point	DELETE
Zoom in to edit in the Data pane

To	Press
Open the Zoom Field dialog box to enter values in small input areas in the Data pane more conveniently	SHIFT+F2


Function keys in Microsoft Query

To	Press
Switch to Help	F1
Get context-sensitive Help for the currently selected item	SHIFT+F1, and then press ENTER
Select or cancel selection of current value	F2
Save a query with the Save command	ALT+SHIFT+F2 or SHIFT+F12
Save a query with the Save As command	ALT+F2 or F12
Open the Zoom Field dialog box to enter values in small input areas in the Data pane more conveniently	SHIFT+F2
Close the active query	CTRL+F4
Quit Microsoft Query	ALT+F4
Go to a specific record (row) in the Data pane	F5, type the number of the record in the Record box, and then press ENTER
Move to the next pane in the Query window	F6
Move to the previous pane in the Query window	SHIFT+F6
Switch to the next open Query window	CTRL+F6
Switch to the previous open Query window	CTRL+SHIFT+F6
Run the currently open query	F9


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. For more information about using joins in queries, click  [image].


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-95 and 1-Jan-96).


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 is checked, 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. For more information about changing data in a result set, click  [image].


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.


Contents and Index (Help menu)

Displays online Help contents for this Microsoft program.


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.


Couldn't open file <name>.

Microsoft Query couldn't open or read from the file you're trying to open, or it couldn't save the file. There may be a disk error, or the file may be corrupted.


Text too long; paste canceled.

The text you're trying to paste from the Clipboard is too long to fit in the selected field in the Data pane or Criteria pane. The maximum number of characters you can fit into the field depends on the length specified for the field when the table was created.


Can't paste.

The Clipboard isn't responding. Another program may be using it, the data on the Clipboard may not be in a format that Microsoft Query recognizes, or there may not be enough free memory to paste the data.

Try closing other windows in Microsoft Query or closing other programs. If the error repeats, restart Microsoft Windows and try to copy and paste the data again.


A display driver resolution of at least 400 x 350 pixels is required to run Microsoft Query.

Your video display driver doesn't have enough resolution to run Microsoft Query. Replace the display driver by using the Microsoft Windows Setup program. Before you replace the video display driver, you may also need to upgrade your computer's video card to one that supports a higher resolution.


Clipboard format not supported.

The data on the Clipboard is in a format that Microsoft Query doesn't recognize.


Couldn't open Clipboard.

The Clipboard isn't responding, possibly because another program is using it. Close that program, and then try the operation again.


Data format error; can't paste.

Microsoft Query can't paste the data from the Clipboard because there may not be enough free memory.

Try closing other windows in Microsoft Query or closing other programs. If the error repeats, restart Microsoft Windows and try to copy and paste the data again.


Not enough system resources to update display.

Microsoft Query doesn't have enough free memory to complete the operation.

Close windows in Microsoft Query and close other programs. If the error repeats, restart Microsoft Windows and try the operation again.


Incompatible version of <filename>; Microsoft Query can't run.

The Ddeml.dll file is either outdated or missing. Try reinstalling Microsoft Query or Microsoft Windows.


Unable to quit Microsoft Query.

Microsoft Query is communicating with another program through dynamic data exchange (DDE). Wait for the DDE action to finish, and then quit Microsoft Query.


Can't start Microsoft Query. Please try again.

A system error occurred, or there isn't enough free memory to start Microsoft Query.

Start Microsoft Query again or close other programs. If the error repeats, restart Microsoft Windows and try the operation again.


Value must be a number.

You entered a value that isn't a number for an option setting that requires a numeric value. Enter a number instead.


Number is too large.

Enter a smaller value.


Not a recognized unit of measurement.

Microsoft Query doesn't recognize the characters you typed for this option setting. Enter a number for the measurement, but don't include characters for the type of unit, such as " (a double quotation mark), "in" (for inches), or "cm" (for centimeters).


Save changes to Query <name>?

You've made changes to the design or format of the current query since you last saved it.

•	To save your changes before closing the query, click Yes.

•	To discard the changes and close the query, click No.

•	To keep the query open without saving the changes, click Cancel.


Can't undo this operation. Continue anyway?

The Undo command isn't available for this operation because the operation is too large or there isn't enough free memory.

•	To continue the operation without the possibility of undoing it, click OK.

•	To discontinue the operation, click Cancel.


Can't open DDE channel; Microsoft Query couldn't find the specified program and topic.

Microsoft Query can't locate the specified program and topic (the subject of the Dynamic Data Exchange [DDE] conversation). Make sure that the other (destination) program is running, the topic is open, and that you spelled the name of the program and topic correctly when you defined the DDE conversation.


DDE conversation closed or changed.

The other program closed or changed the Dynamic Data Exchange (DDE) conversation unexpectedly, possibly by issuing a DDETerminate or DDETerminateAll statement.

Close the current conversation, and then establish a new conversation with the other program.


Message queue filled; DDE message lost.

Microsoft Query can't keep up with the number of Dynamic Data Exchange (DDE) operations that are being tried.

Try closing some of the DDE conversations, and then try the operation again.


The other application is busy.

The other program in the Dynamic Data Exchange (DDE) conversation is busy and can't perform a DDE operation. Wait until the other program is available.


The other application quit.

The other program in the Dynamic Data Exchange (DDE) conversation quit unexpectedly. Restart the other program before you reinitiate the DDE conversation.


The other application won't perform the DDE operation you attempted.

The other program can't perform the Dynamic Data Exchange (DDE) operation you are attempting.

•	You may have supplied data or commands that the other program doesn't recognize. See the program's documentation to determine what data or commands it recognizes.

•	Based on the defined topic of the conversation, the other program may not recognize the Item argument. See the other program's documentation to determine what items it recognizes.


The DDE transaction failed. Check to ensure you have the correct version of DDEML.DLL.

Your installation of Microsoft Query is incomplete or corrupt. To reinstall Microsoft Query, run the Setup program.


Timeout while waiting for DDE response.

The other program in the Dynamic Data Exchange (DDE) conversation didn't respond in the time specified by the DDE Timeout option.

•	The other program may be waiting for a response from the user. Switch to that program and close the dialog box, or respond to the message displayed by the other program.

•	The other program may be too busy to respond to DDE messages.

Note   You can't change the time specified in the DDE Timeout option.


Between operator without 'And'.

When you use the Between...And operator, you must enter the reserved word And. Use the following syntax:

expression Between value1 And value2


Expression too complex.

Your expression is too complex to be evaluated successfully. Try to simplify the expression.


Extra ')'.

Your expression has too many closing parentheses. Delete any parenthesis that doesn't have a matching opening parenthesis.


Invalid use of '.', '!', or '( )'.

Your expression has an invalid . (dot) or ! operator or an invalid parenthesis. For example, you may have entered an invalid identifier or typed a parenthesis after the constant Null. Make sure you enter the expression correctly.


Missing ')', ']', or '|'.

Your expression is missing a closing parenthesis, bracket, or vertical bar. Make sure you enter the expression correctly.


Out of memory during calculation.

There is not enough memory available to perform calculations.

Close windows in Microsoft Query, or close other programs. If the error repeats, restart Microsoft Windows and retry the calculation.


Syntax error in date.

Your expression has an invalid date value. Make sure you enter the expression correctly.


Syntax error in number.

Your expression has an invalid number. Make sure you enter the expression correctly.


Syntax error in string.

Your expression has an invalid string. For example, you may have exceeded the maximum length of a string. Make sure you enter the expression correctly.


Syntax error.

Your expression has invalid syntax. For example, you may not have entered an operand or operator, you may have entered an invalid character or a comma, or you may have entered text without surrounding it in quotation marks ( " or ' ). Make sure you enter the expression correctly.


Couldn't add the table <name>.

The specified table could not be added. Make sure the following conditions exist:

•	The specified table is present in the database.

•	The table name is spelled correctly. Check for missing underscore characters ( _ ) or other punctuation.

•	The specified table contains valid fields (columns).


Syntax error in GROUP BY clause.

Your SQL statement has an invalid GROUP BY clause. A reserved word may be misspelled or missing, or the punctuation may be incorrect.


Syntax error in ORDER BY clause.

Your SQL statement has an invalid ORDER BY clause. A reserved word may be misspelled or missing, or the punctuation may be incorrect.


Syntax error in HAVING clause.

Your SQL statement has an invalid HAVING clause. A reserved word may be misspelled or missing, or the punctuation may be incorrect.


Syntax error in JOIN expression.

The ON clause in your SQL statement isn't followed by a valid expression. The expression should be in the following format:

{oj C:\QT\orders orders LEFT OUTER JOIN C:\QT\orddtail orddtail ON orders.ORDER_ID = orddtail.ORDER_ID}


Incorrect column expression: <expression>.

The expression you specified in the Select list isn't correct. The expression should be in the following format:

SELECT employee.EMPLOY_ID, employee.HIRE_DATE
FROM C:\QT\employee employee


Expected 'BY' after 'GROUP'.

Your SQL statement includes a GROUP clause instead of a GROUP BY clause. BY may be either misspelled or missing.


Expected 'BY' after 'ORDER'.

Your SQL statement includes an ORDER clause instead of an ORDER BY clause. BY may be either misspelled or missing.


Expected expression after 'WHERE'.

The WHERE clause in your SQL statement isn't followed by a search condition.


Expected expression after 'HAVING'.

The HAVING clause in your SQL statement isn't followed by a search condition.


Expected 'JOIN' after 'OUTER'.

Your SQL statement includes a LEFT OUTER or RIGHT OUTER clause instead of a LEFT OUTER JOIN or RIGHT OUTER JOIN clause. JOIN may be either misspelled or missing.


Expected 'ON' after right join table.

Your SQL statement includes a LEFT OUTER JOIN or RIGHT OUTER JOIN clause and its expression, but the expression isn't followed by an ON clause and its expression. ON may be either misspelled or missing.


Expected table name after 'FROM'.

Your SQL statement includes a FROM clause that isn't followed by a table name.


Didn't expect <text> after the column list in the GROUP BY clause.

The expression or clause after the column list in your GROUP BY clause isn't valid.


Didn't expect <text> after the HAVING clause.

The expression or clause after the HAVING clause in your SQL statement isn't valid.


Didn't expect <text> after the column list in the ORDER BY clause.

The expression or clause after the ORDER BY clause in your SQL statement isn't valid.


Didn't expect <text> after the select list in the SELECT statement.

The expression or clause after the select list in your SELECT statement isn't valid.


Expected join operator after table <name>.

Your LEFT OUTER JOIN or RIGHT OUTER JOIN clause is missing the = operator between the two table names in the expression. Outer joins must be equi-joins (use the = operator).


Left and right outer joins must be equi-joins.

Your LEFT OUTER JOIN or RIGHT OUTER JOIN clause either omits the = operator between the two table names in the expression or uses a different operator. Outer joins must be equi-joins (use the = operator).


Expected 'OUTER' after LEFT/RIGHT.

Your SQL statement includes a LEFT or RIGHT outer join clause that's missing the keyword OUTER. An outer join clause in the SQL statement must be either a LEFT OUTER JOIN or a RIGHT OUTER JOIN.


Didn't expect <text> after the WHERE clause.

The expression or clause after the WHERE clause in your SQL statement isn't valid.


Expected LEFT or RIGHT after table <name>.

Your SQL statement includes an OUTER JOIN clause but isn't preceded by the keywords LEFT or RIGHT. An outer join clause in an SQL statement must be either a LEFT OUTER JOIN or a RIGHT OUTER JOIN clause.


Expected expression after 'ORDER BY'.

Your SQL statement includes ORDER BY, but ORDER BY isn't followed by an expression.


Expected expression after 'GROUP BY'.

Your SQL statement includes GROUP BY, but GROUP BY isn't followed by an expression.


Can't display the criterion.

The criterion you specified is invalid.


Can't set criteria unless you've specified a field.

Before you can specify criteria for a field, you must add that field to the Criteria pane.


Can't apply aggregates to tables.

You typed an asterisk (*) for the Field option instead of specifying a specific field on which you want to calculate a total. Remove the *, and then enter the field name.


Expression too long.

The expression is too long for Microsoft Query to evaluate. Try to make the expression shorter, or break it into two separate expressions.


Record doesn't exist.

The record number you specified doesn't exist. The record number may be too large or too small. Make sure there are records in the Data pane, and then verify that you entered the record number correctly.


Help isn't available due to lack of available memory or improper installation of Windows or Microsoft Query.

To free some memory, try closing windows or other programs. If necessary, reinstall Microsoft Windows or Microsoft Query.


<Number> records will be updated.

The Data pane contains other records that match the record you revised. As a result, Microsoft Query will automatically update the matching records with the same changes.

•	To avoid duplicate records in the Data pane, add all fields to the Data pane before you make changes to your data.

•	To update the records, click OK.

•	To prevent updating any records, click Cancel.


Delete <number> record(s).

You indicated that you want to delete the specified number of records.

Note   If the number of records is greater than you specified, Microsoft Query found duplicate records based on the fields in the Data pane. To avoid duplicate records, add all fields to the Data pane before you delete any records.

•	To delete the records, click OK.

•	To prevent the deletion, click Cancel.


Must have two instances of the table to create a self-join.

You have two different tables in the Table pane and have defined a self-join by using the same table name in the Left and Right boxes in the Joins dialog box.

•	To create a self-join, you must have two instances of the same table in the Table pane.

•	To join two different tables, you must use one of the tables for the left side of the join and the other table for the right side of the join.


Can't specify * as a field. To add all fields, drag and drop * into the grid.

You typed an asterisk (*) in the Field cell in the Criteria pane. To specify criteria for all fields in the query, use one of these methods instead of the asterisk:

•	Drag the * from the table to the Criteria pane.

•	Double-click the asterisk in the table.

•	Add each field individually.


Columns <name> and <name> that you are about to join are of different types. Create the join anyway?

You're trying to join fields (columns) that contain different data types. For example, you may be trying to join a field that contains dates with a field that includes text.

•	To prevent the join, click Cancel.

•	To create the join, click OK.

Note   This type of join may not produce the results you want. Make sure Microsoft Query displays the data you want in the result set.


Function <name> can't be used with <column name>.

You selected a function that can't be calculated for the type of data in this field. For example, you can't calculate the average of the SHIP_CNTRY field if the field contains text.


Can't access table <name>.

The specified table can't be found, is locked by another user or by another query you're running, or contains no valid fields.


Refresh failed.

Microsoft Query can't update the list of tables in the Add Tables dialog box because the data source isn't responding. Make sure you're still connected to the data source.


Executed SQL statement successfully: <number> rows affected.

Microsoft Query ran the SQL statement you specified, affecting the number of rows (records) indicated.


I/O error during save.

Microsoft Query can't save the query to the disk. There may be a disk error.


I/O error during load.

Microsoft Query can't open or read the file you selected. There may be a disk error, or the file may be corrupted.


Couldn't create new window.

Microsoft Query can't display the file you selected, possibly because there isn't enough memory.

Close other windows in Microsoft Query, or close other programs. If the error repeats, restart Microsoft Windows.


Not all functions supported by driver.

The expression you entered contains one or more functions that your data source doesn't support. To determine what functions your data source supports, see the documentation that comes with the driver your data source uses, or contact the driver vendor.


Too many values selected.

There isn't enough memory to store all the values you specified as criteria for this field. Specify fewer values.


Can't open multiple copies of a file.

You can't have two copies of the same query file open at the same time.


Save stopped due to invalid query state.

The design of your query isn't valid. For example, you may not have specified criteria correctly. If the Automatic Query command is turned off, try running the query by clicking Query Now on the Records menu.


Couldn't open file.

Microsoft Query can't open or read the Microsoft Query file you specified in the Execute SQL dialog box. The file may not be in text format, there may be a disk error, or the file may be corrupted.


Can't create file.

Microsoft Query can't create the query file you specified. There may be a disk error, or the file may be corrupted.


Can't save the column <name> to this data source.

You tried to save a result set as a table, but the data source you're saving it to either doesn't support the data type assigned to the specified field or can't convert it to a comparable data type. Try saving to another data source that supports the data type.


Can't open file.

Microsoft Query can't open the query file you specified. There may be a disk error, or the file may be corrupted.


Unable to execute query. View SQL text?

Microsoft Query can't run the SQL statement, because the SQL statement refers to an item that is not currently valid. For example, it may refer to a table that no longer exists. To view the SQL statement and make the necessary changes, click OK.


General ODBC Error

Microsoft ODBC or your data source returned an error with no detailed information available. Make sure your database is running and available, and try the operation again.


Driver error.

This error was returned by the driver. For more information, check the documentation for the driver you are currently using.


File name isn't valid.

File names must follow the standard naming conventions your data source requires. See the documentation for your data source.


Can't add expression <name> to the sort list.

The data source you're using doesn't allow you to sort on a field that displays the results of an expression.


<Value> exceeds the numerical range of this column.

The value exceeds the maximum number allowed for values in this column (field). Enter a smaller value.


<Value> has too many digits after the decimal.

There are more digits after the decimal than are allowed for values in this field. Use fewer digits.


<Value> isn't a valid number.

You entered the wrong type of value in the field. For example, you may have entered text in a field that accepts only numbers.


<Value> isn't a Boolean value.

You entered a non-Boolean value in a field that accepts only Boolean values. For example, you may have entered a date when the field accepts only a value of "0" or "1".


<Value> isn't a valid date.

You entered the wrong type of value in the field. For example, you may have entered text in a field that accepts only dates.


Can't edit multiple-table query.

You can't edit data in a query that contains more than one table.

•	To edit data in a table, add only that table to the query, and then click Allow Editing on the Records menu.

•	To edit data in the current query, delete any extra tables from the query; keep only the table that contains the data you want to edit. Then click Allow Editing on the Records menu.

Note   When you delete a table in a query, the data in the underlying table is not deleted. The deletion only removes the table and its data from the current query.


Couldn't remove data source.

Microsoft Query can't remove the data source you selected. You must be disconnected from the data source to remove it.


Couldn't add data source.

Microsoft Query can't add the data source you selected because of a problem with connections to the data source.


Can't remove; some queries are using this source.

One or more queries are using the data source you want to remove. You must first close the queries that are using the data source.


Table <name> already exists; replace it?

A table with the name you entered already exists.

•	To replace the previous table with the one you're creating, click Yes.

•	To prevent replacing the previous table, click No, and then enter a different name for the new table.


Error in the name <name>.

The table name and field names must follow the standard naming conventions your data source requires. See the documentation for your data source.


Maximum length for type <data type name> is <number>.

You entered a number in the Length box that is greater than the maximum length allowed for a field with this data type.


Number of digits after the decimal should be less than or equal to the total length.

You entered a number in the Decimal box that is greater than the number in the Length box. Change the value in the Decimal box to a number that is less than or equal to the number in the Length box.


Length <number> must be numeric.

You used a nonnumeric value in the Length box. Change the value to a number.


Table <name> was created successfully.

Microsoft Query created the table you defined with the data source you selected.


Delete table <name>?

You indicated that you want to delete this table. If you delete the table, all data in the table will be deleted.

Caution   If you delete the table, you can't undo the deletion.

•	To delete the table and the data stored in it, click Yes.

•	To prevent the deletion, click No.


Table <name> was deleted successfully.

Microsoft Query deleted the table and all the data stored in it. You can't undo this deletion.


SQL execution failed.

Microsoft Query can't run your SQL statement. Make sure you enter the statement correctly.


Couldn't fetch procedures.

Microsoft Query can't display the list of procedures from the data source, because the data source isn't responding. You may be disconnected from the data source.


Couldn't read this file.

Microsoft Query can't read your file. The file may not be in Microsoft Query or Q+E format, there may be a disk error, or the file may be corrupted.


First select a data source.

You tried to run an SQL statement before you selected a data source. Click Data Sources in the Execute SQL dialog box, and then select a data source.


No procedures found.

This data source doesn't contain any procedures. To create procedures, see the documentation for your data source.


Can't save to a file that is already open.

The file you want to save to is open. Close the file before you try to save to it again.


Table <name> already in the query; add it again?

You tried to add a table that's already in the Table pane.

•	To add the table if you want to create a self-join, click OK.

•	To prevent adding the table, click Cancel.


Can't edit grouped query.

You can't edit data in a query that contains totals or that has the Group Records check box selected in the Query Properties dialog box. To edit the data, remove the totals or cancel selection of the Group Records property.


Unable to retrieve values.

Microsoft Query can't display the values you selected for criteria. There may not be enough memory, or you may be disconnected from the data source.

If you're still connected to the data source, try closing other windows in Microsoft Query or closing other programs. If the error repeats, restart Microsoft Windows.


Couldn't create index.

The data source for this table doesn't support indexes.


Length specified must be greater than 0.

The length specified in the Length box in the Table Definition dialog box must be greater than 0 (zero).


Some queries are using <connection name>; close anyway?

One or more queries are using the data source you want to remove.

•	To remove the data source and close the queries, click OK.

•	To keep the data source in the list and the query open, click Cancel.


Remote links exist; close anyway?

The data in your query is linked to another program, such as Microsoft Excel or Microsoft Word.

•	To close the query and the link, click OK. You can't reestablish the link once you close the query.

•	To keep the query open and the link established, click Cancel.


Width must be between 0 and <number>.

Specify a column width between these two numbers, or select the Standard Width option in the Column Width dialog box.


Height must be between 1 and <number>.

Specify a row height between these two numbers, or select the Standard Height option in the Row Height dialog box.


Save large Clipboard from <name>?

You placed a large amount of data on the Clipboard.

•	To save the data on the Clipboard, and to close the query or quit Microsoft Query, click Yes.

•	To delete the data, and to close the query or quit Microsoft Query, click No.

•	To continue working in Microsoft Query with the data still on the Clipboard, click Cancel.


Update failed. Continue editing record?

Microsoft Query can't update the data you edited because the underlying table or data source is read-only, the underlying table is corrupted, or there isn't enough disk space to store the changes.


Can't edit a calculated column.

You can't edit data in a column on which you calculated totals or in which you display the results of a custom expression.


Only two tables are allowed with an outer join. Delete outer join to add table?

You already have two tables in the Table pane connected with an outer join. If you want to use three or more tables in the Table pane, you must delete the outer join or change it to an inner join.


Query can have only one outer join.

You can have only one outer join in a query.


Can't have outer joins if there are more than two tables in the query.

You can have only two tables in the Table pane when you use an outer join. Remove the extra tables from the Table pane, and then create the outer join.


SQL Query can't be represented graphically. Continue anyway?

Microsoft Query can't display the Table pane for this query. If the query returns records, you can't edit them or the query's design in the Query window. However, you can edit the query's design by clicking SQL on the View menu and then editing the SQL statement directly.


Must select one value for the operator <operator>.

When you define criteria with this operator, you must use only one value. Microsoft Query then selects only the records that contain this value in the field you set the criteria for. For example, if you select "begins with" in the Operator box, Microsoft Query searches the field for which you set the criteria for values that begin with the value you specify. Only those records are selected for the result set.


Must select two values for the operator <operator>.

When you define criteria with this operator, you must use two values. For example, if you select "is between" in the Operator box, Microsoft Query searches the field for which you set the criteria for values that fall between the two values you specify. Only those records are selected for the result set.


Column <name> can't be used in the criteria.

You can't specify criteria in a column (field) with this data type.


Column <name> can't be used with the operator <operator>.

Your data source doesn't allow this field to be used with the operator you specified, because the data type of the field can't be used with the operator. See the documentation for your data source to determine which operators you can use with fields of this data type.


Incorrect extension <extension>.

The table name you specified does not contain a valid file name extension. See the documentation for your data source for the correct file name extension.


Existing tables can't be modified. Changes can be used to create a new empty table.

To create a new table from an existing table, you must change the table name in the View Table Definition dialog box. The new table doesn't contain the data that is stored in the existing table. You can't edit existing table definitions in Microsoft Query.


Can't find record. More columns are needed to identify it, or another user may have modified it.

Microsoft Query can't find a record in the underlying table that matches the record you updated or deleted in the query. You may not have enough columns in the query to distinguish the record you want to update, or another user may have changed the record since the last time you ran the query. Before you update the record, do one of the following:

•	Add more columns to the query, or add the column that uniquely identifies the record.

•	To get the most current data from the underlying table, run the query again by clicking Query Now on the Records menu.


Save file as <name>.

You tried to save a result set as a table with a file name for which the file name extension is incorrect or missing.

•	To save the result set to a file with the correct extension for your data source, click OK.

•	To return to Microsoft Query without saving the result set, click Cancel.


Nonunique record found.

The result set contains identical records because the Data pane doesn't include a field whose values uniquely identify each record, such as a customer ID or order ID. Any changes to one of these identical records affect all the identical records.

To distinguish records, make  sure to add all the fields from the underlying table to the Data pane before you begin to edit the data.


Since the database is read-only, you can't create or remove tables. Continue?

You can view a table definition in a database that's read-only, but you can't create new tables or delete existing tables.


Index was created successfully.

Microsoft Query successfully created the index in the specified field or fields in the table.


Failed to create unique index. Create one yourself?

Microsoft Query can't create a unique index for this table even though the result set you want to save as a new table comes from a table that contains a unique index.

•	To display the Create Index dialog box so that you can create a unique index for the table, click Yes.

•	If you don't want to create a unique index, click No.


No unique indexes exist in this table. Create one yourself?

The result set you want to save as a new table comes from a table that doesn't contain a unique index.

•	To display the Create Index dialog box so that you can create a unique index for the table, click Yes.

•	If you don't want to create an index, Click No.


Couldn't access <database>.

The data source you're using limits you to adding tables from only the current database (or database equivalent).


<Number> records match the selection. Delete them all?

Because the Data pane doesn't include a field whose values uniquely identify each record, such as a customer ID or order ID, the result set contains other records that match the one you want to delete. If you delete the selected record, Microsoft Query will also delete any identical records.

To distinguish records before you begin to edit data, make sure to add all the fields from the underlying table or to create a unique index in the table.


Unable to display Help.

The Microsoft Query Help file is not in the current folder. Try moving or copying the file to the current folder.


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.


Closes the Select Data Source dialog box, and ignores any selections you've made.


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.


Add or change parameters that prompt the data source for specific data to create the result set in the Data pane.


Displays the active query name.


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.


Activate Command (DDE)

Activates the specified query window. This command is used to change the active window when more than one window is open. It is available only on the system channel.

Syntax

Activate(queryname)

queryname   The name of the query window to activate

Example

This example executes two queries, creating two query windows. After executing the second query, the example makes the window created by the first query the active window.

DDEExecute chan, "[BuildSQL('Select * From Customers')]"

DDEExecute chan, "[QueryNow()]"

stQueryname1 = DDERequest(chan, "Query")

DDEExecute chan, "[BuildSQL('Select * From ORDERS')]"

DDEExecute chan, "[QueryNow()]"

stQueryName2 = DDERequest(chan, "Query")

DDEExecute chan, "[Activate('" & stQueryname1(1) & "')]"


AllowEdit Command (DDE)

Determines whether editing is allowed in a query window. This command is available on both the system channel and the query channel. It is equivalent to the Allow Editing command on the Records menu.

Syntax 1

AllowEdit(allowedit, locked)

Syntax 2

Allow.Edit(allowedit, locked)

The two forms of syntax are equivalent.

allowedit   Determines whether editing is allowed in the query window. Can be True or False.

locked   Determines whether the Allow Editing command on the Records menu is disabled when UserControl is in effect. Can be True or False.

Example

This example executes a previously built SQL statement. The example then sends the AllowEdit command and the UserControl command to Microsoft Query to allow the user to view but not edit data in the data source (the Allow Editing menu command is disabled after UserControl is in effect).

DDEExecute chan,"[BuildSQL(""" & sql & """)]")

DDEExecute chan,"[QueryNow()]")

DDEExecute chan,"[AllowEdit(False, True)]")

DDEExecute chan,"[UserControl(""&Return Data"", 3, False)]")


BuildSQL Command (DDE)

Builds an SQL statement one item at a time to create a new query on the DDE channel. This command is available only on the system channel.

Syntax

BuildSQL[(sqltext)]

sqltext   A valid SQL statement. If sqltext is 0 (zero) or omitted, the SQL statement is cleared.

Remarks

The BuildSQL command is useful for building long SQL statements. For example, if an SQL statement is too long to send in a single instruction, you can send it in several smaller pieces.

After you build an SQL statement, you can execute it using the QueryNow command.

Example

This example uses the QueryDefinition request item to return the SQL statement for a query.

sql = DDERequest(chan,"QueryDefinition")

This example uses the BuildSQL command to resend the returned SQL string with an additional WHERE clause.

DDEExecute chan, "[BuildSQL(""" & sql & """)]"

DDEExecute chan, "[BuildSQL("WHERE Freight > 10")]"

DDEExecute chan, "[QueryNow()]


Close Command (DDE)

Closes the current query. This command is available on both the system channel and the query channel.

Syntax

Close( )


Command Command (DDE)

Sends an Open command to Microsoft Query. This command is provided for programs that limit the length of strings used with DDEExecute.

This command is available only on the system channel.

Syntax

Command(opnum, execstring)

opnum   Determines the way Microsoft Query receives the commands. Can be one of the following values.

Value	Description
1	Initializes the buffer in Microsoft Query with execstring (clearing the buffer if it exists), but doesn't execute the query.
2	Concatenates execstring to the end of the buffer.
3	Concatenates execstring to the end of the buffer and executes the complete command buffer. Because the command buffer is cleared after execution, execstring can't be re-executed.
execstring   Segments of an Open command. The entire string must be enclosed in single or double quotation marks.

Microsoft Query supports sending only the Open command using the Command command.

Remarks

This command is provided for Q+E compatibility only. It is recommended that you use the BuildSQL command, if possible, to send long SQL statements.


Exit Command (DDE)

Exits Microsoft Query. This command is available only on the system channel.

Syntax

Exit(exitall)

exitall   Determines whether Microsoft Query will exit if queries on channels other than the one executing the Exit command are open. Can be either True or False.

Use True to exit Microsoft Query regardless of which queries are open. If Microsoft Query is under user control for another channel (that is, the UserControl command has been executed), this setting closes that channel and returns control to the program that started Microsoft Query in user control.

Use False to exit Microsoft Query only if any open queries were opened on the channel executing the Exit command.

Remarks

This DDE command is also used to quit Microsoft Query for the Macintosh.


Fetch and Fetch.Advise Commands (DDE)

The Fetch command retrieves data from Microsoft Query and sends it directly to the destination program.

The Fetch.Advise command updates the destination program automatically as values in Microsoft Query change, not just when the Fetch.Advise command is sent.

These commands are available on both the system channel and the query channel.

Syntax 1

Fetch.Advise(appname, topic, range, values)

Syntax 2

Fetch(appname, topic, range, values)

appname   The name of the destination program to which the data is to be sent. Here are the appname arguments you'd use for Office programs:

Program	Value of appname
Microsoft Access	MSAccess
Microsoft Excel	Excel
Microsoft Word	WinWord
topic   The name of the worksheet, document, or control to which the data is to be sent.

range   The area of the worksheet, document, or control to which the data is to be sent.

values   The values to be retrieved from Microsoft Query. (For valid values, see the table below.)

Note   The Fetch.Advise command automatically updates only query data, not request items or row#col# references.

Can be one of the following values.

Value	Description
request_item	The information to return; for example, QueryDefinition.
row#col#	A reference to retrieve specific rows and columns, such as R3C5 from Microsoft Excel.
/Headers	Retrieves column names.
/Rownum	Retrieves row numbers.
All	Retrieves all records.
Remarks

For Fetch.Advise, the channel created to return data to the destination program isn't closed until a Fetch.Unadvise or Close command is sent.

Example

This example uses the NumRows and NumCols request items to return the number of rows and columns in the query, and then returns the values with column headings to a Microsoft Excel worksheet named "Sheet1."

rows=REQUEST(Chan,"NumRows")

cols=REQUEST(Chan,"NumCols")

DDEExecute chan, "[Fetch(""Excel"", ""Data.xls!Sheet1"", _

    ""R1C1:R"&rows&"C"&cols&""", ""All/Headers"")]"


Fetch.Unadvise Command (DDE)

Removes the automatic updating feature set by Fetch.Advise and closes the channel opened by Microsoft Query to pass data to the destination program. This command is available on both the system channel and the query channel.

Note   The arguments used by Fetch.Unadvise must match the arguments used previously by Fetch.Advise.

Syntax

Fetch.Unadvise(appname, topic, range, values)

appname   The name of the destination program to which the data is to be sent. (For examples, see the Fetch.Advise command.)

topic   The name of the worksheet, document, or control to which the data is to be sent.

range   The area of the worksheet, document, or control to which the data is to be sent.

values   The values to be retrieved from Microsoft Query. (For a table of valid values, see the Fetch.Advise command.)


Logoff Command (DDE)

Logs off the specified data source. This command is available only on the system channel.

Syntax

Logoff(source)

source   The name of the data source you want to disconnect from.


Logon Command (DDE)

Displays a dialog box that enables you to log onto a data source. This command is available only on the system channel.

Syntax

Logon(source)

source   The name of the data source you want to connect to.


ODBCLogon Command (DDE)

Allows a program to connect to a data source. You can specify whether dialog boxes are presented to the user at logon time. This command is available only on the system channel.

Syntax

ODBCLogon(connectstring, dialog)

connectstring   A string that supplies information (such as the data source name, user ID, and password) required by a driver to connect to a data source. This argument must follow the format required by the driver you're using. (For more information about the connection string, see the documentation for your data source).

Note   Because Microsoft Query 97 supports File DSNs, you can use the ConnectionString request item to obtain the DSN-less connection string of a query created from a File DSN. You can then use that connection string to reconnect to the data source from any computer.

dialog   Determines whether dialog boxes are displayed at logon time. Can be one of the following values.

Value	Description
0 (zero)	No dialog boxes are displayed.
1	Dialog boxes are displayed to obtain additional information from the user not provided by the connection string.
2	Dialog boxes are displayed with values from the connection string filled in as default values.
3	Same as 1, except that information not required to connect to the data source appears dimmed.
Example

This example uses the ConnectionString request item to return the string needed to connect with a data source. The example saves the string after the user logs onto a data source, and later uses the ODBCLogon command to connect to the same data source without user input.

connstr = DDERequest(chan, "ConnectionString")

' intervening code

DDEExecute chan, "[ODBCLogon (connstr(1), 0)]"


Open Command (DDE)

Opens and executes a query and displays the data in the Data pane. This command is available only on the system channel.

Syntax

Open(query)

query   The name of the Microsoft Query file (for Windows, the file name extension is .dqy) or Q+E file (Windows only; the file name extension is .qef) that contains the query; or the SQL SELECT statement to execute.

Example

This example opens a query named Sales.

DDEExecute chan, "[Open(""c:\msquery\sales.dqy"")]")

This is the same example on the Macintosh.

DDEExecute chan, "[Open(""drivename:msquery:sales"")]")

This statement uses an SQL SELECT statement to execute a new query.

DDEExecute chan, "[Open(""SELECT * FROM pubs.dbo.titles"")]")


QueryNow Command (DDE)

Executes the active query for the open DDE channel. If you've used the BuildSQL command to build the query, this command executes the SQL statement in the buffer and opens a query window. This command is available on both the system channel and the query channel.

Syntax

QueryNow( )

Example

This example saves an SQL statement from a query and later sends the same statement to re-execute the query.

sql = DDERequest(Chan, "QueryDefinition")

DDEExecute chan, "[BuildSQL(""" & sql & """)]"

DDEExecute chan, "[QueryNow()]"


SaveAs Command (DDE)

Saves a new query's design or an existing query's design under a different name. Performs the same function as Save As on the File menu and is available on both the system channel and the query channel.

Syntax 1

SaveAs(queryname)

Syntax 2

Save.As(queryname)

The two forms of syntax are equivalent.

queryname   The new name for the query.


SetWindowState Command (DDE)

Specifies whether the active window is normal, minimized, or maximized when opened. This command is available on both the system channel and the query channel.

Syntax

SetWindowState(state)

state   The state of the active window. Can be one of the following values.

Value	Description
1	Normal
2	Minimized (Windows only)
3	Maximized


ViewPane Command (DDE)

Specifies which panes in the query window are displayed. This command is available on both the system channel and the query channel.

Syntax

ViewPane(pane)

pane   Specifies which panes are displayed. Can be one of the following values:

Value	Description
0 (zero)	Data pane only
1	Data pane and Table pane
2	Data pane and Criteria pane
3	Data pane, Table pane, and Criteria pane


UserControl Command (DDE)

Activates Microsoft Query and allows users to create, open, or edit a query as if they were running Microsoft Query as a standalone program.

This command is available only on the system channel.

Syntax

UserControl(exittext, appstate, newquery)

exittext   The name of the Exit command used to exit Microsoft Query after a DDE conversation. If this argument is omitted, "Return" is used.

appstate   The state of the program. Can be one of the following values.

Value	Description
1	Normal
2	Minimized (Windows only)
3	Maximized
If this argument is omitted, the current state of Microsoft Query is used.

newquery   Specifies whether the Choose Data Source dialog box is displayed when Microsoft Query is started during a DDE conversation. Can be True or False.

If newquery is True, the Choose Data Source dialog box is displayed, asking the user to select a data source. If newquery is False (the default), no dialog box is displayed and any open queries for that channel are accessible.

Remarks

User control ends when the user clicks Exit on the File menu in Windows or Quit on the File menu on the Macintosh. Microsoft Query then returns to its previous program state and returns control to the destination program.

Important   Do not allow a program that includes a time-out feature to time out before user control ends.

While Microsoft Query is running under user control, any new or existing queries are opened on the same DDE channel.

Example

This example starts Microsoft Query as a maximized program and replaces the Exit command on the File menu (or the Quit command on the File menu on the Macintosh) with the Return Data to Microsoft Excel command. Then it displays the Choose Data Source dialog box.

DDEExecute chan, "[UserControl(""&Return To Excel"",3,True)]")


SetQueryName Command (DDE)

Sets the name of the active query, or creates a query with that name if there is no active query. The name appears in the query window title bar.

Syntax

SetQueryName(name)

name   The name of the query.


EnableParameters Command (DDE)

Controls whether the user can specify parameters in the criteria pane and SQL window. By default, the user cannot specify parameters. You cannot disable parameter specification after specifications have already been entered by the user.

Syntax

EnableParameters(flag)

flag   Determines whether the user can specify parameters. Can be 0 (zero) to disable specifying parameters or 1 to enable specifying them.


SetParameterName Command (DDE)

Sets the name of a parameter received in a query string passed to Microsoft Query. SetParameterName should be sent after a BuildSQL command is sent.

Syntax

SetParameterName(index, name)

index   The parameter index.

name   The name for the parameter.

Remarks

If the SQL statement sent by BuildSQL contains parameters but no SetParameterName commands are received, Query assigns default parameter names in the form "Parameter n," where n is an incrementing number. If the number of parameter names sent using SetParameterName commands is insufficient, the remaining parameters are named "Parameter n". If too many parameter names are sent, the excess names are ignored.


AddParameters Command (DDE)

Adds a parameter to the active query for use by PivotTables.

Syntax

AddParameters(field1, field2, …)

field1, field2, …   The names of table columns that should be added to the criteria as parameters. The names of the new parameters are defined to be the names of the fields themselves. If you specify a field name that isn't available in the active query, an error occurs.

Remarks

AddParameters can be used even when entering parameters is disabled in Microsoft Query.

The modified SQL string can be returned by requesting the ODBCSQLStatement item.

To reset parameters, you must reset the entire SQL string.


BuildODBC Command (DDE)

Builds a connection string of any length one segment at a time.

Syntax

BuildODBC([connectstring])

connectstring   A segment of a valid connection string. If connectstring is 0 (zero) or omitted, the connection string is cleared.


LogonNow Command (DDE)

If you've used the BuildODBC command to build the connection string, this command logs on with the connection string in the buffer. If there is no connection string in the buffer, an error occurs.

Syntax

LogonNow([dialog])

dialog   Determines whether dialog boxes are displayed at logon time. Can be one of the following values.

Value	Description
0 (zero)	No dialog boxes are displayed. If this argument is omitted, the default value is 0 (zero).
1	Dialog boxes are displayed to obtain additional information from the user not provided by the connection string.
2	Dialog boxes are displayed with values from the connection string filled in as default values.
3	Same as 1, except that information not required to connect to the data source appears dimmed.


UserControl2 Command (DDE)

Activates Microsoft Query and allows users to create, open, or edit a query as if they were running Microsoft Query as a standalone program.

This command is available only on the system channel.

Note   The difference between the UserControl command and the UserControl2 command is that UserControl2 allows you to specify a command to add to the File menu that closes all queries created in Microsoft Query and returns to the destination program without any data.

Syntax

UserControl2(exittext, canceltext, appstate, newquery)

exittext   The name of the Exit command used to exit Microsoft Query after a DDE conversation. If this argument is omitted, "Return" is used.

canceltext   The name of the menu item used to cancel the current session. The command is added to the bottom of the File menu in Microsoft Query. If this argument is omitted, no new menu item is created.

appstate   The state of the program. Can be one of the following values.

Value	Description
1	Normal
2	Minimized (Windows only)
3	Maximized
If this argument is omitted, the current state of Microsoft Query is used.

newquery   Specifies whether the Choose Data Source dialog box is displayed when Microsoft Query is started during a DDE conversation. Can be True or False.

If newquery is True, the Choose Data Source dialog box is displayed, asking the user to select a data source. If newquery is False (the default), no dialog box is displayed and any open queries for that channel are accessible.


Remove a join

If your query contains an inner join or an outer join but doesn't retrieve the records you want, you may want to remove the join.

1	In the Table pane, click the join line you want to remove.

2	Press DELETE.

Notes

•	When you remove a join, the records included in the result set of the query will change. Depending on the type of join you remove, records may be added to or removed from the result set.

•	Deleting a join from a query affects only that query. For example, if Microsoft Query automatically creates an inner join between two tables in your query and you remove the inner join, Microsoft Query will automatically create an inner join between the two tables again if you use them in a different query.


Operators in joins

You can use the following operators in joins.

Operator	Description
= Equals	Selects records that contain equal values in the joined fields.
<> Does not equal	Selects records that contain values that are not equal in the joined fields.
> Is greater than	Selects records from the left table that contain values that are greater than values in records from the right table in the joined fields.
>= Is greater than or equal to	Selects records from the left table that contain values that are greater than or equal to values in records from the right table in the joined fields.
< Is less than	Selects records from the left table that contain values that are less than values in records from the right table in the joined fields.
<= Is less than or equal to	Selects records from the left table that contain values that are less than or equal to values in records from the right table in the joined fields.


Go to a specific record in the result set

1	On the Records menu, click Go To.

2	Type the number of the record you want to go to.


A message indicates that the path to my database is not valid.

If your database is on a shared network directory, when you set up the data source and selected the database file, the path to the database may have been recorded in the data source with the mapped drive letter in use by your system at that time. For example, if your database is named Inventory.mdb, and you had drive G mapped to the shared network directory where this database is stored, your data source might record this location as G:\public\Inventory.mdb. When you try to use this data source, or you or other users try to run queries created with this data source, the ODBC driver displays a message that the path is not valid if drive G is not mapped to the same shared network directory.

If you are using the Microsoft Access driver or the Microsoft Excel driver, you can correct this problem by creating a new data source. When you specify the location of the database file, don't select the mapped drive for the shared network directory. Instead, type the UNC address of the shared network directory, and then locate the database file. For example, if your database file is stored on a server named Shared, you could type \\Shared\public and then select the file Inventory.mdb.

For other ODBC drivers, before you use a data source or run a query created with that data source, make sure the same drive is mapped to the shared network directory where the database is located as was mapped when the data source was created.

For information about setting up data sources, click  [image].


Troubleshoot Microsoft Query

What do you need help with?

Why can't I continue to work in Microsoft Query?

I can't find the table I want.

I can't create a new data source.

I can't delete a data source.

Why is my data source non-shareable?

An ODBC driver that I used previously doesn't work.

A message indicates that the path to my database is not valid.

Changes and formatting in my query aren't there the next time I run it.

Microsoft Query repeatedly runs out of time without connecting to my database.

Microsoft Query won't let me combine all the data from two tables.

Microsoft Query won't give me access to data in a Microsoft Excel list.

I want to see the records that two tables don't have in common.

Microsoft Query continues to use connection resources while I'm not working with a database.

Microsoft Query does not return all of the available records.

Microsoft Query won't let me print the result set.

Microsoft Query won't let me make mailing labels.

I can't create the joins I want.

My joins don't give me the records I expect.

I can't save the result set as a table in my database.

I can't add, copy, or move a record in my database.

Microsoft Query won't let me edit parameter types for my SQL procedure.


I can't find the table I want.

•	If the list of tables in the Add Tables dialog box does not include the tables you expect, check the following, depending on the options available for your data source:

•	The tables you want may belong to a different owner. In the Add Tables dialog box, click a different owner in the Owner box. The person who owns the tables may need to grant you permission before you can use the tables.

•	The tables you want may be of types that aren't selected for display. In the Add Tables dialog box, click Options. Under Show, make sure all of the table types are selected.

•	The tables you want might have been added to the database since Microsoft Query connected to it. To see recently added tables, click Options in the Add Tables dialog box, and then click Refresh.

•	If the tables you're working with are in separate files on a shared network directory, make sure you supply the full path to the files, as follows.

•	In the Add Tables dialog box, click Network.

•	In the Path box, enter the network address, such as \\computer1\share

•	Click OK, and in the Drives box, click the share you connected to.

•	In the Directories list, click the folder that contains the table that you want to add.

•	In the Table name box, click the table you want to add, and then click Add.


I can't create a new data source.

First, make sure you have the right ODBC driver for your data source. ODBC drivers make Microsoft Query flexible, so that you can connect to new databases as they become available by installing new drivers. However, you must make sure you install and set up the right driver for the type of database you're using.

In addition to the drivers provided with Microsoft Query, you can use ODBC drivers provided by third-party manufacturers. Before you try to use a third-party ODBC driver, it's a good idea to make sure the manufacturer has tested the driver with Microsoft Query 97. For some databases, the ODBC driver supplied with the database software may be the best choice. Contact the administrator for your database to find out what's available and what works best at your site. For information about ODBC drivers supplied with Microsoft Excel, see Microsoft Excel Help.

Before you set up a data source, make sure you know the address where the database is located on your network and have the necessary permissions to connect to the database and log on. See the administrator for your database for a logon name, password, or any other permissions required, and to make sure the access you've been granted is working properly.

If you don't see the driver you need when you point to Get External Data on the Data menu, click Create New Query, double-click New Data Source on the Databases tab, and click the list in step 2 of the Create New Data Source dialog box, you should check to make sure the ODBC driver is installed properly. For information about installing ODBC drivers, click  [image].

Once you've installed the driver and selected it in step 2 of the Create New Data Source dialog box, make sure you provide all of the necessary information in step 3 of the dialog box. For information about a Microsoft ODBC driver, click Connect, and then click Help in the setup dialog box for the driver. For third-party drivers, see the Help or documentation for the driver.

If you are setting up a data source with an ODBC driver provided by Microsoft, click the name of your driver for information about the settings you should make in step 3 of the Create New Data Source dialog box.

Microsoft Access driver

Microsoft dBase and FoxPro drivers

Microsoft Excel driver

Microsoft Paradox driver

Microsoft text driver

SQL Server driver


Microsoft Access driver

1	In step 3 of the Create New Data Source dialog box, click Connect.

2	Under Database, click Select, and use the Select Database dialog box to locate your database file.

If you want Microsoft Query to open the database read-only, select the Read only check box.

If you want Microsoft Query to be the only user of the database when you use this data source, select the Exclusive check box.

3	Click OK.

If your database requires you to set additional connection parameters, click Advanced. For information about whether you need to change any of these settings, see the administrator for your database. For information about the advanced settings, click Cancel, and then click Help in the ODBC Microsoft Access 97 Setup dialog box.

4	If you want Microsoft Query to access the system database, click Database under System database, and then click System Database. In the Select System Database dialog box, locate your system database file, and then click OK.

5	Click OK, and enter your logon name and password under Authorization. If you don't use a logon name or password to gain access to the database, leave the boxes under Authorization blank.

Note   You can use the ODBC Microsoft Access 97 Setup dialog box to create, repair, and compact Microsoft Access databases. If you are not familiar with these operations, you should perform them only from within Microsoft Access. For more information, see Microsoft Access Help.


Microsoft dBase and FoxPro drivers

Before proceeding with these steps, it's a good idea to place all of the database files that you want to use together in the same folder, so that the Query Wizard can find all of your files and you don't have to type the full path whenever you want to add a table to a query. If you want, you can place the database files in one folder and the index files in another folder.

1	In step 3 of the Create New Data Source dialog box, click Connect.

2	In the Version box, select the version of dBase or FoxPro that was used to create your database files.

3	If the database files are not in the current folder, clear the Use current directory check box, and then click Select Directory to locate the folder.

4	If the index files for the database are also in a different folder, click Select Indexes to locate the folder.


Microsoft Excel driver

With the Microsoft Excel ODBC driver, you can access data only from workbooks in the same folder. Before proceeding with the following steps, copy all workbooks from which you want to access data simultaneously into one folder, and name each worksheet range that you want Microsoft Query to recognize as a table.

You must set up a separate data source for each folder from which you want to access data.

1	In step 3 of the Create New Data Source dialog box, click Connect.

2	In the Version box, click the Microsoft Excel version used to create the workbook. If your workbook was created with Microsoft Excel 95 (Microsoft Excel version 7.0), click Excel 5.0/7.0.

3	Click Select Workbook, and then locate the workbook file.


Microsoft Paradox driver

Get the Microsoft Paradoc Driver from the ValuPack folder on the Microsoft Office 97 or Microsoft Excel 97 CD-ROM, or on the network you installed the Microsoft ODBC drivers from. If you didn't install the Microsoft ODBC drivers from a CD-ROM and you have access to the World Wide Web, in Microsoft Excel, point to Microsoft on the Web (Help menu), and then click Free Stuff.

Before proceeding with these steps, it's a good idea to place all of the database files that you want to use together in the same folder, so that the Query Wizard can find all of your files and you don't have to type the full path whenever you want to add a table to a query.

1	In step 3 of the Create New Data Source dialog box, click Connect.

2	In the Version box, select the version of Paradox that was used to create your database files.

3	If the database files are not in the current folder, clear the Use current directory check box, and then click Select Directory to locate the files.

4	If your database requires you to set advanced options, click Options. For information about whether you need to change any of these settings, see the administrator for your database. For information about the option settings, click Help on the ODBC Paradox Setup dialog box.


Microsoft text driver

You'll need to set up a separate data source for each folder that contains text files from which you want to access data. If you want to use the data source to access data simultaneously from more than one text file, copy all of the text files to the same folder before proceeding with these steps.

1	In step 3 of the Create New Data Source dialog box, click Connect.

2	If the text files are not in the current directory, clear the Use current directory check box, and then click Select Directory to locate the folder.

Note   You can gain access to data in text files in several formats, including columns separated by blank space or by commas or tab characters. For more information about the text file formats that you can use, click Help in the ODBC Text Setup dialog box.


SQL Server driver

1	In step 3 of the Create New Data Source dialog box, click Connect.

2	In the Server box, enter the name of the SQL Server on your network. If you want information about the selections available for Server, click Help.

3	In the Network address box, enter the address of your SQL Server database. For Microsoft SQL Server databases, click Default.

4	In the Network library box, enter the address of your SQL Server Net-Library DLL if required, or click Default. For more information about the Network library box, click Help.

5	Click Options, and enter the name of your SQL Server database in the Database name box.

6	Set any other options required for your database. For information about whether you need to change any of these settings, see the administrator for your database. For information about the option settings, click Help.

7	Click OK, and enter your logon name and password.


I can't delete a data source.

Data sources that you created with previous versions of Microsoft Query have (not shareable) in the data source name. All data sources, including new ones created with Microsoft Query 97, are stored as files with file name extension .dsn.

Data source files from previous versions of Microsoft Query are usually stored in the ODBC Datasources folder in the Common Files folder. To see what folders are used to store data source files, point to Get External Data on the Data menu, click Create New Query, and then click Options.

You can find and delete data source files as follows:

•	If all data source files are in the Common Files\ODBC Datasources folder, double-click the 32bit ODBC icon in the Windows Control Panel, click the File DSN tab, and then use the Remove button to delete any .dsn files that you don't want.

•	If data source files are in several folders, in Windows, search for all files with file name extension .dsn, and then delete any files that you don't want.


Why is my data source non-shareable?

Microsoft Query 97 data sources have a different format from data sources created with previous versions of Microsoft Query. Data sources and queries that you created with previous versions of Microsoft Query can still be used with Microsoft Query 97, but queries that you create using these data sources cannot be shared with other users.

When you install Microsoft Query, all of your data sources created with previous versions of Microsoft Query or ODBC are stored as files, and (not shareable) is added to the names of these data sources to help you identify them.

If you want to share queries or report templates that use data from the external databases specified in your non-shareable data sources, create new Microsoft Query 97 data sources for these databases, and use the new data sources to create the queries and report templates. For information about creating Microsoft Query 97 data sources, click  [image].

For information about deleting non-shareable data sources, click  [image].


An ODBC driver that I used previously doesn't work.

You may need to use a different driver with Microsoft Query 97 in the following situations:

•	Microsoft Query 97 requires 32-bit ODBC drivers, such as the ones supplied with Microsoft Query 97. For example, if you develop a Microsoft Excel 97 program in Visual Basic to get external data and distribute your program to Microsoft Excel version 5.x users, the program may not run with the 16-bit ODBC drivers on the Microsoft Excel version 5.x systems.

•	If you install Microsoft ODBC drivers other than the ones included with Microsoft Excel 97 or Microsoft Office 97, make sure you install version 3.0 or later of these drivers. Earlier versions of the Microsoft ODBC drivers may not work with Microsoft Query 97.

•	If you are working with a third-party ODBC driver, you may also need to install a new ODBC driver for Microsoft Query when you upgrade your database or install a new version of the database. To determine whether this is necessary, check the documentation for your database.


Changes and formatting in my query aren't there the next time I run it.

When you return data to Microsoft Excel, you save the query definition as part of the worksheet, but you don't automatically save a separate query file. When you then edit the query for the external data range, changes to formatting and column headings that you made in Microsoft Query aren't preserved.

To preserve formatting in Microsoft Query, make your formatting changes, click Save As on the File menu in Microsoft Query, and click Query Files (*.qry) in the Save as type box. When you reopen the query file, you will see your Microsoft Query formatting.


Microsoft Query repeatedly runs out of time without connecting to my database.

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 connections to your database often require more time, you can set a longer interval. On the Edit menu in Microsoft Query, click Options, make sure the Cancel the connection if not connected within check box is selected, and then enter the number of seconds to allow for the connection.

If you want to allow 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.


Microsoft Query won't let me combine all the data from two tables.

If you want to combine all of the data from two lists that have similar but not identical data, such as two lists (tables) of customers that each have different information (fields) for each customer, and each list has some customers that aren't included on the other list, you can create a query that includes all of the customers. This type of query is called a full outer join.

1	Create a query in Microsoft Query, and add both tables to the query.

2	Join a field that matches between the two tables (for example, the Customer ID field from two customer lists) by clicking the field in the first table and dragging it to the second table.

Choose a field that has information for every record in each table. For example, if two customer lists have matching fields for Customer ID, join the Customer ID field.

3	Double click the join line between the two tables, and in the Joins dialog box, click option 2.

4	Click Add, and then click Close.

5	Add the fields that you want to the query.

6	On the View menu, click SQL.

7	Select the lines in the SQL Statement dialog box, and press CTRL+C to copy them.

8	Click OK. Double-click the join line again, and in the Joins dialog box, click option 3.

9	Click Add, and then click Close.

10	On the View menu, click SQL.

11	Click at the end of the data in the SQL Statement window, press ENTER, type the following capitalized word, and press ENTER again:

UNION

12	Press CTRL+V to paste the copied information after the word UNION.

13	Click OK, and when prompted that the query can't be displayed graphically, click OK again.


Microsoft Query won't give me access to data in a Microsoft Excel list.

Check the following on your Microsoft Excel worksheet:

•	Make sure the list follows Microsoft Excel list format. (For information about guidelines for creating a list on a worksheet, see Microsoft Excel Help.) Name the ranges that are tables, and make sure you give each range a different name.

•	Make sure you name the list. Microsoft Query recognizes only named ranges as tables. To name a list, select the entire list in Microsoft Excel, and then type a name in the Name box on the formula bar.

Do not attempt to gain access to Microsoft Excel tables by clicking the System tables option under Show in the Table Options dialog box. If you use this approach, Microsoft Query may recognize blank cells on your worksheets as data, may not recognize some of your actual data, and may not be able to retrieve your data correctly.

•	Use the same format for columns in each list that you want to combine. For example, if each list has a date column, apply the same date format to each column. If you mix text and number formats within a column, the Microsoft Excel driver may not be able to find and combine all of your data.

•	Don't use column labels, such as Table or Database, in your list. Microsoft Query uses some labels internally and does not recognize them in your data.

•	A data source that you set up for the Microsoft Excel driver can provide access to workbooks in only one folder at a time. If the lists are in two different workbooks, make sure both workbooks are in the same folder as the workbook that you specified when you set up the data source.

•	Before you start Microsoft Query, make sure you copy all the workbooks you want to use to the folder specified in your data source. Microsoft Query does not automatically recognize new workbooks that you add to the folder while you are creating a query. You can gain access to new workbooks by clicking Options in the Add Tables dialog box and then clicking Refresh.

Note   Microsoft Query is useful when you want to combine data from two or more Microsoft Excel lists. If you're working with data from a single worksheet or you want to use simple criteria to select rows from a single worksheet, you may find the Microsoft Excel AutoFilter and Advanced Filter commands (Data menu, Filter submenu) easier to use. For more information about autofilters and advanced filters, see Microsoft Excel Help.


I want to see the records that two tables don't have in common.

If you have two tables that have similar data, such as two lists (tables) of customers, and each list has some customers that aren't included on the other list, you can create a query that returns only the records from the first table that do not match the records from the second table. This type of query is called a subtract join.

1	Create a query in Microsoft Query, and add both tables to the query.

2	Join the fields that match between the two tables (for example, the Customer ID field from two customer lists) by clicking the field in the first table and dragging it to the second table.

3	Double-click the join line between the two tables, and in the Joins dialog box, click option 2.

4	Click Add, and then click Close.

5	Add the joined field to the query.

6	Add a field other than the joined field from the second table. Choose a field that has data for every record in the second table. For example, if you joined the Customer ID fields in the two tables, you might add the Customer Name field from the second table.

7	On the View menu, click Criteria.

8	In the first criteria field, click the field you added in Step 6.

9	In the first Value field, type Is Null

10	Click Query Now to see the records in the first table that aren't included in the second table.

11	Add any additional fields that you want in the query, and return the data to Microsoft Excel

Note   If you also want to see the records in the second table that don't match records in the first table, create a second query in which you follow these same steps, but make the second table first, and vice versa.


Microsoft Query continues to use connection resources while I'm not working with a database.

By default, Microsoft Query makes a single connection to your database and keeps the connection open until you exit from Microsoft Query, regardless of how many query windows you open for the database during that time. If you're creating queries to more than one database during a Microsoft Query session, the open connections can tie up resources and expensive connect time.

If you want to minimize connect time, you can have Microsoft Query close the connection when you don't have a query window open for a database. To keep the connection open only as long as you have a query window open, click Options on the Edit menu, and then clear the Keep connections open until MS Query is closed check box.


Microsoft Query does not return all of the available records.

•	Make sure the number of records to be returned has not been restricted. Click Options on the Edit menu, and then make sure the Limit number of records returned to check box under User settings is cleared.

•	Make sure any criteria you have specified are entered correctly, so that they retrieve the records you want. For more information about specifying criteria, click  [image].

•	Make sure any joins you have created have the desired effect. For more information about joins, click  [image].


Microsoft Query won't let me print the result set.

Microsoft Query is a tool that retrieves data from a database. It cannot create printed reports directly.

To print the result set, return it to Microsoft Excel by clicking Return data to Microsoft Excel on the File menu. Once you have the result set in Microsoft Excel, you can use all of the formatting and printing commands in Microsoft Excel to arrange and print the data. For more information about printing in Microsoft Excel, see Microsoft Excel Help.


Microsoft Query won't let me make mailing labels.

Microsoft Query is a tool that retrieves data from a database. It cannot create mailing labels. However, you can use data retrieved by Microsoft Query with Microsoft Excel and Microsoft Word to create mailing labels.

First return the data to Microsoft Excel by clicking Return data to Microsoft Excel on the File menu. Once you have the result set in Microsoft Excel, you can use the data as a mailing list in Word. For more information about mailing labels, see Microsoft Excel Help.


I can't create the joins I want.

If your query has more than two tables, you cannot any create outer joins in the query. Outer joins are options 2 and 3 in the Joins dialog box.

You can create as many inner joins (option 1 in the Joins dialog box) as you want, but you can create only one outer join in each query. If you know how to program in the SQL language, you can try changing the SQL statement in the SQL dialog box (View menu, SQL command) to create more than one outer join.

For information about using outer joins to combine records, click  [image].


My joins don't give me the records I expect.

The default join that Microsoft Query uses when it joins tables automatically is an inner join (option 1 in the Joins dialog box). When two tables are joined with an inner join, records from the second table that don't have corresponding records in the first table are not included in the result set.

To retrieve all of the records from both tables when one table contains records not present in the other table, you can use an outer join (options 2 and 3 in the Joins dialog box). For information about using outer joins to combine records, click  [image].

An outer join between two tables sometimes does not return any records from the second table. If you then add an inner join between the two tables, Microsoft Query will not retrieve any records from either table. To display records, delete one of the joins.

For more information about joins, click  [image].


I can't save the result set as a table in my database.

Microsoft Query 97 does not allow you to save the result set as a table in the database. If your database can import Microsoft Excel data, you can return the result set to Microsoft Excel and then use the commands in your database to import the Microsoft Excel data as a table. For example, Microsoft Access has a spreadsheet import wizard that you can use to create a table from Microsoft Excel data. For more information, see the documentation or Help for your database.

If your database can import text files, you can return the result set to Microsoft Excel, save it as a text file, and then use the commands in your database to import the text file as a table. For information about text conversion, see Microsoft Excel Help.


I can't add, copy, or move a record in my database.

In Microsoft Query 97, you can edit and delete records that are already present in the database, but you can't add new records or change the sequence of the records.

If you need to add or copy a record, open the database directly, and use the commands provided by the database. For information and instructions, see the administrator for your database.

You can change the order of the records in the result set by using the Sort command on the Records menu. Sorting in Microsoft Query does not affect the underlying database, only the result set. For more information, click  [image].


Microsoft Query won't let me edit parameter types for my SQL procedure.

To create parameters in a SQL stored procedure and set the data types:

1	On the File menu, click Execute SQL.

2	In the SQL statement box, create your query using fixed arguments for the procedure, and then click Save.

3	On the View menu, click SQL.

4	In the SQL statement box, replaced each fixed argument with ?

5	Click OK, and then click Parameters on the View menu.

6	To change the data type for a parameter, click the parameter and then click Edit.


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 gray 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/96, 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

Identifies the database a user wants to gain access to and includes the information needed to connect to that data. For example, a Microsoft Access database; a directory that contains a set of Microsoft FoxPro or dBASE files you want to gain access to; a Microsoft SQL Server database, the server on which it resides, and the network used to gain access to that server; or an Open Database Connectivity (ODBC)-enabled data server.


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 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 Microsoft Excel. If the data in the underlying database changes, you can update the external data range in Microsoft 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 what 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) from 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 SQL Server, requires a different driver. ODBC drivers make Microsoft Query flexible, so that 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) which 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 it runs, 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. You can enter your own custom text for the prompt that Microsoft Query displays when the query is run, to describe the parameter it requests. 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

An interactive table that summarizes and analyzes data from existing lists and tables. Use the PivotTable Wizard to specify the list or table you want to use and to define how you want to arrange the data in the PivotTable. After you create a PivotTable, you can reorganize the data by dragging the fields and items.


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 data source.


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 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 PivotTables based on external data. When you save a report template, Microsoft 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 gray 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 alphabetically or numerically. 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.


Add a table to a query

1	Click Add Table(s)  [image].

2	Select the table you want to add.

3	Click Add.

Note   You can add as many tables as you want to the query. When you finish, click Close.


Delete a table from a query

1	Click the table you want to delete.

2	On the Edit menu, click Delete.


Join tables in a query

If your query contains more than one table, Microsoft Query automatically creates a "join" between the two tables when it recognizes a primary key field in one table and a field with the same name in the other table. A join shows how data is related between two tables and determines which records Microsoft Query includes in the result set. If you don't see a join line between the tables, or if you want to specify a join between fields that are not identical, you can create a join.

By default, Microsoft Query creates an inner join, which tells Microsoft Query to select only those records that have the same value in the joined fields in both tables. You can create an outer join, which selects all records from one of the tables whether or not there are matching records in the other table, or you can select an operator that Microsoft Query will use to compare the data in the joined fields.

Because a join indicates which records Microsoft Query selects, you may want to refine the result set of your query by changing the type of join between tables or selecting an operator that Microsoft Query will use to compare the data.

For more information about how joins affect the result set in a query, click  [image].

What do you want to do?

Create a join

Change the type of join between tables in a query

Combine all the data from two tables

Retrieve the records that two tables don't have in common


How joins affect the result set in a query

If your query contains more than one table, Microsoft Query automatically creates a "join" between the two tables when it recognizes a primary key field in one table and a field with the same name in the other table. If two tables are connected by a join line, the line shows Microsoft Query how the data is related. The type of join is important, because it indicates which records Microsoft Query selects and includes in the result set of your query.

An inner join — the default join type — selects only records that have the same value in the joined fields in both tables. Microsoft Query combines the two matching records from each table and displays them as one record in the query result set. If one record doesn't have a matching record in the other table, neither record appears in the result set.

You can create an outer join, which selects records that are not equivalent. When you create an outer join, Microsoft Query 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 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, Microsoft Query displays empty cells where no matching record occurred in the other table.

For more information about creating a join or changing the join type, click  [image].


Create a join

•	Drag a field from one table to the equivalent field (a field of the same data type that contains similar data) in the field list for the other table.

Note   With an inner join, Microsoft Query selects records from both tables or queries only when the values in the joined fields are equal. For more information about join types and how they affect the results of a query, click  [image].


Change the type of join between tables in a query

1	Double-click the join line between the tables.

2	To select an operator that Microsoft Query will use to compare the data in the joined fields, click the operator you want in the Operator box. For more information, click  [image].

3	To create an outer join that uses all of the records from the table on the left, click option 2 under Join Includes. You must select the = operator to create outer joins.

To create an outer join that uses all of the records from the table on the right, click option 3 under Join Includes.

To create an inner join that uses only records in both tables, click option 1 under Join Includes.

4	Click Add.

5	Click Close.

For more information about join types and how they affect the results of a query, click  [image].


Make changes to data in a queried database

In some cases, you can modify, delete, or make other changes to the data results of a query. When you make changes to the results of the query, Microsoft Query also changes the data in the underlying table that you used for the query.

You can't make changes to data in a queried database in the following situations:

•	The data source is protected from changes or doesn't allow changes.

•	The query contains more than one table.

•	The query contains totals or custom expressions.

•	The field you're trying to change is locked or disabled, or the field is in a record that is locked.

•	The data you're trying to enter is not a valid type for the database. Depending on the database you're trying to change, you may need to enter a specific value in a field.

•	Your administrator has made the modification features on the database unavailable.

What do you want to do?

Change data in a queried database

Delete data from a queried database

Undo changes to data in a queried database


Change data in a queried database

1	In Microsoft Query, make sure the Allow Editing command is checked on the Records menu.

In some instances, Microsoft Query will not allow you to change the data. If the Allow Editing command appears dimmed or Microsoft Query displays a message when you click the Allow Editing command, you can't change the data. For more information, click  [image].

2	In the result set, click the field you want to change.

3	Change the information the way you want, and then press ENTER.

Note   If you make a mistake, you can reverse the most recent change by clicking Undo on the Edit menu. You must undo your change before you switch to another record or window.


Delete data from a queried database

Caution   Once you delete a record from a database, it is permanently deleted from both the result set and the database.

1	On the Records menu, make sure the Allow Editing command is checked.

In some instances, Microsoft Query will not allow you to change the data. If the Allow Editing command appears dimmed or Microsoft Query displays a message when you click the Allow Editing command, you can't change the data. For more information, click  [image].

2	Select the entire record you want to delete by clicking the square to the left of the record.

3	On the Edit menu, click Delete.


Undo changes to data in a queried database

•	On the Edit menu, click Undo.

Note   You can reverse the most recent change you made to the data. You must undo your change before you switch to another record or window.


Format data in Microsoft Query

Microsoft Query saves the changes when you save your query. If you format data in Microsoft Query, the formatting is not displayed when you return the result set to Microsoft Excel.

What do you want to do?

Change a field column heading so that it's more descriptive

Change the width of a column or the height of rows

Change the font, style, or size of text in a result set

Hide or display a field in a result set

Change the layout of a result set


Change a field column heading so that it's more descriptive

1	Double-click the column heading of the field you want to change.

2	In the Column Heading box, type the heading you want.


Change the width of a column or the height of rows

•	To change the width of a column, point to the right border of the column heading, and then drag the border until the column is the width you want.

Tip   To quickly adjust the column width to the width of the largest value in the column, double-click the right border of the column heading.

•	To change the height of rows, point to the lower border of any row heading, and then drag the border until the rows are the height you want.

Note   Changing row height affects all the rows in the result set.


Hide or display a field in a result set

1	Click the column heading of the field you want to hide.

2	On the Format menu, click Hide Columns.

To display a hidden column, click Show Columns on the Format menu, select the column you want, and then click Show.


Change the font, style, or size of text in a result set

1	On the Format menu, click Font.

2	Make the changes you want.


Change the layout of a result set

When you insert fields or change the order of fields, your layout changes are retained while you work in Microsoft Query and when you return the result set to Microsoft Excel.

What do you want to do?

Change the order of fields in a result set

Insert a field into a result set


Change the order of fields in a result set

1	Click the column heading of the field you want to move.

2	Move the pointer over the column heading, and then drag the column to the place you want.


Insert a field into a result set

1	Click the column heading of the field to the right of where you want to insert the new field.

2	On the Records menu, click Insert Column.

3	In the Field box, select the column you want to insert.

If you want a more descriptive heading for the column, type it in the Column Heading box.

4	Click Insert.


Open a query

1	Click Open Query  [image], click the query file you want to open, and then click Open.

A query file has a .dqy or .qry file name extension.

2	If prompted, enter your logon name, password, and any other information required for your data source.

3	If prompted, enter parameter values for the query, and then click OK. If you do not know what the valid parameters are, contact the person who created the query.


Create a new query

You have several options for constructing a query and retrieving the data you want from an external data source in Microsoft Query:

•	You can use the Query Wizard to create a simple query.

•	You can use advanced features in Microsoft Query to create a more complex query.

•	You can create a parameter query in Microsoft Query that prompts for criteria when you run it.

What do you want to do?

Learn about guidelines for constructing a query

Create a query by using the Query Wizard

Create a more complex query in Microsoft Query

Create a parameter query


Guidelines for constructing a query

With Microsoft Query, you have several options for retrieving external data. To take advantage of these options and ensure that you get the results you're looking for, keep the following guidelines in mind:

•	Use the Query Wizard when you want to create a simple query. With the Query Wizard, you can select the tables and fields you want to include. An inner join will be created automatically when the Query Wizard recognizes a primary key field in one table and a field with the same name in the other table. In addition, you can sort and do simple filtering in the Query Wizard. At the end of the Query Wizard, you can choose to return the data to Microsoft Excel or further refine the query in Microsoft Query. After you create the query, you can run it in Microsoft Excel or Microsoft Query.

•	Create a query in Microsoft Query when you want to create a more complex query. This method gives you the most control, because you can narrow your result set either by creating an inner join or outer join or by using criteria and expressions. Expressions are calculations you can perform by using operators, functions, field names, and constants. You can do more complex filtering by using criteria. You can view the result set in Microsoft Query. When you're sure the result set contains the data you want, you can return it to Microsoft Excel. After you create the query, you can run it in Microsoft Excel or Microsoft Query.

•	A parameter query is a query that prompts for criteria when you run it. You create a parameter query only in Microsoft Query, not in the Query Wizard. The criteria you enter are used to retrieve data from the tables in your query. After you create the query, you can run it in Microsoft Excel or Microsoft Query.


Create a query by using the Query Wizard

1	Click New Query  [image].

2	On the Databases tab, click the database from which you want to retrieve the data.

If you need to set up a new data source, double-click New Data Source, and then specify the data source.

How?

3	Make sure the Use the Query Wizard to create/edit queries check box is selected, and then click OK.

4	Follow the instructions in the Query Wizard.

5	To return the data directly to Microsoft Excel, click Return Data to Microsoft Excel, click Finish, and then click where you want to put the external data.

How?

To refine the query further, click View data or edit query in Microsoft Query, click Finish, and then make the changes you want.

How?

If you choose to edit the query further in Microsoft Query, the result set is displayed in the Data pane. If you want, you can return the result set to Microsoft Excel at a later time. For more information, click  [image].

Note   You can cancel a query that's taking a long time to retrieve data by pressing ESC.


Create a more complex query in Microsoft Query

1	Click New Query  [image].

2	On the Databases tab, click the database from which you want to retrieve the data, or create a new data source.

How?

3	Clear the Use the Query Wizard to create/edit queries check box, and then click OK.

4	Add the tables you want.

How?

5	Add the fields you want to appear in the result set.

How?

If you want to narrow your result set to include specific records, enter the criteria to filter the records.

How?

If you want to create a parameter query that prompts for criteria when you run it, specify the criteria.

How?

You can create an inner join or outer join between your tables to change the result set.

How?

Notes

•	When you finish retrieving the data you want, you can return the result set to Microsoft Excel. For more information, click  [image].

•	You can cancel a query that's taking a long time to retrieve data by pressing ESC.

•	If you want to wait to retrieve data until you've added all the fields to your result set, make sure that the Auto Query  [image]  button is not pressed in. When you're ready to run your query, click Query Now  [image].


Create a parameter query

1	Create a query that includes the tables and fields for the records you want.

How?

2	Make sure Auto Query  [image]  is not pressed in.

3	Make sure Show/Hide Criteria  [image]  is pressed in to display the Criteria pane.

4	Click the first cell in the Criteria Field row, and then click the arrow in the cell to select a field from the list that you want to use as the parameter for the query.

5	Click the first cell in the Value row. Type [ (a left square bracket) followed by the text for the prompt you want Microsoft Query to display when the query is run, and then type ] (a right square bracket). An example is shown in the following illustration.

The text of the prompt must be different from the field name, although it can include the field name.

To create more than one parameter for the query, repeat steps 4 and 5 for each parameter you want to add in the next available column in the Criteria pane.

For more information about criteria you can use to refine a query, click  [image].

6	Press ENTER.

7	To run the query, click Query Now  [image].

Note   When you finish retrieving the data, you can return the result set to Microsoft Excel. For more information, click  [image].


Specify a source to retrieve data from

1	If the Choose Data Source dialog box is not displayed, click New Query  [image].

2	On the Databases tab, double-click New Data Source.

3	In box number 1, type a name to identify the data source.

4	Click box number 2, and click a driver for the type of database you are using as your data source.

If you do not see the database you want, you may need to install the ODBC driver for your data source.

How?

5	Click Connect.

Depending on the database you selected for your data source, the dialog box contains different information you must provide in order to connect to the data source. You may be asked to supply a logon name, a password, the version of the database you're using, the database location, or other information specific to the type of database you selected. When you finish entering the required information, click OK.

If you don't want to type your logon name and password when you use the data source, select the Save my UserID and Password in the Data Source definition check box. If the check box is unavailable, check with the database administrator to determine whether this option has been disabled.

If you want a particular table in the database to be displayed automatically in the Query Wizard, click box 4, and click the table you want.

6	Click OK.

Your data source is now set up.


Install an ODBC driver so you can access an external data source

1	Close all open programs.

2	On the Start menu, point to Settings and click Control Panel.

3	Double-click Add/Remove Programs.

4	Click the Install/Uninstall tab, click Microsoft Office or Microsoft Excel, and then click Add/Remove.

5	When the Setup dialog box is displayed, click Add/Remove.

6	Click Data Access and ActiveX Controls, and then click Change Option.

7	Click Database Drivers, and then click Change Option.

8	Click the ODBC drivers you want to install.

To install all of the ODBC drivers, click Select All.

9	Click OK.

10	Click Continue, and follow the directions to finish Setup.


Return a result set to Microsoft Excel

1	If the Returning External Data to Microsoft Excel dialog box is not displayed, click Return Data  [image],  and if prompted, enter any parameter values required by the query.

2	Click Properties to control formatting and layout options for how the external data range is brought into Microsoft Excel. For more information, see Microsoft Excel Help.

3	To put the result set on the selected worksheet, click Existing worksheet. On your worksheet, click the cell where you want to place the upper-left corner of the result set, and then click OK.

To put the result set on a new worksheet, click New worksheet, and then click OK. Microsoft Excel adds a new worksheet to your workbook and automatically puts the result set in the upper-left corner of the new worksheet.

To use the external data in a PivotTable, click PivotTable Report, and then click OK.

Note   You can check the status of a query that's taking a long time to return data. Click Refresh Status  [image]  on the External Data toolbar. To cancel returning data, click Stop Refresh.


Add or remove fields

•	To add a single field to the result set, in the Table pane, double-click the field you want to add.

•	To remove a field from the result set, click the column heading of the field you want to delete in the Data pane, and then click Remove Column on the Records menu.

Tip   To add all the fields from a table to the result set, double-click the asterisk. You might need to scroll to the top of the table to see the asterisk.


Run a query automatically, or wait to run the query at a later time

Microsoft Query automatically runs a query and refreshes the data in the result set each time you add a field to the Data pane or add or change criteria in the Criteria pane. If your query contains a large number of records, you might want to wait to run the query until you've finished making changes to the query.

•	To run a query automatically each time you add new criteria or a field, make sure that the Auto Query  [image]  button is pressed in.

•	To wait to run a query until after you've finished adding new criteria or fields, make sure that the Auto Query  [image]  button is not pressed in. When you're ready to run the query, click Query Now  [image].


Limit the records you retrieve by using criteria

You can retrieve specific records by using criteria. For example, if you want to view only suppliers from France, you can specify that the Country field must contain France. Microsoft Query uses your criteria to retrieve the records you want and display them in the result set.

What do you want to do?

Learn about how to use criteria

Retrieve records that match a specific value

Specify what records to retrieve by using criteria

See examples of expressions you can use as criteria


About using criteria

When you select a criteria field, make sure that you use the field from the correct table. For example, if you want to find all the suppliers for a particular country, select the Country field from the Supplier table rather than from the Customer table.

You add criteria in the Criteria pane in Microsoft Query.

You can specify criteria to retrieve records as follows:

•	You can create a calculation and then use the calculation as the criteria for selecting records. For example, you can retrieve all records for orders greater than $500 by specifying that the price field multiplied by the quantity field must be greater than $500. To use this criteria, you would type Unit_Price*Quantity in the Criteria Field box and then type >500 in the Value box. To see examples of expressions you can use as criteria, click  [image].

•	You can find all the records that fall into a certain range. For example, you can retrieve all records for orders placed on June 5, 1996, through June 23, 1996. To use this criteria, you would click Order_Date in the Criteria Field box and then type >=6/5/96 And <=6/23/96 in the Value box.

•	You can find all the records with values that contain characters you specify. For example, you can find all the records for suppliers whose phone numbers begin with the area code (111). To use this criteria, you would click Phone in the Criteria Field box and then type Like  '(111)%' in the Value box. You must place the text you want to find within single quotation marks.

•	You can find all records with values that meet one criteria or another criteria. For example, you can find all records for suppliers who provide dairy products, or you can find all records for suppliers from Norway, regardless of the products they supply. To select these records, you would type two sets of criteria in the Criteria pane. First you would click Country in the Criteria Field box and then type Norway in the Value box. Then you would click Category in the next Criteria Field box and type =Dairy in the corresponding Value box.

•	You can find all the records with values that meet multiple criteria. For example, you can find all the records for suppliers who provide dairy products and who are also from Norway or Italy. To select these records, you would type two sets of criteria in the Criteria pane. First you would click Country in the Criteria Field box and then type Norway Or Italy in the Value box. Then you would click Category in the next Criteria Field box and type =Dairy in the corresponding Value box.

•	You can find records that don't match a value. For example, you can find all the records for suppliers who are located outside of the United States. To use this criteria, you would click Country in the Criteria Field box and then type Not USA in the Value box.

•	You can find records that contain missing or incomplete values. For example, you can find all the records for employees who are missing a value for their supervisor's name. To use this criteria, you would click Reports_To in the Criteria Field box and then type Is Null in the Value box.

•	You can find records that contain any of several values. For example, you can find all the records for suppliers located in Germany, France, or the United States. To use this criteria, you would click Country in the Criteria Field box and then type Germany Or France Or USA in the Value box.


Specify what records to retrieve by using criteria

1	Click Show/Hide Criteria  [image].

Microsoft Query displays the Criteria pane.

2	In the Criteria pane, click the first cell of the Criteria Field row, and then click the field you want to apply criteria to.

If your query uses multiple tables with similar field names, make sure to click the field you want from the correct table.

3	In the Value row, click the cell directly under the field you selected in the Criteria Field row.

4	Type the criteria you want Microsoft Query to use to select records, and then press ENTER.

How?

5	If you haven't already done so, add the fields you want to appear in the result set.

How?

To display records from different tables, make sure that you've added those tables to the Table pane in your query. For more information about adding tables, click  [image].

Notes

•	You can further limit your result set by adding additional criteria to the Criteria pane. For example, you can find all the records for suppliers who provide dairy products and who are also from Norway or Italy. To select these records, you would type two sets of criteria in the Criteria pane. First click Country in the Criteria Field box, and then type Norway Or Italy in the Value box. Then click Category in the next Criteria Field box, and type =Dairy in the corresponding Value box.

•	If the value you want to use for criteria already appears in the result set, you can copy it to the Criteria pane. In the result set, click the value you want to copy, click Copy on the Edit menu, click the cell in the Value row where you want to paste the value, and then click Paste on the Edit menu.

•	To avoid a long wait while data in each field is being returned, you can wait to retrieve data until after you've added all the fields to your result set. To wait to retrieve data, make sure the Auto Query  [image]  button is not pressed in. When you're ready to run your query, click Query Now  [image].

•	You can cancel a query that's taking a long time to retrieve data by pressing ESC.


Retrieve records that match a specific value

1	Make sure the Auto Query  [image]  button is pressed in.

2	Add the field for which you want to specify criteria to the Data pane.

How?

For example, if you want to retrieve records that have France in the Country field, add the Country field to the Data pane.

3	In the result set, click a cell that contains the value you want to match.

4	Click Criteria Equals  [image].


Restore the full result set by removing criteria

If your query contains many records, you can save time by waiting to refresh the result set until you've removed all the criteria. To wait to refresh the result set, make sure the Auto Query  [image]  button is not pressed in.

1	In the Criteria pane, click the heading of the column that contains the criteria you want to delete.

2	Press DELETE.

Repeat steps 1 and 2 for each set of criteria you want to remove.

To remove all criteria at one time, click Remove All Criteria on the Criteria menu.

3	When you finish removing criteria, click Query Now  [image]  to restore the full result set.


Examples of expressions

An expression is a calculation that uses a combination of operators, functions, field names, and constants.

You can use an expression as criteria to select records. To use an expression in the Criteria pane, you select the field that contains the values you want to evaluate in the Criteria Field and then type the operator and value you want in the Value box. When you select a criteria field, make sure you use the field from the correct table. For example, if you want to find all the suppliers for a particular country, select the Country field from the Supplier table rather than from the Customer table. For more information about criteria, click  [image].

What do you want to do?

See examples of expressions that use arithmetic operators

See examples of expressions that use comparison operators

See examples of expressions that use logical operators

See examples of expressions that use other types of operators


Examples of expressions that use arithmetic operators

Arithmetic operators perform basic mathematical operations, such as addition, subtraction, multiplication and division, and produce numeric results. You can use arithmetic operators in an expression in the Criteria pane or in a calculated field.

Arithmetic operator	Meaning	Example	Syntax
+ (plus sign)	Addition	Total payment due from a customer	Order_Amt+Freight
– (minus sign)	Subtraction	Total price of an order, including any discount	Order_Amt-Discount
* (asterisk)	Multiplication	Total order amount for a specific product	Unit_Price*Quantity
/ (forward slash)	Division	Price per unit	Order_Amt/Quantity


Examples of expressions that use comparison operators

Comparison operators are used in expressions that are used as criteria to select records and limit a result set.

Comparison operator	Meaning	Example	In the  Criteria Field, click	In the Value box, type
= (equal sign)	Equal to	Finds records for customers in Germany	Country	=Germany
<> (not equal to sign)	Not equal to	Finds records for suppliers outside the United States	Country	<>USA
> (greater than sign)	Greater than	Finds records for orders placed after a specific date	Order_Date	>6/30/97
< (less than sign)	Less than	Finds records for orders placed before a specific date	Order_Date	<6/30/97
>= (greater than or equal to sign)	Greater than or equal to	Finds records for orders placed on or after a specific date	Order_Date	>=6/30/97
<= (less than or equal to sign)	Less than or equal to	Finds records for orders placed on or before a specific date	Order_Date	<=6/30/97


Examples of expressions that use logical operators

Logical operators compare two values.

Logical operator	Meaning	Example	In the Criteria Field, click	In the Value box, type
And	This value and another value within the same field must be true for records in the result set.	Finds records for orders placed within a certain range of dates	Order_Date	>6/5/97 And <6/23/97
Or	This criterion or another criterion must be true for records in the result set.	Finds records for suppliers in either of two locations	Country	USA Or Brazil
Not	This criterion must not be true for records in the result set.	Finds records for suppliers not located in a specific country	Country	Not Canada


Examples of expressions that use other types of operators

The following table lists operators you can use to select a range of values, find values that are equal to any of several values, determine whether a record does or does not contain a value in a specified field, or find records by using a wildcard character.

Operator	Meaning	Example	In the Criteria Field, click	In the Value box, type
Between	Shows a range of values between and including the two endpoints	Finds records for order amounts between $1,000 and $3,000	Order_Amt	Between 1000 And 3000
In	Determines whether a value is equal to any of several values in a list	Finds records for customers in Portland, Seattle, and Denver	City	In('Portland', 'Seattle', 'Denver')
Is Null	Determines whether a record has no value in the specified field	Finds employee records that don't have an entry for the employee's supervisor	Reports_To	Is Null
Is Not Null	Determines whether a record has a value in the specified field	Finds employee records that have an entry for the employee's supervisor	Reports_To	Is Not Null
Like	Uses Like and the wildcard character % to compare values	Finds records for employees whose last names begin with the letters "Sm", such as Smith and Small	Last_Name	Like 'Sm%'


Create a calculated field

You can display the results of a calculation you specify by creating a new field called a calculated field. You can create a calculated field by typing your own expression, which is a calculation that uses a combination of operators, functions, field names, and constants. You can also use a built-in function to calculate totals in a field.

What do you want to do?

Create a calculated field by using an expression

Create a calculated field by using a built-in function


Create a calculated field by using an expression

1	In the Data pane, click a blank column heading.

2	Type the expression for the calculation, and then press ENTER. Do not begin the expression with an equal sign (=).

For example, if employees receive a commission that equals 15 percent of their total sales, you can calculate each employee's commission by adding the sales for each employee and then multiplying the total by 0.15.

To see examples of expressions that use arithmetic operators, click  [image].


Create a calculated field by using a built-in function

1	Add a field that contains the values you want to total.

How?

2	Click anywhere in the field you want to total, and then click Cycle through Totals  [image].

3	Continue to click Cycle through Totals until Microsoft Query displays the type of total you want.

Each time you click Cycle through Totals, Microsoft Query calculates and displays a different type of total (the function used is indicated in the column heading). To see a description of the functions you can use to create totals in a calculated field, click  [image].

4	After you select the function that generates the type of total you want, click Query Now  [image]  to display the results of the calculated field.

Notes

•	To remove a calculation from a field, click Cycle through Totals until the column heading contains the original field name.

•	If your query contains many records, you can save time by waiting to display the results of the calculated field until you select the function you want. Make sure the Auto Query  [image]  button is not pressed in.


Functions you can use in a calculated field

A function 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 supports the following functions. The actual functions available for a data source depend on the ODBC driver for the data source. To see what functions are available for your data source, click a numeric field, and then click Cycle through Totals  [image].

To return this value	Use
The average of a set of values in a field	AVG
The total number of records	COUNT
The maximum of a set of values in a field	MAX
The minimum of a set of values in a field	MIN
The sum of a set of values in a field	SUM


Save a query

You don't have to save a query to return data to Microsoft Excel. Save a query when you want to preserve formatting or when you want to use the same query later or in a different workbook. For more information about returning data to Microsoft Excel, click  [image].

1	Click Save File  [image].

2	In the File name box, type the name you want.

3	In the Save as type box, Microsoft Query automatically selects Query Files (*.dqy). If you want to use the query in an earlier version of Microsoft Excel or Microsoft Query, click Query Files (*.qry).

A parameter query can be saved only in *.dqy format and can't be used in an earlier version of Microsoft Excel or Microsoft Query.

4	In the Save in box, click the folder where you want to store the query.

5	Click Save.

Notes

•	When you save a query file, Microsoft Query does not save the current result set displayed in the Data pane. The result set is automatically refreshed, before the Save As dialog box is displayed, even when the Auto Query  [image]  button is not pressed in.

•	When you save a query in the default location, the file name of the query appears on the Queries tab when you open a new query in Microsoft Query or when you choose Get External Data in Microsoft Excel.


Run, view, or edit an SQL command, procedure, or statement

If you know Structured Query Language (SQL), you can use SQL to edit a query's SQL SELECT statement. For more information about SQL, see the documentation for your database.

1	Create a new query. Do not return the results to Microsoft Excel.

How?

2	Click View SQL  [image].

Microsoft Query displays the SQL dialog box with the SQL SELECT statement that corresponds to the query.

3	Type any changes you want to make into the SQL statement, and then click OK.

To add a new line in the SQL statement box, press CTRL+ENTER.

Notes

•	To change or replace the SQL statement, use the same SQL syntax that Microsoft Query uses or the syntax your data source supports. Microsoft Query tries to interpret the syntax you use and displays both the query's design and its result set.

•	If Microsoft Query can't display the query's design because it can't interpret a portion of the syntax you used, the result set is still displayed. However, you can change only the result set's formatting, such as the display font or column width. You can't edit data, and you must make any change to the query definition, such as adding or removing fields, in the SQL dialog box.


Execute an SQL statement or procedure

If you know Structured Query Language (SQL), you can use SQL to execute an SQL SELECT statement. For more information about SQL, see the documentation for your database.

1	On the File menu, click Execute SQL.

To execute an SQL statement on a table in another data source, click Data Sources, click the data source you want, and then click OK. You can specify a new data source.

How?

2	In the SQL statement box, type the SQL statement you want to run.

If you have an existing SQL statement you want to run, click Open, click the SQL file, and then click Open.

To run an SQL procedure stored in your external database, click Procedures, click the procedure you want in the Query box, and then click Paste to copy the procedure to the SQL statement box.

3	Click Execute.

Microsoft Query runs the SQL statement. If the query is taking a long time to return data, you can cancel the query by pressing ESC.

To save the SQL statement, click Save.

Notes

•	To change or replace the SQL statement, use the same SQL syntax that Microsoft Query uses or the syntax your data source supports. Microsoft Query tries to interpret the syntax you use and displays both the query's design and its result set.

•	If Microsoft Query can't display the query's design because it can't interpret a portion of the syntax you used, the result set is still displayed. However, you can change only the result set's formatting, such as the display font or column width. You can't edit data, and you must make any change to the query definition, such as adding or removing fields, in the SQL dialog box.

•	If the query returns more than one result set, Microsoft Query displays the first one in the Data pane. Any other result sets are not displayed.

•	If the query runs successfully but does not return a result set, Microsoft Query displays a message that the statement was run successfully. If the query affected rows in the underlying tables, the message also specifies how many rows were affected.

•	If the query didn't run successfully, Microsoft Query displays the SQL error message that corresponds to the problem. To display the Execute SQL dialog box and correct the statement, click OK.


Sort records in a query

1	Make sure the Auto Query  [image]  button is not pressed in.

2	In the result set, click in the field you want to sort.

3	To sort records in an ascending sort order, click Sort Ascending  [image].

To sort records in a descending sort order, click Sort Descending  [image].

Notes

•	To sort multiple columns at one time, arrange the columns from left to right in the Data pane in the order you want to sort them. Drag across the column headings to select the columns you want to include in the sort, and then click Sort Ascending or Sort Descending.

•	To return the records to their original order, click Sort on the Records menu. In the Sorts in Query box, click the field for which you want to remove the sort order, and then click Remove.

•	If you change the order of columns in the result set after you sort the records, the sort order is not affected.


View different fields in a query

1	On the Format menu, click Show Columns.

2	To temporarily prevent a column from appearing in the Data pane, click the column, and then click Hide.

To redisplay a column, click the column name, and then click Show.

Note   Hidden fields are included when you return the result set to Microsoft Excel. If you don't want hidden fields to be returned to Microsoft Excel, delete the fields from the Data pane before you return the result set to Microsoft Excel.


Use Dynamic Data Exchange (DDE) with Microsoft Query

Microsoft Query supports dynamic data exchange (DDE), a form of communication that allows two programs to exchange data with each other. You can use DDE methods in Visual Basic for Applications to communicate and send data back and forth between Microsoft Query and another program, such as Microsoft Excel.

What do you want to do?

Learn more about how to use DDE

Open a DDE channel

Execute DDE commands

Learn about the available DDE commands

Request status and other information

Retrieve data

Exit Microsoft Query from DDE

Close a DDE channel

Use Microsoft Query with other programs


Use DDE

Two programs exchange information by engaging in a DDE conversation on a DDE channel. A program can conduct more than one conversation with Microsoft Query, but each conversation occurs on a different channel.

The program that initiates the conversation is called the destination program; the program that responds to the destination program is called the source program. Microsoft Query is always the source program in a DDE conversation. This means that other programs are always the ones to initiate a DDE conversation with Microsoft Query.

Note   Not all programs support DDE. To find out if your program does, consult your program's documentation.

DDE Channels

There are two types of DDE channels: system channels and query channels.

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

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

How DDE Works

You can compare a DDE conversation to a typical conversation between two people. For example, in a job interview, the interviewer may ask the applicant a series of interview questions. The interviewer (the destination) initiates and requests information from the applicant (the source) and records the information on a piece of paper (the worksheet or document). Of course, the applicant can also ask questions of the interviewer and establish a two-way conversation.

Similarly, the participating programs in a DDE conversation pass information back and forth. For example, to use DDE to communicate with Microsoft Query, a Visual Basic for Applications procedure might perform these steps:

1	The procedure opens a channel by using the DDEInitiate method.

2	Optionally, the procedure sends information, such as what the available data sources or current connections are, to Microsoft Query.

3	The procedure activates Microsoft Query. This step enables Microsoft Query to accept commands. At this point, either the user or the procedure can build or modify a query. The procedure can send commands directly to Microsoft Query by using the DDEExecute method.

4	After the user or procedure exits Microsoft Query, control returns to the destination program. The procedure can retrieve query data and send it back to the destination program by using the DDERequest method, or the procedure can send data to Microsoft Query.

5	The procedure closes the channel by using DDETerminate.

For details about specific DDE methods in Visual Basic for Applications, see Visual Basic Help.


Open a DDE channel

To open a DDE channel, your procedure uses the DDEInitiate method. The DDEInitiate method has two arguments: the name of the source program and the "subject" of the DDE conversation (called the topic). The program name for Microsoft Query is "MSQuery."

Microsoft Query topics can be one of the following.

Topic	Description
query_window_name	The name of an open query window. The window name can be specified only if Microsoft Query is running and a window is open.
System	A general topic that allows the destination program to link to Microsoft Query itself rather than to a query window, a file, or an SQL SELECT statement.
When Microsoft Query receives a request for a DDE conversation about a topic it recognizes, it responds and opens a channel. The DDEInitiate method returns a DDE channel number, which can be assigned to a variable and used for subsequent operations on this channel.

To open multiple windows on a single channel, use the System topic. When you use the System topic to open a DDE channel, you can activate only windows that were opened on that DDE channel.


Execute DDE commands

You can send commands to Microsoft Query using DDEExecute. Commands are specific to Microsoft Query and may or may not correspond to Microsoft Query menu commands. For example, the Open and SaveAs commands perform the same functions as equivalent menu commands in Microsoft Query, but the ODBCLogon and UserControl commands do not. For a list of the available commands, click  [image].

The DDEExecute statement has two arguments.

Argument	Description
channel	The DDE channel value returned by DDEInitiate.
command	The command to send to Microsoft Query.
To let a user build queries in Microsoft Query, you execute the UserControl command. The following example sets My Exit Command as the Exit command on the File menu in Windows or the Quit command on the File menu on the Macintosh.

DDEExecute Chan,"[UserControl('&My Exit Command',3,True)]"

The DDE channel value has been previously assigned to Chan, and the command being sent to Microsoft Query is UserControl. Notice that the UserControl command and its parameters are enclosed in double quotation marks ( " " ) and square brackets ( [ ] ).


DDE commands

Click a command to display reference information.

Activate

AddParameters

AllowEdit

BuildODBC

BuildSQL

Close

Command

EnableParameters

Exit

Fetch and Fetch.Advise

Fetch.Unadvise

Logoff

Logon

LogonNow

ODBCLogon

Open

QueryNow

SaveAs

SetParameterName

SetQueryName

SetWindowState

UserControl

UserControl2

ViewPane


Request status and other information

Once you've opened a DDE channel to Microsoft Query, you can return a result set or other information using DDERequest.

The DDERequest method has two arguments.

Argument	Description
channel	The DDE channel value returned by DDEInitiate.
request_item	The data to be returned. This data can be the contents of a field or range of fields in a query window, or other types of information, such as the number of records in a query window.
Request items can be requested on both the query channel and the system channel. On the query channel, you can return information only about the query, such as the number of rows or columns, or the query definition. On the system channel, you can return general information about Microsoft Query, such as the current connections, topic names, and program status, as well as information specific to the active query.

Request items for a query channel or the active query on a system channel can be one of the following.

Request item	Information returned
ColumnTitles	An array of column titles. The column title is the same as the field name, unless the user has modified the column title in Microsoft Query.
ConnectionString	An array of connection string segments (each of which can be up to 255 characters long) that makes up the connection string used to connect to the active query window. If the query was created from a File DSN, this item returns a DSN-less connection string.
ConnectionString/n	An array of connection string segments (each of which can be up to n characters long) that makes up the connection string used to connect to the active query window. If the query was created from a File DSN, this item returns a DSN-less connection string.
DataSourceName	The data source name (DSN) used by the active query.
ErrorText	The error text, if any, from the most recently executed SQL statement. This item can be requested multiple times to retrieve all the errors that have occurred. After all errors are retrieved, the item returns an empty string. The item returns a null string if the most recently executed SQL statement was successful, even if previously executed SQL statements were not successful.
FieldDef	An array of values that describes the expressions and data types of the columns (fields) in the query window. Each row of the array describes a field in the query. The array returns the following data in columnar format: the field name, the field Q+E data type, the field width, the field precision, and the field SQL data type.
GetUniqueItems	An array of the unique items in a given column of the query results. The actual request item must be the zero-based column index of the column appended to "GetUniqueItems" (for example, the unique items in the second column are returned by "GetUniqueItems1"). You cannot request the unique items for a column not included in the query. This item returns the same values as a SELECT DISTINCT statement for the requested column, with any joins and criteria clauses in effect. Any criteria clauses that use parameters are not included.
NameSeparator	The single character used as the ODBC qualifier name separator.
NumCols	The number of columns (fields) in the query.
NumRows	The number of rows (records) in the query.
ODBCSQLStatement	An array of SQL segments (each of which can be up to 255 characters long) that makes up the ODBC SQL statement for the query. With this string, you can bypass Microsoft Query and send an ODBC SQL statement directly to ODBC for processing.
ODBCSQLStatement/n	An array of SQL segments (each of which can be up to n characters long) that makes up the ODBC SQL statement for the query. You can use this request item to store an ODBC SQL statement in several smaller segments.
ParameterNames	An array of parameters in the query. An empty array is returned if no parameters exist. This item can be requested at any time, even if parameters are disabled for the query.
Query	The name of the query.
QueryDefinition	An array of query definition segments (each of which can be up to 255 characters long) that makes up the definition of the query (an SQL statement), as defined by Microsoft Query. This item can be retrieved and saved for future queries.
QueryDefinition/n	An array of query definition segments (each of which can be up to n characters long) that makes up the query definition of the query (an SQL statement).
Recest	A rough estimate of how many rows can be retrieved at a time.
TierType	A single digit specifying the type of data source: 1 indicates a file that can contain only a single table; 2 indicates a file for a database containing one or more tables; 3 indicates the data can't be browsed. This request item is used if the program needs to provide an Open dialog box for browsing the data on a disk.
When you use the above request items on the system channel, information is returned for the query window most recently used in a DDE operation.

For the system channel, in addition to the above request items, you can also request the following items.

Request item	Information returned
Sources	All current data source connections (DSN only).
Logon	All remote databases you can connect to (DSN entries from the registry).
Logoff	All currently connected remote databases (DSN only).
Tables/source/user/database	All tables for the specified DSN connection, user, and database. Note that the source must be a valid DSN.
Users/source/database	All users for the specified DSN connection and database. Note that the source must be a valid DSN.
Database/source	All databases for the specified DSN connection. Note that the source must be a valid DSN.
Username/source/database	The user name for the specified DSN connection and database. Note that the source must be a valid DSN.
Topics	The names of the topics open on the system channel, along with System.
Status	The status of the program, which can be Ready or Busy. Ready means that the program can have a DDE conversation with Microsoft Query. Busy means that Microsoft Query isn't ready to have a DDE conversation.
This example inserts the value returned by the NumRows request item into cell A10 in Microsoft Excel.

ActiveWorksheet.Range("A10").Value = DDERequest(chan, "NumRows")


Retrieve data

Whereas DDERequest can return data to a program, the Fetch and Fetch.Advise commands deserve special mention because they retrieve data from Microsoft Query and send it directly to a worksheet or document. Fetch and Fetch.Advise are used as the command argument to DDEExecute.

Fetch.Advise is similar to Fetch, except that the channel created by the first retrieval isn't closed immediately. Microsoft Query continues to send any changes to the data until the original DDE channel is closed or until Microsoft Query receives a Fetch.Unadvise command.


Exit Microsoft Query from DDE

To exit Microsoft Query, a program can use the Exit command, rather than have the user choose Exit or Quit from the File menu.

Command	Description
Exit (False)	Exits Microsoft Query only if no queries are open on other channels
Exit (True)	Exits Microsoft Query regardless of whether other queries are open


Close a DDE channel

To close a DDE channel, use DDETerminate. This method has only one argument — the channel number of the DDE channel to be closed. All queries that are opened on this channel are closed automatically when you use DDETerminate, and you are logged off from all connected data sources.

An error is returned if the channel number you use is invalid.


Use Microsoft Query with other programs

Typically, other programs use Microsoft Query to accomplish three main tasks:

•	To create a new query

•	To update existing data

•	To modify the query definition or data

Microsoft Query as a data query tool for users

Using DDE, you can have Microsoft Query function as a tool for retrieving data. Once your macro or program opens a DDE channel, it can activate Microsoft Query and specify which command replaces Exit on the File menu in Windows or Quit on the File menu on the Macintosh, what program state (minimized [Windows only], maximized, or restored) Microsoft Query is in, and whether to display a series of dialog boxes.

A user can then create, open, or edit a query as if Microsoft Query were running as a standalone program. When the user has finished building the query, the user can click the custom Exit command or click the Exit button on the toolbar.

Once control returns to the destination program (the document, worksheet, or other object) data can be requested from Microsoft Query. For example, the user may request the number of rows or columns in the query, the SQL string defining the query, the connection string for the data source, or the data source name. Data can be returned to the destination program before the DDE channel is closed and the macro or program ends.

Update retrieved data

After you have executed a query and returned a result set, you may later want to update the data with whatever is then current in the data source. Using DDE, you can update data without input from the user: A program can send an SQL statement to Microsoft Query, which can execute the query and then return the result set to the destination program.

Modify a query definition or data

Another way a program can use Microsoft Query is to allow a user to modify an existing query definition in Microsoft Query or to edit data in a data source.

A program starts this process by opening a DDE channel, connecting to a data source, and sending an SQL string (saved from the previous query) to Microsoft Query to build a new query. Once the program has opened the new query, user control begins, and the user can modify the query or edit the data as needed. Once the user exits Microsoft Query, the data is returned to the program, the new query definition is saved, and the query is closed.

For example, a user working in Word could choose a custom command called "Get Sales Figures" to start the corresponding macro. Without the user being aware of it, this macro could then start Microsoft Query, build a query, and then return the sales data to the Word document.


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, the query window must have already been opened 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.


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.


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


Closes this dialog box without saving any changes you've made.


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 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. For more information about creating, editing, or viewing SQL statements in Microsoft Query, click  [image].


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 name of the active table.


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.


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.


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.


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


Removes all columns from the Columns in your query box.


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.


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.