Table join - Including wildcard

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to join two tables to find the like values between them. Any of
the values that possess a wildcard are not being matched. If the one table
has value (AM) and the other table has value (A*) how can I get them to find
one another. I am sure I did not phrase this in Access-speak so please
forgive.
 
An * in the text string stored in a field will not be treated as a wildcard
in a standard join -- it's treated as just another character because a
standard join is an = comparison, not a Like comparison. You would need to
use an nonequi-join.

Provide us with some info about the tables' structures and field names.
 
One table is named OPN_tbl the other is Sample_Data. The like character
between them is the part number, named OPN here. If the OPN in the OPN_tbl
is found in the Sample_Data table then the Sample_Data value will be treated
differently than the rest of the values in the Sample_Data table. An example
of a value in the OPN_tbl is AM29LV320*B120WMI the like value in the
Sample_data table could be AM29LV320DB120WMI. I am not sure if this is the
information you are looking for, if I have not answered your question just
let me know what info you need. Thank you, Joe
 
So let's try this SQL statement as a starting point (note that you'll need
to enter it into the SQL view of the query, as the QBE window cannot display
it):

SELECT OPN_tbl.*, Sample_Data.*
FROM Sample_Data INNER JOIN OPN_tbl
ON Sample_Data.FieldName Like OPN_tbl.FieldName;



--

Ken Snell
<MS ACCESS MVP>
 
the field in the OPN_tbl is named OPN, and the field in the Sample_Data table
is named AMD OPN#. I used those names within your query where you have
fieldname. When I execute the query it does not return results but instead
returns a blank sheet with all the column headings from both tables. Also
when I execute it prompts me with two little boxes to enter a parameter value
for both tables/fields.
 
Ken,

I did not follow your directions properly...I changed the query to the
following, and it worked. Thank you for your help...

SELECT OPN_tbl.OPN, Sample_Data.AMDOPN
FROM Sample_Data INNER JOIN OPN_tbl
 
Back
Top