Partial match Join query

  • Thread starter Thread starter Steve_A
  • Start date Start date
S

Steve_A

I am using Access 2007 and trying to join two tables by matching a partial
field on one of them.

Problem seems to be that if the RonsList.[Dwg Number] does not have a dash
(-) it will not find the partial match


Table name: RonsList
Field name: Dwg Number
Format as text: 779-302080

Table name: OPT09
Field name: Part Num
Format as text: 779-302080-1
779-302080-1-802
779-302080-1-804


SQL
SELECT RonsList.[Dwg Number], OPT09.[Part Num]
FROM RonsList LEFT JOIN OPT09 ON RonsList.[Dwg Number] Like OPT09.[Part Num]
& "*";


Dwg Number should match Part Numbers
779-302080 779-302080-1
779-302080-1-802
779-302080-1-804

148t3000-203 does match 148t3000-20
148t3000-204
148t3000-205
148t3000-207
 
The best solution would be to update the field by replacing any space in it
with an -.

Make a backup

UPDATE RonsList SET [part number] = Replace( [part number], " ", "-" )



If you cannot, then, would be slow:

SELECT RonsList.[Dwg Number], OPT09.[Part Num]
FROM RonsList LEFT JOIN OPT09
ON Replace(RonsList.[Dwg Number], " ", "-") Like OPT09.[Part Num] &
"*";


Note that this JOIN does not update the field in the table! it just compute
an expression based on a field, but doing so, it cannot use an index and the
job will be slow.


Vanderghast, Access MVP
 
Well the problem is that 779-302080 is not like 779-302080-1*, although
779-302080-1 is Like 779-302080*

You can try using a cartesian join
SELECT RonsList.[Dwg Number]
, OPT09.[Part Num]
FROM RonsList , OPT09
WHERE RonsList.[Dwg Number] Like OPT09.[Part Num] & "*"
OR OPT09.[Part Num] Like RonsList.[Dwg Number] & "*"

That will drop records where there is no match in either direction.

Another option would be to use a UNION query:
SELECT RonsList.[Dwg Number], OPT09.[Part Num]
FROM RonsList LEFT JOIN OPT09
ON RonsList.[Dwg Number] Like OPT09.[Part Num] & "*"
UNION
SELECT RonsList.[Dwg Number], OPT09.[Part Num]
FROM RonsList LEFT JOIN OPT09
ON OPT09.[Part Num] Like RonsList.[Dwg Number] & "*"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John, the union query did the trick

I will have to read up on union queries now to see what make it tick "S"

Thanks a bunch

John Spencer said:
Well the problem is that 779-302080 is not like 779-302080-1*, although
779-302080-1 is Like 779-302080*

You can try using a cartesian join
SELECT RonsList.[Dwg Number]
, OPT09.[Part Num]
FROM RonsList , OPT09
WHERE RonsList.[Dwg Number] Like OPT09.[Part Num] & "*"
OR OPT09.[Part Num] Like RonsList.[Dwg Number] & "*"

That will drop records where there is no match in either direction.

Another option would be to use a UNION query:
SELECT RonsList.[Dwg Number], OPT09.[Part Num]
FROM RonsList LEFT JOIN OPT09
ON RonsList.[Dwg Number] Like OPT09.[Part Num] & "*"
UNION
SELECT RonsList.[Dwg Number], OPT09.[Part Num]
FROM RonsList LEFT JOIN OPT09
ON OPT09.[Part Num] Like RonsList.[Dwg Number] & "*"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Steve_A said:
I am using Access 2007 and trying to join two tables by matching a partial
field on one of them.

Problem seems to be that if the RonsList.[Dwg Number] does not have a dash
(-) it will not find the partial match


Table name: RonsList
Field name: Dwg Number
Format as text: 779-302080

Table name: OPT09
Field name: Part Num
Format as text: 779-302080-1
779-302080-1-802
779-302080-1-804


SQL
SELECT RonsList.[Dwg Number], OPT09.[Part Num]
FROM RonsList LEFT JOIN OPT09 ON RonsList.[Dwg Number] Like OPT09.[Part Num]
& "*";


Dwg Number should match Part Numbers
779-302080 779-302080-1
779-302080-1-802
779-302080-1-804

148t3000-203 does match 148t3000-20
148t3000-204
148t3000-205
148t3000-207
Click to expand...
Click to expand...
 
Back
Top