Too many nested IIF's

[Gareth Campbell]

Hello Everyone,

While I could run this in SQL, I'm not entirely used to the Access way of doing things. I have a query that has far too many nested IIFs and have been trying to figure out if there is a way to code the following in VBA? Or if there is another more efficient way? Any help would be greatly appreciated!

Country_Assignment:
IIF(TRIM(UCASE([dbo_CUSTOMER]![Postcode]))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_name])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE( [dbo_CUSTOMER]![Postcode] ))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_Code])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address1]))=TRIM(UCASE([ISO_Country_LOOKUP]![Country_Code])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address2]))=TRIM(UCASE([ISO_Country_LOOKUP]![Country_Code])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address3]))=TRIM(UCASE([ISO_Country_LOOKUP]![Country_Code])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address4]))=TRIM(UCASE([ISO_Country_LOOKUP]![Country_Code])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address5]))=TRIM(UCASE([ISO_Country_LOOKUP]![Country_Code])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address6]))=TRIM(UCASE([ISO_Country_LOOKUP]![Country_Code])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address1]))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_name])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address2]))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_name])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address3]))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_name])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address4]))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_name])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address5]))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_name])), [ISO_Country_LOOKUP_1]![Country_name]
    ,IIF(TRIM(UCASE([dbo_CUSTOMER]![Address6]))=TRIM(UCASE([ISO_Country_LOOKUP_1]![Country_name])), [ISO_Country_LOOKUP_1]![Country_name]
    ,"UNKNOWN"))))))))))))))
 

Gareth

[NeoPa]

Gareth, what are you trying to achieve (and please don't expect me to reverse-engineer your code to work out what your question should include)?

There are various functions (Switch(); Index(); etc) that can help in some case but the details determine where and when.

[Gareth Campbell]

Hi thanks for your response.

I have a central customer table dbo.Customer with the following fields:
Address1
Address2
Address3
Address4
Address5
Address6
Postcode

And a country table that has:
Country_code
Country_name

What I'm trying to do is check each variable on the customer table (for each row) for the Country_code and the Country_name and then if it exists, assign a variable Country with the Country_name.

So if I match on any of those fields for a country name OR a country code then I get assigned that country name.

Thanks

Gareth

[Gareth Campbell]

Sorry just to be completely clear, I want to end up with a table that has:

Customer_Code
Country

That is populated by checking each field listed above and if there is a match then a new entry goes into the new table with Country.

[ADezii]

What Field in dbo.Customer would uniquely identify a Customer? I see no such Field present in the Table.

[Rabbit]

IIf(Country_Code IN (PostCode, Address1, Address2, etc), Country_Name, IIf(Country_Name IN (PostCode, Address1, Address2, etc), Country_Name, "Unknown")

[Gareth Campbell]

Sorry forgot to include it in my description, the id field is Customer_Code

[Gareth Campbell]

Rabbit,

Is it really that simple!?!? I understand the code, but how do I join my tables to run that query... There are no common fields between the two tables, so how would a join work without doing a separate query where I left join on every field separately.

[Gareth Campbell]

Customer:
Customer_Code
Address1
Address2
Address3
Address4
Address5
Address6
Postcode

Lookup:
Country_Code
Country_Name

You can see my dilemma for any joins. I can imagine this is a commonly encountered problem where one wants to scan each address field for a country (or region) and make a match. It's doing my head in because I know there must be an efficient way to do it.

[Rabbit]

The country information should go into a country field in a first place. The only time you should need to do this is if you're cleaning up someone else's data.

You don't have to actually join on anything. You can do a cross join, meaning you join every record in the address table to every record in the country table.

Then you just filter out the ones that don't match.

[Gareth Campbell]

Rabbit,
That's exactly what I'm doing, cleaning up another dataset.

So if I don't specify a join is that automatically a join on everything?

[Rabbit]

I take that back, you can join on something.

SELECT tblAddresses.*, Nz(CountryName, "Unknown") AS CName
FROM tblAddresses AS A
LEFT JOIN tblCountries AS C ON
   A.Address1 = C.CountryID OR
   A.Address1 = C.CountryName OR
   A.Address2 = C.CountryID OR
   A.Address2 = C.CountryName OR
   et cetera

[Rabbit]

Not quite, if you don't sepcify a join condition, it joins on nothing. It returns every possible permutation; it's called a cartesian product.

[ADezii]

@Gareth:There is a purely Code based solution if you are having difficulty dealing with the SQL but it would be a little clumsier, requires no SQL, no Joins, but would not be nearly as efficient as Rabbit's approach I would imagine.

[Rabbit]

Or you could join on

(A.Address1 & A.Address2 & etc ) Like ("*" & C.CountryName & "*")
OR
(A.Address1 & A.Address2 & etc ) Like ("*" & C.CountryID & "*")

It's hard to say which would be more efficient without running test queries on large numbers of records.

[ADezii]

@Gareth: How large of a Data Set are you speaking of?

[NeoPa]

I would suggest a solution similar to Rabbit's, but without connecting on the ID field (I think that was probably a confusion that crept in), and I would also reintroduce your Trim() and UCase() calls into the mix as they can prove very helpful in such situations (They hit performance quite fundamentally, but do a more thorough job, which seems to me to be more important in this type of situation).

You could filter the data through subqueries that return the data fixed for extraneous spaces and case of course. That would make the main SQL easier to understand and work with.

[Gareth Campbell]

Hi Guys,

Thanks very much for your help. I think Rabbit's code has been successful! Much appreciated. I'm just running some checks to make sure it has picked up all matches.

Thanks

G

[ADezii]

@Gareth: Here is a purely Code-based solution, should you be interested.