Create new field from 1 of 3 fields based on value.

[scottc_00]

I have a data table that contains data that was created by someone else. I only take that data and drop it in my table so I can work with it. I realize that the data setup is nonsense. I am just trying to work with what I have.
The table contains 10 fields. 3 of those fields contain values that I want to put in a new field IF the value meets certain criteria. I will call the fields Lvl6, Lvl7, Lvl8, and the new field will be Lvl678.

If the value of Lvl6 = Beatles then Lvl678 = Beatles
If the value of Lvl7 = Kinks then Lvl678 = Kinks
If the value of Lvl7 = Stones then Lvl678 = Stones
If the value of Lvl8 = Doors then Lvl678 = Doors
Etc



I tried to set up a new “rollup” table like this
Lvl6 Lvl7 Lvl8 Lvl678
Beatles Beatles
Kinks Kinks
Stones Stones
Doors Doors

And then I created a query to join the data table with this “rollup” table on Lvl6, Lvl7, Lvl8 so that the resulting data would include the field Lvl678.

I adjusted the Join Properties for ALL rows from the data table and ONLY the rows from the “rollup” table where the joined fields are equal.

So I am getting the data but the field Lvl678 is blank. I tried different join properties with no luck. FYI…I am fairly new to Access. I have been using it for years to store data and pull it out using Excel pivot table setc but I am no expert when it comes to Queries etc.

Thanks for your help.

[scottc_00]

I have figured out that I can use the switch function and that works fine. But it would be nice if this table comparison would work. Anyone have any ideas ?

[June7]

Not really understanding why you have the 'rollup' table anyway. A query can generate the Lvl678 field and retrieve all the data from the original table.

[scottc_00]

The only way I know to do that is by using the switch function. That works but it seems like a lot of hard keyed criteria in an experession. If I ever need to build another query similarly I will have to do the same thing again. I think if I had the rollup table I could just use that same information in all my queries that need that rollup. Also if anything changes I would only have to change it in one place if I use a rollup table. If I use a switch function in all of my queries I will have to change it in each individual query.

It is highly likely that I am missing something obvious to someone with more experience. Any advise would be greatly appreciated.
Thanks

[June7]

A query can be used in other queries, like a table.

I do not understand why you want the calculation anyway. I do suspect data structure is not normalized.

If you want to provide db for analysis, follow instructions at bottom of my post.