IIf function

[seb]

How do I do to use the IIf function and calculate the totals in rows and columns in this query?

SELECT [tbl_EAH_Sig].SigID, [bl_EAH_Sig].AmaID, [tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value] AS Expr1


FROM tbl_Hat INNER JOIN tbl_EAH_Sig ON [tbl_Hat].AmaID = [tbl_EAH_Sig].AmaID
GROUP BY [tbl_EAH_Sig].SigID, [tbl_EAH_Sig].AmaID, [tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value];

Many thanks

[weekend00]

what do you want to do with IIF?

you didn't tell us what do you want, so I just guess you want a sum of [tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value], in this case, the query should be:

SELECT [tbl_EAH_Sig].SigID, [bl_EAH_Sig].AmaID, sum([tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value]) AS Expr1
FROM tbl_Hat INNER JOIN tbl_EAH_Sig ON [tbl_Hat].AmaID = [tbl_EAH_Sig].AmaID
GROUP BY [tbl_EAH_Sig].SigID, [tbl_EAH_Sig].AmaID

[seb]

what do you want to do with IIF?

you didn't tell us what do you want, so I just guess you want a sum of [tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value], in this case, the query should be:

SELECT [tbl_EAH_Sig].SigID, [bl_EAH_Sig].AmaID, sum([tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value]) AS Expr1
FROM tbl_Hat INNER JOIN tbl_EAH_Sig ON [tbl_Hat].AmaID = [tbl_EAH_Sig].AmaID
GROUP BY [tbl_EAH_Sig].SigID, [tbl_EAH_Sig].AmaID

Many thanks
I'm new to sql. I'm sorry I wrote a mistake in my SQL code

Yes I need the IFF with Sum. My idea is to have a row title as a column title
II already fixed my mistake like this
SELECT tbl_EAH_Sig.SigID, [tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value] AS Expr1
FROM tbl_Hat INNER JOIN tbl_EAH_Sig ON tbl_Hat.AnimalID = tbl_EAH_Sig.AmalID
GROUP BY tbl_EAH_Sig.SigID, [tbl_EAH_Sig]![Anils]*[tbl_Hat]![Value];




I think I have to write something in the SQL code like



Sum((IIf([AmalID]="02",[Anils],0))) AS Expr1
Sum((IIf[Hat]="01",[Value],0)))


But I have been breaking my head with no success.
Could you give an idea how to do it?

[weekend00]

if you meant you want the sum of Anils * Value for those having [aMalID]='02' and [hat]='01'? you don't need IIF, just use where clause:

SELECT tbl_EAH_Sig.SigID, Sum([Anils] * [Value]) AS Expr1
FROM tbl_Hat INNER JOIN tbl_EAH_Sig ON tbl_Hat.AnimalID = tbl_EAH_Sig.AmalID
where [aMalID]='02' and [hat]='01'
GROUP BY tbl_EAH_Sig.SigID

[seb]

if you meant you want the sum of Anils * Value for those having [aMalID]='02' and [hat]='01'? you don't need IIF, just use where clause:

SELECT tbl_EAH_Sig.SigID, Sum([Anils] * [Value]) AS Expr1
FROM tbl_Hat INNER JOIN tbl_EAH_Sig ON tbl_Hat.AnimalID = tbl_EAH_Sig.AmalID
where [aMalID]='02' and [hat]='01'
GROUP BY tbl_EAH_Sig.SigID

Many thanks
With your idea I found what I need as first step with the following code:
SELECT tbl_EAH_Sig.SigID, Sum(IIf([AmalID]="01",[Anils],0))*1 AS VaPa, Sum(IIf([AmalID]="02",[Anils],0))*1 AS VaSe, Sum(IIf([AmalID]="07",[Anils],0))*0.25 AS Teras, Sum(IIf([AmalID]="08",[Anils],0))*0.25 AS Teros, [VaPa]+[VaSe]+[Teras]+[Teros]+ AS [Total UA]
FROM tbl_Hat INNER JOIN tbl_EAH_Sig ON tbl_Hat.AmalID = tbl_EAH_Sig.AmalID
GROUP BY tbl_EAH_Sig.SigID;

As in my previous message I would like to calculate in nthis query or in another the totals for each column (VaPa, VaSe, Teras, Teros). Could you suggest how to do it?
Thanks againg