Using definitions table for lookup in records

[Medwar19]

Hi all,

Am struggling here with how to use records in one table to lookup matches in another table records.

Table1 contains the lookup terms and is called 'defTable' which has a Field 'Name' and records:

Cat
Dog
Fish
Pony

Table2 is called 'custTable' which has a Field 'Details' and records:

MrsSmithhasaCatwhosenameistabby
johnthoughhisFishwassick
JasonsDoghasbeenshedingthisweek

I want to search all the records in defTable for a match in each record of custTable and return the match text

i.e.

MrsSmithhasacatwhosenameistabby returns Cat
johnthoughhisFishwassick returns Fish
JasonsDoghasbeensheedingthisweek returns Dog

Thanks for your time

Mark.

[Jerry Maiapu]

Mark,
You problem should be take care of if you know more about table relationships.
Relate the tables. (1:many)..

(defTable:custTable)

[Medwar19]

Hey there,

Not sure I understand? I related the tables 1:many and tried entering a query with criteria instr(1,Name,Details) but Details is type memo so the join fails...

Thanks,

Mark

[Jerry Maiapu]

Why not create num field in custTable and join with Auto number field in defTable.
That'll do. You cannot create relationships using memo type fields

[Delerna]

If I am understanding you correctly

The key point here is

Am struggling here with how to use records in one table to lookup MATCHES in another table records.

I think you need to join the two tables to achieve your goal
and the only way you could join two tables
on a field with the value Cat in one
and MrsSmithhasaCatwhosenameistabby in the other joining fields would be through the keyword like

SELECT field list
FROM table1
JOIN table2 on table1.f like '%' + table2f + '%'
 

There are 2 problems here
1) That is not legal syntax, you can't use like in a join expression
2) Even if it was legal syntax it could produce very slow queries.


I think you might need to rethink what you are trying to do.....there is always more than one way to skin a cat

Try using recordsets in vba

[Medwar19]

Hi all,

So I did figure out how to solve this problem:

SELECT defTable.Name
FROM defTable, Cust
WHERE (((Cust.Details) Like "*" & [defTable].[Name] & "*"))
ORDER BY Cust.ID;

Thanks for the hints. I got there.

Mark.

[NeoPa]

This is a solution Mark, but is somewhat jumbled in its makeup (although should produce the results you require). Try this :

SELECT   tDT.Name
FROM     Cust AS tC INNER JOIN
         defTable AS tDT
  ON     tC.Details Like '*' & tDT.Name & '*'
ORDER BY tC.ID

As a general rule it is worth avoiding any processing based on cartesian products (See SQL JOINs).

[NeoPa]

Delerna: There are 2 problems here
1) That is not legal syntax, you can't use like in a join expression
2) Even if it was legal syntax it could produce very slow queries.

<snip>
Try using recordsets in vba

I don't mean to contradict, but Like is usable in a JOIN expression. Certainly within Jet-SQL.

I agree that anything along these lines would be fundamentally slow, however :

1. Anything based on a cartesian product with a WHERE clause could only be slower still. It may manage to keep up due to optimisations, but logically it can never be faster.
2. Recordsets are themselves built on SQL of some form. I can't conceive of a scenario where something could perform quicker at the client end (with recordset processing) than could be possible at the server end (SQL).

I can't claim to have done thorough time trials on any of this, but the SQL I posted did work for me in a very similar environment.

[Delerna]

I don't mean to contradict, but Like is usable in a JOIN expression.
...
...
I can't claim to have done thorough time trials on any of this, but the SQL I posted did work for me in a very similar environment.

hmmmmm
contradict me if I am wrong by all means but when I tried it, and I haven't done extensive testing either as I just threw something together to try out the theory for this question, I got an error message along the lines that like could not be used in that context and concluded that it is not legal syntax.

Based on your experience I'll try again and see what the error I got really meant.

By the way, apologies for the %, I develop using microsoft SQL server and that is the sytax for it.

[Delerna]

Neopa, You are correct. I had accidentally selected Memo for the field types and the error was actually fields of type Memo cannot be used in joins. When I saw the error I made an assumption and we all know what happens when we assume

[NeoPa]

Really no need to apologise Delerna. I make mistakes regularly. You've more than earned the right to one or two.

As for the % syntax, although SQL Server does indeed use this as standard, this is not a package relateive thing as much as a standards one. Access, from 2003 onwards, has had an option to be ANSI-92 compliant, rather than the ANSI-89 as supported by previous versions. Thus it is not correct to say Access doesn't use the % syntax. It merely isn't used often. Most Access dbs out there seem to use the ANSI-89 (*) syntax still. The rest of the world will catch up with you some day soon I expect ;)

[Delerna]

Yea, especially as I get older mistakes come easier.


Incidentally, my comment on producing slow queries was just a gut feeling I had when thinking about a text like this.

Dogbreath's nicname is eponymous to a dogs breath and his concatenation techniques are fishy to say the least

and the matches a query using like in a join

(if you could and as it turns out you can)

might return.

This record alone would return 4 matching records for the bolded parts and I feel that none of them were actually meant to be matches. Using like in a where condition here would only return the record once although still accidentally.
There would of couse be ways around this, by putting a leading and trailing space on the words in the defTable for example

[Delerna]

Ps
That said, I do agree with the counter points Neopa raised

[NeoPa]

Delerna: Dogbreath's nicname is eponymous to a dogs breath and his concatenation techniques are fishy to say the least

It's a valid point, but I would guess from the OP's first post that they're more interested in seeing all the flag words than finding each record as such. Their question only mentions displaying the flag word, yet one must suppose that was to keep it short and to the point. I expect other fields are displayed also, that they need no help with.

Bear in mind also, the data seems to be all of a type - Comments related to animals managed at some form of menagerie. A couple of false positives probably wouldn't be the end of the world.

Other techniques can be used of course, to give similar and even different results, should that be required.

Lastly, nice example sentence :) Quite innovative.

[Delerna]

Yes, I had a chuckle over that sentence too