sumif for same field range & criteria

[sunitarobert]

Hi All,

Need your expertee for below query..

I have query which is fetching data from SAP Table and has expression Inv_No: IIf([Type]<>"KY",[Reference],[Assign]). It means if the KY Document is it should take the Invoice Reference from Assign Field else from Reference field. which is working fine..



Now I have another requirement i.e., sum if based on expression "Inv_No: IIf([Type]<>"KY",[Reference],[Assign])" In field 'Amount in DC' have negative & positive and I want have sumif in same query.


eg. in Query field Invoice No. Amount in DC "in new field"
1234 5000 0
1234 -5000 0

In excel it is pretty easy, but I want to do this in access because of huge data.. sample file attached.. kindly help

[CJ_London]

it sounds like you need a groupby query and

summed:sum(IIf([Type]<>"KY",[amount in dc],0)

but not clear if this is really what you mean.

you need to clarify what you mean by 'want have sumif in same query.'

suggest provide some example data and the result you want to achieve

[CJ_London]

ah - think I've worked out what you mean

select inv_no, [amount in dc], (select sum([amount in dc]) from sap as s where inv_no=sap.inv_no) as newfield
from sap

[sunitarobert]

Thank you so much.. it works superb.. but Inv_no is not field in query. It is expression "Inv_No: IIf([Type]<>"KY",[Reference],[Assign])" fetching data from SAP table. Above solution works if I create an another query. Is it possible to consider IIf([Type]<>"KY",[Reference],[Assign]) in same query instead of Inv_No.

[sunitarobert]

Hi Ajax,

Found Answer.. thanks a lot for your help.. Contra: (select sum([amount in dc]) from sap as s where IIf([Type]<>"KY",[Reference],[Assign])=IIf(sap.[Type]<>"KY",sap.[Reference],sap.[Assign]))s where IIf([Type]<>"KY",[Reference],[Assign])=IIf(sap.[Type]<>"KY",sap.[Reference],sap.[Assign]))

Rgds,