SOMETHING SIMILAR TO A MINUS SQL STATEMENT??

[Rohdem]

Microsoft Access doesn't support the 'MINUS' SQL statement. Does anyone know of a way I can return all the records in one table that are not in another?
Mike Rohde
rohdem@marshallengines.com

 

[JeremyM]

Its long way. But read one into an array. Then as u read in the other array compare to the current array. If not equal write into the new array (yes u need 2). Then array2 will be all that arent on the other one. Its the long way. But hope it helps.

Jeremy

 

[THoey]

Take a look in the help for JOINS, I think it is an OUTER JOIN.

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[albywalt]

Terry has the right idea, but the easiest way to do this is to use the Find Unmatched Query Wizard.

Jeff

 

[Rohdem]

I've tried using the "Find Unmatched Query Wizard", but the problem is it only looks for unmatched records in a single field, I need to check for identical data in all of the fields in the record.
Mike Rohde
rohdem@marshallengines.com

 

[albywalt]

What you want to do is use the same design structure as the "Find Unmatched Query Wizard", but extend it to the additional fields you want to check for. Look at the design of the query that the wizard gives you. It uses a left join that gives you "ALL records from 'table A' and only those from 'table B' where the joined fields are equal. It also adds a single criteria that says the joined field in 'table B' must be null. Thus you get all records in 'table A' that don't match to 'table B'.

All you have to do to extend this to multiple fields is to add left joins for each additonal field you want to match on. I usually add additional criteria that each additional field in 'table B' must be null as well, but as I'm writing this I don't think this is necessary.

After I got the hang of this I stopped using the wizard altogether because it's faster to set up the query in design view.

Jeff