Where <>

[pspencer]

I have a master table that has 1800 records. I have queried the table for duplicate records (qryDups).

I want to be able to select records from the master table that do not match the empno in the qryDups.

I know that I have to do a where <> but I can't get it to work.

Can someone help.

Thanks in advance.

Trice

 

[PHV]

Simply follow the unmatched query wizard ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pspencer]

I can't use the unmatched query as the values in the qryDups are in the master file. I want all of the records that don't match so I can manipulate a date.

I was trying:

Select *
From qryTI
Where qryTI.MEMNO <> qryDups.MEMNO

But it keeps asking me for a value. I guess I need a Select MEMNO from qryDups but I don't enough of the syntax to get it right.

Thanks,
Trice

 

[PHV]

A very inefficient way:
SELECT *
FROM qryTI
WHERE MEMNO Not In (SELECT MEMNO FROM qryDups)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pspencer]

Hi,

Thanks for answering PHV.

I know not very efficient but it's for a one time thing. A client sent us a file with 26000 records of event history. We need to look at the transfer in and transfer out and if they are the same day then add 1 day.

The problem is there are employees with duplicate records that need to be evaluated.

So I did a find duplicate records query based on my service code. That gave me 1800 records of which 300 employees have duplicate records. So I then did a query to group by on the memno so I have one memno per employee that has a duplicate record.

So I want to take my master table and update the employees that do not have duplicate records.

I don't really need an elegant solution just a quick one (and it's not my project just helping out).

So I tried the following and got an error message that the query was too complex:

Select *
From qryTI
Where qryTI.MEMNO Not In (Select MEMNO From qryDups);

Any other suggestions?

Thanks in advance.
Trice

 

[ck1999]

Do you not need to reference the query qrydups

Select *
From qryTI,qrydups
Where qryTI.MEMNO <> qryDups.MEMNO

ck1999

 

[pspencer]

ck1999,

Thanks for the help. I used what you wrote and then added some syntax and it worked great.

For those that may need this here is what I used:

SELECT qryTI.MEMNO, qryTI.ERNO, qryTI.SRVCODE, qryTI.EDATE
FROM qryTI LEFT JOIN qryDups ON qryTI.MEMNO = qryDups.MEMNO
WHERE (((qryTI.MEMNO) Not In ([qryDups]![MEMNO])));


Trice