Comparing 2 Queries!!

[Aidy680]

Is there a way to comapre the contents of 2 queries and return the records where there is no match?

Thanks.

 

[Aidy680]

...a bit more info.

Each query is based on a different table.

I want to return the records where there is no match, from just the one query.

I suppose it's a bit like using the Unmatched Query wizard except I need to do this at runtime.

 

[alvechurchdata]

If you can compare the two results on their primary key then a subquery might help. Something like:

Select * from thisTable where IDfield 
  not in (select IDfield from thatTable);

That will get you the records that are in thisTable but not in thatTable so you'll need a union with the opposite query to get the records that are in thatTable but not in thisTable.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[dhookom]

The answer would depend on what you mean by "no match". Do you mean a single primary key field or any or all fields don't match?

Maybe you should take the time to explain and then enter a few records from each query so we can see what you want.

Duane
Hook'D on Access
MS Access MVP

 

[Aidy680]

Apologies for the dearth of info but alvechurchdata pointed me in the right direction.

Rather than use a primary key field (which would have been perfectly adequate), I created my own concatenated field and linked on that.

Where they didn't match or were null, they were the records that I was after.

Doing this in SQL Server just complicates matters...!

Thanks again guys for your trouble.