FastTips: Querying Questions & Answers

PSS ID Number:  Q100848
Article last modified on 08-27-1993

1.00 1.10
WINDOWS

======================================================================
  Microsoft® Product Support Services Application Note (Text File)
                 WX0639: QUERYING QUESTIONS & ANSWERS
======================================================================
                                                   Revision Date: 5/93
                                                               No Disk
The following information applies to Microsoft Access, version 1.0
Copyright 1992-1993.
 ---------------------------------------------------------------------
| 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 © 1992-1993 Microsoft Corporation.  All Rights
Reserved.|
|
|
| Microsoft, Microsoft Access, and MS-DOS are registered trademarks
|
| and Windows is a trademark of Microsoft Corporation.
|
|        Btrieve is a registered trademark of SoftCraft, Inc.,
|
|                          a Novell Company.
|
|       dBASE III PLUS and dBASE IV are registered trademarks of
|
|                       Ashton-Tate Corporation.
|
|         Paradox is a registered trademark of Ansa Software,
|
|                          a Borland company.
|
|    This document was created using Microsoft Word for Windows™
|
 ---------------------------------------------------------------------
1.  Q. How can I export the result of a query?
    A. To export the results of a query, you must create a table that
       contains the results. To do this, create a make-table query in
       Design view (choose Make Table from the Query menu). The
       resultant table will contain the data to be exported. After the
       make-table query is complete, choose Export from the File menu
       to export the data in the desired format. For more information,
       please refer to Chapter 4 of the" User's Guide."
2.  Q. Can I join tables from different databases into one query?
    A. Yes, you can join tables from different databases into one
       query. Using the Attach Table method, you can generate queries
       from any supported data source (for example, Btrieve®, dBASE
       III PLUS®, dBASE IV®, 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 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.
       If you use implicit parameters in your query, Microsoft Access
       will 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, if 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.

Additional reference words: 1.00 1.10 ivr ivrfax fasttips sql
relational
KBCategory:
KBSubcategory: