Query without wizard

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

How can I create a unmatched query without using the
wizard?? As you are aware there are several requirements
to perform an unmatched query.

Two Tables/Queries and matching fields. How does the
Access determine the unmatched portion???


I have two tables that have several different matching
fields and I'm interested also in how the join works
within an unmatched query.
 
Create one with the wizard, then view the SQL behind it. You will see
exactly how it works.

Once you figure it out, you could create one directly in SQL.


Rick B


How can I create a unmatched query without using the
wizard?? As you are aware there are several requirements
to perform an unmatched query.

Two Tables/Queries and matching fields. How does the
Access determine the unmatched portion???


I have two tables that have several different matching
fields and I'm interested also in how the join works
within an unmatched query.
 
Well then, it would look something like this...

SELECT [TableA].[Field1], [Field2]
FROM [TableA] LEFT JOIN TableB ON [TableA].[Field1] = TableB.[Field1]
WHERE (((TableB.[Field1]) Is Null));


In this example, Table A has records including two or more fields. In this
case, we only wnat to display Field1 and Field2 in our results.

Table B also has records. It too has a Field1 and for every record in
TableA, therre should be a matching record in TableB.

The query above will find all records in TableA that do not have a matching
record in TableB (using Field1 as the common entry).

HTH

Rick

what if the wizard doesnot work?
 
How can I create a unmatched query without using the
wizard?? As you are aware there are several requirements
to perform an unmatched query.

Two Tables/Queries and matching fields. How does the
Access determine the unmatched portion???
Click to expand...

It's not hard to create a "frustrated outer join" query without the
wizard. The logic requires bending your brain like a pretzel the first
couple of times you do it, but you'll recover! <g>

1. Create a Query joining the two tables on the matching field or
fields (up to ten fields). This will of course give you just the
matching records - not what you want, but we're not done!
2. Select each Join line in turn and change it to Option 2 (or 3) -
"Show all records in Table1 and matching records in Table2". This
gives you every record in Table1, whether it has a match or not.
3. Select any one of the Join fields from Table2 and put a criterion
on it of

IS NULL

This will exclude those records which *do* have a match (they'll have
a non-NULL join field), leaving only the unmatched records.
 
Back
Top