Solved Find records in one table that doesn't exist in another. (1 Viewer)

[Sun_Force]

The following is two tables in a database

tblTemp
TempPK AutoNumber
PsNo String


tblOrders
OrderPK AutoNumber
PsNo String

Both tables have several more fields, but the only common field between them is PsNo. No relationship between them.

I'm trying to write a query that shows a list of All PsNo from tblTemp that doesn't exists in tblOrders.

I tried this :

SELECT TempPK, PsNo FROM tblTemp
WHERE Not Exists
( SELECT * FROM tblOrders WHERE tblTemp.PsNo=tblOrders.PsNo)

The query runs and shows 922 records.
I checked 10 random PsNo from the result, but a search showed that the same PsNo exists in tblOrders.

How can I get a list of PsNo from tblTemp that don't exist in tblOrders?

Thanks for any kind of advice.

PS: I can have the result with vba, but I prefer having a query for this job (if possible)

 

[Minty]

Would this do it:

SELECT TempPK, PsNo FROM tblTemp
WHERE PSNo Not IN ( SELECT PsNo FROM tblOrders WHERE tblTemp.PsNo=tblOrders.PsNo)

 

[isladogs]

There are several ways of doing this but the simplest is usually to create an unmatched query using the wizard.
The query SQL will probably be something like this

SELECT tblTemp.TempPK, tblTemp.PsNo
FROM tblTemp LEFT JOIN tblOrders ON tblTemp.PsNo = tblOrders.PsNo
WHERE tblOrders.PsNo Is Null;

 

[Sun_Force]

I really appreciate your help.
Thank you.

 

[isladogs]

You're welcome. For info I've just corrected my 'air code' as I typed tblUsers instead of tblTemp in error!

 

[Sun_Force]

You're welcome. For info I've just corrected my 'air code' as I typed tblUsers instead of tblTemp in error!

yes, I noticed that when testing. There was another error that Access complained about the join.
But your air code gave me the idea.
Thanks again.

I'm facing another problem now. I'm trying to check tblPart's PsNo partially. But it's another story and you already answered this problem .
I'll post another thread and hope you can share your experience there too.

I appreciate your time.

 

[isladogs]

Glad you were able to solve that issue. Can't see what the syntax error was in my air code but it doesn't matter now ...
I'll look out for your other thread