eqivalent of sumif?

[byrne]

Hello,

I'm trying to do an access query which will sum 'Amount' based on 'Status'. I don't want to put them in separate columns, because there are three status options and I want a column for each one. eg column 1 - 'Amount' (sumif status = 1), column 2 - 'Amount' (sumif status =2), etc.

Thanks in advance for your assistance.

Best regards.

 

[Dugantrain]

Select the same field three times in the Query Design Grid. Select the Totals button on the Toolbar (the one that looks like the Sum button, the letter E, in Excel). In the Totals row, select Sum for all three fields. In the first column, [Yourtable.Status]=1 as criteria, 2nd field [Yourtable.status]=2, and 3rd column [Yourtable].[status]=3.

*Check that; don't make three columns. Make only one "Amount" column and put these criteria on three separate "Or" lines. Your recordset should then have three records.

 

[byrne]

Thanks for your reply. I have tried entering... [tblSavings]![StatusID] =3 but am getting an error when I try to run it.."You tried to execute a query that does not include the specified expression '[tblSavings]![StatusID] =3' as part of an aggregate function."

Any other suggestions?

 

[Dugantrain]

You have to have an Aggregate function in every field. That columns that you're not summing, you should have "Group By" in the Totals field. Is this the case?