Finding possible matches

[DawnTreader]

Hello All

i have 2 tables, one has my company part numbers, the other has a
supplier set of part numbers. the company part table has a place for
manufacture part numbers. sometimes the number put in there by an
employee may have a portion of it that matches the actual supplier
part number. i need to know how to find those parts in my company
table that may contain the mfg pn from the other table. here is what i
have so far:

SELECT dbo_PART.ID, dbo_PART.DESCRIPTION, dbo_PART.MFG_PART_ID,
Table3.mfgpn
FROM dbo_PART RIGHT JOIN Table3 ON dbo_PART.MFG_PART_ID =
Table3.mfgpn;

how do i add the ability to do a "like *something*" on the join?

 

[John W. Vinson]

Hello All

i have 2 tables, one has my company part numbers, the other has a
supplier set of part numbers. the company part table has a place for
manufacture part numbers. sometimes the number put in there by an
employee may have a portion of it that matches the actual supplier
part number. i need to know how to find those parts in my company
table that may contain the mfg pn from the other table. here is what i
have so far:

SELECT dbo_PART.ID, dbo_PART.DESCRIPTION, dbo_PART.MFG_PART_ID,
Table3.mfgpn
FROM dbo_PART RIGHT JOIN Table3 ON dbo_PART.MFG_PART_ID =
Table3.mfgpn;

how do i add the ability to do a "like *something*" on the join?

You can do it in the JOIN just as you would in a Where clause:

SELECT dbo_PART.ID, dbo_PART.DESCRIPTION, dbo_PART.MFG_PART_ID,
Table3.mfgpn
FROM dbo_PART RIGHT JOIN Table3 ON dbo_PART.MFG_PART_ID LIKE "*" &
Table3.mfgpn & "*";

Since the wildcard will defeat any index on mfgpn it will force a full table
scan; it may be slower and it may not be updateable.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[DawnTreader]

You can do it in the JOIN just as you would in a Where clause:

SELECT dbo_PART.ID, dbo_PART.DESCRIPTION, dbo_PART.MFG_PART_ID,
Table3.mfgpn
FROM dbo_PART RIGHT JOIN Table3 ON dbo_PART.MFG_PART_ID LIKE "*" &
Table3.mfgpn & "*";

Since the wildcard will defeat any index on mfgpn it will force a full table
scan; it may be slower and it may not be updateable.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

THANKS!

 

[DawnTreader]

You can do it in the JOIN just as you would in a Where clause:

SELECT dbo_PART.ID, dbo_PART.DESCRIPTION, dbo_PART.MFG_PART_ID,
Table3.mfgpn
FROM dbo_PART RIGHT JOIN Table3 ON dbo_PART.MFG_PART_ID LIKE "*" &
Table3.mfgpn & "*";

Since the wildcard will defeat any index on mfgpn it will force a full table
scan; it may be slower and it may not be updateable.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

would there be an easy way to do fuzzy matches on part of a field? for
example part of the MFG_PART_ID might match something in the mfgpn. so
if i could some how do a "like" that slides through the MFG_PART_ID
say 5 characters at a time.

probably have to do that with code wont i?

 

[John W. Vinson]

would there be an easy way to do fuzzy matches on part of a field? for
example part of the MFG_PART_ID might match something in the mfgpn. so
if i could some how do a "like" that slides through the MFG_PART_ID
say 5 characters at a time.

Ouch. That's going to be REALLY slow and inefficient - if you want any
arbitrary five-character substring of MFG_PART_ID to match any arbitrary five
character substring of mfgpn, you will indeed need either code or a really
complex expression in the query; if MFG_PART_ID is (say) 20 characters then
you'll have 15 full table scans (no indexes!) of the second table for every
single record in the first table. I would certainly NOT include such a query
as the recordsource of a routinely used form, though I can see having it check
a single record on demand.

What's the real-life situation? Is there no control at all of these (nominally
unique and stable and properly normalized!) part numbers? Could you post some
examples of the data that you're trying to match?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Barrows]

would there be an easy way to do fuzzy matches on part of a field? for
example part of the MFG_PART_ID might match something in the mfgpn. so
if i could some how do a "like" that slides through the MFG_PART_ID
say 5 characters at a time.

probably have to do that with code wont i?

Well ....
Are the part IDs fixed-length? If so, I suppose you could calculate the
5-character blocks, and then add joins for each calculated field. For
example, if the part numbers are all 7 characters in length:

SELECT MFG_PART_ID,t1.mfgpn,t2.mfgpn,t3.mfgpn
FROM ((dbo_PART As p LEFT JOIN Table3 As t1
ON mid(MFG_PART_ID,1,5) like "*" & t1.mfgpn & "*")
LEFT JOIN Table3 As t2
ON mid(MFG_PART_ID,2,5) like "*" & t2.mfgpn & "*")
LEFT JOIN Table3 As t3
ON mid(MFG_PART_ID,3,5) like "*" & t3.mfgpn & "*"

As John says, this would be very inefficient. I'm talking hours, perhaps
days, to get the answer, depending on the sizes of the tables involved.. And
it would be just as bad, perhaps worse, if code were used.

 

[DawnTreader]

Well ....
Are the part IDs fixed-length? If so, I suppose you could calculate the
5-character blocks, and then add joins for each calculated field. For
example, if the part numbers are all 7 characters in length:

SELECT MFG_PART_ID,t1.mfgpn,t2.mfgpn,t3.mfgpn
FROM ((dbo_PART As p LEFT JOIN Table3 As t1
ON mid(MFG_PART_ID,1,5) like "*" & t1.mfgpn & "*")
LEFT JOIN Table3 As t2
ON mid(MFG_PART_ID,2,5)  like "*" & t2.mfgpn & "*")
LEFT JOIN Table3 As t3
ON mid(MFG_PART_ID,3,5)   like "*" & t3.mfgpn & "*"

As John says, this would be very inefficient. I'm talking hours, perhaps
days, to get the answer, depending on the sizes of the tables involved.. And
it would be just as bad, perhaps worse, if code were used.

yeah, kind of figured...

oh well...

 

[DawnTreader]

Ouch. That's going to be REALLY slow and inefficient - if you want any
arbitrary five-character substring of MFG_PART_ID to match any arbitrary five
character substring of mfgpn, you will indeed need either code or a really
complex expression in the query; if MFG_PART_ID is (say) 20 characters then
you'll have 15 full table scans (no indexes!) of the second table for every
single record in the first table. I would certainly NOT include such a query
as the recordsource of a routinely used form, though I can see having it check
a single record on demand.

What's the real-life situation? Is there no control at all of these (nominally
unique and stable and properly normalized!) part numbers? Could you post some
examples of the data that you're trying to match?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

the problem is that the entered data is done by humans. everyone and
thier dog has a different idea about what is important in the
manufacturers part number.

we are getting better at having procedures and rules as to how this is
done, but for the longest time the company got by on "get 'er done".
now we pay the price in data management.

 

[David-W-Fenton]

if you want any
arbitrary five-character substring of MFG_PART_ID to match any
arbitrary five character substring of mfgpn, you will indeed need
either code or a really complex expression in the query; if
MFG_PART_ID is (say) 20 characters then you'll have 15 full table
scans (no indexes!) of the second table for every single record in
the first table.

Actually, a starts-with LIKE will utilize the index (i.e., LIKE
"12345"), so I think it's only 14 table scans...