IIF With Nested IIF?

[jo15765]

I need to check table AD and check if field CG is null, if it is I need to check if the AD.CI exists in my table SSCG.id, if it does I want to update AD.CG with the value in SSCG.dpt IF AD.CI does NOT exist in SSCG.id, then I want to update AD.CG with the value in SSC.alpha.

If I need to provide data structure/join conditions I can, but this may help



This is pseudocode of what I want to execute

if [AD].[CG] IS NULL 
if [AD].[CI] Exists in [SSCG].[id] then [SSCG].[dpt]
else [SSC].[alpha]

[Bob Fitz]

Perhaps it would be better to do the validation and any other necessary entries when the original record is created

[orange]

Just curious -- why do you have such cryptic names? Do you really enjoy coding with all the square brackets?

In addition to Bob's suggestion, I think it would be useful to readers, and better for your getting focused responses, if you could describe your "database and/or business opportunity" in plain English.

Good luck.

[jo15765]

Perhaps it would be better to do the validation and any other necessary entries when the original record is created

Unfortunately, fixing it on the front end is not an option. We received 3 Excel files and are attempting to merge into one database table and that is the business rule that needs to be applied.

[jo15765]

Just curious -- why do you have such cryptic names? Do you really enjoy coding with all the square brackets?

In addition to Bob's suggestion, I think it would be useful to readers, and better for your getting focused responses, if you could describe your "database and/or business opportunity" in plain English.

Good luck.

It may look cryptic but those are actually abbreviations for internal metrics/tables that we use. For example (again may not make sense to someone else) this is what the tables/field names actually equate to, and based off internal business rules is how I was trying to set up the iif statement

AD = Alpha Domingo
CG = Consumer Grid
SSCG = Steve Sacos Consumer Grid 
dpt = data point
SSC = Sara Smiths Consumer
Alpha = Alpha

[orange]

Interesting. Show a jpg of your tables and relationships-- make sure tables are expanded so we can see all fields.

Yes, they are cryptic-- much like the old Fortran days---you know x, x1, x2 and y....

As for

We received 3 Excel files and are attempting to merge into one database table and that is the business rule that needs to be applied.

the typical approach sis to bring the excel data into a temporary table, then using queries or code, pick out the fields and combinations and update your "final table". Excel files/sheets are not normalized generally,but hopefully your database is.
That my be the essence of your problems, but we need some description that makes sense -outside your internal metrics/tables.