Subset of the overlap of two queries

[EnemyGateIsDown]

Hi

I have two queries that return the same columns and I want use them as the source for a third query that only shows the rows that appear in both sets.

Union shows all the rows from both.. I want sort of "subset" the queries together to show only the rows that appear in both queries...

Any ideas anyone?

Thanks in advance for any help.

Cheers,

Chris

 

[Crowley16]

well, are the results from the 2 seperate queries distinct?

if so then you can just union the 2 queries, and then select the records that appear more than once...

SELECT * FROM qry1 INNER JOIN qry2 ON qry1.key = qry2.key
WHERE COUNT(qry1.key) > 1;

 

[EnemyGateIsDown]

Thanks Crowley16 the only thing is that I need to run this from ASP and need to pass variables into the two source queries. Will the JET engine allow me to expand query1 and query2 into sub selects?

Thanks

Chris

 

[Crowley16]

yeah, I think so...

try it...

 

[JonFer]

Wouldn't it be more efficient to use an Inner Join with the two queries directly?

Select A.*
From QueryA as A Inner Join QueryB as B on A.Key1=B.Key1