Design View Query - NOT IN another table

  • Thread starter Thread starter JE
  • Start date Start date
J

JE

Hello,

I have two tables that are unique based on Account Number. I want
everything in table A where account number in table A is not found in table
B. This is a design view table. I am extremely new to MS Access and would
like to keep this simple. Can someone direct me to a resource that I can
reference to accomplish this? Many thanks.
 
I'm unfamiliar with the term "a design view table". In my experience, you
either have two tables or you don't.

If you do have two tables (or a mix of tables & queries), you can use the
Access query wizard to help build an "unmatched" query.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Let me attempt to clarify. I have two tables. I not using SQL to write the
query. I create the queries under the queries tab and then "design view'. I
have used the "unmathced query" but in this scenario I am also capturing data
from table b. I only want data from table A and only if the specified field
is in table B.

Is this more clear? Thanks.
 
JE said:
Hello,

I have two tables that are unique based on Account Number. I want
everything in table A where account number in table A is not found in
table
B. This is a design view table. I am extremely new to MS Access and
would
like to keep this simple. Can someone direct me to a resource that I can
reference to accomplish this? Many thanks.
Click to expand...


Try this:

1. Create a new query, not based on any table.

2. Switch to SQL View.

3. Enter this SQL:

SELECT * FROM [Table A]
WHERE [Account Number] NOT IN
(SELECT [Account Number] FROM [Table B]);

In the above, replace "Table A" with the real name of your table A, "Table
B" with the real name of your table B, and "Account Number" with the name of
the account-number field in both tables. If that field has a different name
in each table, replace the first use of "Account Number" with the name of
that field in Table A, and the seconf use of "Account Number" with the name
of that field in Table B.

4. Switch to datasheet view to see if you get the correct results. If you
do, you can switch to design view to see what such a query looks like in
design view.

Note: the "find unmatched" query wizard will create a slightly different
query that should return the same results. That query will generally be
more efficient than the one above, but the way it works is less clear. That
one would have SQL like this:

SELECT [Table A].* FROM [Table A] LEFT JOIN [Table B]
ON [Table A].[Account Number] = [Table B].[Account Number]
WHERE [Table B].[Account Number] Is Null;
 
Please post the SQL statement of the "unmatched" query you are using.

Your original post seemed to indicate that you ONLY wanted tableA data
(because there wasn't any matching record in tableB).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
KenSheridan via AccessMonster.com said:
One point perhaps worth mentioning, though I suspect it won't have any
bearing on the result in this case, is that the NOT IN predicate will fail
if
any of the rows returned by the subquery contain a Null in the relevant
column.
Click to expand...

True -- or at least, I think it's true, though I confess to never having
thought about it before. But I don't think it matters in this case, if the
OP is correct in saying that the two tables "are unique based on Account
Number."
 
Back
Top