Iif Contains in column from other table

[NSAMSA]

Hi, I have run into a conundrum that I have spent far too many hours trying to work through. :banghead: Hopefully I can find some assistance here.

I'm trying to streamline a presentation for a department in my company by pulling their spreadsheets into access via the external data, and running various queries to dump back into another excel sheet that would present the specific information they're looking for, as opposed to them constantly repopulating. One issue I've run into is that one of the categories, (colour), has various entries with notes in the same cell, (e.g. instead of Georgia Green, it has "Georgia Green 11 Shop 2:35am"). I need a column that simply has "Georgia Green". Clearly if I only had one or four colours, I could just use an iif statement with the "like" operator. However, we have over a dozen colours, and we'll be adding more next year.

Sorry about the long intro, but what I want to do is create a table with Like lookup words like "*Georgia*" and another column with the desired entry "Georgia Green" and have an iif statement that looks through the first column and if the entry contains a word from the column, the formula cell will be populated with the corresponding name. Something like the following:

Iif(Table1.Color Like "*Table2.ColorCode*", Table2.ColorTitle, ""

This clearly does not work, but I think you get the point. Any ideas?

thank you in advance for any help and/or any consideration of the question.

 

[plog]

You have a few issues. For example, suppose Table2 has these 2 colors:

ColorCode, ColorTitle
Blue, Blue
Blue Green, Aqua
Royal Blue, Royal Blue

When you get 'Blue Green Blah Blah Blah' or 'Royal Blue 123456 XYZ' in Table1, which does it resolve too? Both of those will match their specific colors as well as the 'Blue' record.

I'd really need to know more about the values where the color appears. Can you logically state any rules about the color? For example,

The color always begins at the first character.
A space and then a number always appear after the last character.
The smallest color is 9 characters long.
The longest color is 18 characters long.
Etc.

Can you tell us any rules like that. That could be the key to extracting it.

 

[NSAMSA]

I assumed that was the issue. However, with the format of the excel page I'm linking to, it would never come up. Essentially, the page actually has a drop down in which the colors are located and they choose one. However, often they unlock the page and put notes. For example, when changing to Georgia Green, they will choose that color, the unlock and put 5:09am to indicate the time they tested. If the color is not within spec, they would test again and unlock to put 7:03am. etc. However, the color will always be Georgia Green, and therefore I could search for "Georgia Green" or even "Georgia". If they have "Spring Green", they will choose that color. Unfortunately, I do not have control of that sheet, so I cannot make any changes to normalize the color column and add a comment column, or anything like that that would make sense.

Regarding your question, the cell always does begin with the color and notes are added afterwards. Is there a way to do what I want asking for the query to search for cells "beginning with"?

I hope that all made sense.

 

[plog]

Yes it would be:

Like "Your Color Here*"

However, its going to take a lot of processing power to match every record against every color. If you could devise some logical rules like I listed above it would help speed things up.

 

[NSAMSA]

I'm still not certain how I would be able to coordinate with my other table. For example, if I have only 4 records so far:

Georgia Green 8:00AM
Horizon Blue
Georgia Green 7:36AM
Slate Blue 5:22pm

And my table with the colors looks like:

Color_Like Color
Georgia Georgia Green
Horizon Horizon Blue
Slate Slate Blue

How do I make the query enter the like formula you state above, look through column A and result in column B?

 

[plog]

You're on the right track with a mapping table. Here's how I would do it:

1. Find the length of the shortest color identifier in your external table (let's call this T1). With the example you have given it is 'Slate Blue' at 10 characters.

2. Create a mapping table (map_Colors) with 2 fields like so:

ColorCode, text, 10 characters
ColorFull, text, 255 characters

3. Make an APPEND query on T1 to get put all the unique 10 characters strings from it into map_Colors.

4. Update the ColorFull field of map_Colors to what each color code should resolve to. After this step, using the sample data you posted, it would look like this:

map_Colors
ColorCode, ColorFull
Georgia Gr, Georgia Green
Horizon Bl, Horizon Blue
Slate Blue, Slate Blue

5. Whenever you need to get the color of a record in T1, you make a query (Q1), bring in all its fields plus a calculated field to get the first 10 characters of the color value. Then you build another query using Q1 and map_Colors linking that 10 character calculated field to map_Colors.ColorCode.

Be aware though, this lends it to the issue I mentioned before--duplicates. For example Georgia Green might be confused with Georgia Gray.

 

[NSAMSA]

Thank you so much! I know how I'm going to do it now. Use the left function make a table and then query off of that table connecting the color key with the left column. I really appreciate all of your help.