SumIf with multiple crititeria

[Prufrock]

HI,
I have a report which operates off a query. I have used a number of sumif statements in the report to provide totals for enrolments on particular programs for a given month.

All works fine. Now they have combined 2 programs into one for reporting only. How can I combine them in my statement to ensure it totals the results for both?

Example of my statement:
=Sum(IIf([ProgramTitle]="Drug Education",[StudentsCommencing],0))

What if I need to include Car Maintenance and Drug Education?

Thanks

 

[GingerR]

I'm assuming you have a normalized db with a table which has ProgramTitle as a field name, and Program Titles listed in it. This is not the final thing you want (I'd need more info) but combine two to be reported as one, then sum them:

iif([ProgramTitle] = "Drug Education" or ProgramTitle = "Car Maintenance","New Title",[ProgramTitle])

Another thing you could do is if this kind of thing may happen again in the future, make a new table, called i.e. ProgramReportTitles, with fields "ProgramTitle" and "ReportTitle".

ProgramTitle ReportTitle
Making Bread Making Bread
Car Maintenance Teenage Program
Drug Education Teenage Program
Knitting Dresses Knitting Dresses

then join this in queries. An option would be to only put items in this table which have a different 'Report' title than their original program title, that way if another program is added to the db, you/someone doesn't have to remember to add it to THIS table as well (or, you could really just add code to do that for you Anyhow, this way your db is more easily maintainable, and doesn't require further hardcoding by you in the future. You won't have to go into every query/form/report and make adjustments. You could even display it on a Maintenance Screen for certain users to keep up, then you're off the hook completely.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Try
=Sum(IIf([ProgramTitle]="Drug Education" OR [ProgramTitle]="Car Maintenance", [StudentsCommencing],0))

I'm not sure where GingerR was heading in the first part of her response but the second recommendation is spot on! You should be maintaining data/records and not expressions. What happens if when "they" come back at you and want to regroup titles.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]