Count and Sum in Report

[Brian62]

I am trying to show a count of Regulatory items and a sum for Consent.
In the control source I have wrote:



IIF(Count([Total Subjects])=Regulatory Or IIF(Sum([Total Subjects])=Consent
Of course this isn't working.

When the report is selected I have a criteria (Like "*" & [Enter: Consent or Regulatory] & "*") through my Query that I use to select either Consent or Regulatory that only shows one of them with a count. The problem is I don't want to sum the Regulatory but need a count and a sum for Consent. Regualtory almost always give me a 0. Consents give me the amount of were audited to the reason why I need to show sum.

[jbarrum]

I think you have to many things going on. You mentioned that you need a COUNT of one field and a SUM of another. However, in your expression you are using and IIF clause. What are tyring to accomplish with the IIF clause beacuse it appears that you have only provided the condition part of the epression and not what needs to be done IIF that condition exists.

[Brian62]

I used the IIF statement because I thought that would work but it may not be the right condition to use. As I mentioned I want to count the keyword "Regulatory" and Sum "Consent". I only need to count Regulatory because there will always be 1 inspection while consents can be much more than one. Sometimes both will done at the same time that's why we don't count Regulatory. That's why regulatory will always be zero so that's why it I have to have two separate conditions. I don't want to make two separate reports unless I have to. Trying to keep it as one. Thx!

[jbarrum]

to begin, you do not have a one to one relationship with the two fields that you want conditions on then you will need to create two seperatre queries. If this will still help you accomplish what you want then after you build the query that contains all records for "Regulatory" while still in edit mode change you query to a totals query. You can do this by clickin on "view" from the tool menu. then select "Total" from the drop down menue. This will add a row to your query called "total" which will have a drop down. This will allow you to choose conditions on fields such as "sum" and "count".

Another way to accomplish this would be to use an unbound control box on either your form or report that contains the expression =Sum(FieldName] or =count[FieldName]