Ultimate goal update query based on values from another table with partial text strings from a field in another table

[TB11]

My ultimate goal is to reach a point where I can do an append query based on the results of the query.

Tables invovled
FileNameTbl: IDFileName, FileName
1 BackgroundTextureRoughBlack
2 YellowSmoothTextureAbstract

FileNameSnipetsTbl: IDSnipet, Snipet
1 Black
2 Rough
3 Smooth
4 Yellow
5 Texture

ColorNamesTbl: IDColor, Color
1 Yellow
2 Black

TextureTbl: IDTexture, Texture
1 Rough
2 Smooth


Query needed: Somehow pull out the words Yellow or Black from the file name (FileNameTbl) and get the IDColor that will be appended to original FileNameTbl.
Then I would like to do the same thing (hopefully simultaneously) for the texture.

I'm stuck. I've done a query with the inst function, bascially running a new update query for each field in my color table, but that was tedious.

Any help is appreciated.

 

[theDBguy]

Hi. Try to do a search on "non-equi" joins.

 

[TB11]

* I meant update not append (oops)

 

[The_Doc_Man]

The fact that you have the attributes jammed together into a single string means you have to do some kind of wildcard scheme.

Can you give us a before-and-after of what you have and what you want? Your "Somehow pull out the words Yellow or Black from the file name" is a little vague. It might be possible to do this as a layered query with a "LIKE" operator and some wildcarding but at the moment, I can't visualize where you need this to go.

 

[TB11]

Sorry about that.

The FileNameTbl with the following results:, with fk for both the color and texture fields from the FileNameSnipetsTbl

FileNameTbl: IDFileName, FileName, fkTexture, fkColor
1, BackgroundTextureRoughBlack , 2, 2
2, YellowSmoothTextureAbstract, 3, 4

FileNameSnipetsTbl: IDSnipet, Snipet, fkColor, fkTexture
1 Black, 2, 0
2 Rough, 0, 1
3 Smooth, 0, 2
4 Yellow, 1, 0

I would like to have a query (or two) return the ID from the FileNameSniptetsTbl where the Snipet is part of the text string for the FileName field in the FileNameTbl.

 

[TB11]

@The_Doc_Man I'm trying categorize thousands of records based on the file names.

 

[MajP]

For the color update

UPDATE colornamestbl,
       filenametbl
SET    filenametbl.fkcolor = [idcolor]
WHERE  (( ( filenametbl.filename ) LIKE "*" & [color] & "*" ));

do another exactly like that for the texture.
Same for snippet table. I would do the four update queries and then in vba run them all at once.

 

[MajP]

However, I assume the name can only have one color and one texture. If it can have more you will need child tables.

 

[TB11]

@MajP THANK YOU! I can't even guess how many hours you saved me.