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: 50Enter 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:
>=[Min Sq Footage Required]- 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: kbinfo kbusage KB109956
