Microsoft KB Archive/109956

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

Article ID: 109956

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



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:

  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   Enter the following data for the Clients table:

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

  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

 Create a new query based on both tables. Drag all of the fields from both tables to the field row in the query grid.  In the Criteria row for the Sq Footage column enter the following:

<pre class="fixed_text">     >=[Min Sq Footage Required] </li> Run the query.</li></ol>

The results of the query will be:

<pre class="fixed_text">  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: kbinfo kbusage KB109956

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.