Remove from one query, all records which have common field values in another query's all records

[krag]

I have 2 queries A and B, with similar lists and one common text field X.
I want to remove from query A all records whose X values are equal to the X values in query B.
I have made query C, which has both A and B in it but not linked in anyway.
I have selected all the fields in A.
For field X, I have set criteria as <>B![X].
In properties I have set "Unique Records" as Yes but does not have any effect anyway.

Result is that it is simply opening all the records in A (including the common ones from B) and seems to ignore the criteria altogether.


Any suggestions would be appreciated.

[Minty]

Left Join query A to Query B on the x field.
In the query designer add Query B x field and in the criteria put Is Null

That should do what you want.

[jojowhite]

you also use:

DELETE QueryA.* FROM QueryA WHERE EXISTS (SELECT NULL FROM QueryB Where QueryB.X = QueryA.X);

[krag]

Minty and jojowhite
Thanks for the very fast response.
It worked.
In fact I had used it long back (pre-corona days) once and totally forgot the trick.
Thanks again for reminding me.

[jojowhite]

Minty and jojowhite
Thanks for the very fast response.
It worked.
In fact I had used it long back (pre-corona days) once and totally forgot the trick.
Thanks again for reminding me.

Can you tell us which Query works for you so that others
who will find this thread will get the right solution.

[Minty]

I think both will work.
I suspect but (don't know) that the join might be more efficient than the Exists clause in Access.
Depends on the Query Plans. A SQL server plan may be the other way around.
They may even work out to be the same.