I'm having issues with my query where I need to look at four columns to get my output cost in Completed FY YR Cost2 column. For the most part I can get it to work except I cannot get the FY 21-22 Costs. Also, the reason why I have to identify status "Completed" is because I also have costs with status "Active" which I do not want to show those costs in the completed cost column.
I know there's an order here, so I've rearranged the statements which made sense (to me) but then the cost drops elsewhere...so, now I'm stumped.
Query:
Completed FY YR Cost2: IIf([Status]="Completed" And [FY 22-23 Costs]>[FY 21-22 Costs],[FY 22-23 Costs],IIf([Status]="Completed" And [FY 20-21 Costs] Is Null,[FY 22-23 Costs],IIf([Status]="Completed" And [FY 21-22 Costs] Is Null,[FY 20-21 Costs],IIf([Status]="Completed" And [FY 22-23 Costs] Is Null,[FY 21-22 Costs]))))
Datasheet view?
FY 20-21 Costs FY 21-22 Costs FY 22-23 Costs Status Completed FY YR Cost2 $21,485.59 $25,435.66 Completed $25,435.66 $45,319.58 $53,498.53 Completed $53,498.53 $14,649.58 $16,804.15 $18,830.26 Completed $18,830.26 $17,860.05 $23,616.85 $26,571.20 Completed $26,571.20 $19,255.64 Completed $19,255.64 $11,828.62 Completed $11,828.62 $340,029.92 Completed $184,250.49 Completed $62,724.61 Completed $62,724.61 $68,997.07 Completed $68,997.07