Find similar/closest matching records

[whitee]

Hi,

I need to compare two lists of chemicals from two different queries. The chemical names are long and complicated and include commas and parentheses. Because of this, I suspect there may be small spelling mistakes between the two lists so I was wondering if it is possible to include a criteria that would also find close matching records?

I have used the Find Unmatched Query Wizard to give me a list of the unmatched records but it is too big to search for them individually.

Thanks.

 

[wilpeter]

A simple query in design view, including both queries (without joining them), and the similar field from each; using as criteria in the less reliable query field: Like "*" & [Queryname.Queryfield] & "*"
would get you a comparison.

 

[whitee]

Thank you so much for your help, that worked perfectly

 

[Brianwarnock]

I'm a little confused here, I understood that you requirement was far more demanding than one field was bigger than the other.
Lets say A was H2SO4 and B H2SO3, how does the Like show that they are similar. It would catch B (H2SO4) and similar, but is it that simple?

Brian

 

[wilpeter]

I'll withhold my 'you're welcome' until I hear that the result did indeed include all required records. I should have asked, for example, what the two queries being compared used as criteria for inclusion. Perhaps as a first step, the correctly matched records would need to be excluded from the records being compared.

 

[whitee]

I have a few things still to iron out but basically in table 1 I had a chemical called "Quaternary ammonium compounds" whereas in table 2 it was called "(Champion Servo Bac80)Quaternary ammonium compounds", so using what wilpeter had suggested Access was able to match these two chemicals which I know are the same.

I've just realised that it has unfortunately duplicated some of the chemicals from my unreliable query and matched them with more than one chemical from my other table but the basic idea is there and I will have a look to see if I can stop the duplication.

I just wonder is there anyway to highlight the 'Like' chemicals...? This would make it easier to spot the duplication.

 

[whitee]

Yes, I did try comparing it with the unmatched chemicals query and it narrowed it down to a smaller number of records so I can see by eye where the duplication is but I would prefer if Access could do this but at least you have put me on the right track.

 

[wilpeter]

Yes, use as one of the matching queries, the results of your Unmatched Query. The two queries can be run sequentially from the form but I can't think of a shorter method. Brian?