Unequal Join

[pbruce66]

I am trying to locate the differences in 2 tables. An inner join gives me exactly the opposite of what I need. Table 1 (Standards) contains information that all records must comply with. Table 2 (Active_Records) contains all active records. The tables are joined by Item_Name (unique in Standards, but not in Active_Records). I need to locate the records in Active_Records where the Item_name does not exist in Standards, Item_Name. Users have been able to enter items that were not on the Standards list in the past and that has led to a lot of bad data. For instance the Standard Item_Name for an item may be "3/8" Bolt" and users have entered evrything from "3/8 inch bolt" to "Hinge Bolt" for the same item.

I think I need an unequal join to identify non-compliant records but I have no idea how to make one. (Once all records have valid Item_Names I will lock down the field).

 

[BobStubbs]

Why not use the Access wizard to create a 'Find Unmatched' query?

You could create a query which will list all Item_name values which occur in 'Active_Records', but do not occur in 'Standards'.

Then you can set a flag field to mark these records, run update queries to fix them, as you require.

Keep your original 'find unmatched' query. Run this again when you think you have fixed all the bad data. If it returns no records, you know you have cleaned up all the bad records in 'Active_Records'.

Bob Stubbs

 

[pbruce66]

Thanks Bob. I feel like an idiot, that was simple. Instead of looking for enequal I should have looked for unmatched. It is exactly what I needed.