Query using NOT IN statement

  • Thread starter Thread starter Microsoft
  • Start date Start date
M

Microsoft

Hi,

We have lost a number of records from our database.

I have imported the current customer data file and a backup taken before the
data loss into Access.

I need to identify which customers are in the old data file table and not in
the new.

The query i have created is:
select cu_code from alcustprepurge where cu_code not in (select cu_code
from alcustcurrent;);

but this returns zero records, when i know for a fact there are at least 10.

Please help as this is my first foray into using the NOT IN statement and I
need the query to work.

Thanks,
Joe
 
Hi,

We have lost a number of records from our database.

I have imported the current customer data file and a backup taken before the
data loss into Access.

I need to identify which customers are in the old data file table and not in
the new.

The query i have created is:
select cu_code from alcustprepurge where cu_code not in (select cu_code
from alcustcurrent;);

but this returns zero records, when i know for a fact there are at least 10.

Please help as this is my first foray into using the NOT IN statement and I
need the query to work.
Click to expand...

There is an alternative query to find unmatched records which will be
more efficient for sure, and may work better:

SELECT alcustprepurge.cu_code
FROM alcestprepurge LEFT JOIN alcustcurrent
ON alcustprepurge.cu_code = alcustcurrent.cu_code
WHERE alcustcurrent.cu_code IS NULL;


John W. Vinson[MVP]
 
Thanks John.

It works a treat.

Joe
John Vinson said:
There is an alternative query to find unmatched records which will be
more efficient for sure, and may work better:

SELECT alcustprepurge.cu_code
FROM alcestprepurge LEFT JOIN alcustcurrent
ON alcustprepurge.cu_code = alcustcurrent.cu_code
WHERE alcustcurrent.cu_code IS NULL;


John W. Vinson[MVP]
Click to expand...
 
Back
Top