Grouped weighted average

[mas0212]

Hello,

I'm trying to do a grouped weighted average query and am not having much luck.

I have the following sub-query defined as [Mike - Total Floating Balance by Deal Number]:

[tt] SELECT Order.[Deal Number],
Sum(IIf(Order.[Fixed/Float]="Fixed",0,Order.[QTY MM]*Issue.[Factor])) AS [Total Floating Balance]
FROM Issue
INNER JOIN [Order]
ON Issue.Cusip=Order.OCusip
GROUP BY Order.[Deal Number]
HAVING Order.[Deal Number]<>"";
[/tt]

Then I have this query that attempts to use the subquery above:

[tt] SELECT O.[Deal Number],
Sum(O.[QTY MM]) AS [Notional Balance],
Sum(O.[QTY MM]*I.[Factor]) AS [Current Balance],
Sum(IIf(O.[Fixed/Float]="Floating",O.[QTY MM]*I.[Factor],0)) AS [Floating Balance],
Sum(IIf([Floating Balance]=0,0,IIf(I.[Fixed/Float]="Floating",O.[QTY MM]*I.[Factor],0)*O.[Spread/DM])/T.[Total Floating Balance]) AS [Current DM]
FROM Issue AS I
INNER JOIN (
[Order] AS O
INNER JOIN [Mike - Total Floating Balance by Deal Number] AS T
ON O.[Deal Number] = T.[Deal Number])
ON I.Cusip = O.OCusip
GROUP BY O.[Deal Number],
T.[Total Floating Balance];
[/tt]

This query gives me the following error:

[tt]Subqueries cannot be used in the expression: (IIf([Floating Balance]=0,0,IIf([Fixed/Float]="Floating",[QTY MM]*[Factor],0)*[Spread/DM])/T.[Total Floating Balance])[/tt]

Any suggestions as to where I'm going wrong, or whether this can actually be done?

Thanks very much.

 

[mas0212]

FYI this is on Access 2003.