Count and crieria on Report question

[AndycompanyZ]

I have a rather complex report to build for my db. The question I have is can I do a count based on certain criteria. What i have is a report based on a query that shows all the delegates that have been scheduled onto events. Each delegate has a status concerning their attendance or non attendance for the course. I have grouped the report based on date first by month and then grouped on coursename (each event belongs to a coursename). This is fine but now I need to count all the delegates who have a status of either 4 or 5 and also those with a status of 6 or 7 for each event and show both these separately at the end of the report (or maybe for each month but the solution will do for each I think). Then I need to count all the delegates who have a status of the above for all the courses and finally the same but by their department (each delegate belongs to a department). I have tried various count statements in control sources including =Count([Status]=6 or 7) and Sum([Status=6 or 7]) but they both add all up all the records whatever their status. Is this possible to do it this way or will I have to do something else (I'm a newcomer to reports so it's all a bit alien)



Thanks to anyone who can help or point me to where to look

[AndycompanyZ]

I've nearly got it I have =Count(IIf([Status]=4 And 5,0)) but this only counts the records with a status of 4. I've tried OR and + and "4" and "5" and other variations but to no avail

[rpeare]

=Sum(IIf([Status]=4 or [status]= 5,1,0))

[AndycompanyZ]

Thanks that worked perfectly. I may have another problem in that I need now to get a grand total of the subtotals at the bottom of the report but I'm looking at it and trying to work it out myself for the moment.

[rpeare]

put in a report footer section, you don't need to total the subtotals, just total the field that goes into that subtotal.

[AndycompanyZ]

Thanks the totals I need are based on different criteria but I'm working on it

[AndycompanyZ]

I'm still having issues with this report. basically what I have is my report grouped by date first and then grouped on Course. At the footer of each course I have a subtotal of those delegates who have attended and a separate subtotal of those who have cancelled. This works correctly but I need to have a total of all those who have attended and cancelled at the bottom of the report but sorted by Course. If I put a total based on these subtotals I get a running total of all the delegates but is there an easy way of getting the totals for each course. I did this before by sorting by department by putting a textbox for each department (there were only 11) and setting the control source as =Sum(IIf([DepartmentID]=1 And [Status]="Attended",1,0)) but is this the only way of getting the course as well even though I have subtotalled already (there will be 25 courses).

Also when I have tried exporting this report to an excel spreadsheet (which needs to be done so the data can be manipulated) it only copies the data and not the subtotals or totals is this not possible.

[rpeare]

The best thing to do in this case is to make a subreport that shows only the aggregate data you want. Otherwise you are going to have to write a bunch of formulas for every possible class.