Creating a one to many relationship when one link is a subset of the other

[Pat]

Hi,


I have two tables and need find create a one to many relationship using the field name of "part number" in each table. Obviously it's very simple if the two fields match. However, because I don't control how the "part number" data is entered in the parent database there may be records in the other data base that "should" match but don't because someone typed in the "part number" a little differently. Unfortunately there is no way to control the inputting of data into that database because I'm actually receiving the data from my customer via an excel spreadsheet. An example of what I might see:
Main Table Child Table
Part Number ABC12ATR ABC12A-TR
or
Part Number STABC12ATR ABC12A-TR

Because I deal with so many customers there is no way to predict how they might enter data into this field. So my thought was to write a query to creates a mid string of the "part number" field in the main table and call it "part number truncated". In the first example above, if my new field pulled in the first 6 characters and I could define the link as a "like" link (i.e. as long as it found those same characters in the second database it would be considered a match - *ABC12A* to ABC12A-TR) then it would find the matching part. It may also find other records too, but as long as I could show all the matches I would then be able to pick the one that best matches the actual part in the first database. There are several more fields in each database, but I don't think that is relative to my question. Is there any way to create that one to many link were it is considered a match as long as the first database is a subset of the second database?

If this is possible in code I have a second question. I could hard code the mid string character count in the query, but I'd rather make it dynamic. The first pass would be to look for perfect matches. The second pass might remover the first 2 and last 2 characters. The user would continue to narrow the main table's part number until it started to find matches or it got so small it didn't makes sense to shorten it any further. FYI, the length of characters in the "part number" field can vary from 3 or 4 up to 15 or 20. I would like to have the operator prompted prior to running the query with the following:

Enter the number of characters you would like to remove from the beginning of the part number string:
Enter the number of characters you would like to leave in the part number string:

Thanks in advance for any guidance anyone can provide. Solving this would be a huge help for me.

[June7]

A query can't make 'passes'. Either the records are associated or they aren't.

VBA code procedure could do the parsing search you described but could get complicated.

Can retrieve records that match the LIKE criteria or use InStr function. All this is fine as long as can depend on one string length ALWAYS being same or longer than the other, and has to always be the same. Either the submitted part number is always = or longer or the real part number is always = or longer. And nothing can deal with character transpositions or drops.

Let's say you do find the real part number - then you need to save it into the order record so the two will be permanently associated.

Whose part numbers are these anyway? If they are created by you and provided to customers in a catalog or list or whatever then they better enter them properly or the order is rejected. At least that's what I would do.

[Pat]

Hi,
Unfortunately I can't guarantee that one will always be longer than the other, there just isn't enough control. As for not taking the order, that would be ideal. But, we are in an industry where value add service is a must and this is part of the offering. That is why I'm trying to automate as much as possible. Thanks

[June7]

You can do everything possible to search for real part number but there will always be failures. Take the import as is then do a Find Unmatched query to locate the orphans and fix as you see fit.

Sounds like a lot of followup phone calls and emails are in your future.

[Pat]

If I can guarantee that the main table will always have a Part Number that is shorter then the child table, what would you recommend? Thanks

[June7]

Whatever you do, must have valid PartNumber in child table (or query) for PK/FK linking. I have never tried to link queries on calculated fields where I could not absolutely control the result of the calc and was certain would match with value in other table, usually just a concatenation of fields or extract of value from a known, consistent string sequence. There is enough variability in your case makes me wary of joining on calculated field.

Either fix the value submitted or duplicate the submitted value into the actual FK field then fix the duplicate where needed. That way you have the original for documentation. Code could update the original or the duplicate where a match is found. The code could check for each string within the other and if a match, provide real part number for consideration. If you trust the code can just run batch update on the new records. If no match found (transpositions, drops) will be a case-by-case review of the unmatched submitted part numbers.