By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Using definitions table for lookup in records

P: 17
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.
Aug 8 '10 #1
Share this Question
Share on Google+
14 Replies


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

(defTable:custTable)
Aug 8 '10 #2

P: 17
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
Aug 9 '10 #3

Jerry Maiapu
100+
P: 259
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
Aug 9 '10 #4

Delerna
Expert 100+
P: 1,134
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

Expand|Select|Wrap|Line Numbers
  1. SELECT field list
  2. FROM table1
  3. JOIN table2 on table1.f like '%' + table2f + '%'
  4.  
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
Aug 9 '10 #5

P: 17
Hi all,

So I did figure out how to solve this problem:

Expand|Select|Wrap|Line Numbers
  1. SELECT defTable.Name
  2. FROM defTable, Cust
  3. WHERE (((Cust.Details) Like "*" & [defTable].[Name] & "*"))
  4. ORDER BY Cust.ID;
Thanks for the hints. I got there.

Mark.
Aug 9 '10 #6

NeoPa
Expert Mod 15k+
P: 31,768
This is a solution Mark, but is somewhat jumbled in its makeup (although should produce the results you require). Try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tDT.Name
  2. FROM     Cust AS tC INNER JOIN
  3.          defTable AS tDT
  4.   ON     tC.Details Like '*' & tDT.Name & '*'
  5. ORDER BY tC.ID
As a general rule it is worth avoiding any processing based on cartesian products (See SQL JOINs).
Aug 9 '10 #7

NeoPa
Expert Mod 15k+
P: 31,768
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.
Aug 9 '10 #8

Delerna
Expert 100+
P: 1,134
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.
Aug 9 '10 #9

Delerna
Expert 100+
P: 1,134
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
Aug 9 '10 #10

NeoPa
Expert Mod 15k+
P: 31,768
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 ;)
Aug 9 '10 #11

Delerna
Expert 100+
P: 1,134
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
Aug 10 '10 #12

Delerna
Expert 100+
P: 1,134
Ps
That said, I do agree with the counter points Neopa raised
Aug 10 '10 #13

NeoPa
Expert Mod 15k+
P: 31,768
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.
Aug 10 '10 #14

Delerna
Expert 100+
P: 1,134
Yes, I had a chuckle over that sentence too
Aug 10 '10 #15

Post your reply

Sign in to post your reply or Sign up for a free account.