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


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
This article was previously published under Q109956
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

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.
Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbinfo kbusage KB109956
©2002 Microsoft Corporation. All rights reserved.