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

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

   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