Lookup and Like

[Thompyt]

I have a query where I want lookup a field in a reference table.

tbleRef
Wave
Type
Out

TblA
Nets
Waves
Sub

I am trying to do something like this:

SELECT tblA.Waves, IIf([Nets] Like "*" & [Type] & "*",[Out],"") AS [Output]
FROM tblref LEFT JOIN tblA ON tblref.Type = tblA.Waves
WHERE (((tbla.WaveForms)="VHF"));

TblRef.out
CMD
FS
Fires
F1..........................

I have tried right/left/inner join but get multiple outs for the same row

What I am attempting is to look at a part of tbla.Nets field where it is like one of the rows in tblref.type and display tblref.out

[aytee111]

I think there would be no join - you want every record in tblref to be compared to every record in tbla. Depending on the number of records, this could take a while. Do a prior query which filters the records, that will have less records to deal with.

[Micron]

Maybe you should post some sample data and indicate the desired output. You're joining Type from one table to Waves in another, which might be perfectly OK, but the difference in field names makes one wonder. I find that copying/pasting in a post from Excel usually results in a nice quick table. Seems to work best if the source data is centered first.

[Thompyt]

I've simplified it without a join and added an ID number to tblA

SELECT TblA.UID, IIf([Nets] Like "*" & [Type] & "*",[Out],"") AS test
FROM TblA, TblRef
WHERE (((IIf([Nets] Like "*" & [Type] & "*",[Out],""))<>"") AND ((TblA.Waves)="VHF"));

Thanks

Micron
Database1.zip

[Micron]

What I am attempting is to look at a part of tbla.Nets field where it is like one of the rows in tblref.type and display tblref.out

try

SELECT TblRef.Out
FROM TblRef
WHERE (((Exists (select * from tblA where TblA.Nets like "*" & tblRef.[Type] & "*"))<>False));

If that's not right, I have no other clue because the data is very odd. If it is right, it is a miracle, because me and subqueries don't usually get along.
BTW, "Type" is a poor name for any Access object since it's a reserved word.

[Thompyt]

Thanks Micron,
Your codes gives 1 instance of what was output in the query.

[Micron]

I take it that the subquery approach worked. In that case, glad to have helped.