Lookup Table

[rachael]

Hello -
I am trying to creat a lookup table that is based on contains, not the
entire field matching exactly.

For example, in my lookup table, I have two columns "lookup_value" and
"number". the lookup value field contains entries like 'smi" or "joh" the
number field contains 1 and 2 respectively. my main table has two fields
"last_name" and "result". I want to write a qry that says if last_name
contains *lookup_value" then update "result" with corresponding number, else
leave null. So if the last_name field is smith or resmith or smithson, I
want number 1 returned.

thoughts?

 

[Jeff Boyce]

Rachael

You are describing "how", but "how" depends on "what"...

If you have something already in your main table, why do you want to
duplicate that data in your lookup table?

And if you had a [LastName] = "smitjohn", would you want a "1" or a "2" or
???! returned? How are you ensuring that your search terms are mutually
exclusive?

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[rachael]

Hi -

The example I gave was a symplification of the data I'm actually working
with. I have a bunch of text data that I need to group. There are key words
within the data. So I'm trying to say: if data contains key word x then
identify as product a, if data contains key word y then identify as product
b. There are about 62 different key words that can be identified as 30
products.

Rachael

You are describing "how", but "how" depends on "what"...

If you have something already in your main table, why do you want to
duplicate that data in your lookup table?

And if you had a [LastName] = "smitjohn", would you want a "1" or a "2" or
???! returned? How are you ensuring that your search terms are mutually
exclusive?

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello -
I am trying to creat a lookup table that is based on contains, not the
entire field matching exactly.

For example, in my lookup table, I have two columns "lookup_value" and
"number". the lookup value field contains entries like 'smi" or "joh" the
number field contains 1 and 2 respectively. my main table has two fields
"last_name" and "result". I want to write a qry that says if last_name
contains *lookup_value" then update "result" with corresponding number,
else
leave null. So if the last_name field is smith or resmith or smithson, I
want number 1 returned.

thoughts?

 

[Jeff Boyce]

My question still stands... if more than one of the keywords is 'found',
what do you want Access to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi -

The example I gave was a symplification of the data I'm actually working
with. I have a bunch of text data that I need to group. There are key
words
within the data. So I'm trying to say: if data contains key word x then
identify as product a, if data contains key word y then identify as
product
b. There are about 62 different key words that can be identified as 30
products.

Rachael

You are describing "how", but "how" depends on "what"...

If you have something already in your main table, why do you want to
duplicate that data in your lookup table?

And if you had a [LastName] = "smitjohn", would you want a "1" or a "2"
or
???! returned? How are you ensuring that your search terms are mutually
exclusive?

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello -
I am trying to creat a lookup table that is based on contains, not the
entire field matching exactly.

For example, in my lookup table, I have two columns "lookup_value" and
"number". the lookup value field contains entries like 'smi" or "joh"
the
number field contains 1 and 2 respectively. my main table has two
fields
"last_name" and "result". I want to write a qry that says if last_name
contains *lookup_value" then update "result" with corresponding number,
else
leave null. So if the last_name field is smith or resmith or smithson,
I
want number 1 returned.

thoughts?

 

[rachael]

Well, I was hoping to set up a second qry to check for this issue.

My question still stands... if more than one of the keywords is 'found',
what do you want Access to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi -

The example I gave was a symplification of the data I'm actually working
with. I have a bunch of text data that I need to group. There are key
words
within the data. So I'm trying to say: if data contains key word x then
identify as product a, if data contains key word y then identify as
product
b. There are about 62 different key words that can be identified as 30
products.

Rachael

You are describing "how", but "how" depends on "what"...

If you have something already in your main table, why do you want to
duplicate that data in your lookup table?

And if you had a [LastName] = "smitjohn", would you want a "1" or a "2"
or
???! returned? How are you ensuring that your search terms are mutually
exclusive?

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello -
I am trying to creat a lookup table that is based on contains, not the
entire field matching exactly.

