The complement of a select query

[Guest]

Hi,
I have a query that selects records from TableA that have matching first AND
last names in Table B. How may I modify the code to do the opposite ?
(I want to select records for which the Full Name does not match.)

Here is my code:

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB] ON TableA.OWNERLAST = [TableB].[Last Name]
WHERE ((([TableB].[First Name])=[TableA]![OWNERFIRST]));

David

 

[John Spencer (MVP)]

To find unmatched records in tableA, try the following SQL

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB]
ON TableA.OWNERLAST = [TableB].[Last Name] AND
[TableA]![OWNERFIRST] =[TableB].[First Name]
WHERE [TableB].[Last Name] Is Null

 

[John Vinson]

Hi,
I have a query that selects records from TableA that have matching first AND
last names in Table B. How may I modify the code to do the opposite ?
(I want to select records for which the Full Name does not match.)

Here is my code:

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB] ON TableA.OWNERLAST = [TableB].[Last Name]
WHERE ((([TableB].[First Name])=[TableA]![OWNERFIRST]));

David

Use the "Unmatched Query Wizard" in the new query tab.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Hi all,

I hit on the idea of using a subquery, and
I was able to get the following code (which include a subquery) to do what I
need, but there is one major problem. After the query is run, it takes
"forever" to advance from record 1 to the the last record.
Here is my code:
SELECT *
FROM zip01 AS Table1
WHERE ID
NOT IN (SELECT zip01.ID
FROM zip01 LEFT JOIN [Fltr1-Agt1] ON zip01.OWNERLAST = [Fltr1-Agt1].[Last
Name]
WHERE ((([Fltr1-Agt1].[First Name])=[zip01]![OWNERFIRST])));

I have tried a number of things:
opening the database exclusively, locked versus not locked, etc..
have the 2 tables indexed different ways, or not indexed at all
Try with and without having primary keys in the tables.

Other queries in my database run fine (i.e. does not exhibit the long delay
in navigating through the records.)

Can anybody give me some guidance here? Thanks.

For John S:
By the way, I tried the code you suggested, but got into syntax problem when
adding "AND".
Here is my code for that experiment:
SELECT *
FROM zip01 AS Table1
WHERE ID
NOT IN (SELECT zip01.ID
FROM zip01 LEFT JOIN [Fltr1-Agt1] ON zip01.OWNERLAST = [Fltr1-Agt1].[Last
Name]
AND
((([Fltr1-Agt1].[First Name])=[zip01]![OWNERFIRST])))
WHERE ( [Fltr1-Agt1].[Last Name] Is Null );
-------------

For John V:
Unmatched Query Wizard is not the solution since there are two tables
involved.
Sorry I did not explain the entire problem initially as I was trying to be
brief.

David

To find unmatched records in tableA, try the following SQL

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB]
ON TableA.OWNERLAST = [TableB].[Last Name] AND
[TableA]![OWNERFIRST] =[TableB].[First Name]
WHERE [TableB].[Last Name] Is Null

Hi,
I have a query that selects records from TableA that have matching first AND
last names in Table B. How may I modify the code to do the opposite ?
(I want to select records for which the Full Name does not match.)

Here is my code:

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB] ON TableA.OWNERLAST = [TableB].[Last Name]
WHERE ((([TableB].[First Name])=[TableA]![OWNERFIRST]));

David

 

[John Spencer (MVP)]

That is not the SQL I suggested. The SQL I suggested should be executed without
the subquery. Here is my original query modified to use your table names with
my "aliases" or range variables.

SELECT TableA.*
FROM Zip01 as TableA LEFT JOIN [Fltr1-Agt1] as [TableB]
ON TableA.OWNERLAST = [TableB].[Last Name] AND
[TableA].[OWNERFIRST] =[TableB].[First Name]
WHERE [TableB].[Last Name] Is Null

Your SQL based on this has the wrong number of open and close parens. And if it
worked would return all records in Zip01 that are NOT only in zip01, but are in
both ZIP01 and Fltr1-Agt1 (the same thing as an inner join).

SELECT *
FROM zip01 AS Table1
WHERE ID
NOT IN (SELECT zip01.ID
FROM zip01 LEFT JOIN [Fltr1-Agt1] ON zip01.OWNERLAST = [Fltr1-Agt1].[Last
Name]
AND
((([Fltr1-Agt1].[First Name])=[zip01]![OWNERFIRST])))
WHERE ( [Fltr1-Agt1].[Last Name] Is Null );

Your query that does work will always be slow. NOT IN is always slow and very
little can be done to improve its speed.

Hi all,

I hit on the idea of using a subquery, and
I was able to get the following code (which include a subquery) to do what I
need, but there is one major problem. After the query is run, it takes
"forever" to advance from record 1 to the the last record.
Here is my code:
SELECT *
FROM zip01 AS Table1
WHERE ID
NOT IN (SELECT zip01.ID
FROM zip01 LEFT JOIN [Fltr1-Agt1] ON zip01.OWNERLAST = [Fltr1-Agt1].[Last
Name]
WHERE (([Fltr1-Agt1].[First Name]=[zip01]![OWNERFIRST])));

I have tried a number of things:
opening the database exclusively, locked versus not locked, etc..
have the 2 tables indexed different ways, or not indexed at all
Try with and without having primary keys in the tables.

Other queries in my database run fine (i.e. does not exhibit the long delay
in navigating through the records.)

Can anybody give me some guidance here? Thanks.

For John S:
By the way, I tried the code you suggested, but got into syntax problem when
adding "AND".
Here is my code for that experiment:
SELECT *
FROM zip01 AS Table1
WHERE ID
NOT IN (SELECT zip01.ID
FROM zip01 LEFT JOIN [Fltr1-Agt1] ON zip01.OWNERLAST = [Fltr1-Agt1].[Last
Name]
AND
((([Fltr1-Agt1].[First Name])=[zip01]![OWNERFIRST])))
WHERE ( [Fltr1-Agt1].[Last Name] Is Null );
-------------

For John V:
Unmatched Query Wizard is not the solution since there are two tables
involved.
Sorry I did not explain the entire problem initially as I was trying to be
brief.

David

To find unmatched records in tableA, try the following SQL

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB]
ON TableA.OWNERLAST = [TableB].[Last Name] AND
[TableA]![OWNERFIRST] =[TableB].[First Name]
WHERE [TableB].[Last Name] Is Null

Hi,
I have a query that selects records from TableA that have matching first AND
last names in Table B. How may I modify the code to do the opposite ?
(I want to select records for which the Full Name does not match.)

Here is my code:

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB] ON TableA.OWNERLAST = [TableB].[Last Name]
WHERE ((([TableB].[First Name])=[TableA]![OWNERFIRST]));

David

 

[Guest]

Unmatched query is a good solution. Thanks.

Hi,
I have a query that selects records from TableA that have matching first AND
last names in Table B. How may I modify the code to do the opposite ?
(I want to select records for which the Full Name does not match.)

Here is my code:

SELECT TableA.*
FROM TAbleA LEFT JOIN [TableB] ON TableA.OWNERLAST = [TableB].[Last Name]
WHERE ((([TableB].[First Name])=[TableA]![OWNERFIRST]));

David

Use the "Unmatched Query Wizard" in the new query tab.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps