Nested IIF Query Too Complex

[kestefon]

I am trying to create a query that transposes data from multiple fields into one "Variable Value" field. The query works, but I'm hitting Access' complexity limit for nested IIF statements. I Googled for a solution, and someone suggested table joins instead of IIf logic... any idea how I would go about doing that?

For context:
-"data" is the raw data file with the fields I'm trying to transpose
-"label" is a table that includes all of the field names, which are populated into a new field called "Variable Name"

 SELECT label.[Variable Name],IIf(label.[Variable Name] = "Business to Business-$$$ (000)", data.[Business to Business-$$$ (000)],
IIf(label.[Variable Name] = "Cable TV-$$$ (000)", data.[Cable TV-$$$ (000)],
IIf(label.[Variable Name] = "FSI Coupon-$$$ (000)", data.[FSI Coupon-$$$ (000)],
IIf(label.[Variable Name] = "Local Magazine-$$$ (000)", data.[Local Magazine-$$$ (000)],
IIf(label.[Variable Name] = "Local Newspaper-$$$ (000)", data.[Local Newspaper-$$$ (000)],
IIf(label.[Variable Name] = "Local Sunday Supplement-$$$ (000)", data.[Local Sunday Supplement-$$$ (000)],
IIf(label.[Variable Name] = "National Cinema-$$$ (000)", data.[National Cinema-$$$ (000)],
IIf(label.[Variable Name] = "National Internet-$$$ (000)", data.[National Internet-$$$ (000)],
IIf(label.[Variable Name] = "National Magazine-$$$ (000)", data.[National Magazine-$$$ (000)],
IIf(label.[Variable Name] = "National Newspaper-$$$ (000)", data.[National Newspaper-$$$ (000)],
IIf(label.[Variable Name] = "National Sunday Supplement-$$$ (000)", data.[National Sunday Supplement-$$$ (000)],
IIf(label.[Variable Name] = "Network Radio-$$$ (000)", data.[Network Radio-$$$ (000)],
IIf(label.[Variable Name] = "Network TV-$$$ (000)", data.[Network TV-$$$ (000)],
IIf(label.[Variable Name] = "Outdoor-$$$ (000)", data.[Outdoor-$$$ (000)],
IIf(label.[Variable Name] = "Regional Cinema-$$$ (000)", data.[Regional Cinema-$$$ (000)],
IIf(label.[Variable Name] = "Spanish Language Cable TV-$$$ (000)", data.[Spanish Language Cable TV-$$$ (000)],
IIf(label.[Variable Name] = "Spanish Language Network TV-$$$ (000)", data.[Spanish Language Network TV-$$$ (000)],
IIf(label.[Variable Name] = "Spot Radio-$$$ (000)", data.[Spot Radio-$$$ (000)],
IIf(label.[Variable Name] = "Spot TV-$$$ (000)", data.[Spot TV-$$$ (000)],
IIf(label.[Variable Name] = "Syndicated TV-$$$ (000)", data.[Syndicated TV-$$$ (000)], "Error")))))))))))))))))))) AS [Variable Value]

[Xipooo]

Maybe you should try using the "Switch" function instead.

[kestefon]

Could you give an example of how I would apply it to this? I'm not familiar with that one...

[kestefon]

I tried using Switch instead of IIF, but I'm still getting a complexity error when I try it with all 20 variable names...

[kestefon]

Still can't seem to figure this one out. Is there a way for me to set this up using a reference table instead of IIF/Switch logic?

[thebigthing313]

What is the structure of the data and label tables?

[kestefon]

Hi,

See attached for a sample Access database with dummy data. I'm essentially using the query to transpose my data. The query works fine as is, but it fails when I have more fields to transpose. I read somewhere that you could use tables instead of IIf statements, but I'm not sure how to go about it.

Sample.accdb

[JoeM]

I would question the structure of your data. If you have that many fields, it leads me to believe that perhaps your data tables are not normalized, which can make seemingly simple requests much more complex than they have to be.

How is you "data" tables structured? Is it just one record with a ton of fields?
If so, the structure should probably change to just two (or maybe more fields), one for the type/description and one for the associated value.
Then, you should just be able to join/lookup the "variable name" field to this type/description field and return the associated value.

[thebigthing313]

JoeM is correct. Having looked at your sample DB, the data is not normalized. Your "data" file, are you getting this as an external file that you are importing to your Access database? If not, then I would really suggest restructuring the database.

If you don't want to do this, or if this is an external file, then I'm not entirely sure how to go about approaching your query through the query builder, but it can probably be done through VBA.

Have you attempted a VBA solution?

[JoeM]

Yep, that was going to be my next suggestion. If for some reason you cannot alter the structure of your data, I would recommend creating a User Defined Field (UDF) in VBA to do this calculation (where you can use a SELECT CASE statement for all your possible options, and lookup the value you need using a DLOOKUP formula).

You can use the UDF in a query like any other native Access function.

[rpeare]

Horrible Naming structure too. Here are some general rules you should follow when naming ANYTHING in a database (table, query, field, text box, etc)

1. No special characters, underscore (_) is usually the exception, this means no dollar signs, no hyphens, no apostrophes, etc
2. No spaces use underscore instead of a space
3. No reserved words (week, day, month, date, now, etc)

Breaking any of these generally accepted rules will make it MUCH harder on you in your coding as the other folks have already told you. And I echo their sentiments.

Secondly you can, with your current structure (I wouldn't recommend keeping this structure at all) you could use union queries like this:

This is your base query:

SELECT data_Sample.[Parent Company], data_Sample.Brand, data_Sample.Week, "Business to Business-$$$ (000)" AS GroupLabel, data_Sample.[Business to Business-$$$ (000)]
FROM data_Sample;

You'd build a union query like this:

SELECT data_Sample.[Parent Company], data_Sample.Brand, data_Sample.Week, "Business to Business-$$$ (000)" AS GroupLabel, data_Sample.[Business to Business-$$$ (000)]
FROM data_Sample
UNION ALL
SELECT data_Sample.[Parent Company], data_Sample.Brand, data_Sample.Week, "Cable TV-$$$ (000)" AS GroupLabel, data_Sample.[Cable TV-$$$ (000)]
FROM data_Sample
UNION ALL
SELECT data_Sample.[Parent Company], data_Sample.Brand, data_Sample.Week, "FSI Coupon-$$$ (000)" AS GroupLabel, data_Sample.[FSI Coupon-$$$ (000)]
FROM data_Sample

in other words you're creating a separate query for each one of your columns, then just mashing them together in one big query.

my example only shows 3 of your columns you'd have to add your remaining 7 columns.

[kestefon]

Thanks for all the input, everyone! The data file is actually an export from a program, hence the poor structure and naming. The union solution seems to work for what I'm trying to do.

[rpeare]

If your data is coming to you in a text file or other format, you can have access ignore the first row (names of columns) and import the data starting on the second row and name the columns whatever you want.

Might save you some aggravation.