Update Query where the update is based on table values???

[Myself]

Views: 6 Size: 432.0 KB">ExampleDatabase.accdb

Goal: I want to convert the name of every thing from my Main Table to its corresponding color and I want this information to come from a Color Table. Example: Cherry to Red, Apple to Red, Sky to Blue.

I want to have the query get the color from a Color Table because I want to be able to expand the list easily as time goes on. Also, this list needs to be able to be quite large.

I am currently using nested IIF statements but that will not cut it as the number of items grows.

I don't know how to orient the table for this to work best and I don't know how to use the query to accomplish this.

If anyone has any ideas or can point me in the right direction I would really appreciate it.

[June7]

So color table 'maps' object name to color? Or save colorID into main table?
Query joins tables on related fields.

[Myself]

ExampleDatabase.accdb

I need the color table to map the names of the things from the Main Table to its corresponding color from the Color Table. So after I run the update query the Main Table no longer will have names like Apple and Sky but instead will only have the corresponding colors red and blue.

[orange]

Main Table no longer will have objects but instead will only have the corresponding colors.

I have no idea of what your request is asking. Object(s) is a meaningful term in database.

Perhaps someone else is on your wavelength.
Often clearer communication if you give us:
a) some starting data
b) a sample of your desired output.

Good luck.

[Myself]

Thank you for your input. I have attached an example database and have changed the language of "object"
I hope my question is more clear.

[June7]

Why would you want to eliminate 'objects' data?

Neither colors table is properly structured for linking to main table (the one named "(Better Orientation?)" is especially nonsensical, definitely NOT better). Probably needs to be like:

ID	Color	Obj
1	Blue	Sky
2	Blue	Ocean
3	Blue	Sapphire
4	Red	Cherry
5	Red	Apple
6	Red	Ruby
7	White	Cloud
8	White	Baking Soda
9	White	Egg

[Myself]

Sure an output like that is fine.

Functionally I want to be able to import a fresh "Main Table" full of new things, run the query where it converts all the things to colors, and export colors. I want to use the access database as a machine to data correct excel sheets.

[June7]

Edited my comment. Might review again.

Is this real data?

How would it be meaningful to reduce data to colors only?

How would Access know which colors to assign to 'new' things if they aren't already in color map table?

[Myself]

The initial idea was something like it would search the color table and then if it was found it could return the column name or row name. So the table has all red items sorted in a row or column.

I don't know how that matters exactly. But its for data cleaning survey information. So if a survey asks what is your favorite drink and people respond soda, pop, and coke then for my purposes I want to convert all of that into one word like Soda. Similarly if I ask what are your favorite shoes and they respond flip flops or sandals, or chanklas then i want to convert all of those entries to sandals. The reason I want a table to manage this information is because someone may give me a new word for Sandals that I want to add to my list.

[June7]

And what is the decision logic you want Access to follow? Must be data in a 'mapping' table. If Chanklas isn't already there, then Access will not 'know' what to do with it. You must 'teach' by creating record. If you want to reduce code then build lookup table as I show. Then if object is not found, prompt user to select color (or some category such as "Sandals") for associating with new object and create new record.

If new main table has all new items not already in lookup table then this is useless. User must make decision for every record.

[Myself]

Thank you for your help. I have made your changes and it works just fine. I am worried that as time goes on and this list gets longer and longer it will get more difficult for people to interact with it or harder to manage. But it works and i'm happy. Thank you.