For example, in my lookup table, I have two columns "lookup_value" and
"number". the lookup value field contains entries like 'smi" or "joh"
the
number field contains 1 and 2 respectively. my main table has two
fields
"last_name" and "result". I want to write a qry that says if last_name
contains *lookup_value" then update "result" with corresponding number,
else
leave null. So if the last_name field is smith or resmith or smithson,
I
want number 1 returned.

thoughts?

 

[Jeff Boyce]

I suspect you'll find it sooner than you expected ... but, hey, we'll burn
that bridge when we get to it!

I'm thinking that you could use the Eval() function to add in the matching
criteria into the selection criterion area under the field in the design
view of a query, something like:

Like * & ... & *

Have you tried creating a query and joining the two tables?

You might also use the Instr() function to see if you can find the matching
field value.

If the query approach doesn't work, you might try a 'brute force' approach,
doing each conversion/update one-by-one. If you'll never need to do this
again, this may prove faster than trying to work out the query approach.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Well, I was hoping to set up a second qry to check for this issue.

My question still stands... if more than one of the keywords is 'found',
what do you want Access to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi -

The example I gave was a symplification of the data I'm actually
working
with. I have a bunch of text data that I need to group. There are key
words
within the data. So I'm trying to say: if data contains key word x
then
identify as product a, if data contains key word y then identify as
product
b. There are about 62 different key words that can be identified as 30
products.

:

Rachael

You are describing "how", but "how" depends on "what"...

If you have something already in your main table, why do you want to
duplicate that data in your lookup table?

And if you had a [LastName] = "smitjohn", would you want a "1" or a
"2"
or
???! returned? How are you ensuring that your search terms are
mutually
exclusive?

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello -
I am trying to creat a lookup table that is based on contains, not
the
entire field matching exactly.

For example, in my lookup table, I have two columns "lookup_value"
and
"number". the lookup value field contains entries like 'smi" or
"joh"
the
number field contains 1 and 2 respectively. my main table has two
fields
"last_name" and "result". I want to write a qry that says if
last_name
contains *lookup_value" then update "result" with corresponding
number,
else
leave null. So if the last_name field is smith or resmith or
smithson,
I
want number 1 returned.

thoughts?

 

[rachael]

i'm going to go with the one by one approach. Thanks!

I suspect you'll find it sooner than you expected ... but, hey, we'll burn
that bridge when we get to it!

I'm thinking that you could use the Eval() function to add in the matching
criteria into the selection criterion area under the field in the design
view of a query, something like:

Like * & ... & *

Have you tried creating a query and joining the two tables?

You might also use the Instr() function to see if you can find the matching
field value.

If the query approach doesn't work, you might try a 'brute force' approach,
doing each conversion/update one-by-one. If you'll never need to do this
again, this may prove faster than trying to work out the query approach.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Well, I was hoping to set up a second qry to check for this issue.

My question still stands... if more than one of the keywords is 'found',
what do you want Access to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi -

The example I gave was a symplification of the data I'm actually
working
with. I have a bunch of text data that I need to group. There are key
words
within the data. So I'm trying to say: if data contains key word x
then
identify as product a, if data contains key word y then identify as
product
b. There are about 62 different key words that can be identified as 30
products.

:

Rachael

You are describing "how", but "how" depends on "what"...

If you have something already in your main table, why do you want to
duplicate that data in your lookup table?

And if you had a [LastName] = "smitjohn", would you want a "1" or a
"2"
or
???! returned? How are you ensuring that your search terms are
mutually
exclusive?

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello -
I am trying to creat a lookup table that is based on contains, not
the
entire field matching exactly.

For example, in my lookup table, I have two columns "lookup_value"
and
"number". the lookup value field contains entries like 'smi" or
"joh"
the
number field contains 1 and 2 respectively. my main table has two
fields
"last_name" and "result". I want to write a qry that says if
last_name
contains *lookup_value" then update "result" with corresponding
number,
else
leave null. So if the last_name field is smith or resmith or
smithson,
I
want number 1 returned.

thoughts?