Using Iif to Select Alternate Fields

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

Guest

I have a table and a query that are inner joined on an officer# field. The
query is named "ALL", and the table is named "Rollup Calculation". If an
officer number appearing in ALL does not appear in Rollup Calculation, then I
want to use the value from the Deposits_Outstanding field of ALL. If an
officer number in ALL appears in Rollup Calculation, then I want to use the
value from the field called "DepositTransfersAddedBack" in the query
"rollupDepositTransfers". My SQL is below, and it does not work. It prompts
me for a parameter on DepositTransfersAddedBack.

My question is, how can I select either one field or another from two
different result sets, based on the value of a field in one of the result
sets?

Thanks,
GwenH

SELECT Sum([ALL].Loan_Portfolio_Outstanding) AS Loan_Portfolio_Outstanding,
Sum(IIf([DepositTransfersAddedBack]>0,[SELECT DepositTransfersAddedBack FROM
rollupDepositTransfers],[SELECT Deposits_Outstanding FROM ALL])) AS [Deposits
Outstanding]
FROM [Rollup Calculation] INNER JOIN [ALL] ON [Rollup
Calculation].[EmpOfficer#] = [ALL].[Officer#]
GROUP BY [Rollup Calculation].[SupvOfficer#]
HAVING ((([Rollup Calculation].[SupvOfficer#])=107));
 
Hi,


If TableA.FieldA is not in TableB.FieldB,
display TableA.FieldC will be use,
else,
TableB.FieldD will be use:



SELECT Nz(TableB.FieldD, TableA.FieldC)
FROM TableA LEFT JOIN TableB
ON TableA.FieldA =TableB.FieldB




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top