Sum a field then minus

[aron.ridgway]

Im trying to sum a qty field naming it 'TotalRecieve' and then minus a qty from that sum?

i've tried the following

RemainingQty: Sum([tblReceiveDetail].[QTY])-[tblOrderDetail].[Qty]

but i get an error saying 'Your query does not include the specified expression as part of an aggregate function?

any pointers?

 

[pr2-eugin]

This method might be very slow, but you could try,

RemainingQty: DSum("QTY", "tblReceiveDetail")-[tblOrderDetail].[Qty]

 

[aron.ridgway]

i managed to sort it with the following, seems to be working ok!

RemainingQty: Avg([tblOrderDetail].[Qty])-Sum([tblReceiveDetail].[QTY])