Trouble using Like Operator.....having a dumb friday

[dmorand]

I've got a table which contains some items that I need to compare against another table which contains a more detailed look at the items, and there could be multiple.

Example:

Table1:
Field: ACETAMINOPHEN

Table2:
Fields: ACETAMINOPHEN
ACETAMINOPHEN CHEWABLE
ACETAMINOPHEN DROPS
ACETAMINOPHEN SUPP
ACETAMINOPHEN SUSP
ACETAMINOPHEN W/COD #3
ACETAMINOPHEN W/COD ELIX
ACETAMINOPHEN W/COD LIQ 15ML

I want to be able to run a query which would look at ACETAMINOPHEN and retrieve all values from Table2 where the field is like ACETAMINOPHEN

Here is my query I'm using which returns 0 results:

SELECT TABLE1!PRIMARY_NAME AS OS_DRUG
FROM TABLE1, TABLE2
WHERE ((([TABLE2]![DETAILEDDRUG]*) Like [TABLE1]![PRIMARY_NAME]))
ORDER BY TABLE1!PRIMARY_NAME;
 

I tried using the below as well, but get no results.

SELECT TABLE1!PRIMARY_NAME AS OS_DRUG
FROM TABLE1, TABLE2
WHERE ((([TABLE2]![DETAILEDDRUG] + '*') Like [TABLE1]![PRIMARY_NAME]))
ORDER BY TABLE1!PRIMARY_NAME;
 

[NeoPa]

You don't say exactly what the fields are called and I suspect what you describe as field values are actually different records, but try this :

SELECT TABLE1.PRIMARY_NAME AS OS_DRUG,
       TABLE2.DETAILEDDRUG AS DTL_DRUG
 
FROM   TABLE1 LEFT JOIN TABLE2
  ON   (TABLE2.DETAILEDDRUG LIKE '*' & TABLE1.PRIMARY_NAME & '*')
 
ORDER BY TABLE1.PRIMARY_NAME

[dmorand]

Excellent, that worked like a charm, I appreciate your help!!!!!

[NeoPa]

Very pleased to hear it :)

Did you follow what and why though?