INF: Querying Questions & Answers
  
PSS ID Number: Q104390
Article last modified on 02-07-1995
 
1.00 1.10
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access versions 1.0 and 1.1
---------------------------------------------------------------------
 
======================================================================
  Microsoft Product Support Services Application Note (Text File)
                 WX0639: QUERYING QUESTIONS & ANSWERS
======================================================================
                                                                  9/93
                                                      No Disk Included
 
The following information applies to Microsoft Access, versions
1.0, and 1.1.
 
-----------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
| Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER      |
| EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED      |
| WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR       |
| PURPOSE. The user assumes the entire risk as to the accuracy and    |
| the use of this Application Note. This Application Note may be      |
| copied and distributed subject to the following conditions:  1) All |
| text must be copied without modification and all pages must be      |
| included;  2) If software is included, all files on the disk(s)     |
| must be copied without modification (the MS-DOS  utility         |
| diskcopy is appropriate for this purpose);  3) All components of    |
| this Application Note must be distributed together;  and  4) This   |
| Application Note may not be distributed for profit.                 |
|                                                                     |
| Copyright (C) 1992-1993 Microsoft Corporation.  All Rights Reserved |
| Microsoft, FoxPro, Microsoft Access, and MS-DOS are registered      |
| trademarks                                                          |
| and Windows is a trademark of Microsoft Corporation.                |
| dBASE III PLUS, dBASE IV, and Paradox are registered trademarks of  |
| Borland International, Inc.                                         |
| Btrieve is a registered trademark of Novell, Inc.                   |
| Sybase is a registered trademark of Sybase, Inc.                    |
-----------------------------------------------------------------------
 
1.  Q. How can I export the results of a query?
 
    A. To export the results of a query, you can either create a table
       that contains the results or create a macro to export the query
       itself.
 
       METHOD 1: To create a table that contains the results of the
       query, change the select query to a make-table query by opening
       it in Design view and choosing Make Table from the Query menu.
       The resulting table will contain the data to be exported. Then
       choose Export from the File menu to export the data in the
       format you want. For more information, please refer to Chapter
       4 of the "Microsoft Access User's Guide."
 
       METHOD 2: To create a macro to export the query, use the
       TransferText macro action to export the data in text file
       format or use the TransferSpreadsheet macro action to export
       the data in spreadsheet format. Using the name of a query in
       the TableName parameter of these macro actions allows you to
       export the query in either file format.
 
        NOTE: This feature is undocumented. To export a parameter
        query, you must use Method 1. For specific instructions on
        using the TransferText or TransferSpreadsheet macro actions,
        search for "TransferText" or "TransferSpreadsheet" using the
        Help menu.
 
2.  Q. Can I join tables from different databases into one query?
 
    A. Yes, you can join tables from different databases into one
       query by choosing Attach Table from the File menu. Using these
       remotely-attached tables, you can generate queries from any
       supported data source (for example, Btrieve, dBASE III
       PLUS, dBASE IV, FoxPro, or Paradox).
 
3.  Q. Is data in tables stored in a sorted order? How can I view my
       data in sorted order?
 
    A. Data in tables is not stored in sorted order. Data is stored in
       the order in which it was entered. To view data in sorted
       order, create either a query or a form based on a query that
       uses the Sort field on the query grid. By default, if the table
       includes a primary key, the Datasheet view of the table will
       display the data sorted by the primary key. To view the data
       sorted by something other than the primary key alone, use a
       query, as previously described.
 
4.  Q. How can I create computed columns in tables?
 
    A. You can create computed columns or expressions with queries.
       Within the query, create a column that is defined as an
       expression. In general, it is helpful to think of queries as
       virtual tables; you can use a query wherever you can use a
       table. If you're familiar with SQL terminology, this is very
       similar to creating a view. Unlike most implementations of
       views, however, Microsoft Access views can be updated even if
       they involve joins from different data sources, such as
       Paradox, Btrieve, or separate Microsoft Access data sources.
 
5.  Q. What is the difference between the keywords DISTINCT and
       DISTINCTROW?
 
    A. DISTINCT is part of the SQL standard and causes a query to
       return unique data, rather than unique records. For example,
       even if there are 10 customers named Jones, the query "SELECT
       DISTINCT Name FROM Customer" returns only one row containing
       Jones. With Microsoft Access queries, you specify DISTINCT by
       choosing Query Properties from the View menu and then selecting
       the Unique Values Only check box.
 
       DISTINCTROW is unique to Microsoft Access and is not part of
       the SQL standard. It causes a query to return unique records,
       rather than unique data values. For example, if there are 10
       customers named Jones, the query "SELECT DISTINCTROW Name FROM
       Customer" returns all 10 rows containing Jones.
 
       The main reason for adding the DISTINCTROW keyword to Microsoft
       Access SQL is to support semi-joins that can be updated (for
       example, one-to-many joins in which the output columns all come
       from the one-sided table). DISTINCTROW is specified by default
       in Microsoft Access queries and is ignored in queries for which
       it has no effect.
 
6.  Q. Why do queries change the order of my columns?
 
    A. When you close a query, Microsoft Access moves the sorted
       fields to the leftmost columns in the Query-By-Example
       (QBE) grid. For example, if you open an existing query in
       Design view, revise it, and save your changes, Microsoft Access
       displays the sorted fields to the left of the datasheet. You
       can rearrange the fields if necessary.
 
7.  Q. I am sending a query to Microsoft SQL Server or Sybase SQL
       Server through Microsoft Access, but the query runs very
       slowly. Why does this occur, and how can I increase the query
       speed?
 
    A. The query speed most often decreases when you send implicit,
       instead of explicit, parameters to the remote server.
 
       Query parameters can be either implicit or explicit. A
       parameter entry made only in the Query-By-Example (QBE) grid is
       called an implicit parameter. A parameter entry made in both
       the QBE grid and the Query Parameters dialog box is called an
       explicit parameter.
 
       When you use implicit parameters in your query, Microsoft
       Access does not send the query to the remote server, because
       Microsoft Access cannot verify what implicit conversions the
       remote server provides. As a result, Microsoft Access processes
       the query locally, which can noticeably reduce the query speed.
       However, when you use explicit parameters in your query,
       Microsoft Access knows the data types of the parameters.
       Subsequently, Microsoft Access sends the query to the remote
       server for processing, which speeds up the query.
 
       Note also that Microsoft Access automatically treats
       unrecognized or misspelled names and expressions as implicit
       parameters, rather than as errors. Microsoft Access then makes
       a "best guess" about the implicit parameter's data type. If
       Microsoft Access guesses the wrong data type, a query can
       return unexpected results or values, either when you enter a
       parameter value or when you execute queries on a remote server.
 
       For additional optimization tips for attached SQL Server
       tables, please obtain Technical Note #99321. You can order this
       technical note by selecting the FastTips Technical Library
       option from the Fast Tips Main Menu.
 
Additional reference words: 1.00 1.10
KBCategory: kbusage kbfasttip kbappnote
KBSubcategory: FstQa
=============================================================================
Copyright Microsoft Corporation 1995.
