matching unequal text fields

[Mr. Wish-I'd-taken-those-courses]

I want to find records that contain disparent records with data in comparable
fields like this:

Data in Table 1 = BRG, ROLLER, 6020, FLANDERS, ER53
Data in Table 2 = BRG, ROLLER, 6020, FLANDER, ER53, 6.22X5.11, OD 5"

Notes: the data layout is not standard for all records (i.e. the commas,
naming convention are not universal, so we can't just strip-out the data).

 

[Ken Snell]

I'm not sure what you want to compare between the two fields? Which text
from Table 1 is to match which text in Table 2? Show specific examples,
please, and provide table and field names so that we can tailor suggestions
directly to your setup.
--

Ken Snell
http://www.accessmvp.com/KDSnell/



"Mr. Wish-I'd-taken-those-courses" <Mr.
Wish-I'(e-mail address removed)> wrote in message
news:[email protected]...

 

[John W. Vinson]

I want to find records that contain disparent records with data in comparable
fields like this:

Data in Table 1 = BRG, ROLLER, 6020, FLANDERS, ER53
Data in Table 2 = BRG, ROLLER, 6020, FLANDER, ER53, 6.22X5.11, OD 5"

Notes: the data layout is not standard for all records (i.e. the commas,
naming convention are not universal, so we can't just strip-out the data).

This is a case of "garbage in, garbage out" I fear. These should evidently
have been six or seven different fields, not jammed into one, and should have
had some sort of quality control on data entry.

For instance, are

BRG, ROLLER, 6020, FLANDER, ER53, 6.22X5.11, OD 5"

and

BRG, ROLLER, 6020, FLANDER, ER54, 8.22X5.11, OD 6"

the same? They're a lot more similar than your other example, in terms of text
content (just three digits difference); but I bet a guy on the shop floor
wouldn't think they were!

You'll need to come up with either some *OBJECTIVE* way to split up this
composite field into its components, identify which components go with which,
and match them up; or use a USB* interface. Good luck!


* (Using Someone's Brain)