ACC: Using Criteria to Simulate Non-Equal Joins in Queries

Last reviewed: April 2, 1997
Article ID: Q109956
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

Microsoft Access allows equi-joins and outer joins between tables. These joins show only rows where the values in the join fields match exactly, or there isn't a match at all in one of the tables (in the case of an outer join).

This article describes how to create a query to show unequal (less than or greater than) data comparisons using the query grid's Criteria row when there is no join between the tables.

MORE INFORMATION

The following example demonstrates how to find out if a set of buildings has square footage greater than or equal to the square footage requirements of a list of companies:

  1. Create the following new tables:

          Table: Clients
          ----------------------------------
          Field Name: Client Name
           Data Type: Text
           Field Size: 50
          Field Name: Min Sq Footage Required
           Data Type: Number
           Field Size: Long Integer
    

          Table: Buildings
          ------------------------
          Field Name: Building Name
           Data Type: Text
           Field Size: 50
          Field Name: Sq Footage
           Data Type: Number
           Field Size: Long Integer
          Field Name: Address
           Data Type: Text
           Field Size: 50
    

  2. Enter the following data for the Clients table:

          Client Name       Min Sq Footage Required
          -----------------------------------------
          ABC Company       10000
          Cats, Inc.         2500
          XYZ Company        5000
    
    

  3. Enter the following data for the Buildings table:

          Building Name   Sq Footage   Address
          ---------------------------------------------
          Paragon Towers   4500        123 South St
          The Emporium    10000        East Avenue
          Twin Peaks      20000        401 4th Place
          Pyramid          1200        Lakeshore Drive
    
    

  4. Create a new query based on both tables. Drag all of the fields from both tables to the field row in the query grid.

  5. In the Criteria row for the Sq Footage column enter the following:

          >=[Min Sq Footage Required]
    

  6. Run the query.

The results of the query will be:

   Client       Min Sq Footage  Building        Sq
   Name         Required        Name            Footage  Address
   ----------------------------------------------------------------------
   ABC Company  10000           The Emporium    10000    East Avenue
   ABC Company  10000           Twin Peaks      20000    401 4th Place
   Cats, Inc.    2500           Paragon Towers   4500    123 South St
   Cats, Inc.    2500           The Emporium    10000    East Avenue
   Cats, Inc.    2500           Twin Peaks      20000    401 4th Place
   XYZ Company   5000           The Emporium    10000    East Avenue
   XYZ Company   5000           Twin Peaks      20000    401 4th Place

Only buildings that have square footage greater than or equal to the minimum required by each company satisfy the expression in the Criteria row in the query.


Keywords : kbusage QryJoin
Version : 1.0 1.1 2.0 7.0
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.