Using Access Query in similar fashion of a vlookup function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have tried to used the IIF then statement to do this but too many nests
have forced me to rethink. Is there another function I should look at for
this problem?

I have two tables. The first table dictates which field to grab from the
other table.

The gist of it:

If Waterfall![Allocate Y or N] = "N" then 0 else
If Waterfall![Alloc Metric] = "PIF" then [YTD Metrics at Subchannel
Level]!PIF else
If Waterfall![Alloc Metric] = "EP" then [YTD Metrics at Subchannel
Level]!EP else
If Waterfall![Alloc Metric] = "NB" then [YTD Metrics at Subchannel
Level]!NB else
If Waterfall![Alloc Metric] = "ASO" then [YTD Metrics at Subchannel
Level]!ASO else [YTD Metrics at Subchannel Level]!PIF


This is what I have built:
Subchannel Metric: Sum(IIf(IIf(Waterfall![Allocate Y or
N]="N",0,IIf(Waterfall![Alloc Metric Subchannel] Like "PI*",[YTD Metrics at
Subchannel Level]!SumOfPIF,IIf(Waterfall![Alloc Metric Subchannel] Like
"N*",[YTD Metrics at Subchannel Level]!SumOfNB,IIf(Waterfall![Alloc Metric
Subchannel] Like "E*",[YTD Metrics at Subchannel
Level]!SumOfEP,IIf(Waterfall![Alloc Metric Subchannel] Like "AS*",[YTD
Metrics at Subchannel Level]!SumOfASO,[Metrics Outfile]!PIF))))) Is
Null,0,IIf(Waterfall![Allocate Y or N]="N",0,IIf(Waterfall![Alloc Metric
Subchannel] Like "PI*",[YTD Metrics at Subchannel
Level]!SumOfPIF,IIf(Waterfall![Alloc Metric Subchannel] Like "N*",[YTD
Metrics at Subchannel Level]!SumOfNB,IIf(Waterfall![Alloc Metric Subchannel]
Like "E*",[YTD Metrics at Subchannel Level]!SumOfEP,IIf(Waterfall![Alloc
Metric Subchannel] Like "AS*",[YTD Metrics at Subchannel Level]!SumOfASO,[YTD
Metrics at Subchannel Level]!SumOfPIF)))))))
 
Typically, with normalized tables, you do not have problems like this.
Please feel free to list the table structures.
 
If you have to do this, then you might consider using the SWITCH function
instead of the nested IIF
SWITCH(Waterfall.[Allocate Y or N] = "N",0,
Waterfall.[Alloc Metric] = "PIF",[YTD Metrics at Subchannel Level].PIF,
Waterfall.[Alloc Metric] = "EP", [YTD Metrics at Subchannel Level].EP,
Waterfall.[Alloc Metric] = "NB",[YTD Metrics at Subchannel Level].NB,
Waterfall.[Alloc Metric] = "ASO", [YTD Metrics at Subchannel Level].ASO,
TRUE, [YTD Metrics at Subchannel Level].PIF )
 
PMFJI, but I read these posts for my own edumacation. John, I haven't
seen TRUE used in that way before. Spiffy!

LeAnne

John said:
If you have to do this, then you might consider using the SWITCH function
instead of the nested IIF
SWITCH(Waterfall.[Allocate Y or N] = "N",0,
Waterfall.[Alloc Metric] = "PIF",[YTD Metrics at Subchannel Level].PIF,
Waterfall.[Alloc Metric] = "EP", [YTD Metrics at Subchannel Level].EP,
Waterfall.[Alloc Metric] = "NB",[YTD Metrics at Subchannel Level].NB,
Waterfall.[Alloc Metric] = "ASO", [YTD Metrics at Subchannel Level].ASO,
TRUE, [YTD Metrics at Subchannel Level].PIF )

I have tried to used the IIF then statement to do this but too many nests
have forced me to rethink. Is there another function I should look at for
this problem?

I have two tables. The first table dictates which field to grab from the
other table.

The gist of it:

If Waterfall![Allocate Y or N] = "N" then 0 else
If Waterfall![Alloc Metric] = "PIF" then [YTD Metrics at Subchannel
Level]!PIF else
If Waterfall![Alloc Metric] = "EP" then [YTD Metrics at Subchannel
Level]!EP else
If Waterfall![Alloc Metric] = "NB" then [YTD Metrics at Subchannel
Level]!NB else
If Waterfall![Alloc Metric] = "ASO" then [YTD Metrics at Subchannel
Level]!ASO else [YTD Metrics at Subchannel Level]!PIF


This is what I have built:
Subchannel Metric: Sum(IIf(IIf(Waterfall![Allocate Y or
N]="N",0,IIf(Waterfall![Alloc Metric Subchannel] Like "PI*",[YTD Metrics
at
Subchannel Level]!SumOfPIF,IIf(Waterfall![Alloc Metric Subchannel] Like
"N*",[YTD Metrics at Subchannel Level]!SumOfNB,IIf(Waterfall![Alloc Metric
Subchannel] Like "E*",[YTD Metrics at Subchannel
Level]!SumOfEP,IIf(Waterfall![Alloc Metric Subchannel] Like "AS*",[YTD
Metrics at Subchannel Level]!SumOfASO,[Metrics Outfile]!PIF))))) Is
Null,0,IIf(Waterfall![Allocate Y or N]="N",0,IIf(Waterfall![Alloc Metric
Subchannel] Like "PI*",[YTD Metrics at Subchannel
Level]!SumOfPIF,IIf(Waterfall![Alloc Metric Subchannel] Like "N*",[YTD
Metrics at Subchannel Level]!SumOfNB,IIf(Waterfall![Alloc Metric
Subchannel]
Like "E*",[YTD Metrics at Subchannel Level]!SumOfEP,IIf(Waterfall![Alloc
Metric Subchannel] Like "AS*",[YTD Metrics at Subchannel
Level]!SumOfASO,[YTD
Metrics at Subchannel Level]!SumOfPIF)))))))
Click to expand...
Click to expand...
 
Back
Top