Query - Sumiif

[Jishnu Surendran]

I have a query with the following columns:



ID l Particulars l Category l Group l Amount

I want another column where I can do a sumiif. I put the formula Sum(iif(Category=& Category,Amount) where it sums the amounts of all categories if it is same as the category in the present row. However, access aks me to group the data for the performing the aggregate function and when i did so sumiif returns the same value as in the amount column for the corresponding row rather than the sum. What did I miss? Is there is anything wrong with the formula? Do I have to try Sum(Amount,Category=&Category)?

[isladogs]

Try using DSum

DSum("Amount", "YourTableName", "Category = '" & [Category] &"'")

Substitute with correct names for your own database

Note that using domain functions in a query may be slow

[Jishnu Surendran]

I know Dsum. I want to use Sumiff as Dsum runs slow.

[isladogs]

There is no Access equivalent of the Excel function SUMIF

IIf statements need a true part and a false part (which is missing in your post)
Possibly something like this

[CODE](IIf([Category] = some value, Sum([Amount], 0) /CODE]

However, you might be better constructing an aggregate query where you sum the amount field & filter the category field

[Jishnu Surendran]

There is no Access equivalent of the Excel function SUMIF

IIf statements need a true part and a false part (which is missing in your post)
Possibly something like this

[CODE](IIf([Category] = some value, Sum([Amount], 0) /CODE]

However, you might be better constructing an aggregate query where you sum the amount field & filter the category field

As I've told you, this returns the amount in 'Amount' column against each item in 'Particulars' rather than summing up based on 'Category'.

[Jishnu Surendran]

Just to clarify, 'Particulars' is a specific item; 'Category' is the item's group; 'Group' is a subgroup of the 'Category'. 'GROUP BY' clause doesnt work in here I guess.

[isladogs]

As I've told you, this returns the amount in 'Amount' column against each item in 'Particulars' rather than summing up based on 'Category'.

The code I suggested was different to that you gave in post 1
The clarification in your last post actually made this less clear.
Suggest you provide more details such as query SQL as at the moment I'm making best attempt guesses on minimal information

[kd2017]

Maybe this?

 Sum(iif([Category]='Particulars', [Amount] , 0))

[Jishnu Surendran]

SELECT [Test-TB].ID, [Test-TB].Particulars, [Test-TB].CYDr, [Test-TB].CYCr, [Test-TB].PYDr, [Test-TB].PYCr, [Test-TB].Category, [Test-TB].Group, [Test-TB].CatNum, [Test-TB].GroupNum, [Test-TB].Misc, IIf(Left([CatNum],1)=1,nz([CYDr])-nz([CYCr]),IIf(Left([CatNum],1)=4,nz([CYDr])-nz([CYCr]),nz([CYCr])-nz([CYDr]))) AS [CY(Net)], IIf(Left([CatNum],1)=1,nz([PYDr])-nz([PYCr]),IIf(Left([CatNum],1)=4,nz([PYDr])-nz([PYCr]),nz([PYCr])-nz([PYDr]))) AS [PY(Net)], Format(DSum("[CY(Net)]","[Test-TB Query]","[CatNum]=" & [CatNum]),"Standard") AS CatSumCY, DSum("[PY(Net)]","[Test-TB Query]","[CatNum]=" & [CatNum]) AS CatSumPY, DSum("[CY(Net)]","[Test-TB Query]","[GroupNum]=" & [GroupNum]) AS GrpSumCY, DSum("[PY(Net)]","[Test-TB Query]","[GroupNum]=" & [GroupNum]) AS GrpSumPY
FROM [Test-TB]
GROUP BY [Test-TB].ID, [Test-TB].Particulars, [Test-TB].CYDr, [Test-TB].CYCr, [Test-TB].PYDr, [Test-TB].PYCr, [Test-TB].Category, [Test-TB].Group, [Test-TB].CatNum, [Test-TB].GroupNum, [Test-TB].Misc, IIf(Left([CatNum],1)=1,nz([CYDr])-nz([CYCr]),IIf(Left([CatNum],1)=4,nz([CYDr])-nz([CYCr]),nz([CYCr])-nz([CYDr]))), IIf(Left([CatNum],1)=1,nz([PYDr])-nz([PYCr]),IIf(Left([CatNum],1)=4,nz([PYDr])-nz([PYCr]),nz([PYCr])-nz([PYDr])));

[isladogs]

Thank you for posting the SQL.
However, I'm not a mind reader and you've provided no explanation of which part to look at.
I've looked at the SQL several times and have no idea which query field relates to this issue.

In post 6 you stated that Particulars is a specific item. That suggested a field value as kd2017 surmised.
Now it appears its a field name as are Category and Group.

For info, Group is a reserved word in Access and should not be used as a field name

Without a clear explanation from you, I have no idea what to suggest.
Perhaps you should upload the relevant sections of your database and explain clearly what to look at as well as expected outcomes

[Jishnu Surendran]

Particulars	CYDr	CYCr	PYDr	PYCr	Category	Group	CatNum	GroupNum
Building	20,814,225.00		19,708,241.00		Property and equipments	Building	10100	10101
Acm. Dep. - Building		9,241,348.00		8,768,802.00	Property and equipments	Building	10100	10101
Machinery - Automatic	64,503,093.00		68,514,342.00		Property and equipments	Machinery	10100	10102
Acm. Dep. - Vessels		3,830,905.00		4,148,159.00	Property and equipments	Machinery	10100	10102
Equipment & Tools	26,466,233.00		26,426,879.00		Property and equipments	Office equipments	10100	10104
Acm. Dep. - Equipment & Tools		4,035,056.00		3,576,825.00	Property and equipments	Office equipments	10100	10104
Furniture and fittings	506,792.00		506,792.00		Property and equipments	Furniture and fittings	10100	10103
Acm. Dep. - Furniture and fittings		280,794.00		248,919.00	Property and equipments	Furniture and fittings	10100	10103
Motor Vehicles	11,070,500.00		11,245,000.00		Property and equipments	Motor vehicles	10100	10199
Acm. Dep. - Motor vehicles		2,355,077.00		2,191,252.00	Property and equipments	Motor vehicles	10100	10199
Investment in Insurance	250,000.00		500,000.00		Investments	Investment in insurance	10200	10201
Investment in Properties	750,000.00		250,000.00		Investments	Investment properties	10200	10202
Investment in Shares	500,000.00		250,000.00		Investments	Investment in shares	10200	10203

First of all, thanks for spending your time on this! If you see above, it is an image of my query (extract). Let me tell you what this is and what I am trying to achieve. The above are year end balances of a company's accounts. I want to make the following reports in the given formats:
1.
[CATEGORY]
[GROUP]
[PARTICULARS] [CY(Net)] [PY(Net)]

I've made this and it is Ok! I am having the problem with the next two:
2.
[CATEGORY]
[GROUP] [CY(Net)] [PY(Net)]------ these two should be the sum of specific groups.

3.
[CATEGORY] [CY(Net)] [PY(Net)]------these two should be the sum of specific categories.

I can achieve all of the above if I use:
1. DSum in query but this can make the query slow
2. Sum in reports but this is recalculating every time I scroll and takes some time to appear (getting on my nerves)!

[Jishnu Surendran]

Particulars	CYDr	CYCr	PYDr	PYCr	Category	Group	CatNum	GroupNum
Building	20,814,225.00		19,708,241.00		Property and equipments	Building	10100	10101
Acm. Dep. - Building		9,241,348.00		8,768,802.00	Property and equipments	Building	10100	10101
Machinery - Automatic	64,503,093.00		68,514,342.00		Property and equipments	Machinery	10100	10102
Acm. Dep. - Vessels		3,830,905.00		4,148,159.00	Property and equipments	Machinery	10100	10102
Equipment & Tools	26,466,233.00		26,426,879.00		Property and equipments	Office equipments	10100	10104
Acm. Dep. - Equipment & Tools		4,035,056.00		3,576,825.00	Property and equipments	Office equipments	10100	10104
Furniture and fittings	506,792.00		506,792.00		Property and equipments	Furniture and fittings	10100	10103
Acm. Dep. - Furniture and fittings		280,794.00		248,919.00	Property and equipments	Furniture and fittings	10100	10103
Motor Vehicles	11,070,500.00		11,245,000.00		Property and equipments	Motor vehicles	10100	10199
Acm. Dep. - Motor vehicles		2,355,077.00		2,191,252.00	Property and equipments	Motor vehicles	10100	10199
Investment in Insurance	250,000.00		500,000.00		Investments	Investment in insurance	10200	10201
Investment in Properties	750,000.00		250,000.00		Investments	Investment properties	10200	10202
Investment in Shares	500,000.00		250,000.00		Investments	Investment in shares	10200	10203

First of all, thanks for spending your time on this! If you see above, it is an image of my query (extract). Let me tell you what this is and what I am trying to achieve. The above are year end balances of a company's accounts. I want to make the following reports in the given formats:
1.
[CATEGORY]
[GROUP]
[PARTICULARS] [CY(Net)] [PY(Net)]

I've made this and it is Ok! I am having the problem with the next two:
2.
[CATEGORY]
[GROUP] [CY(Net)] [PY(Net)]------ these two should be the sum of specific groups.

3.
[CATEGORY] [CY(Net)] [PY(Net)]------these two should be the sum of specific categories.

I can achieve all of the above if I use:
1. DSum in query but this can make the query slow
2. Sum in reports but this is recalculating every time I scroll and takes some time to appear (getting on my nerves)!

[isladogs]

Ok I'm finally getting a picture of this!
I think it may be easier to do this in two step queries but it would be quicker to test my thoughts on the real thing
Can you upload your database ... or a cut down version of it with the relevant table(s), queries ... and you've now mentioned reports....
No forms?
Change anything confidential before posting

[Jishnu Surendran]

Two queries? I can't set two queries as record source for my report. I don't know where to attach this.

[Jishnu Surendran]

Can you tell me why if I use calculated fields (here Sum) in reports it takes some time to appear when I scroll reports in report view?