Excluding items from one query in another

[phoenixfire]

Hello,
I have a question. I have two queries A and B. Both of have a column Item No and have a few items in common.
If I need to display query A by excluding those common items, what can I do?
This might sound as a basic question, but I am having trouble with the processing time.

I tried to use a exclusion filter in query A -
[Query A].[Item No] <> [Query B].[Item No].
It took forever to run. I had to abort the execution.

I am trying to use another method right now.
Create a new query using 'Intersect' set operation and then use this new query as part of the exclusion filter. But, I am not sure if this makes a difference in the processing time. ( I got around to creating the intersect query today. Will have to go back and test the exclusion filter tomorrow)

Does anybody have any other ideas? Thank you very much for your help and time. :o

[phoenixfire]

Figured it out...Never mind!

[baru]

Hi,

Can you explain how you figured it?

[phoenixfire]

Sure...it would be a pleasure.

As I said, I had two queries A and B with the same column 'Item No.' Both of them had some items in common, say a,b,c.
I needed to display query A without these items.
When I tried to do a filter where [Query A].[Item No.] <> [Query B].[Item No.], it was giving me a very bad performance.

So I tried this.
Create Query C which is a intersection of A and B. So Query C will display only a,b,c.
Now create a new query - Query D by creating a join between Query A and this new Query C where the cardinality is 1..n and 0..n respectively.
Add a filter in Query D where [Query D].[Item No.] = [Query A].[Item No.] and [Query C].[Item No.] IS NULL.

Let me know if you have any questions.

[MFGF]

Sounds quite complex. Couldn't you just have created Query C as an an "Except" of A and B instead of an Intersect?

MF.