Using Access Query in similar fashion of a vlookup function

[Scott]

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)))))))

 

[[MVP] S.Clark]

Typically, with normalized tables, you do not have problems like this.
Please feel free to list the table structures.

 

[HanSolo]

You could use "select" "case" in visual basic. See below links:

http://visualbasic.about.com/od/learnvbnet/a/blecvbsbs0704.htm
http://www.blueclaw-db.com/select_case.htm

 

[John Spencer]

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 )

 

[LeAnne]

PMFJI, but I read these posts for my own edumacation. John, I haven't
seen TRUE used in that way before. Spiffy!

LeAnne

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)))))))